You don't need result cells to generate a scenario summary report, but you do need them for a scenario PivotTable report. 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. Scenario reports do not automatically recalculate. If you create named ranges for the cells before you run the summary report, the report will contain the names instead of cell references. If you use different structures for the scenarios from various sources, it can be difficult to merge the results.īy default, the summary report uses cell references to identify the Changing cells and Result cells. For example, Revenue might always go in cell B2 and Expenditures might always go in cell B3. When you collect different scenarios from various sources, you should use the same cell structure in each of the workbooks. The wizard will tell you how many scenarios you have on each source worksheet you select. 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. For example, you might want each department to supply expenditure projections, but only need revenue projections from a few. Each source can supply as many or as few changing cell values as you want. You can gather these scenarios into one worksheet by using the Merge command. 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. For example, suppose you are trying to create a company budget. However, you may want to gather scenario information from other sources. 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. ![]() 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. In the Scenario name dialog, name the scenario Worst Case, and specify that cells B2 and B3 are the values that change between scenarios. Goto the Data tab > What-If Analysis > Scenario Manager > Add. You then use the Scenario Manager dialog to save these values as a scenario. 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). To define this set of values as a scenario, you first enter the values in a worksheet, as shown in the following illustration: By using scenarios, you can define different possible values for the revenue and then switch between scenarios to perform what-if analyses.įor 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. Suppose that you want to create a budget but are uncertain of your revenue. ![]() For more advanced models, you can use the Analysis ToolPak add-in. You can also create forecasts by using the fill handle and various commands that are built into Excel. The Solver add-in is similar to Goal Seek, but it can accommodate more variables. ![]() In addition to these three tools, you can install add-ins that help you perform What-If Analysis, such as the Solver add-in. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want. 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. If you want to analyze more than 32 values, and the values represent only one or two variables, you can use Data Tables. 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.Įach scenario can accommodate up to 32 variable values. Scenarios and Data Tables take sets of input values and project forward to determine possible results. There are three kinds of What-If Analysis tools that come with Excel: Scenarios, Data Tables and Goal Seek.
0 Comments
Leave a Reply. |