The budgeting process is often criticised for being time-consuming, adding far too little value and being out of date before the printer ink is dry. Arguments are also made against forecasting: either the outturn result is known (in which case why bother forecasting in the first place?) or the uncertainty means any forecast is meaningless. Various suggestions for overhauling the process have been proposed, most notably in Beyond Budgeting, by Hope and Fraser which was published way back in 2003.
But still, the sense of discipline associated with budgeting has not gone away – even as markets have evolved and competition has increased. And at F1F9 we’ve observed some evolution in the work we do with our clients: a budget is no longer a one-off exercise. Budgets are subject to re-forecasting as new information comes to light. Variance analysis remains important: outturn results compared with the latest forecast, the interim forecast, and the original budgeted numbers.
We’ve also noticed that the level of forecasting undertaken has become more granular. Plenty of businesses forecast at the level of detail contained in their general ledgers, which allows for much more detailed tracking and analysis.
Preparing budgets using Excel spreadsheets
Excel-based budgeting financial models are still common and are used to set annual budgets. As outturn results appear, the requirement for variance analysis arises. At 3 months in, perhaps you need to re-forecast and so a new budget is produced based on those first few months outturn results. Repeat once 6 months have passed and then again after 9 months.
A reminder: the targets should be challenging but not seen as impossible. Keep the critical players motivated and reassured that the process is designed to change as circumstances change. The budget should take proper note of what happens to actuals – and on a timely basis.
Excel-based budgeting models need to be carefully designed if they are to keep up with the rapid changes described above. Be prepared for multiple copies that the model should be able to produce and store for reference.
Here are some key features of the budgeting models we build at F1F9.
Figure 1: Actuals & Budget data sheets
The design should cover variance analysis requirements. Flexibility is crucial: variance reports should show actual results compared with any budget (original, 3 months, 9 months, for example) as well as showing key differences between different budgets.
Figure 2: Variance calculation
A clear audit trail to source data remains important. That will let you dig into the details of the variance and understand where issues lie. It should be easy to follow the forecast financial modelling back to the details of the general ledger.
Well-defined inputs / assumptions sheets will help you to re-forecast as required.
Figure 3: Inputs sheet structure
Bringing actuals into the budget finance model should be seamless and quick with minimum manual intervention. That includes being able to deal with change at the source data level: new general ledger codes, for example, or journal entries that cause changes in the trial balance. A budget model needs sufficient checks to make sure that all such items are picked up and properly alerted. And that’s against the pressure arising during reporting cycles.
Figure 4: Data dump sheet
At F1F9, we have developed a tried-and-tested approach to designing and building such budget models (we think of them as enterprise, reporting and analysis (“ERA”) models). Our clients use the models we build for years since they are designed with user-friendliness in mind. That’s not to say that more advanced technology has no role to play: we make careful and well-judged use of macros and power queries to help support efficient reporting cycles. So the budget model becomes part of the solution.
While budgeting processes remain an important part of most companies’ financial management, we’ll continue to provide robust, well-designed and easy-to-use enterprise models to support them.
If you are interested in a demo or would like to discuss how we can help with your requirements, please contact us.
