Corporate budgeting: understanding the complexities

Corporate budgeting in Excel - how we do it at F1F9

Author:

Ashish Yadav

Published:

29 Oct 2021

Comments:

0 Comments

Category:

Budgeting and reporting via Excel-based financial models

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, 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 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.

Actuals and budget data sheets

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 month, 9 month, for example) as well as showing key differences between different budgets.

budget variance calculation

Figure 2: Variance calculation

 

A clear audit trail to source data remains important. That will let you dig into the detail of the variance and understand where issues lie. It should be easy to follow the forecast model back to the detail of the general ledger.

Well defined inputs / assumptions sheets will help you to re-forecast as required.

inputs sheet structure

Figure 3: Inputs sheet structure

 

Bringing actuals into the budget 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.

data dump sheet

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 me.

Ashish Yadav
Ashish Yadav joined F1F9 in April 2012 and is now part of the leadership team in F1F9's India office. He has worked on a wide range of modelling assignments across many sectors including real estate, recruitment services, power and financial services. He specialises in designing and creating Excel based solutions for optimising the performance of corporate finance functions.