What truths are spreadsheet and email budgets hiding from the CFO?
Previously, when I wrote about the issues of using spreadsheet-based budgets, I highlighted the risks of using spreadsheets to analyze budget-to-actual variances.
In this second of a three-part series, I’ll address the dangers of sharing spreadsheets with department heads to create a budget. Spreadsheets create collaboration issues for finance teams. Examples of these issues include digging through emails to find spreadsheets and using what you think is the latest spreadsheet version when it actually isn’t.
In my next and final article of this series, I will address the ‘board worthiness’ of spreadsheet-based reporting.
Spreadsheets seem simple
As we all know, spreadsheets and email are easy to use. Finance teams know how to create standardized budget templates and reports, and department heads know how to enter their estimates into templates and email them back to the finance teams. The process sounds straightforward and fairly simple, right?
Beware. There are trade-offs in developing budgets with spreadsheets and using email to collaborate with others. Although the process for collecting budget information from coworkers is very simple using spreadsheets and email, it introduces two critical issues. First, budget review and approval are delayed since too much time is spent digging through emails searching for the latest department spreadsheets to merge into a consolidated view that doesn’t always have the latest changes. Second, actual-to-budget variance analysis during the year is unsuccessful since spreadsheet-based budgets don’t show the critical assumptions and context underlying the budget numbers to help evaluate actual results. Because of these two issues, the CFO can’t know the truth about the business.
When I worked in professional services, I helped finance teams adopt new budgeting and planning software solutions to replace spreadsheets. Prior to making the change, my clients created spreadsheet templates to collect information from other department heads and used email to distribute and share spreadsheets with each other. When it was time to consolidate and review the company budget, a finance person created a separate consolidated report (another spreadsheet) and either copied and pasted or created links to the information collected from the departments into the separate report.
This process always broke down because once the department heads started iterating changes to their respective budgets, the finance team would have to keep track of the different department versions and update the consolidated report anytime there was a change. The consolidated report was never correct because the process didn’t allow the finance person to capture department changes that hadn’t been shared with the finance team. Those changes would be sitting in another spreadsheet that finance wasn’t aware of. That’s only the first issue.
The second issue with creating a separate consolidated report is that it only showed the total company budget values by GL account. That’s fine if you only needed to present a company budget that resembled a financial report. But for doing real-time analysis and comparing actual results to budget, this kind of spreadsheet-based reporting doesn’t provide the context for any finance person to evaluate actual results against the budget. The underlying assumptions used to derive the budget numbers from the department heads were missing in the report. The assumptions were unfortunately ‘left behind’ in the departmental spreadsheets. As a result, a finance person didn’t have a way to address the question, “What was our thinking and what were our assumptions when we came up with these budget targets at the beginning of the year?”
Business case
A former client of mine provided skilled nursing services in nursing centers located in 20 states. The finance team developed a budget based on a list of assumptions about patient-per-day economics (i.e. average daily census per nursing center, revenue per patient day per nursing center, nursing hours, nursing labor cost per patient day, etc.). These assumptions were used as the basis for a budget model that calculates nursing center revenues, expenses, and profitability.
Using spreadsheet templates for each region of nursing centers, the finance team emailed templates to the regional managers, who then input the nursing center assumptions to calculate the budget for each month in the upcoming year. The consolidation of spreadsheets and budget review process were painful. Between what seemed like an endless back-and-forth of spreadsheet emailing, manually consolidating budget information for tens of nursing centers, iterating changes, and managing multiple versions in the final hours before the deadline, the VP of Finance afterwards remarked, “I never want to go through that again.”
But the pain didn’t go away after the budget was approved by the board. Two months into the new year, the company was performing below plan, and the CFO had to present a forecast to project financial results for the remainder of the year. But when the finance team revisited the company spreadsheet-based budget, they couldn’t explain the budget variance to actual results since the nursing center patient per day assumptions were not in the report. The finance person had to have separate meetings with the department heads to understand the thinking behind the budget assumptions to analyze nursing center financial performance. By the time the finance team completed their analysis, more than a month had passed, and it was too late to share the analysis with the board.
Working together in real-time
Spreadsheet-based budgets take too long to consolidate and iterate to compare different financial scenarios. And when email is used to collaborate with other department heads, executives have no other choice but to wait a very long time to get the information they need, which is also never accurate whenever the latest versions aren’t included.
Ideally, a finance person should have a solution that not only automates consolidated reporting anytime a change is made but also stores the modeling inputs that drive the thinking underlying the budget numbers. All the essential reporting and key underlying assumptions are in a single place, accessible by anyone at any time. Sage Intacct Budgeting and Planning is a solution that can automate the correct and only version of your plan and free up time to analyze important what-if scenarios and do better planning.
Learn more by downloading “The 8 Signs that Your Company Has Outgrown Excel for Budgeting and Planning.”
Ask the author a question or share your advice