This is an audio version of a blog article.
The original article was published here:
https://www.riskinsights.com.au/blog-1/survive-the-damage-caused-by-a-spreadsheet-model-error
In this episode, we explore three ways to reduce the risk of spreadsheet error.
Spreadsheets are often used for modelling and analysis, largely because they are easy to use and highly flexible. This has been the case for decades.
But what happens when a simple error like cutting-and-pasting the wrong formula, or omitting data in a calculation, ends up costing you thousands or even millions of dollars?
To build for sustainability and to reduce the number of inadvertent human errors, especially for large and complex models, you can reduce the risk of error.
But how do you do that?
ReviewA minimum of 2 types of Quality assurance reviews for each model:
i) A technical peer review (ideally by someone who has not been involved in the development of your model) to review and evaluate the accuracy of the formulae, calculations and code.
ii) A business user review, (by someone who understands the purpose of the model and the underlying business rules), to determine whether the model is working as it is supposed to - functionally.
ProtectIf you continue to use the spreadsheet model, lock the calculation cells.
This provides a layer of protection from unexpected changes; however, it does not necessarily prevent other users from unlocking the cells.
Change platformMoving the model to an analytics platform allows you to enter your variable inputs through an interface (e.g. Excel, web form, visualisation tool) which can rerun the model on the fly (behind the scenes) and produce the scenario results.
With this approach, the model can be used more broadly, with reduced risk of change to the underlying formulae / algorithms.
This is a public episode. If you would like to discuss this with other subscribers or get access to bonus episodes, visit riskinsightsblogcast.substack.com
Hosted on Acast. See acast.com/privacy for more information.