Preventing glaring spreadsheet errors
Excel is both revered and despised, depending on who you ask. In certain occasions, it can be classified as both by the very same person.
It can be a great tool, as long as it's used properly. However, to properly use a tool, you have to understand how it functions.
The biggest mistake I have witnessed involved a forecast the client had already presented to their lender. The lender was not satisfied with the forecast as presented. As it turns out, the numbers were about to get materially worse.
There were input errors on 6 lines (out of 250) which lead to an overstatement of sales by $2.0 million, approximately 5% of the company's forecast. Once the error was fixed, the gross profit was reduced from $7.2 million to $5.2 million, this $2.0 million reduction directly increased the projected net loss by the same amount.
What caused the input error? The standard items: miscommunication, unclear instructions, and insufficient review. What happened in reality was that the sales rep entered case pricing instead of using unit pricing. This should have been easily caught by reviewing both the inputs and looking at the profitability by product (at gross margin level). The model already generated gross profit by product, which was a nice feature not included in many forecasting models. The company's average product margin (including the error) was 16% -- a 92% gross margin for select products should have jumped out at anyone.
Ultimately, it comes down to design and review. One does not need to be a spreadsheet guru to even ask some basic questions to understand if a forecast model is well built. A few key questions someone could ask is:
- How is it designed?
- How does the data flow?
The answers, or potential lack thereof, can be very insightful as to the integrity of the model.
Similar to blueprints for a house, the "architect", or user of the model, should be able to easily demonstrate the structure and data flow on a whiteboard. Barring the ability to do that, keep asking questions.
Excellent post. I have "built my career in replacing Excel Data Marts".. Once an excel mart reaches a tipping point; it does not have the bandwidth to be an application. It usually reaches the tipping point around audibility, Security, complexity , the inventor leaving the firm and no one to maintain it... Then we institutionalize it in IT and try to prevent those glaring errors...