Excel® 2010 Workbook for Dummies®

Chapter 19. Performing What-If Analysis

In This Chapter

• Doing what-if analysis with one- and two-variable data tables

• Creating and playing with different scenarios

• Performing goal seeking

• Creating models with the Solver add-in

Using what-if analysis in the spreadsheet to project possible future outcomes based on different variables is, to put it mildly, one of Excel's fortes. The program offers you what-if analysis in the form of its one- and two-variable data tables, goal seeking, and Scenario Manager. And if this is not enough, it also includes the Solver add-in utility, which enables you to model more complex problems. In this chapter, you get a chance to practice performing what-if analysis using all of these tools.

Using Data Tables

In a normal Excel spreadsheet, you see the effect of changing an input value on the result returned by a formula as soon as you enter that new input: Each time you change this input value, Excel automatically recalculates the formula and shows you the new result based on the new value. This method is of limited use, however, when you are performing what-if or sensitivity analysis and need to be able to see the range of results produced by using a series of different input values in the same worksheet so that you can compare them to each other.

To perform this type of what-if analysis, you can use Excel's Data Table command. When creating a data table, you enter a series of input values in the worksheet, and Excel then uses each of them in the formula ...