Spreadsheet Skills for Planning, Forecasting & Budgeting

Day 1

Introduction to 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

• Case study: create, build & protect a reusable template

Day 2

Proper 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

• Case study & template for using Solver to plan the Optimal product mix

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

• Case study & template: Use the GEOMEAN function to calculate your AGR

Day 4

Beyond 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

• Case study & template: Build a complete budget

Day 5

Putting it together – building the comprehensive model

• Build your planning model

• Build your forecasting model

• Build your budgeting model

• Link these together

• Leverage optimization through a capital structure (Case study Example)

• Bond buybacks (Case study Example)