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
Search JabSto ::




Custom Search