One of Excel’s greatest strengths is its simplicity and flexibility. However, Excel’s greatest strength can also be its greatest weakness. The flexibility that comes with being able to design any number of spreadsheets or excel models also introduces the possibility of some pretty big errors. The lack of structure and built in controls often mean that errors creep into spreadsheets. Ultimately this means we are making decisions based on incorrect information.
Unfortunately, these errors aren’t just occurring in spreadsheets used to calculate the weekly footy tips, but can also be found in some of the largest and most prominent organisations on the planet. In 2010 two highly regarded US economists Carmen Reinhart and Kenneth Rogoff made a handful of spreadsheet errors that led to controversial findings that were widely published and relied upon by global policy makers when determining the best way to pull their economies out of the GFC. Some research suggests that up to 80% of spreadsheets contain a material error. There are a few simple steps that can help reduce the risk of errors and I spend the rest of this article touching on 3 of the most important ones.
1. Dynamic Cross Checks
When building, a financial model make sure you include cells that that tell you if you are making an error. The most well-known cross check is, does my net assets equal my total equity. As you build a spreadsheet you should be constantly thinking, how can I confirm that this calculation is working correctly? Can I tie these numbers back to a total? Does the growth rate tie back to my assumed rate? If you put in cross checks as you go, you will pick up errors as they occur, which will save you a lot of time down the track. Make sure you include a summary of all your checks for each worksheet and the entire workbook.
2. Smell Checks
Often errors in spreadsheets are missed because the builder is bogged down in the detail and forgets to take the time to look at the big picture. Make sure that when you have finished building a model that you take the time to step back and ask yourself “are the results/ outputs what I expected?” if they aren’t then make sure you find out why. A helpful tool for doing this is to graph key outputs, visually it is much easier to tell if something is going wrong, for example, are my sales growing as I expected?
3. Detailed review by a fresh set of eyes.
As Reinhart and Rogoff can attest it doesn’t take much to not sum an entire row, and once these simple errors creep in they can be hard to spot. Once the build is finished we recommend that a fresh set of eyes review your spreadsheet. I don’t send a spreadsheet to a client without getting a colleague to perform a detailed review, and you should do the same. Your colleague will be able to challenge your logic and with a fresh set of eyes and will be able to pick up those errors that you have missed. When budgeting your time, always make sure you budget enough time to have a colleague review your work.
It is important that you get someone that knows excel and is aware of how to review your model. If you don’t think there is someone in your company that can do it, we can help. We spend a lot of time looking at models and can quickly identify errors and common mistakes. The small investment in getting your spreadsheet reviewed can save you thousands if not more when material errors are identified.
DISCLAIMER: This article is intended to provide a general summary only and should not be relied on as a substitute for professional advice.