“I spent a week without sleep trying to forecast the impact,” McNair says. “Unfortunately, our spreadsheet-based systems were inadequate to successfully provide this analysis in a timely manner. I couldn’t take all 50 budget centers and blow down the sales impact quickly to each individual budget, so I ended up doing this top-down, with a new operating plan focused on executive level budgets. We spent the next six months trying to drive the changes down to the responsibility managers who needed the appropriate metrics for measuring their performance against the revised plan. For the rest of the year, we were not as effective as we could have been.” Had he used an analytical application for the re-forecasting, McNair says, “none of this would have happened — I would’ve hit a couple buttons and budget managers would have instantly realized the impact of the re-forecast on budgets, allowing them to make decisions accordingly.”
C.F. Martin is currently rolling out an analytical application for planning to its budget managers. “We bought it as a budgeting and planning tool, and the first thing we learned was that it was a great financial reporting tool, much better than our ERP system,” McNair notes. “No one ever kept a database of financial performance here because of the spreadsheet-based process and the inferior reporting provided by our accounting module. Now we’ve got one, and it takes two seconds to use it.”
Wastren Inc. considered its former spreadsheet-based budgeting process ineffective, with too many individuals required to contribute to it. “It would take so long before it was completed that two months later when we were into it, it just didn’t mean a whole lot,” says Tom Kaupas, CFO of the Colorado-based privately-owned waste maintenance contractor, with $43 million in annual revenues, nearly all from government contracts. “I needed something that would refresh quarterly,” Kaupas adds. “I wanted a rolling four-quarter forecast, the consolidations, and the ‘what if’ scenarios that I could review — the spreadsheets always came up short. We did a technology versus manpower assessment [as it related to planning, budgeting, and forecasting] and it clearly weighed in favor of applying technology — an automated analytical tool.” The company built the tool internally in early 2003.
Interview subjects say poor data integrity and accuracy affect their satisfaction with planning, budgeting, and forecasting. Finance staff at Thales Broadcasting & Multimedia, for example, used to manually key in an entire trial balance for two separate divisions as part of its consolidation process. This was “an enormous volume of line items and numbers, making the process prone to error,” says Joan Hartung, manager of financial analysis and reporting at the Massachusetts-based manufacturer of television transmitter systems. “Because this was a consolidation, from a financial standpoint it has to equal out to the penny. But spreadsheets are open to human error. Even I’ve stepped on a mathematical formula and made it go away, consuming time and resources.”
Hartung says the company had a “pretty complex spreadsheet process — the ‘file from hell,’ we called it — that only one person [in finance] could effectively use at a time. It is huge, and it occasionally locked up the entire computer, and we weren’t nearly as efficient as we could have been.” Thales recently purchased an analytical tool that it is using for corporate reporting and financial consolidations, and expects to roll out the tool’s planning and budgeting features in the next year.
Many users of analytical applications for their planning, budgeting, and forecasting do not migrate completely away from spreadsheets. The survey indicates nearly 70 percent of analytical application users continue to use spreadsheets for local, ad hoc analyses of data from other applications. Companies that use analytical applications for planning, budgeting, and forecasting are generally satisfied with the software. The converse seems to be the case among midsize companies reliant on spreadsheets. Of the 189 survey respondents whose primary planning, budgeting, and forecasting technology is spreadsheets, 20 percent of respondents say their finance staff is “very satisfied,” compared with 33 percent of those whose primary technology is an automated analytical application.
This article is excerpted and adapted from Budgeting and Planning at Midsize Companies: When Are Spreadsheets Alone Not Enough?, a report that summarizes the findings of a mail survey of finance executives at 287 companies and further interviews with executives at 6 companies. Prophix, a provider of software for budgeting, planning, financial consolidation, management reporting, and analysis, funded the research and the publication of the findings; CFO Research Services produced the final report. You may download a copy of the full report by filling out a brief form.