When creating a financial forecast or a project budget in Microsoft Excel, you often need to present multiple different outcomes. For example, you might want to show a “Best Case” scenario with high sales and low costs, a “Worst Case” scenario, and a “Most Likely” scenario.
Most users accomplish this by manually copying and pasting their entire data table onto three different worksheet tabs, which creates a bloated, difficult-to-manage file. A much more elegant solution is to use the Scenario Manager. This feature allows you to save multiple sets of variables within a single data table and instantly snap between them to see how they affect your final calculations.
How to Create Your First Scenario
Before using the Scenario Manager, you must have a working formula that relies on specific input cells. For example, imagine cell B1 is “Revenue” (£10,000), cell B2 is “Expenses” (£5,000), and cell B3 is a formula calculating the profit (=B1-B2).
- Click the Data tab in the top ribbon menu.
- In the “Forecast” group, click What-If Analysis.
- Select Scenario Manager… from the dropdown menu.
- The Scenario Manager dialogue box will appear. Click the Add… button.
- Type a descriptive name for your first scenario, such as “Most Likely”.
- In the Changing cells: box, click the arrow icon and highlight the input cells you want to experiment with (in our example, highlight B1 and B2). Do not select your formula cell (B3).
- Click OK.
- A new window will appear asking for the exact values for this specific scenario. Enter your baseline numbers (e.g., 10000 for B1 and 5000 for B2). Click OK.
How to Add Alternative Scenarios
Now that your baseline is saved, you can add your alternative projections.
- In the Scenario Manager window, click Add… again.
- Name this scenario “Worst Case”.
- The changing cells will automatically remain the same (B1 and B2). Click OK.
- Enter your pessimistic numbers (e.g., 7000 for Revenue and 6500 for Expenses). Click OK.
- Repeat this process to add a “Best Case” scenario.
How to Switch Between Scenarios
Once your scenarios are built, the Scenario Manager window will list “Most Likely”, “Worst Case”, and “Best Case”.
To view a different projection, simply click on the name of the scenario in the list and click the Show button at the bottom of the window (or double-click the scenario name).
The numbers in your actual spreadsheet will instantly change, and your Profit formula in cell B3 will immediately recalculate based on those new variables. You can rapidly snap back and forth between the different scenarios during a presentation without ever having to touch a worksheet tab or manually retype data.
How to Generate a Scenario Summary Report
If you need to show all your projections side-by-side to a client, you do not need to manually copy the data.
Inside the Scenario Manager window, click the Summary… button. Excel will automatically generate a brand new, beautifully formatted worksheet tab containing a summary table that displays the inputs and the final calculated results for every single scenario you created.