Introduction

Planning, forecasting, and budgeting are essential for business success, and Excel® offers powerful tools to enhance these processes. This course teaches participants how to develop accurate models, perform what-if analyses, and optimize plans efficiently using techniques like goal seek, scenario manager, and solver. Designed for Excel® ’03 and ’07, the course includes practical demonstrations and templates for hands-on learning. Bring your laptop and gain skills to improve precision, save time, and drive business performance.

Objectives

• 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 & when not to

• Learn to use & expand a range of Excel® models (provided)

Process

The training comprises teaching sessions covering each topic area with the theory blended with practical application. Additionally, real-world examples & case studies will be used so that the Delegates will be able to apply these techniques immediately. Delegates will use the provided Excel® templates to apply their new knowledge case studies to reinforce the concepts.

Benefits

• Having Experts in-house to develop advanced decision support models

• Increased productivity of personnel involved in planning, forecasting & budgeting

• Improved performance of plans developed into budgets

• Better ways to plan & measure results of forecasts

• Realizing better options when facing forecasting choices

• Better integration between functional areas leading to better decisions

Results

• Developing an improved understanding of how well prepared Excel® models to benefit the corporate in today’s challenging world.

• Really understanding the risks & rewards of various planning decisions

• Being able to calculate the impact of alternative inputs on critical outputs

• Understanding to cost/benefit of every decision

• Enhancing their knowledge with the use of the models in the case studies

• Significantly increasing their Excel® analysis skills

Outlines

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)