Spreadsheet for Planning, Forecasting & Budgeting (Advanced Excel)

Course Objectives

  • 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

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