1 performing spreadsheet what-if analysis applications of spreadsheets
TRANSCRIPT
11
Performing Performing Spreadsheet What-If Spreadsheet What-If
AnalysisAnalysis
Applications of SpreadsheetsApplications of Spreadsheets
2
Performing Spreadsheet Performing Spreadsheet What-If AnalysisWhat-If Analysis
►Microsoft Office Excel 2003 allows us to Microsoft Office Excel 2003 allows us to create dynamic models.create dynamic models.
►A dynamic model uses formulas that A dynamic model uses formulas that instantly recalculate when you change instantly recalculate when you change values in cells to which the formulas values in cells to which the formulas refer.refer.
►When you change values in cells in a When you change values in cells in a systematic manner and observe the systematic manner and observe the effects on specific formula cells, you are effects on specific formula cells, you are performing a type of what-if analysis.performing a type of what-if analysis.
3
Avoid Hard-coding Values in a Avoid Hard-coding Values in a FormulaFormula
►You should always set up your You should always set up your worksheet so that you have maximum worksheet so that you have maximum flexibility to make changes.flexibility to make changes.
►Do not hard-code values (i.e. use Do not hard-code values (i.e. use constants) in a formula!constants) in a formula!
►Store the values in separate cells and Store the values in separate cells and use cell references in the formula.use cell references in the formula.
4
Types of What-If AnalysesTypes of What-If Analyses
►Excel gives you three basic options:Excel gives you three basic options: Manual what-if analysis – plug in new values Manual what-if analysis – plug in new values
and observe the effects on formula cells.and observe the effects on formula cells. Data tables – create a table that displays the Data tables – create a table that displays the
results of selected formula cells as you results of selected formula cells as you systematically change one or two input cells.systematically change one or two input cells.
Scenario Manager – create named scenarios Scenario Manager – create named scenarios and generate reports that use outlines or and generate reports that use outlines or pivot tables.pivot tables.
5
Performing What-If AnalysisPerforming What-If Analysis
► In an Excel worksheet, assumptions In an Excel worksheet, assumptions are are known as input cells or known as input cells or variablesvariables are typically grouped together to speed are typically grouped together to speed
data entrydata entry outcome cells contain formulas that outcome cells contain formulas that
reference the input cells either directly or reference the input cells either directly or indirectly.indirectly.
6
Performing Manual What-If Performing Manual What-If AnalysisAnalysis
►Based on the idea that you have one Based on the idea that you have one or more input cells that affect one or or more input cells that affect one or more key formula cells.more key formula cells.
►You change the value in the input cells You change the value in the input cells and see what happens to the formula and see what happens to the formula cells.cells. print the results orprint the results or save each scenario to a new workbook.save each scenario to a new workbook.
7
Creating Data TablesCreating Data Tables
►When working with a what-if model, Excel When working with a what-if model, Excel displays only one scenario at a time.displays only one scenario at a time.
►Data tables allow you to summarize key Data tables allow you to summarize key formula cells for each scenario you run.formula cells for each scenario you run. Manually create a table orManually create a table or Use Excel’s Data Use Excel’s Data Table command to create Table command to create
a summary table automatically.a summary table automatically.►Data tables can only deal with one or two Data tables can only deal with one or two
input cells at a time.input cells at a time.
8
Creating Data TablesCreating Data Tables
► A one-input data table displays the results of A one-input data table displays the results of one or more formulas when you use multiple one or more formulas when you use multiple values in a single input cell.values in a single input cell.
► Can place the table anywhere in the Can place the table anywhere in the worksheet.worksheet.
► Usually the left column contains formulas or Usually the left column contains formulas or references to formulas located elsewhere in references to formulas located elsewhere in the worksheet.the worksheet. Can use a single formula reference or any Can use a single formula reference or any
number of formula references.number of formula references.
9
Creating Data TablesCreating Data Tables
►The upper left cell of the table remains The upper left cell of the table remains empty.empty.
►Excel calculates the values that result Excel calculates the values that result from each level of the input cell and from each level of the input cell and places them under each formula places them under each formula reference.reference.
10
Creating Data TablesCreating Data Tables
►To create a one-input data tableTo create a one-input data table Select the data table rangeSelect the data table range Choose Data Choose Data Table to open the Data Table to open the Data
Table dialog box.Table dialog box. Specify the worksheet cell that contains Specify the worksheet cell that contains
the input value (place this cell reference the input value (place this cell reference in the text box called Column Input Cell. in the text box called Column Input Cell. (leave the Row Input cell field blank.)(leave the Row Input cell field blank.)
Click OK, and Excel fills in the table with Click OK, and Excel fills in the table with the appropriate results.the appropriate results.
11
Creating Data TablesCreating Data Tables
►The data is generated with a multi-cell The data is generated with a multi-cell array formula.array formula. An array formula is a single formula that An array formula is a single formula that
can produce results in multiple cells.can produce results in multiple cells.►You can arrange a one-input table You can arrange a one-input table
vertically or horizontally.vertically or horizontally. If you place the values of the input cell in If you place the values of the input cell in
a row, enter the input cell reference in the a row, enter the input cell reference in the text box labeled Row input cell in the text box labeled Row input cell in the Table dialog box.Table dialog box.
12
Working with One- and Two-Input Working with One- and Two-Input Data TablesData Tables
13
Creating a One-Input Data TableCreating a One-Input Data TableEX0930
Worksheet used for creating a one-input data table in this lesson
Worksheet used for creating a two-input data table in the next lesson
14
Preparing a One-Input Data Preparing a One-Input Data TableTable
15
Creating a One-Input Data Table Creating a One-Input Data Table for Performing Two Calculationsfor Performing Two Calculations
For a one-input data table, enter either a row input cell or a column input cell; but not both
Outcome formulas
Input cells
16
Formatting a One-Input Data Formatting a One-Input Data TableTable
17
Creating a Two-Input Data TableCreating a Two-Input Data Table
►Different from the one-input data table Different from the one-input data table in that it can show the results of only in that it can show the results of only one formula at a time.one formula at a time. The top row holds the values for the The top row holds the values for the
second input cell.second input cell. The upper-left cell of the table contains a The upper-left cell of the table contains a
reference to the single result formula.reference to the single result formula.
18
Creating a Two-Input Data TableCreating a Two-Input Data Table
19
A Two-Input Data TableA Two-Input Data Table
20
Using Scenario ManagerUsing Scenario Manager
►Data tables are limited:Data tables are limited: Can vary only one or two input cells at a time.Can vary only one or two input cells at a time. The process of setting up a data table is not The process of setting up a data table is not
intuitive.intuitive. A two-input data table shows the results of A two-input data table shows the results of
only one formula cell (you can create only one formula cell (you can create additional tables for more formulas, additional tables for more formulas, however).however).
Often you want to see just a few select Often you want to see just a few select combinations rather than an entire table of combinations rather than an entire table of possible combinations.possible combinations.
21
Using Scenario ManagerUsing Scenario Manager
►With Scenario Manager, you can With Scenario Manager, you can automate your what-if models.automate your what-if models. Store different sets of input values (aka Store different sets of input values (aka
changing cellschanging cells) for any number of variable ) for any number of variable an give a name to each set.an give a name to each set.
Can then select a set of values by name Can then select a set of values by name and Excel will display the worksheet by and Excel will display the worksheet by using those values.using those values.
22
Using Scenario ManagerUsing Scenario Manager
Can generate a summary report that Can generate a summary report that shows the effect of various combinations shows the effect of various combinations of values on any number of results cellsof values on any number of results cells
Summary report can be an outline or a Summary report can be an outline or a pivot table.pivot table.
►To access the Scenario ManagerTo access the Scenario Manager Select Tools Select Tools Scenarios Scenarios The Scenario Manager Dialog box displaysThe Scenario Manager Dialog box displays To add a scenario, click the Add button.To add a scenario, click the Add button.
23
Using Scenario ManagerUsing Scenario Manager
Create a range name by selecting a cell and then typing in the Name box
24
Using the Scenario ManagerUsing the Scenario Manager
►The Add Scenario dialog box displays four The Add Scenario dialog box displays four parts:parts: Scenario Name – any name you want.Scenario Name – any name you want. Changing Cells – the input cells for the Changing Cells – the input cells for the
scenario.scenario.►Input the cell addresses directly, or point to them Input the cell addresses directly, or point to them
using the mouse.using the mouse.►Multiple cells are allowed; don’t have to be adjacent.Multiple cells are allowed; don’t have to be adjacent.►Each named scenario can use the same set of Each named scenario can use the same set of
changing cells or different changing cells. (limit is changing cells or different changing cells. (limit is 32)32)
25
Add Scenario Dialog BoxAdd Scenario Dialog Box
Enter the input cells that you want to modify to achieve the desired outcome
Enter a descriptive name for the scenario
Enter an optional comment to describe the scenario
26
Using the Scenario ManagerUsing the Scenario Manager
Comment – by default, it is your name Comment – by default, it is your name and the date.and the date.
Protection – there are two options that are Protection – there are two options that are in effect only when you protect the in effect only when you protect the worksheet and choose the scenario worksheet and choose the scenario option.option.
►Preventing changes – no one can modify the Preventing changes – no one can modify the scenario.scenario.
►Hiding a scenario – doesn’t appear in the Hiding a scenario – doesn’t appear in the Scenario Manager dialog box.Scenario Manager dialog box.
27
Using the Scenario ManagerUsing the Scenario Manager
►Click OK to continue to the Scenario Click OK to continue to the Scenario Values dialog box.Values dialog box. Displays one field for each changing cell Displays one field for each changing cell
that was specified in the previous step.that was specified in the previous step. Enter the values for each cell in the Enter the values for each cell in the
scenario.scenario.
►Click OK to return to the Scenario Click OK to return to the Scenario Manager dialog box and create more Manager dialog box and create more scenarios if you desire.scenarios if you desire.
28
Scenario Values Dialog BoxScenario Values Dialog Box
29
Displaying ScenariosDisplaying Scenarios
►The Scenario Manager dialog box The Scenario Manager dialog box displays all defined scenarios. To displays all defined scenarios. To display onedisplay one Select one of the scenarios and click the Select one of the scenarios and click the
Show button.Show button. Excel inserts the corresponding values Excel inserts the corresponding values
into the changing cells and calculates the into the changing cells and calculates the worksheet to show the result for that worksheet to show the result for that scenario.scenario.
30
Modifying ScenariosModifying Scenarios
► To modify a scenarioTo modify a scenario Open the Scenario Manager dialog box.Open the Scenario Manager dialog box. Select the scenario you want to edit.Select the scenario you want to edit. Click the Edit button.Click the Edit button. In the Edit Scenario dialog box, click OK to In the Edit Scenario dialog box, click OK to
access the Scenario Values dialog box.access the Scenario Values dialog box. Make your changes and click OK.Make your changes and click OK. Excel automatically updates the comments box Excel automatically updates the comments box
with new text stating when the scenario was with new text stating when the scenario was modified.modified.
31
Editing & Manipulating Editing & Manipulating ScenariosScenarios
The Prevent changes check box only protects a scenario when the active worksheet is protected
32
Merging ScenariosMerging Scenarios
►Can merge two or more scenarios into Can merge two or more scenarios into one workbook:one workbook: Click the Merge button in the Scenario Click the Merge button in the Scenario
Manager dialog box.Manager dialog box. On the Merge Scenarios dialog box,On the Merge Scenarios dialog box,
►choose the workbook from which you are choose the workbook from which you are merging in the Book drop-down list.merging in the Book drop-down list.
►choose the sheet that contains the scenarios choose the sheet that contains the scenarios you want t o merge from the sheet list box.you want t o merge from the sheet list box.
Click OK.Click OK.
33
Merging a ScenarioMerging a Scenario
The dialog box tells you that this workbook contains one saved scenario
34
Generating a Scenario ReportGenerating a Scenario Report
► Click the Summary button in the Scenario Click the Summary button in the Scenario Manager dialog box.Manager dialog box.
► On the Scenario Summary dialog box, select On the Scenario Summary dialog box, select the report type:the report type: Scenario Summary – the summary report Scenario Summary – the summary report
appears in the form of an outline.appears in the form of an outline. Scenario PivotTable – the summary report Scenario PivotTable – the summary report
appears in the form of a pivot table.appears in the form of a pivot table.► Specify the results cells (the cells that Specify the results cells (the cells that
contain the formulas you are interested in.)contain the formulas you are interested in.)► Excel creates a new worksheet to store the Excel creates a new worksheet to store the
summary table.summary table.
35
Creating a Scenario Summary Creating a Scenario Summary ReportReport
36
Displaying a Summary ReportDisplaying a Summary Report