Graphed LogoGraphed
Login

What-If Analysis in Excel Scenario Manager

Cody Schneider•

Tired of copying your spreadsheet over and over just to tweak a few numbers and see how your bottom line changes? Excel's What-If Analysis tools are designed to solve precisely this problem, and Scenario Manager is the most powerful of the bunch for comparing multiple outcomes at once. This article will walk you through setting up, comparing, and summarizing different business scenarios to make more informed decisions without the manual mess.

Understanding What-If Analysis in Excel

In simple terms, What-If Analysis is the process of changing the values in certain cells to see how those changes affect the results of formulas in your worksheet. It lets you answer questions like, "What happens to our profit if an ad campaign drives 20% more sales?" or "How will our project timeline be affected if our key supplier is a week late?"

Excel groups three powerful tools under its "What-If Analysis" button:

  • Goal Seek: This tool works backward. You know the result you want from a formula, and Goal Seek figures out what a single input needs to be to get you there. For example, "What sales price do we need to hit a $100,000 profit?"

  • Data Tables: These are ideal for seeing how changing one or two variables affects a single formula. It generates a table showing all possible outcomes. For instance, you could see how different interest rates and loan terms affect your monthly payment.

  • Scenario Manager: This is the star of our show. The Scenario Manager is perfect when you need to change multiple input variables at the same time. It lets you create and save different groups of values (scenarios) and switch between them instantly. Think "Best Case," "Worst Case," and "Most Likely Case."

While Goal Seek and Data Tables are fantastic for specific uses, Scenario Manager excels at creating comprehensive models of potential futures, making it invaluable for business forecasting and strategic planning.

Why Choose Scenario Manager?

Picture this: you’re planning a digital marketing budget. You don't just want to know what happens if your Facebook ad budget increases. You want to see the combined impact of increasing the Facebook budget, decreasing the display ad budget, and a potential 5% jump in cost-per-click across Google Ads. Trying to model this by hand would involve saving multiple versions of the same file, which quickly becomes confusing.

Scenario Manager solves this by storing all these different possibilities right within your original worksheet. It's the best choice when:

  • You have more than two input variables that might change.

  • You want to define and label specific situations, like "Aggressive Growth Plan" or "Conservative Q4 Forecast."

  • You need to present a side-by-side comparison of these scenarios in a clean, professional report.

It helps you analyze the sensitivity of your models to different conditions, giving you a much clearer picture of potential risks and opportunities.

Step-by-Step Guide to Using Scenario Manager in Excel

Let's walk through a practical example. Imagine we're forecasting the profit for a new product launch. Our profit depends on several factors that could change: the number of units sold, the sale price per unit, and the variable cost per unit. We want to model three scenarios: a Best Case, a Worst Case, and an Expected Case.

1. Set Up Your Data Model

