Budgeting and Forecasting in Excel

This practical course covers issues such as using budgets to measure staff performance, linking budgets to the strategic planning process and a comparison of budgeting techniques.  We explore the difference between budgets and forecasts, and learn how to create your own budget, rolling forecast, variance report and budget scenarios in Excel. 

Each participant on this course receives a hard copy of the book Using Excel for Business Analysis.   Re-using your copy, got several participants or want to book on multiple courses?  Consider making a bulk booking and save.

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheet users. It is designed for users who do use Excel on a regular basis, and are comfortable with using its tools and functions.

At minimum, it is assumed that participants will know how to:

• Navigate confidently in Excel
• Use absolute cell references (e.g. =$A$1) 
• Create and use advanced Excel tools and functions on a regular basis

If you are unsure about your level of Excel, you might also consider joining the online Excel School prior to coming on the course.

Software used

Public courses are run in a computer laboratory with workstations provided. Participants may bring their own laptops if they prefer.   The course is demonstrated using Microsoft Excel 2013, but participants can use Excel 2007 or 2010 if they prefer.  Course materials contain instructions for different versions, and Excel 2011 for Mac users are also catered for. Those who would like to use a Mac are most welcome to bring their own laptops.

Course Format

This one-day course is delivered in two parts; the first part of the day covers the theory of budgeting and forecasting within organisations.  Issues such as budgeting to organisational objectives, level of granularity and selecting the correct budgeting tool will be discussed.  Students are encouraged to share their own experiences with the class.  
In the second part of the day, participants will build their own budget and forecasting models to bring a practical application to the skills they have learned.   The model will include a budget, rolling forecast, variance report as well as scenarios and sensitivity testing.  During the course, participants will create their own Excel budgets & reporting analysis to take away with them.  Each participant is provided with a USB memory stick on which they will be able to take their files away for future reference.

Course Content

Part One: Budgeting and Forecasting Theory

Budgets and Strategic Planning

• Linking Budgets to business objectives
• Budgeting to achieve organisational objectives
• How to turn measurement into management

Using Budgets as Performance Management

• The impact of budgets on organisational performance
• Successfully integrating forecasting and budgeting as essential tools for measuring performance
• Translating the organisation’s values into measures and targets

Management Reporting

• Using reporting to promote accountability
• Variance analysis
• Budgeting and forecasting as a planning and management tool
• Assessment and selection of appropriate systems

Budgeting Techniques 

• Historical-based versus Zero-based budgeting
• Top down vs bottom up
• Activity Based Budgeting
• Differences between a static, flexible and rolling budget
• Budgeting for capital expenditure vs. operational expenditure

Limitations of budgets 

• Estimations and rigidity
• Staff perceptions and changing environments
• Dysfunctional budgeting behaviour

Budgeting “Soft” Skills

• Achieving buy-in from stakeholders
• Involving non-financial management of the organisation
• Techniques in getting your budget approved
• Managing conflicting priorities and demands
• Setting timelines

Part Two: Budget and Forecast Modelling

Building a Budget

• Revenue calculation
• Headcount and other staffing costs
• Operational expenses
• Capital expenditure

Building a Forecast

• Incorporating budgets, actuals and forecast
• Creating a “rolling” forecast with live data

Reporting

• Creating an actual vs budget vs forecast report
• Building a variance analysis with “alert flags”

Scenarios

• Stress-testing, break-even and sensitivity analysis
• Creating and displaying budget scenarios
• Shortcuts for data manipulation in Excel

Comments from past students

“An excellent combination of technical and practical applications to financial forecasting, control and budgeting.”
"The instructor has years of experience and was able to solve any problems or questions we had in the course."
"I was highly impressed with facilities, equipment and material provided. This course should be a core requirement for anyone considering undertaking an analytical role. I feel the course has provided me with the confidence to address stakeholders in discussing forecasting and budgeting - especially the procedure and method of approach. I'll be back for another course!"