Spreadsheet Skills for Planning, Forecasting and Budgeting

In this course we will leverage the power of Excel to demonstrate how to improve your forecasting, planning and budgeting. By developing better spreadsheets and models, planning is more accurate, forecasting is more precise and budgeting is more attainable.

Delegates will be able to:

• Fully 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
• Properly use some of the most advanced spreadsheet techniques
• Know when to take a chance and when not to

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 key board shortcuts

Proper Planning

• Introduction to planning
• What-if analysis
• 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

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

Beyond Budgeting

• What is a 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

Putting it together – building the comprehensive model

• Build your planning model
• Build your forecasting model
• Build your budgeting model

TRAINING METHODOLOGY

The training methodology combines lectures, discussions, group exercises and illustrations. Participants will gain both theoretical and practical knowledge of the topics. The emphasis is on the practical application of the topics and as a result participant will go back to the workplace with both the ability and the confidence to apply the techniques learned to their duties.