Set up "what-if" scenarios in Microsoft Office Excel 2007

Learn how to gather, analyze, and change data variables for what-if scenarios using Microsoft Office Excel 2007.

Video: Set up "what-if" scenarios in Microsoft Office Excel 2007

Students can learn to gather data and manipulate different variables in a scenario to change the end result. Microsoft Office Excel 2007 saves scenario data and lets them substitute it to perform “what-if” analyses to predict different outcomes. Watch the video

When to use scenarios

You can use scenarios to take snapshots of data as you change certain variables or conditions in a science project. For example, what would happen to the temperature of a material if you gradually increased the temperature of the environment?

What-if" scenarios are helpful for data analyses.

You can create and save different groups of values on a worksheet and then switch to any of those scenarios to view different results. In the following example, a user can switch between scenarios that show what happens to a personal budget when payments on a second car are added and what happens when a second car is added with lower working hours.

Create scenarios

You can use summaries of "what-if" scenarios to build charts or graphs.

Open Office Excel 2007. Either open a file that includes some formulas that you would like to use for your analysis. Or start from a new worksheet and type in your data and formulas.
Decide which values in the worksheet you will change and which will display the results. For example, you might want to see what happens when you change several variables that affect the budget and then display the result as an amount over or under budget.
To create a new scenario, on the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager
Click Add.
In the Scenario name box, type a name for the new scenario.
In the Changing cells box, enter the references for the cells that you want to change. To add a cell to the list, press CTRL and then click the cell.

Note: To preserve the original values for the changing cells, first create a scenario that contains the original cell values. Then, create the scenarios in which you change the values.
Under Protection, select the options that you want, and then click OK.
Next, in the Scenario Values dialog box, type the values that you want for the changing cells, and then click OK.
If you want to create additional scenarios, click Add again, and then repeat steps 5 through 9. When you finish creating scenarios, click OK.
Double-click a scenario on the list in Scenario Manager to view it on the worksheet.

Summarize scenarios

On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
To summarize the data in the scenarios, click Summary.
Decide whether you want a summary or PivotTable Report.
Enter the cells that display the results, and then click OK. Excel 2007 creates a new worksheet that summarizes the data in your scenarios. You can also merge the data from scenarios on different worksheets.