Hacker budget spreadsheets

What’s the one thing every Finance person complains about if their organization uses Excel for budgeting? That users break links or overwrite formulas or somehow introduce errors into the spreadsheet that Finance worked so hard to create. Intentional hacking? Unintentional hacking? Doesn't matter -- the results are the same.

The implications are truly frightening. A single error, introduced at the lowest level of the budgeting process, can go undetected all the way up to the consolidated P&L that’s reviewed by senior management. This isn’t some theoretical risk, like an asteroid hitting your home: it’s a very real risk that every Finance organization needs to cope with.

I worked with a CFO of a joint venture between two very large insurance companies. She told me how a consolidated spreadsheet that produced a P&L was built using multiple linked spreadsheets. Unknown to her at the time, or anyone else, a link with one particular spreadsheet was broken. That resulted in a sizeable error on the consolidated P&L. Sounds bad, doesn’t it? But what made the matter so much worse was the error was not discovered until after the budget was blessed by not just one, but two Board of Directors (one for each of the parent companies).

I worked with the CFO of a university that was in a slightly better position. His team discovered a spreadsheet error before the budget was actually presented to the Board. Good. But in his case the problem was revealed the morning of the Board meeting… so he had to announce that a problem had been discovered and Finance was in the process of reconciling it (and verifying no other errors existed). The Board of Directors review of the budget was pushed back two weeks as a result. Now just imagine standing in front of your Board of Directors and saying “Sorry folks, we won’t be reviewing the budget today after all.”

Broken links. Formulas being over written or deleted. Rows and column being inserted and blowing up elegant formulas that Finance so thoughtfully crafted. Ever wonder why these users seem so intent on making your life miserable? Why do they want to hack the budget?

Excel budget templates don’t reflect how people actually think about the business. So people wind up hacking the budget templates to work the way they think. That might mean inserting rows to add line item detail, overwriting an existing formula, writing a new formula (improperly) or any one of a hundred hacks that introduce errors.

There are two strategies Finance typically employs to try to prevent or eliminate spreadsheet errors. One is to attempt to lock down budget spreadsheets. This can prove fatal because it provides a false sense of security… “Hey, no reason to validate these spreadsheets, they’re locked down!”  Most of us have learned the hard way that a smart user will get around passwords and other lock down features.

The other problem with locked down spreadsheets is, by design, they limit choice and flexibility. End users are frustrated enough with the budget process and this only serves to irritate them and make them less willing to engage in the process.

The other common strategy is to spend hours validating spreadsheets, pouring over them to identify potential errors. This strategy is often deployed in conjunction with locking down templates; because hackers may gotten around the passwords, and because Finance itself may have inadvertently created some error (nobody’s perfect).

The “let’s check all the spreadsheets to make sure they’re right” is monstrously inefficient, and significantly limits the time Finance can spend on value added analysis. But it’s also impractical. There is no way to check every cell of every individual budget spreadsheet –and all the cells in all the consolidating spreadsheets. You might think you're doing a good job of this today, but just ask yourself, “Would I bet my house that even after Finance checks the budget spreadsheets, there isn’t an error buried somewhere?”

The bottom line is hackers – of both the intentional and non-intentional kind – undermine the credibility of the budget P&L. Since Finance runs the budget process, confidence in the Finance team itself is undermined.

What’s to be done?

If you’re using Excel for budgeting, the only answer is “work hard and cross your fingers.” But the longer term answer is to move to a real budgeting application, one built on a database (not spreadsheets) that is flexible enough to allow people to budget the way that they think -- in a well controlled environment. That’s exactly what I had in mind when I first envisioned a planning application as a FP&A Manager at Pepsi Cola International, and that an inspired and incredibly talented team brought to life at XLerant.

If you have personal experience with broken links and formula errors and other budgeting nightmares that wake you up in the middle of the night, please share them here.