Following up on our last lesson, where we mastered working backward to a target using Goal Seek, we now address its main limitation. While Goal Seek is excellent for finding one specific input value, financial analysis often requires you to understand the impact of changing multiple assumptions at once. What happens to profitability if sales grow faster than expected, but costs also increase? Answering this type of multi-variable question is precisely what today's topic is about.
This lesson introduces Scenario Manager, another powerful "what-if" analysis tool in Excel. You will learn how to create, store, and compare different sets of financial assumptions—such as best-case, worst-case, and most-likely scenarios. This skill is fundamental for robust financial modeling, budgeting, and strategic planning, allowing you to present a comprehensive view of potential business outcomes.
From One Variable to Many: Introducing Scenario Manager
In the previous lesson, we used Goal Seek to find the single input needed to achieve a specific output. Scenario Manager works in the other direction and on a larger scale. You define different sets of input values, and Excel calculates and presents the resulting outputs for each set.
Unlike Goal Seek which changes one cell, or Data Tables which can handle one or two, Scenario Manager allows you to change up to 32 input variables simultaneously. This makes it the ideal tool for comparing complex situations, a common task in your field of accounting.
Using Excel's Scenario Manager for decision-making
This article from the Journal of Accountancy provides an excellent introduction to Scenario Manager and its role in financial decision-making.
Read the first five paragraphs, starting from the beginning. Pay close attention to how it contrasts Scenario Manager with Goal Seek and Data Tables.
To access Scenario Manager, you'll follow the same path as you did for Goal Seek.
Step 1: Setting Up the Model and Naming Cells
Before using Scenario Manager, you need a financial model where your key result cells are driven by formulas that depend on your input cells. A crucial best practice is to name your input and result cells. While optional, this step makes the final summary report infinitely more readable because it will use names like "Unit_Price" instead of cell references like "$B$2".
The following video provides a concise demonstration of both setting up a simple model and naming the cells.
Excel Scenario Manager for budgeting and forecasting by Chris Menard
This video from Chris Menard clearly demonstrates the preliminary steps for using Scenario Manager effectively.
First, watch the initial setup from the beginning to understand the distinction between input cells and dependent (formula-driven) cells. Next, watch the section on naming cells using the "Create from Selection" feature. This is the key technique for making your scenarios understandable.
Step 2: Creating and Storing Scenarios
With your model prepared and cells named, you can now begin creating scenarios. A common approach is to create a "Base Case" or "Current" scenario with your initial assumptions, and then add alternative scenarios like "Optimistic" (or Best Case) and "Pessimistic" (or Worst Case).
For each scenario, you will:
- Provide a name (e.g., "Best Case").
- Confirm the Changing cells (your inputs).
- Enter the specific values for those inputs for that particular scenario.
This video from Chris Menard continues from the previous clip and walks you through this exact process.
Excel Scenario Manager for budgeting and forecasting by Chris Menard
This part of the video demonstrates the core workflow of adding multiple scenarios to the Scenario Manager.
Watch the segment from this point. Observe how he adds an "Original" scenario first, and then adds "Higher price" and "Lower cost" scenarios by changing the values in the dialog box.
Once you have created your scenarios, the Scenario Manager dialog box will list them. You can click on any scenario and press "Show" to see its effect on your worksheet instantly.
Step 3: Generating the Scenario Summary Report
While toggling between scenarios is useful, the true power of this tool lies in its ability to generate a Scenario Summary report. This creates a new, neatly formatted worksheet that displays the input values and resulting outputs for all scenarios side-by-side, making comparison and analysis straightforward.
Using Excel's Scenario Manager for decision-making
This section of the Journal of Accountancy article explains how to create the summary report.
Read the section titled Side-by-side comparison. Pay attention to how you must select the result cells (e.g., Gross Profit, Net Profit) that you want to see in the summary. The screenshot provided shows what a clean, effective summary looks like.
The resulting report is static. If you go back and edit a scenario, you must generate a new summary report to see the changes reflected.
Best Practices for Effective Analysis
To ensure your scenario analysis is robust and professional, it's important to follow some key principles. These practices elevate the analysis from a simple mechanical exercise to a credible tool for strategic decision-making.
What-If Analysis in Financial Modeling
The Corporate Finance Institute provides a great checklist for conducting high-quality what-if analysis.
Please read the section titled Best Practices. Focus on the five key tips provided: Start with Clear and Realistic Assumptions Use Simple and Flexible Models Validate Your Results Run Multiple Scenarios Document and Communicate Your Findings
Adhering to these principles will ensure that your analysis is not only technically correct but also strategically valuable, which is essential in a professional accounting environment.
Conclusion
In this lesson, you've moved beyond single-variable analysis and learned how to manage and compare multiple sets of assumptions using Scenario Manager. This is a cornerstone of financial modeling, enabling you to analyze the potential impact of different economic conditions or business decisions on financial outcomes.
Key Takeaways:
- Scenario Manager is used to create, store, and compare multiple sets of input values to see their collective impact on formula-driven results.
- It handles up to 32 changing cells, making it ideal for complex what-if analysis.
- A critical best practice is to name your input and result cells before creating scenarios to produce a clear and readable summary.
- The Scenario Summary report provides a powerful side-by-side comparison of all scenarios, which is invaluable for presentation and decision-making.
So far, we have explored "what-if" analysis by changing assumptions. But what if you want to project future performance based on historical data? In our next lesson, we will delve into forecasting by learning to use the FORECAST.LINEAR function to create data-driven predictions.