Quoting from the blog ‘Revolutions’, the author refers to another blogger, James Kwak, a University law professor and co-founder of the ‘Baseline Scenario’ blog where he refers to the the use of a ‘Value at Risk’ (VaR) model that underpinned the hedging strategy “”operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another”” and that it should be automated but never was”.
It’s a complicated story but one that has received widespread and embarrasing publicity for Jamie Dimon, the head of JP Morgan Chase as he tried to explain how one trader could have exposed the bank to so much risk. But for us, the real story is the SPREADSHEET story and my ‘skyscraper’ premise.
If you walk into a twenty story building, I doubt you wonder if the building will stand erect until you leave it. None of us do. But, do you know if the foundation was poured to standards, strengthened correctly and the materials used were not sub-standard. It’s really impossible for you to know unless you were involved in some way in its construction. But everything in that building’s walls and foundation were built one upon another. In a similar parallel, a colleague of mine once reminded me that an airplane was built of millions of moving parts all supplied by the lowest bidder!
But what has all this have to do with spreadsheets? I’ve seen statistics quoted that as many as 75% of all spreadsheets contain significant calculation or assumption errors. The spreadsheet I’ve always been most comfortable with is one I built, cell protected, and could generate an answer for me that I could recognize as being reasonable. Beyond that, they scare me to death. So here are some of my ‘Best Practices’ that Controllers should consider:
- Just because you, or someone else, loves building spreadsheets is not a good enough reason to build it
- Avoid whenever possible using spreadsheets to actually run a part of a your business, like recording sales, or managing inventory, accounts receivable, order entry, etc.
- Learn how to use Excel’s spreadsheet auditing tools. I can’t emphasize this enough.
- Find a way to test a spreadsheet. Perhaps manually figuring the answer and then seeing if you get the same result from your spreadsheet.
- Look at your inventory of spreadsheets. Identify which ones are critical. (used to calculate bonuses, compensations, commissions to be paid and ones that lead to decisions are an example)
- Critical spreadsheets should be ‘tested’ for accuracy. Have they always had the formulas protected?
- Think of a spreadsheet like a ‘house of cards’ (no, not the Netflix show, but I do love it) where only one minor shift in balance can bringing it all crashing down. The thing is, in a ‘House of Cards’ you can see the cards falling. You can’t in a spreadsheet with severe defects. As you add layers of complexity, test, test, test.
- Even if you’ve been out of college for only ten years, that course you took for one semester, did not teach you all you need to know. Be a student of spreadsheets. (See Below)
Once, while working as a Senior Financial Analyst for a publicly held corporation, and working a lot in the acquisitions area, I was given a spreadsheet to work with that had been designed by someone earlier, some years before. No one knew who the author was and there was absolutely nothing documenting how the spreadsheet was put together or how it should work. In the workbook, there were numerous tabs all feeding up to the main sheet. If you estimated capital expenditures over a period (and this was before we ever saw bonus depreciation) a depreciation sheet projected how much depreciation expense the acquisition might incur for that year. The model was based on a 10 year forward projection of net income and if return on investment exceeded your hurdle rate (cost of capital). It was a nightmare because I was so uncomfortable with its reliability and struggled to see how entering one number might replicate through the entire spreadsheet.
There are tons of books out there on spreadsheeting. Want to really learn how to use macros? There’s a book for that. Want to create a ‘breakeven analysis’ there’s a book for that. Who is my ‘go to guy’? Carlton Collins. I first met him through an AICPA course. He is one of the most knowledgable guys anywhere on Excel. Just google him or, if you subscribe to The Journal of Accountancy, you’ll find him in the ‘Technology Q&A’ section. He’s also served as an adviser to Microsoft on matters regarding Excel. He’s a CPA and he understands very well your challenges.
Finally, the absolute worst thing you can find out is that the commission dollars paid to a salesforce and calculated by a spreadsheet you inherited has overpaid them by 2 or 3% (or worse) for the last five years and you didn’t catch it. Probably some salesman complained he was shorted on his commission and when the accountant began checking into it, they found that actually that sales person plus 20 others have been overpaid. Believe me, it can get much much worse than that. When it comes to spreadsheets, as they say, ‘TRUST, BUT VERIFY”.