A few days ago, I was speaking with a colleague that had recently reviewed a financial model. He was going on about how the modeler didn’t follow best practices and how it was impacting his ability to review the model as well as identifying several model flexibility issues that may have occurred later on in the project.
The purpose of a spreadsheet is to help accounting and finance professionals make complex decisions. They assist us in forecasting the future when there are many interconnected factors. A well-built model should be easy to follow and understood.
Standardize The Modeling Approach
Spreadsheets are powerful tools, but they can also become very difficult to use. When people construct a model, they often do things that aren’t intuitive or easy to understand. The Hallmark of a bad financial model is when other people try to use those spreadsheets and can’t. They might be confused or frustrated because the spreadsheet doesn’t behave the way they expect. Been there and done that!
A good model will allow you to test different scenarios and understand the impact of potential future events, which in turn identify the critical issues a project/company is likely to face in the future.
Benefits Of Implementing Best Practice Modeling
There are countless benefits of using best practices with Excel modeling. Some of these are:
- Making the model easier for other users to understand
- Makes the model easier to modify in the future
- Reducing the risk of incorrect analysis and results
- Model errors are easier to identify and amend
- Instils confidence with everyone that has involvement in the model – what would be your perception of a colleague that shows up well-groomed and wearing professional business attire as to one who is scruffy looking and makes no effort in their appearance?
Best Practice Techniques
In no particular order (except the first one!):
- Keep it simple
- Logical calculation flow – a model should be read like a book, left to right and top-down
- Use consistent formulas in each row and column; highlight cells where things have to change
- Use consistent formatting throughout a workbook – How To Format Like An Excel Wizard
- Separate inputs, calculations, and outputs
- Use separate worksheets and sections to display different topics (i.e. tax calculation should be done in a discreet section)
- Do not imbed hard-coded values within a formula or calculation
- Avoid external links
- Avoid circularity calculations and use a copy-paste macro if you must
Hope you found this helpful and it saves you time unpicking your own spreadsheet!