Excel: Scenario Planning and Analysis

Course details

A multitude of factors can affect the trajectory of your business. Learning how to document, summarize, and present projected business scenarios can help provide a basis for insightful business analysis, and help you evaluate the impact of various choices on your organization. In this course, explore techniques for analyzing a series of business scenarios using the flexible and powerful capabilities built into Excel. The course starts with a chapter on the art and craft of scenario planning before turning to the technical capabilities of Excel. Tools covered include row grouping to show and hide detail, PivotTables, and functions for using the normal distribution.

Learning objectives
Designing a scenario-planning exercise
Estimating scenario plausibility and outcomes
Establishing parameter value ranges
Calculating the standard deviation of a dataset
Indicating the probability of a scenario value occurring
Walking through a scenario presentation
Performing retrospective analysis using a PivotTable
Changing PivotTable summary operations

Concepts

0. Introduction

01 - Welcome
02 - Using the exercise files

1. Introducing the Art of Scenario Planning

03 - Describe scenarios and scenario planning
04 - Explore the link between scenarios and storytelling
05 - Design a scenario-planning exercise
06 - Estimate scenario plausibility and outcomes

2. Manage Scenarios in Excel

07 - Define scenarios using static values
08 - Define scenarios using percentages of base values
09 - Select scenarios using data validation rules
10 - Show and hide details by grouping worksheet rows
11 - Walk through a scenario presentation

3. Establish Parameter Value Ranges

12 - Introduce the normal distribution
13 - Calculate the standard deviation of a dataset
14 - Indicate the probability of a scenario value occurring
15 - Estimate values using the triangular distribution
16 - Walk through a scenario presentation

4. Present Scenarios Using PivotTables

17 - Define PivotTable dataset
18 - Create and pivot a PivotTable
19 - Summarize PivotTable data using subtotals and grand totals
20 - Filter PivotTable data
21 - Change the values area summary operation
22 - Record a macro to recreate a PivotTable position
23 - Link a macro to a shape
24 - Walk through a scenario presentation

Conclusion

25 - Next steps

Excel: Scenario Planning and Analysis

Authors

Curt Frye