1.26.2006

RANT: When companies outgrow their spreadsheets

via CFO - (When are spreadsheets alone not enough?) ...Midsize companies have relied on spreadsheets and manual budgeting and forecasting processes since the earliest days of personal computers, and spreadsheets remain the de facto standard for day-to-day quantitative analysis. Indeed, well-crafted spreadsheets may be all the technology a new company needs to get up and running quickly. They are easy to understand and use; nearly all staff have some proficiency with Microsoft Excel.

However, large enterprises in recent years have migrated from spreadsheet-dependent processes toward more sophisticated automated planning, budgeting, and forecasting tools. These tools promise a greater level of operational detail for analytical purposes, more robust financial reporting, quick consolidations of financial data, and more input by business managers. The tools also liberate finance departments from the mundane manual processes of "racking and stacking" data to focus on a more robust understanding of the real drivers of business.

...A recent survey by CFO Research Services (a sibling of CFO.com) asked finance executives at midsize companies about their efforts to transform their planning, budgeting, and forecasting processes... This study found, across the board, that survey respondents believe they spend too much time on forecasting, budgeting, and planning. When asked about the most acute problems with their current planning process, more than 60 percent said it "takes too long." Nearly 43 percent said "not enough time to analyze data," ... What are the root causes of these problems? Respondents indicated that human factors such as collaboration among planning participants and uneven technical proficiency were primary causes. "Overdependence on key personnel" was cited by nearly 50 percent of respondents, "version control" by more than 35 percent, and "collaboration, consolidation of users' work" by nearly 35 percent of respondents. These issues add time to the planning, budgeting, and forecasting process, thereby reducing the amount of time left to actually analyze data on operational performance.

Interviews with CFOs at midsize companies confirm these survey findings and shed light on the real world problems that companies face when running their enterprises off a mosaic of spreadsheets... "With our spreadsheet model, I was unable to do 'what if' scenarios or work flow management," Green says. "I spent more time building and managing the model, and making sure none of the links were broken, than I did managing the data and analyzing it to ensure it fit the strategic plan. Aligning the spreadsheets was a nightmare. A simple change like someone adding an account threw the whole template [of operating expenses] out of whack. But the real drawback was my inability to do an analysis of data to make better decisions, to re-forecast or otherwise plan accordingly." ... And the company's struggle to build and maintain accurate analytical models distracts finance staff from higher value, analytical activities. "It's a hellish process that gets more complex every year,"... "If we take an annual salary for someone and spread it over 21 business days in a month, a simple change to 22 business days requires us to change a ton of formulas. We've also had to endure errors, where we've added up sales for all the branches, but we're undermined by the one branch that didn't extend the formula to reach enough rows and missed a whole person's salary." McArthur says that each year the model gets more complex, and "we introduce more potential for error. We need improved clerical accuracy on the budgeting side, where the numbers always add up. And on the forecasting side we need better analysis tools for looking at the business."... "We hit the month of May and realized there was no way we would make the forecast and cut our sales plan back almost 10 percent." C.F. Martin's executive team needed to immediately determine the overall financial impact of the reduced sales plan. "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."

Interview subjects say poor data integrity and accuracy affect their satisfaction with planning, budgeting, and forecasting...

Category: C++ Quant > Fix the Job You Got > Opportunity vs Trap > Success Breeds Failure

No comments:

Post a Comment