A 'PM Non-Compliance' And 'Break-in Maintenance Effects Cost' Dashboard Leveraging 'Slicers' In MS Excel

Here is an example of a two-pager 'Equipment And/Or Equipment Component PM Non-Compliance and Break-in Jobs Effects Dashboard' using MS Excel 'Slicers'.

To improve efficiency in decision making, this calculator is conjured up as a handy tool for doing 'Work Order History Analysis' and especially to identify areas that you want to focus on. This may also include replacement strategy where you want to have different determining categories and whichever equipment is ranked in the top, for example Top 5, across each of the categories can then be considered for replacement or earns the right for us to focus our improvement efforts on it.

The modelling is done in MS Excel from work orders downloaded from our CMMS (computerised maintenance management system) called SAP. While SAP is powerful, there are some visibilities you want that you can customised to suit by leveraging MS Excel so you can measure and quantify the gaps you want to assess. I appreciate comments on trying out Power BI, but for me, what I can't do in MS Excel, I will probably explore Power BI to leverage those capabilities.

As they say, we cannot improve what we cannot measure. Maintenance needs a report to capture its work management KPIs. These work management KPIs need not be too many but just sufficient to clarify and identify what needs to be done to improve RAMS (reliablity, availability, maintainability and safety).

This workbook intends to report on the work management KPIs for the functional location 2 which is 'Open Cut Mining'.?The functional location is 'MIN.10*'. All data from this workbook is to come from our CMMS which is SAP. Data from SAP modules IW38 and IW29 are imported into this report template to generate the maintenance work management KPIs report.

I have used MS Excel 'Slicers' to create the dashboard shown in the tab 'Dashboard_PM Non-Compliance'. The whole workbook has 'Inputs & Assumptions', 'Executive Summary' then drilldown to 'Equipment No.' and 'Equipment Component Code'. There is more drilldown one-pager dashboards, but these are all hidden for this demo. All of these one-pager dashboards are influence by one point of data entry only which is at the 'Inputs & Assumptions' tab. See link immediately hereunder for more on these other one-pager reports.

For this 'PM Non-Compliance Dashboard' I am using slicers so the user can immediately see the simulations as you select the various slicer fields. For this dashboard I have the PM Compliance by Equipment and/or Component sort from the least compliant to the most compliant and on the secondary y-axis I have a Pareto 80/20 scale. For Pareto Line I have two trend lines of 'Log' and 'Exponential'. Whichever Pareto Line has the highest 'R-Squared' value should be used or referenced.

On the dashboard and below the 'green bar' graph of PM Non-Compliance are the Break-in Jobs Effects. These effects are: High Frequenters Vs Show Stoppers; High Frequenters Vs Direct Cost; Show Stoppers Vs Average Direct Cost; High Frequenters Pareto; Show Stoppers Pareto; and Direct Cost Pareto. This is so that in one view you can see a cross-sectional view of the performance across different criteria and identify your most problematic plant equipment by qualifying base on these criteria so you can direct your focus on the problematic equipment or component.

From the above graphs, which equipment do you think earns the right for us to focus our improvement efforts on it (refer table below for some guidance)?

For reliability/maintenance engineers doing routine 'Plant Work Order History Review', once you automate this task like this, it will make your job much easier and quicker.

Postscript

Some of the things to consider when designing a KPIs report.

The Report Concept

The Report Concept-What To Measure

The Report Concept-How To Measure