This story is in response to a recent story on Accounting Today entitled “Learn to Love Excel Again.” The author discusses how Excel spreadsheets are prone to human error and fail to pass multiple risk and audit checks. Even bulge bracket firms like Goldman Sachs can make mistakes; in 2014, the bank overstated common stock in a spreadsheet during the sale of Tibco software to Vista Equity Partners, costing Tibco shareholders $100 million! Despite all the Excel naysayers, there are a couple strategies and even hacks analysts and accountants use in spreadsheets to prevent errors.
Time & Flexibility Are Inversely Correlated
During the month-end close, analysts in accounting and FP&A are scrambling to submit their budget versus actuals and forecasts. The month-end close might even be divided into several days pre and post-end of the month. It’s like a ticking time bomb where even after the month “closes,” there are still days (i.e. month-end+1, month-end+3) where you still have to turn in your analyses. On top of this monthly cycle, you have quarterly “closes,” half-year plans, and of course annual plans. This happen every single month.
I’m not dogging on the job function for planners and accountants, it definitely takes a certain type of person with strong, well, planning skills to go through this grind every month. For new accountants and analysts, this cycle can seem pretty daunting, and when you’re trying to submit your budget at 11:57PM the night before the budget locks, you are pulling your teeth out to make your spreadsheet calculate correctly.
I’ve found the less time you have to produce an analysis, the more flexible your spreadsheet needs to get. Sure, if you have all the time in the world to properly forecast expenses and make sure all costs are accounted for, you can double-check and triple-check your work. When you’re under a time crunch, Excel’s flexibility gives you all you need to get your work done without having to extensive risk and audit checking.
Have “Check” Cells All Over Your Spreadsheet
Accounting managers I’ve worked with during the month-end close would send me these super large Excel files that show a running history of the budget and expenses. Buried into these files are many audit checks referenced in the Accounting Today article. In practice, these were simple “check” cells littered throughout the spreadsheet to make sure the end result matched up with another source. Here’s a simple example:
You have a budget of $1,000, and you want to make sure that the expenses you’ve compiled add up to the budget. You can quickly see that the $900 is off by $100. However, sometimes these differences are very minor and due to formatting and rounding, the difference may not be as apparent. That’s why you have the “check” cell at the bottom where you take the budget of $1,000 and subtract it from the Sum cell of $900. This gives analysts a quick way to see if the formulas they are building (and the data behind the formulas) calculate to the right amount. When you have checks that are non-zero, you know you have a problem.
This is a super simplified version of a check. In addition to sum checks, I have seen text checks, boolean checks (true/false), and formula error checks (e.g. cells that show “N/A”).
Spreadsheet Technology Must Be Integrated at the Beginning
Given the cyclical nature of most FP&A groups, integrating a brand new technology in the middle of the month would wreak havoc on analysts. Having said that, doing advanced what-if and risk management analysis where the work is more project-focused is where technology can shine. There are probably a few plugins out there for doing what-if analysis aside from Excel’s own built in what-if tool, and these plugins are probably more user friendly than Excel’s built in tool.
Sharing a spreadsheet with a co-worker has always been a difficult task, and it usually involved pinging your co-worker and asking them when they are getting out of the file so you can start editing them. Then you have to deal with version control and overwriting any work they may have done before you touched the file. This is another area where technology can shine. Google Sheets is the de facto tool for sharing, but for enterprise-level risk and version control, you’ll need to source a more robust solution. Better invest in that technology now instead of losing $100 million for innocent shareholders and tarnishing your brand image.