Advanced Budgeting & Forecasting (2-5 days)

Budgeting & Forecasting and Strategic Planning

• Overview of strategic planning
• How budgeting and forecasting links with strategic planning
• Levels of planning, budgeting and forecasting

Driver-based Budgeting & Forecasting

• The 4 primary types of cost and revenue drivers in organisations
• The 4 perspectives essential for considering driver-based budgeting
• The 3 key areas to be considered when formulating drivers

Activities

Group discussions

Budgeting and Forecasting Challenges

• The 6 key challenges with budgeting and forecasting
• The 6 limitations of budgets and forecasts
• The Forecasting Conundrum
• Dealing with high uncertainty in budgets and forecasts
• Gaming and politics in budget negotiations
• Ways that assist in overcoming budgeting and forecasting shortcomings

Activities

Group discussions & activities

Budgeting & Forecasting Techniques

• The 3 primary types of budget approaches
• The purpose, advantages, disadvantages and implementation of rolling forecasts
• The difference between forecasting and targets and the role of each

Activities

Individual exercise in rolling budgets

Budgeting and Forecasting in Medium/Large Organisations

• Typical budgeting and forecasting processes
• Architecture for budgeting and forecasting models using Excel or specialised software systems
• Approaches for common budgeting and forecasting tasks:
  • Connecting to data sources and importing data
  • Consolidating multiple data files
  • Variance analysis
  • Scenarios and sensitivities
  • Breakeven analysis

PowerQuery and PowerPivot (built into Excel 2016+) provide amazing functionality for creating and maintaining budgets and forecasts.

To try and produce a budget or forecast in Excel without using these tools would be far more difficult and time-consuming.

As such, the remainder of the course is focused on real-world techniques that utilise the amazing capabilities of PowerQuery, PowerPivot and Power BI to build and present a budget or forecast model.

We will focus on using PowerQuery and PowerPivot in Excel however the same tools and techniques can be easily applied to Power BI Desktop as well with only minor adjustments.

Introduction to PowerQuery

• Techniques for cleaning up messy data e.g. data from a customer/supplier/partner
• How to transform data from one format to another
• How to integrate data sources that have different time scales and different formats e.g. daily transactions integrated with a monthly budget
• How to merge multiple queries together e.g. data from different accounting systems or charts of accounts
• How to read multiple files from a folder and easily combine them together e.g. data outputs from other systems or daily/weekly transaction files
• How to use mapping tables to translate codes to readable names, or allow grouping of accounts/customers/products to create hierarchies that allow different views of your budget e.g. by country, state, or city; or by product group, product class and individual product etc.

Introduction to PowerPivot

• What is PowerPivot and how does it work?
• Key concepts for understanding and power of PowerPivot and Excel’s data model
• How to apply this to budgets and forecasts
• Introduction to writing formulas in DAX to create calculated columns and measures e.g. for the current year budget, reforecast figures, year-to-date numbers, comparison to last year/month, % or $ variance between actual and budget, reporting actual & forecast in one report etc.
• Comparing PowerPivot techniques to Excel: what’s the same and what’s different?

Putting it all Together

• Creating reports and charts with drill-down functionality that allow users to quickly find the information they’re looking for
• Creating dashboards where every element on the page is controlled by one click
• Transferring your budget/forecast model into Power BI Desktop and using this to present your results in a very graphical, intuitive way

The End Result

Participants will apply their knowledge to create a very flexible, powerful budget/forecast that incorporates the following features:

• • Import actuals from an accounting system up to whatever month is available
  • Import budget data from templates that have been completed by different departments
  • Combine actuals with budget/forecast data to produce:
    • $ and % variance to budget reports, automatically highlighting significant variances
    • Full-year forecasts with actuals to a point then budget or forecast numbers for future months
    • Comparison to last year and last month to identify trends
    • Charts and dashboards of the results

• Ability to view and analyse budget vs actual results at different levels of consolidation:
  • Day, month, quarter, year
  • By country, area and individual project
  • By company, department, and cost centre

How to build-in sensitivity analysis so that key budget inputs can be adjusted graphically and results viewed in real-time.

The model created is flexible and can be adapted to suit almost any organisation.

Introduction to Budgeting/Forecasting Systems

• Compare and contrast to an Excel-based budgeting/forecasting system