If you are an engineer or scientist and you make use of spreadsheets (who doesn’t) then it is useful to know what your shortcomings are and how to address them. In the article ‘The Do’s and Don’ts Of Engineering Spreadsheets’, the author Maxim Millen, addresses errors that are typical in Engineering spreadsheets.
It is a great read for engineers but more importantly for us, it shows us that all disciplines are the same and struggle with the same issues in spreadsheets.
Reasons (engineering) spreadsheets have problems
For the reasons engineering spreadsheets have problems he states that:
- ‘Engineering calculations are a mixture of theoretical and empirical expressions. There are uncertainties in the inputs and calculations, and the assumptions around how these represent the real world need to be stated.’- Financial models have the same issue. Rules of thumb are combined with financial calculations to try and predict the future or to simulate an outcome.
- ‘Engineering design expressions are formalised in documents such as a design standards and engineering calculations must comply with these documents.’- Sounds like the terms sheets from banks or the detailed contracts which spend pages and pages explaining what is meant by DSCR (and often it differs from bank to bank).
- ‘Engineering spreadsheets are used across multiple different jobs by many different engineers for several years. On average a spreadsheets get updated seven times in its life.’- Have a look at our explanation of why spreadsheets are so hard to get right. Common issue is the reuse of models and having multi disciplines using the same spreadsheet. At least all engineers have a common basis. When you combine engineers with accountants with HR you are asking for trouble.
His main issues with engineering spreadsheets make sense (and apply to all the models we have seen)
- Fitting a detailed financial model into a printable page is hard. Lots gets left out. Should we really be obsessed about this. Get the spreadsheet right and then worry about how to share it.
- Financial people also use spreadsheets as an initial brain dump, but once they understand what they need, where they should rebuild, they rather continue with the brain dump.
Practical best practice guidelines for engineering spreadsheets (and spreadsheets in general)
His practical tips also mimic almost exactly the general spreadsheet best practice. Comparing it to our Financial Modelling best practice you will see the similarities. The top is his suggestion and below is links to Financial Modelling Best practice and where we see the similarities
- Lack of naming and commenting
- Calculation flow
Structure
- Unused Cells and unused sheets- This is one of the common reasons for errors in Financial Models. It is part of the point above. If your model calculations flow this should not happen. It is as a result of the brain dump and then leaving old thinking in because when you delete it your model has errors and you don’t have time to address them
- Hidden Cells- Similar to the issue on setout. All areas should be clear.
For us this shows that no matter the discipline, the issues are all similar and more importantly the suggestions to reduce the risk are similar.
Each spreadsheet advocate may have some slightly different suggestions but what is interesting is that people who work lots with spreadsheets have all come to similar conclusions of what works best to reduce the risks.
Learn how to build your own decision and financial models with the online course.