Through my work I have dealt with thousands of Excel, and for those that remember Lotus, financial models and I have to say that most of them are bad. I would like to summarize five of my least favorite things about the “bad” models I have had the unfortunate experience to deal with during my career. All of these issues, except the last one, are sufficient enough for me to walk away from the work or opportunity as they indicate that I am likely to find more problems in the finance area or business.
- Excessive precision. When I see models that have numbers in the millions and show accuracy to dollar, or even worse the cent, red flags fly. That much precision is a distraction and is usually wrong, especially if it is a forecasting model. In the words of Niels Bohr, “Prediction is very difficult, especially about the future.” Thus models that predict amounts to the dollar a year away are wrong. Ask yourself, what level of accuracy is needed. If you are dealing in tens of millions and you show numbers to the thousand, the error level is 0.01% which is more than enough precision. Another test is would you be willing to bet on the outcome being right to that level of precision, and if not ask why are you showing it. Remember the old adage – “I would rather be 90% right and imprecise than precise and 100% wrong.
- Hard coded numbers. Models that have hard coded numbers in them also raise red flags. Users forget they are there and they remain forever with no rhyme or reason leading to bad results. Also if things change it is hard to find them and change them in all the cells that need changing. Finally, requiring hard coded numbers to change a model to get it to produce correct results, could mean the model is fatally flawed in design and operation – time to start again.
- No tracking of results. Recently met a firm that had a model it had been using for all its forecasting for years. I enquired as to how accurate it was and the answer was that the last month had been a huge surprise, but no one had ever tracked its results against actual. If the model is not measured, its effectiveness is not know or cannot be fixed. Thus one could be relying on something totally wrong for years and not realize it. No model is perfect and they are like an iterative process, use them, measure the results and then adjust them to get to improve the results.
- Understand the logic of the model. Understand what the model is trying to accomplish. Often it is good to diagram out how all the parts are going to fit together and where the different parts will reside in the model. In addition think through all the parts carefully about how they work and what they do. It is my experience that usually 6 – 10 items contribute to the majority of the values and so they need the most precision, the rest will not change things much and we don’t need to focus on those as much. In addition, beware of some important items, i.e. exchange rates. A model I received recently had no exchange rate assumptions even through the company had significant European operations – this oversite would lead to incorrect forecasting. Finally remember all the parts – again the same complex financial model for business forecasting that I mentioned above showed P&L, Balance Sheet and Cash Flow statements as well as many other iterations of the data; however, the model didn’t distinguish between book and tax depreciation which would lead to incorrect cash flow statements as well as errors elsewhere.
- Layout. A model should be like a book or an essay – an introduction – the characters – the plot – the conclusion. Many I have seen have everything mixed up and you cannot follow the logic or flow which makes it hard to read and work with. Make the model easy for the user to follow and read.
Thus, I would recommend:
- Put in a tab which explains what it does and describes what is on each tab as well as the color coding if you are using it.
- Use the cell indents for indentation, not another column.
- Use colors – it makes it very easy to see what are inputs, assumptions, outputs etc. If all input cells are yellow then the user can easily know what to change.
- Use lists to control inputs – prevents mistakes accidentally happening.
- Spell check – it is built in so use it.
- Make it clean and easy to read.
- More tabs with a purpose are better than one huge tab that is difficult to navigate around.
- Break tabs down into: Assumptions, Working, Results. Makes it much easier to follow.
- All assumptions should feed into the appropriate parts of the model – just showing them is useless. In addition, remove cells that contain assumptions that don’t feed into anything or do calculations for no specific purpose.
- Put in check boxes – if you are building a forecasting model put in a line showing that the balance sheet balances. Thus if it doesn’t you can easily see it rather than suddenly realize it when it is too late.
I hope that you find these useful. Good luck forecasting.
Posted by Marc Borrelli.