Microsoft Office Tutorials and References

In Depth Information

**Chapter 18: Performing a what-if analysis**

CHAPTER 18

Performing a what-if analysis

Using data tables ................................633

Using the Scenario Manager ......................639

Using the Goal Seek command ....................649

Using the Solver .................................652

ONE
OF
THE
most important financial questions is “What if?”; spreadsheet software

was literally created to answer that question, taking the tedium out of the

complicated calculations necessary to do so. Instead of starting your calculations from

the beginning every time your assumptions change, you can change just a few variables

and instantly see the effect. Excel offers a number of advanced what-if features, which we

discuss in this chapter.

Using data tables

A data table, or sensitivity table, summarizes the impact of one or two variables on

formulas that use those variables. You can click the What-If Analysis button in the Data Tools

group on the Data tab and then click Data Table to create two kinds of data tables: tables

based on a single input variable that test the variable’s impact on more than one formula,

and tables based on two input variables that test their impact on a single formula.

Note

Some of the most powerful tools Excel offers for performing a what-if analysis are not

covered in this chapter because they are awesome enough to have chapters of their

own. Take a look at Chapter 23, “Analyzing data with PivotTable reports,” for a

comprehensive look at PivotTables and see Chapter 24, “An introduction to PowerPivot,” for

coverage of PowerPivot, introduced in Excel 2010 and enhanced in Excel 2013. These

tools give you superhuman powers over mass quantities of data, providing easy access

to giant databases and allowing you to slice and dice the data any way you like.

Data tables based on one input variable

Suppose you’re considering buying a house that requires you to take on a 30-year,

$200,000 mortgage, and you need to calculate monthly payments on the loan for several

633