A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results.
If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one.
After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios.
Scenarios are managed with the Scenario Manager wizard from the What-If Analysis group on the Data tab.
Kinds of What-If Analysis
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.
Creating scenarios
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.
Note: Although this example contains only two changing cells (B2 and B3), a scenario can contain up to 32 cells.
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.
Note: These options apply only to protected worksheets. For more information about protected worksheets, see Protect a worksheet
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:
Merging scenarios
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.
Tip: Consider first creating a scenario yourself, and then sending your colleagues a copy of the workbook that contains that scenario. This makes it easier to be sure that all the scenarios are structured the same way.
Scenario summary reports
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.
Notes:
-
By default, the summary report uses cell references to identify the Changing cells and Result cells. If you create named ranges for the cells before you run the summary report, the report will contain the names instead of cell references.
-
Scenario reports do not automatically recalculate. If you change the values of a scenario, those changes will not show up in an existing summary report, but will show up if you create a new summary report.
-
You don't need result cells to generate a scenario summary report, but you do need them for a scenario PivotTable report.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
See Also
Introduction to What-If Analysis
Define and solve a problem by using Solver
Use the Analysis ToolPak to perform complex data analysis
Find and correct errors in formulas
No comments:
Post a Comment