Last month, I wrote about the pitfalls of being overly trusting of spreadsheets. As a follow-up, I felt compelled to offer some practical advice. Below are my top ten – it could easily be many more.
1. Know the answer before you start. That might sound impossible but it’s important to have a hypothesis before you start to build a spreadsheet. It gives one a chance to formulate a mental model before the spreadsheet model and to constantly ask which is right.
2. Use “check digits”. It’s amazing how many times spreadsheets don’t foot. Balance sheets may not balance and the sum of twelve months may not equal the annual total. Double check the math with a formula that adds all of the months and all of the years to be sure they balance. I always have a few rows and columns that should equal zero; it’s easy to spot if they don’t.
3. Calculate two ways. I often throw in an extra formula to calculate an answer in slightly different manner. There is often more than one way to get to the same answer and taking this extra step will ensure that your logic is sound.
4. Develop all three financial statements. There is a reason accounting relies on an income statement, balance sheet, and statement of cash flows. They all tie together. It’s easy to cheat and stop at EBITDA and I’ll admit to doing it myself from time to time, but each statement presents different information and allows for a greater chance of seeing a result that is illogical.
5. Color code. I often hi-light various rows and columns to draw my eye back to them. Sometimes my spreadsheets look like a rainbow but this is easily eliminated later.
6. Sumif. This is one of my favorite functions. It can eliminate formulas with long strings of adding individual cells which are prone to either missing a cell or capturing it twice.
7. Keep it simple. It’s easy to create exceedingly complex formulas but I’d much prefer a simple formula that yields a result of $10,500 than a complex one that yields the result of $10,479.52. For forecasting purposes one is no more accurate than the other and the simpler formula is easier to audit, trace back, edit and for others to understand.
8. 100 months! Almost all simple formulas are only valid within a relevant range. It takes seconds to run a spreadsheet out for 100 months or more. The model will start to break down logically and you can use these insights to determine if there are things you should be thinking about over a shorter, relevant horizon.
9. Set it aside. Whenever possible, set it aside for a few days or longer. I’m a firm believer that things need to percolate in the sub-conscious for a while.
10. Ask a trusted colleague who understands the business to review the results. Sometimes a fresh set of eyes will spot something that should have been obvious.
Does all of this guarantee that you won’t have an error in a cell? I wish that I could say that it did. Typos happen. Ultimately, however, the goal is neither perfection nor precision. It is accuracy of the significant digits and the soundness of business decisions made from the data.
If your business could benefit from fractional CFO services, I would welcome the chance to speak with you. Please give me a call at (314) 863-6637 or send an email to
your cash is flowing. know where.®
Ken Homza Copyright @ 2011 Homza Consulting, Inc.