Excel: What-If For 3 Or More Variables

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.

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

   

2. 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.

   

3. 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.

   

4. 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.

   

5. 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.

   

6. Adjust the group and outline symbols.

   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.