Excel 2016's Scenario Manager option on the What-If Analysis button's drop-down menu on the Data tab of the Ribbon enables you to create and save sets of different input values that produce different calculated results, named scenarios (such as Best Case, Worst Case, and Most Likely Case).
The key to creating the various scenarios for a table is to identify the various cells in the data whose values can vary in each scenario. You then select these cells (known as changing cells) in the worksheet before you open the Scenario Manager dialog box by clicking Data→What-If Analysis→Scenario Manager on the Ribbon or by pressing Alt+AWS.
This figure shows the Sales Forecast 2017 table after selecting the three changing cells in the worksheet — G3 named Sales_Growth, G4 named COGS (Cost of Goods Sold), and G6 named Expenses — and then opening the Scenario Manager dialog box (Alt+AWS).
You can create three scenarios using the following sets of values for the three changing cells:
Most Likely Case where the Sales_Growth percentage is 5%, COGS is 20%, and Expenses is 12%
Best Case where the Sales_Growth percentage is 8%, COGS is 18%, and Expenses is 10%
Worst Case where the Sales_Growth percentage is 2%, COGS is 25%, and Expenses is 20%
To create the first scenario, click the Add button in the Scenario Manager dialog box to open the Add Scenario dialog box, enter Most Likely Case in the Scenario Name box, and then click OK.
Remember that the three cells currently selected in the worksheet, G3, G4, and G6, are already listed in the Changing Cells text box of this dialog box. Note that Excel 2016 automatically selects the Prevent Changes check box under the Protection heading at the bottom of Add Scenario dialog box to prevent users from changing the values in these cells of the worksheet. If you want their values to be hidden as well, be sure to select the Hide option before you click OK.
When you click OK, Excel then displays the Scenario Values dialog box where I accept the following values already entered in each of the three text boxes (from the Sales Forecast for 2017 table), Sales_Growth, COGS, and Expenses, before clicking its Add button:
0.05 in the Sales_Growth text box
0.2 in COGS text box
0.12 in the Expenses text box
Always assign range names to your changing cells before you begin creating the various scenarios that use them. That way, Excel always displays the cells' range names rather than their addresses in the Scenario Values dialog box.
After clicking the Add button, Excel redisplays the Add Scenario dialog box where you can enter Best Case in the Scenario Name box and the following values in the Scenario Values dialog box:
0.08 in the Sales_Growth text box
0.18 in the COGS text box
0.10 in the Expenses text box
After making these changes, click the Add button again. Doing this opens the Add Scenario dialog box where you can enter Worst Case as the scenario name and the following scenario values:
0.02 in the Sales_Growth text box
0.25 in the COGS text box
0.20 in the Expenses text box
Because this is the last scenario that you want to add, click the OK button instead of Add. Doing this opens the Scenario Manager dialog box again, this time displaying the names of all three scenarios — Most Likely Case, Best Case, and Worst Case — in its Scenarios list box. To have Excel plug the changing values assigned to any of these three scenarios into the Sales Forecast for 2017 table, click the scenario name in this list box followed by the Show button.
After adding the various scenarios for a table in your spreadsheet, don't forget to save the workbook after closing the Scenario Manager dialog box. That way, you'll have access to the various scenarios each time you open the workbook in Excel simply by opening the Scenario Manager, selecting the scenario name, and clicking the Show button.