What-If For 3 Or More Variables

February 15, 2022 - by Bill Jelen

Problem: The previous trick is cool, but what if I have three or more variables to change?

Strategy: If you have 3 variables to change, make many copies of the worksheet in the above example and change the third variable in each copy of the table.

If you have 3 or more variables, you can reluctantly use Excel’s Scenario Manager as described in this topic. If your manager has $99 in the budget, you can instead buy the MrExcel.com Monte Carlo Manager to handle multiple variable scenarios easily.

Excel’s Scenario Manager is found in the What-If dropdown of the Data ribbon tab. The tool will let you specify any number of input variables and any number of output variables. For each scenario, you have to type the input variables into a dialog box. The Scenario Manager will then produce a report of all the scenarios.

1. This step is optional, but the output report will be more meaningful if you name all of the input cells and all of the output cells.
2. Select Data, What-If Analysis, Scenario Manager.
3. Click the Add... button in the Scenario Manager dialog.
4. Type a name for the scenario using the current values. Specify the input cells by clicking the first cell and Ctrl+clicking the other input cells. Click OK.

What-If Data Tables handle 1 or 2 input variables. If you have three or more, you are supposed to use the Scenario Manager. But scenarios are added one at a time and not fun to build. — Figure 333. Specify the input cells.

5. Excel will show you the current input values. These are probably correct for the first scenario.

Building a new scenario using the Scenario Values dialog. You have to enter four numbers: Price, Down Payment, Term, and Rate. — Figure 334. Verify the values for the original scenario.

6. Click Add. You will go back to the Add Scenario dialog.
7. Enter a new scenario name and description. Click OK. You will go to the Scenario Values dialog.
8. Enter new input variables for this scenario.
9. Repeat steps 6 to 8 for each additional scenario. When you are done entering scenarios, click OK instead of Add in the Scenario Values dialog.
10. In the Scenario Manager dialog, choose any scenario and click Show to show that scenario in the worksheet.

After setting up four scenarios, the Scenario Manager dialog shows Original, Longer Term, Smaller Down, Smaller Loan, and Stretch. Each scenario shows which cells will change and shows a comment. Choose a scenario and then click Show. — Figure 335. Choose a scenario and click Show.

11. To see a comparison of all scenarios, click Summary.
12. In the Scenario Summary dialog, specify the output cells to include in the report.

In the Scenario Summary dialog, choose either a Summary or a Pivot Table. Choose which cells are the result cells to include in the report. — Figure 336. Specify output cells.

13. A new worksheet is inserted. It will contain a column for each scenario. Input cells appear in grey. Output cells appear below.

The Scenario Summary report shows the values for Changing Cells and Results Cells. Each scenario fills a new column in the report. — Figure 337. The summary report compares the scenarios.

Additional Details: Group and Outline symbols appear around the report. Clicking the minus symbol above column C will hide the notes in rows 14:16 and produces a cleaner report. Clicking the plus symbol to the left of row 3 will reveal the description that you entered for each scenario. Minus symbols next to row 5 or 10 hide the input or output section of the report. The minus symbol above the final column hides all of the scenarios, leaving only the current values.

The scenario manager is relatively difficult to use because you must build each scenario by typing the values into a dialog. I wrote the MrExcel Monte Carlo Analysis add-in to allow you to specify the scenarios by saying that Price should go from 175,000 to 325,000 in $25,000 increments. Using this method, you can build dozens or hundreds of scenarios very quickly.

This article is an excerpt from Power Excel With MrExcel

Title photo by Keila Hötzel on Unsplash