Spreadsheet-based forecasting is not board worthy—here’s why

Last time, I talked about the important truths hiding from CFOs who use spreadsheet-based budgets and email to collaborate. Read the article. In this final article of a three-part series, I’ll address why spreadsheets don’t give finance teams enough time to do what-if analysis and forecasting and are never “board worthy.” At Sage Intacct, we’ve highlighted “The 8 Signs that Your Company Has Outgrown Excel for Budgeting and Planning.”

Budgets are only as useful as what you anticipate happening to the business. The reality is that you can’t plan for everything, especially when you don’t always receive advanced notice when a big event will occur. So, when something unplanned happens and actual results don’t go according to plan, such as missing profitability targets, the CFO must forecast what the company is likely to achieve by the year-end. And this forecast usually involves revisiting the budget and identifying which accounts need to be adjusted. While there is so much uncertainty to consider when predicting a forecast, the CEO and board expect forecasts to be very accurate, sometimes to within 1% of actual results.

The latest spreadsheet version is outdated, and you weren’t notified of the changes

For finance teams that do spreadsheet-based budgeting, a forecast often lives in a separate spreadsheet that resembles a financial report of all the budget lines, and the budget targets set at the beginning of the year would likely be the starting point for making updates. A finance person would then either change the budget values directly in the spreadsheet or create a new model based on a relationship of input assumptions to calculate projections for the different financial account lines in the report.

This process breaks down when the finance team uses the spreadsheet they think is the latest version of the budget and actuals for doing a forecast when unknowingly, the selected spreadsheet is outdated. When looking at the latest version of a spreadsheet, the finance team doesn’t have a way to verify that all the information is current. Changes have occurred that live in other spreadsheets, such as budget assumptions or updated actuals from the accounting system. When these changes aren’t shared with the finance team or sit in an email that’s buried in an inbox, any forecast based on the “latest but outdated” spreadsheet version will mislead the board and ruin the finance team’s credibility.

Hidden adjustments are buried in spreadsheets

The second issue is when a finance professional adjusts a consolidated company budget to forecast the changes affecting the business. How much of an adjustment should you make relative to the original budget? Which assumptions do you need to change, and which accounts should be impacted? How do you spot the changes in your assumptions that have offsetting effects with each other in the report? While a spreadsheet-based report easily shows the updated forecast values, the issue is that the adjustments you made are hidden within the spreadsheet cells, grid coordinates, and formulas in your model. When someone else looks at this forecast, how can they see which values changed?

When adjustments to the budget are hidden from plain sight on a report, a finance person can’t make the necessary comparisons to evaluate the forecast. For example, if I’m forecasting 10% less revenue than the original plan for the remainder of the year, how much of the projected change is due to my headcount assumption, my average selling price assumption, or some other assumption in the model? And since spreadsheets can’t track the incremental adjustments you’ve made to a report, finance doesn’t have a way to explain the thinking and rationale underlying the forecast relative to the original plan.

For example, a client of mine developed business software sold as subscriptions. The company had missed first-quarter goals, and the CEO wanted a forecast for the rest of the year. Since the competitive landscape had changed, the original budget numbers had to be updated since they didn’t reflect the current environment. Using spreadsheets, the finance team built a sales capacity model to project revenues based on multiple assumptions: headcount hiring, annual subscription sales price, and quarterly sales ramping and seasonality productivity percentages.

However, after the finance team input the forecasting assumptions into the model, they didn’t have any visibility into how much each assumption was contributing to profitability. And when the team met with the CFO and CEO to review the forecast, the team also couldn’t explain how the forecast values had diverged from the original assumptions underlying the budget numbers. When the CEO asked the finance team “what was our thinking in assumptions when we did the budget,” the finance team couldn’t answer since that information was “left behind” months ago in another spreadsheet during the annual budget cycle.

Senior leaders and finance analysts are under pressure and unable to work together to develop a credible forecast. At the end, they finish the meeting feeling unsatisfied about the results and anxious about the upcoming Board meeting. For this client, the business cost and time commitment associated with making procedural decisions around hiring, pricing policy, and sales execution all vary with each other. A productive discussion around forecasting should enable any business leader not only to see their options but also to quantifiably understand the trade-offs amongst their options. Spreadsheets prevent these important discussions from taking place.

Ideal solution

The ideal solution should allow any finance person to make changes in a forecast model and compare the results, both inputs and outputs, to the budget. The ideal solution should also allow changes to be iterated quickly. One of the inherent challenges with forecasting is the uncertainty factor and the likelihood of having to consider different outcomes. The ideal solution should be able to track the incremental changes for multiple outcomes and show quantifiable comparisons with the budget. Sage Intacct Budgeting and Planning is designed to enable finance teams to do rapid what-if analysis and give finance both the time and confidence to produce credible forecasts that are board worthy.

Conclusion

If you’ve read the articles in this series, there is a recurring theme about the loss of visibility into the key details and assumptions underlying a budget when using spreadsheets. Spreadsheets don’t provide the necessary detail and contextual support to explain what’s being reported on the surface. To tell if it’s time to replace your spreadsheet-based budget, look for the 8 signs within your finance team.

Learn more by downloading “The 8 Signs that Your Company Has Outgrown Excel for Budgeting and Planning.”