Spreadsheet for Planning, Forecasting & Budgeting (Advanced Excel) Event, 17.Feb.2025

Introduction

Course Objectives of Spreadsheet for Planning, Forecasting & Budgeting (Advanced Excel)

• Understand the various Excel tools available to perform planning, forecasting & budgeting
• Perform sophisticated “what-if” scenarios to improve decision making
• Know what Excel techniques to use in a given situation
• Use the most advanced spreadsheet techniques
• Learn to use & expand a range of Excel models
• Understand the risks & rewards of various planning decisions
• Calculate the impact of alternative inputs on critical outputs
• Understand the cost/benefit of every decision
• Enhance their knowledge with the use of the models in the case studies

Course Outlines of Spreadsheet for Planning, Forecasting & Budgeting (Advanced Excel)

Day 1

Spreadsheets using Excel

• Worksheet fundamentals
• Using the F4 key for Relative & Absolute cell references
• Expose ALL formulas in a worksheet using the built-in function for faster review
• Naming cells & ranges for quicker, more accurate work
• Freeze panes, columns & rows for easier viewing while scrolling
• Protecting cells & ranges to stabilize models
• Function wizard benefits
• Linking cells from various worksheets to reduce errors
• Secret keyboard shortcuts

Day 2

Professional Planning

• Introduction to planning
• What-if analysis:
  • Scenario analysis techniques
  • Sensitivity analysis using spinners & scroll bars
• Economic Order Quantity (EOQ)
• Build out Data tables to see an entire range of options
• 1-way Data tables
• 2-way Data tables
• Maximizing vs. optimizing techniques
• Utilizing The Goal Seek command to calculate a reverse equation
• Using Solver to calculate optimal product mix, distribution problems & capital budgeting

Day 3

Fantastic Forecasting

• What is forecasting?
• Qualitative techniques & their uses
• Quantitative techniques & their uses
• Smoothing techniques
• Regression analysis
• Time series analysis
• Forecasting growth rates for verification
• Internal (IGR)
• Sustainable (SGR)
• MIN and MAX functions
• MEDIAN, MODE & AVERAGE functions
• GEOMEAN to calculate Average Growth Rate (AGR)
• When to use RANK, QUARTILE & PERCENTILE

Day 4

Proper Budgeting

• What is the budget?
• How to build a simple budget
• Build a Flexible Budget Model
• Build out a complete budget
• Operating budget elements
• Financial budget elements
• Linking them
• What is the Variance Analysis report & why is it important to improve your budget
• Calculating variance analysis to see what must be changed

Day 5

Building The Comprehensive Model (Workshop)

• Build your planning model
• Build your forecasting model
• Build your budgeting model
• Link these together