Budgeting and Forecasting in Excel

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

Prerequisites

This course is designed for users who use Excel fairly regularly, and are comfortable with using simple functions. It is assumed that the user will know how to create simple formulas, and link between workbooks.

Software used

Public courses are run in a computer laboratory and workstations are provided. Course materials are written with both Excel 2007 and 2003 instructions.  The course is demonstrated mostly using Microsoft Excel 2007 and participants can choose which version of Excel they prefer. If participants are more comfortable using their own laptops, they are most welcome to bring their own equipment. PCs are used during this course, so Apple Macintosh users may prefer to bring their own laptops.

Course Format

This course is delivered in two parts; the first 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. On the second day, students will build their own budget and forecasting model 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.

Course Content

Day 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

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

Day 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