Machinery Maintenance Forecasting Tool with MS Excel

Not a normal request, but a number of clients wanted to use our financial modelling logic to set up a Machinery Maintenance Forecasting Tool with MS Excel. The intention was to predict the maintenance cycles on individual components within heavy movable and immovable machinery. The aim was, firstly, to better budget for maintenance but, perhaps more importantly, to also facilitate when to start monitoring particular parts, and to better utilise maintenance shutdowns to replace near to ‘end life’ parts.

Machinery Maintenance Forecasting Tool

Although not a typical financial model with Income Statement, Cash Flow and Balance Sheet, the logic of the ‘Machinery Maintenance Forecasting Tool’ was similar i.e. take each component within a machine and:

  • estimate its useful life in either months or tons,
  • forecast the production profile in tons per month,
  • forecast when the part would be getting close to a required change and create a warning report,
  • forecast the change in components occurring based on expected life, estimate the cost and run it through the model for the rest of the machines life,
  • Graphically show the next 52 weeks worth of upcoming maintenance both in terms of components and likely spend.

Machinery Maintenance Forecasting Tool

Out of this model came some tracking capabilities. It was discovered that the estimated and actual lives were not always the same so the updated models needed to:

  • Take actual changes into account and revise the forecast based on a change that was earlier or later than expected, and
  • Track the historical changes made to see if the estimated lives of each component are reasonable.

Although not a typical financial model, the structure and formulas were very much like a financial model. And for later versions we started combining the actual results with the original budgets to help explain the differences between anticipated and actual spend.

For more information contact adrian@AuditExcel.co.za .