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