First, you need a clean data setup. Create a worksheet with your input values (the variables you'll be changing) and an output cell that contains the formula you want to analyze. It's crucial that your output cell is directly or indirectly dependent on your input cells.

Here’s a simple layout:

Input Variables (Changing Cells):

  • B1: Units Sold (value: 5,000)

  • B2: Sale Price Per Unit (value: $25)

  • B3: Variable Cost Per Unit (value: $10)

Constant Variables:

  • B4: Fixed Costs (value: $20,000)

Result (Formula Cell):

  • B6: Total Profit

In cell B6, you would enter the formula for calculating profit. In this case:

With our initial values, the profit in B6 would display as $55,000.

2. Open the Scenario Manager

Now that your model is set up, head to the Data tab on the Ribbon. In the Forecast group, click on What-If Analysis, then select Scenario Manager from the dropdown.

3. Create Your First Scenario (e.g., "Expected Case")

The Scenario Manager dialog box will be empty. Let’s add our current numbers as the "Expected Case."

  1. Click the Add... button.

  2. In the "Scenario name" field, type Expected Case.

  3. In the "Changing cells" field, select the cells B1, B2, and B3. You can select them together by dragging your mouse or by clicking B1, holding down the Ctrl key, and then clicking B2 and B3.

  4. You can add a comment to describe the scenario if you'd like. Click OK.

  5. A new window, "Scenario Values," will appear, showing the current values in your selected cells. Since we're creating our base scenario, these are already correct. Just click Add... to continue creating the next scenario without closing the main window.

4. Add More Scenarios ("Best Case" and "Worst Case")

Let's create the "Best Case" scenario next.

  1. After clicking "Add" a moment ago, the "Add Scenario" box will reappear. Type Best Case in the "Scenario name" box. The "Changing cells" reference should already be correct ($B$1:$B$3), so just click OK.

  2. The "Scenario Values" box now appears. Let's enter optimistic figures: change Units Sold to 8000, Sale Price to $30, and Variable Cost to $8.

  3. Click Add... one more time to create the "Worst Case." Follow the same steps, naming this one Worst Case. For the values, let's enter pessimistic numbers: Units Sold of 2500, Sale Price of $20, and Variable Cost of $14.

  4. Once you've entered the values for the worst case, click OK. You will return to the main Scenario Manager dialog box, where you will now see all three of your scenarios in a list.

5. Switch Between Scenarios

You can quickly test different scenarios. With the Scenario Manager window open, double-click on any of the scenario names. The values in your spreadsheet will update with that scenario’s data. For example, click on "Worst Case" and then the "Show" button. The numbers for units sold, price, and costs will automatically change in cells B1, B2, and B3, and your profit calculation in cell B6 will update. This lets you ask "what-if" questions without manually retyping anything.

Generating a Scenario Summary Report

Switching between scenarios is great for quick analysis, but the real power of Scenario Manager is its ability to generate a professional summary report, which shows all your scenarios side-by-side on a single page. This presents data comparisons and details that are always readable and understandable at a glance.

How to Generate the Report

  1. Open the Scenario Manager again if you’ve closed it. Click on the "Summary" button. The "Scenario Summary" dialog box will appear. Ensure "Scenario Summary" check is selected. In the "Result cells" field, select the cell that contains your key output that you want to compare. In our case, it's cell B6, the profit formula. Click OK.

  2. Excel will create a brand-new worksheet named "Scenario Summary." This sheet presents a formatted table that shows your current values (the initial numbers from your sheet) along with the values for each of your scenarios: Best Case, Worst Case, and Expected Case. All the inputs and respective results are clearly outlined, so you can easily compare outcomes at a glance.

Tips for Getting the Most Out of Scenario Manager

Scenario Manager is a fantastic feature, but a few tips can make your models even more powerful and easy to manage.

Name Your Cells

It's the best thing you can do to improve your scenario summary. Instead of seeing cell references like $B$1 and $B$6, the summary page will use defined names. Before you build your scenarios, ensure your input cells are labeled using Define Names. So, the summary will say "Units_Sold" instead of $B$1, which is much more readable.

Keep Organized

Before building any scenarios, make sure your data model is clean and well-structured. Clearly label separate inputs and constants (so you know what they do), and follow a standard to avoid confusion when selecting "Changing Cells" and "Result Cells."

Edit Scenarios

The Scenario Manager dialog box allows you to edit, delete, and merge scenarios as you need, making it easy to refine and fine-tune your analysis. You don't have to recreate everything from scratch if one needs updating.

Understand Limitations

The Scenario Manager is incredibly useful but has a limitation of changing up to 32 cells in a single scenario. While this is enough for many business cases, it's good to know the boundaries you’re working within.

Final Thoughts

Excel's Scenario Manager is a powerful yet simple tool for modeling different business outcomes without constantly shuffling your workbook. By setting up scenarios like Best Case, Worst Case, and Expected Case, you can instantly summarize and report outcomes side by side. It’s an effective time-saving tool for anyone looking to better explore and forecast results, and its integration is especially helpful in simplifying decision-making within business tools like Google Analytics and Salesforce.

Use Graphed to help manage your scenarios efficiently. It's a platform designed to keep you organized and save time that you can invest in analysis and informed decision-making.