August 21, 2013
The Hidden Costs of Running Your Planning Process in Excel
Posted by Chris Frey
Our firm has many small-to-medium business clients. Often, we get the question of whether it is feasible for them to let go of Excel as their primary means of forecasting, replacing it with purpose-built software.
Many think they are not large enough to justify buying software to aid in their planning process. Clients often believe Excel is cheaper. It may seem this venerable program is the way to go because it is already owned and everyone is familiar with it.
Yet there are hidden costs to keeping a complex budget process in Excel. Here are some of the hidden costs we’ve observed.
Only one person knows the workbook
As is often the case, there is one person in the company who built the workbook and understands it. Often, the workbook may have a name, such as Joe’s workbook, or The Beast—you get the idea. What happens when they leave the company and carry that knowledge with them? Why not use a system where we have multiple users with access who can act as back-up?
Versioning is hard to control
One of my clients received budgets from about 30 branch offices. These budgets had to be manually pasted into a master workbook. And the branches often sent in multiple versions that had to be updated to the master. Every so often, a branch would make slight modifications to the workbook structure, which led to tedious work tracking down the resulting errors. Planning software has automatically enforced version control as one of its basic features.
Lack of standardized workbooks
Users responsible for various aspects of a plan such as revenue, operating expenses and personnel usually have their own way of planning these areas. This creates a challenge for those responsible for combining these inputs into the corporate plan. They must be extra careful to understand the variety of workbooks that input into the corporate plan, to avoid misunderstandings about level of completeness, assumptions and a variety of other pitfalls. Quality control is tough—how can I necessarily know if a workbook is correct if I did not build it myself? This could be alleviated by having all persons with planning responsibility use the same system and control how they use this system.
Hard to roll data forward/update
We’ve seen some very elaborate workbooks. If you can dream it up, you can do it in Excel. In fact, there is almost nothing you can’t calculate or display in Excel. But elaborate workbooks have a downside. It can be a major exercise to update the workbook when we wish to re-forecast. Another one of my clients built their annual operating plan in Excel by soliciting input simultaneously from 40 department heads and 50 Project leaders. Yet this matrix organization could only manage to do this once a year because it was so time consuming. We shouldn’t let forecasting get so tedious that it prevents us from frequently reforecasting. Instead, use a system that automatically rolls forward forecasts, using the last cycle’s forecast as a starting point.
Difficult to mesh actuals with plan
Time flies. It seems like we just finished closing last month and now we have new actuals again. Of course we want to update our forecasts with attained actuals, but this can be hard to do even for smaller businesses. We would need to paste in these actuals, check the workbook to ensure they were brought in correctly and verify they replaced forecasted figures. A good budgeting system automatically incorporates actuals into a forecast.
Challenges of rolling up departments
How do we know we received and included workbooks for all departments which roll up to the corporate plan? How do we know they have been updated? How do we ensure the whole is the sum of all the parts? This is a major issue, and one I suspect causes many hours of exertion for finance staff. Yet all this checking and re-checking comes at a high cost. This could be improved by doing our planning in a database system which automatically ensures subsidiaries add up to the corporate total.
Excel budgeting can be problematic, yet we can leverage it for maximum effectiveness by using it in conjunction with a purpose-built planning, budgeting and forecasting system. Let this system do what it does best by handling all the routine chores that can make planning difficult—let Excel be our presentation layer and handle all the specialized tasks it was made to handle.