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 wrong. I want 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 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.
When I see models with numbers in the millions and show accuracy to the dollar, or even worse, the cent, red flags fly. Just because Excel can calculate to ten plus decimal places, you don’t need to use it. 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 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 accuracy, and if not, ask why you are showing it. Remember the adage, “I would rather be 90% right and imprecise than precise and 100% wrong.”
Models that have hardcoded numbers in them also raise red flags. Users forget they are there, and they remain forever with no rhyme or reason, leading to wrong results. If things change, it is hard to find them and correct them in all the cells that need changing. Another issue with hard-coded numbers is where a correct model produces #N/A or #DIV0 results due to the values entered, and the model cannot adjust. To fix these, instead of using Excel logic statements, users change the formulas to exclude the offensive input. However, when the correct data is obtained and entered, everyone has forgotten about these changes. As a result, I have seen large companies use fatally flawed models in planning, but no one has realized it. Finally, requiring hardcoded 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 I met a firm that had an Excel model it had been using for all its forecasting for years. I inquired about 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’s results are not measured, its effectiveness is unknown, and no improvements are made. Thus one could be relying on something 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 improve the results.
Understand the logic of the model.
Understand what the model is trying to accomplish. It is often good to diagram out how all the parts will fit together and where the different components will reside in the model. Also, think through all the pieces 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. Also, beware of some essential items, i.e., exchange rates.
Another Excel model I recently reviewed had no exchange rate assumptions even though the company had significant European operations – this oversite would lead to incorrect forecasting. Finally, remember all the parts! The complex financial model used business forecasting mentioned above showed P&L, Balance Sheet, and Cash Flow statements; however, the model didn’t distinguish between book and tax depreciation, leading to incorrect cash flow statements well as errors elsewhere.
A model should be like a book or an essay – an introduction – the characters – the plot – the conclusion. I have seen everything mixed up, and you cannot follow the logic or flow, which makes it hard to read and understand. Make the model easy for the user to follow and read. Thus, I would recommend:
A tab that explains what the model does and describes what each tab does as well.
Color Coding. Use color-coding to distinguish between input, calculation, and output areas.
Indentation. Use the cell indents for indentation, not another column
Colors. Use colors to format types of cells. It makes it very easy to see what are inputs, assumptions, outputs, etc. If all input cells are yellow, then the user can quickly know what to change. Excel provides this through cell formatting.
Data Validation. Use lists to control user inputs, which prevents mistakes from accidentally happening.
Spell check. Excel provides it, so use it.
Make it clean and easy to read.
Tabs, Use more tabs with a purpose rather than one colossal tab that is difficult to navigate around. Break tabs down into Assumptions, Working, Results. These tabs make it much easier to follow.
Assumptions must feed the model. All assumptions should feed into the appropriate parts of the model – just showing them on an Assumptions page is useless. Remove any cells that contain assumptions that don’t feed into anything or do calculations. Users will change them to no avail.
Check cells. Use check cells to ensure that things are correct. 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 this useful. Good luck, forecasting.
© 2013 Marc Borrelli All Rights Reserved
All business owners start somewhere. They learn the very fundamentals and begin putting in all the effort in the world. They grow a little and reach a little success sometimes. Often, even the best businessmen and women stall and run into a few issues. It’s...
Every single business in the world wants to evolve and grow. This will happen using a variety of techniques and strategies. In 2022, digital marketing is more than a household name, and most companies will adopt at least a few ideas when long-term planning and coming...
Many entrepreneurs consider executive business coaching when they start struggling on their professional path. A small business coach is an experienced professional mentor who educates, supports, and motivates entrepreneurs. They will listen to your concerns, assess...
Dreams turn into goals when they have a foundation of long-term strategic planning supporting them. They become reality when the ensuing strategic implementation plan is executed properly. With Kaizen Solutions as their strategic planning consultant, small and...
If you are a CEO or key executive who has come to a crossroads or crisis in your career, you'll gain valuable insights and solutions from a peer group connection more than anywhere else. But what is a peer group, and how can that statement be made with so much...
As I mentioned last week, I am down with COVID and tired, so spending more time reading rather than working. I read Bill Browder's Freezing Order this weekend, and I highly recommend it. However, at the end of the book, Browder says that oligarchs, autocrats, and...
I am currently sick with COVID, so, killing time yesterday, I watched Downfall: The Case Against Boeing about Boeing's issues with the 737 Max and how the focus and financial results versus anything else led to the problems with the plane. The company focused on...
2021 Resolutions, A Scorecard Last year I shared how I came up with resolutions to meet my business and personal needs in my blog post, New Year's Resolutions, Once More Unto the Breach. I broke my 2021 resolutions for 2021 into three areas and scored them as follows:...
COVID has taken a toll on all of us. If you have not taken an extended vacation in a while where you disconnect, you need to now. You and your business will benefit.
I recently discussed how you must be famous for something. If you're famous for something it is easier to: focus on what you can be the best at, find your "tribe," tell people what you do, get referred, and define your core customer While we all know of companies...