Switch between various sets of values by using scenarios

There are three kinds of What-If Analysis tools that come with Excel: Scenarios, Data Tables and Goal Seek. Scenarios and Data Tables take sets of input values and project forward to determine possible results. Goal Seek differs from Scenarios and Data Tables in that it takes a result and projects backwards to determine possible input values that produce that result.

Each scenario can accommodate up to 32 variable values. If you want to analyze more than 32 values, and the values represent only one or two variables, you can use Data Tables. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a Data Table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

In addition to these three tools, you can install add-ins that help you perform What-If Analysis, such as the Solver add-in. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. You can also create forecasts by using the fill handle and various commands that are built into Excel. For more advanced models, you can use the Analysis ToolPak add-in.

Suppose that you want to create a budget but are uncertain of your revenue. By using scenarios, you can define different possible values for the revenue and then switch between scenarios to perform what-if analyses.

For example, assume that your worst case budget scenario is Gross Revenue of $50,000 and Costs of Goods Sold of $13,200, leaving $36,800 in Gross Profit. To define this set of values as a scenario, you first enter the values in a worksheet, as shown in the following illustration:

The Changing cells have values that you type in, while the Result cell contains a formula that is based on the Changing cells (in this illustration cell B4 has the formula =B2-B3).

You then use the Scenario Manager dialog to save these values as a scenario. Goto the Data tab > What-If Analysis > Scenario Manager > Add.

In the Scenario name dialog, name the scenario Worst Case, and specify that cells B2 and B3 are the values that change between scenarios. If you select the Changing cells on your worksheet before adding a Scenario, the Scenario Manager will automatically insert the cells for you, otherwise you can type them by hand, or use the cell selection dialog to the right of the Changing cells dialog box.

Protection – You can also protect your scenarios, so in the Protection section check the options that you want, or uncheck them if you don't want any protection.

• Select Prevent Changes to prevent editing the scenario when the worksheet is protected.

• Select Hidden to prevent displaying the scenario when the worksheet is protected.

Now suppose that your Best Case budget scenario is Gross Revenue of $150,000 and Costs of Goods Sold of $26,000, leaving $124,000 in Gross Profit. To define this set of values as a scenario, you create another scenario, name it Best Case, and supply different values for cell B2 (150,000) and cell B3 (26,000). Because Gross Profit (cell B4) is a formula - the difference between Revenue (B2) and Costs (B3) -  you do not change cell B4 for the Best Case scenario.

After you save a scenario, it becomes available in the list of scenarios that you can use in your what-if analyses. Given the values in the preceding illustration, if you chose to display the Best Case scenario, the values in the worksheet would change to resemble the following illustration:

There may be times when you have all the information in one worksheet or workbook needed to create all the scenarios that you want to consider. However, you may want to gather scenario information from other sources. For example, suppose you are trying to create a company budget. You might collect scenarios from different departments, like Sales, Payroll, Production, Marketing, and Legal, because each of these sources has different information to use in creating the budget.

You can gather these scenarios into one worksheet by using the Merge command. Each source can supply as many or as few changing cell values as you want. For example, you might want each department to supply expenditure projections, but only need revenue projections from a few.

When you choose to merge, the Scenario Manager will load a Merge Scenario wizard, which will list all of the worksheets in the active workbook, as well as listing any other workbooks you might have open at the time. The wizard will tell you how many scenarios you have on each source worksheet you select.

When you collect different scenarios from various sources, you should use the same cell structure in each of the workbooks. For example, Revenue might always go in cell B2 and Expenditures might always go in cell B3. If you use different structures for the scenarios from various sources, it can be difficult to merge the results.

To compare several scenarios, you can create a report that summarizes them on the same page. The report can list the scenarios side by side or present them in a PivotTable report.

A scenario summary report based on the preceding two example scenarios would look something like the following:

You’ll notice that Excel has automatically added Grouping levels for you, which will expand and collapse the view as you click the different selectors.

A note appears at the end of the summary report explaining that the Current Values column represents the values of changing cells at the time the Scenario Summary Report was created, and that the cells that changed for each scenario are highlighted in gray.