Avoiding Spreadsheet H*ll
- Feb 29, 2008
Any CEO or entrepreneur still vertical and breathing has spent more hours than they can likely count pouring over poorly conceived financial information. I probably review at least 50 spreadsheets a week, and few things chap me more than feeling like the spreadsheet I’m reviewing was constructed by an alien. PowerPoint presentations certainly receive more notoriety for being the butt-end of business jokes, however in my opinion a boring slide-show pales in comparison to the sophomoric use of a spreadsheet. I know this may seem like a trifling rant of little consequence, but if you stay with me I promise to connect the dots in a way that will resonate with most of the senior executives that read this week’s column. Let me begin with the premise that few commonly available business tools are as valuable and proficient at justifying business logic, validating proof of concept, synthesizing data, and testing a variety of assumptions and hypotheses than that of a well-crafted spreadsheet. However, it is the caveat “well-crafted” in the preceding sentence that gives strength to my original premise. An adeptly authored spreadsheet is truly a thing of beauty which can serve to crystallize the ambiguity often surrounding financial complexities and decisioning conundrums. Contrast the aforementioned elation with the antipathy you feel when trying to interpret the anathema represented by some spreadsheet monkey’s hack attempt at sophistication and you will start to empathize with my frustration. It should be clear by now that poor spreadsheets are a huge pet-peeve of mine. They not only waste my time, but the time of the people who prepared them to begin with. Rather than serving to advance an idea, decision, or initiative, a poorly conceived spreadsheet can have the unintended outcome of serving as the final nail in the coffin. Just as sound business logic and great presentation can serve to validate, the lack thereof can just as easily serve to invalidate. My point is that if something is worthy of taking the time to assess, then it is also worthy of good analysis. The following 5 items represent a few basic pointers for authoring a spreadsheet that will serve as an asset and not a liability. I would suggest that you forward this post to those within your organization who prepare your spreadsheets in hope of reducing the amount of unnecessary brain damage you’ll incur in the future: 1. Planning: Don’t lose sight of why you’re creating the spreadsheet to begin with. Major in the majors and don’t get lost in extrapolating massive amounts of useless information, or bogged-down with convoluted minor analysis, both of which can be dilutive to the mission at hand. Great spreadsheets are not built on the fly. Rather they are conceived through a rational planning process that leads to a seamless design and a good outcome. I suggest spending about 75 percent of your project-time planning what you want to create and about 25 percent of your time creating it. 2. A Narrative Introduction: Few things are more annoying than not being able to even discern what it is that you’re looking at. The first spreadsheet tab in your workbook should contain a textual, narrative overview of your business case. Explain what it is that you are attempting to prove/validate, as well as your methodology for doing so. Moreover, if there are any major points you want your audience to take away from the presentation, explain them in narrative form by “telling them what you’re going to tell them.” 3. Headers, Navigation & Footnoting: Make your spreadsheet easy to understand and navigate by using consistent formatting, clearly defined row and column headers, collapsible data, drop down menus, pivot tables, freezing panes, and the appropriate use of color and graphics. Be sure to footnote your assumptions and cite your sources using external links, where they provide value-added support and back-up. Where possible use graphs and dashboards to present your data in an easily understandable fashion. Not everybody is a quant-jock that lives to crunch rows and rows of data. I can blow through even the most complex spreadsheet in 10 minutes, if it is well architected, and I can spend an hour on a relatively simple, yet poorly designed, spreadsheet trying to understand the intent, and validate the logic of the presentation. Presentation matters… 4. A Command of the Toolsets: Learn how to use Excel. If you don’t have the patience for self-taught learning, invest in some classroom education. If you won’t invest in learning the toolsets, then sub-out the work to someone who has. The important thing is not to get in over your head. Complexity that serves no purpose does not equal sophistication; it is just a waste of time. If you don’t know how to roll-up data, or use VBA in your spreadsheet don’t just take a whack at it. I would much rather review a simple spreadsheet that is well conceived, than something that looks impressive but has no continuity. As with anything in business, competency matters… 5. Testing: Make sure that your spreadsheet works. Test your formulas, assumptions and presentation. Once you think you have it where you want it, solicit peer review to ensure you have the finished product you’re looking for. Much like you shouldn’t proof your own papers, you shouldn’t proof your own spreadsheets. Where there is smoke there is usually fire–when I’m reviewing a spreadsheet and I see one error, experience tells me there are likely more to follow and I quickly lose interest. I hope this rather simplistic piece will provide you with some useful information to help guide the legions of evil financial deviants bent on wasting executive time and resources with sloppy, ill-conceived, and often useless spreadsheets.