xxxii
Contents
Chapter 36: Performing Spreadsheet What-If Analysis . . . . . . . . . . . . . . 745
A What-If Example ...........................................................................................................745
Types of What-If Analyses .................................................................................................747
Manual What-If Analysis ...................................................................................................747
Creating Data Tables .........................................................................................................747
Creating a one-input data table ...............................................................................748
Creating a two-input data table ...............................................................................750
Using Scenario Manager ....................................................................................................753
Defining scenarios ...................................................................................................754
Displaying scenarios ................................................................................................757
Modifying scenarios ................................................................................................758
Merging scenarios ...................................................................................................758
Generating a scenario report....................................................................................758Chapter 37: Analyzing Data Using Goal Seeking and Solver . . . . . . . . . . 761
What-If Analysis, in Reverse ..............................................................................................761
Single-Cell Goal Seeking ...................................................................................................762
A goal-seeking example ...........................................................................................762
More about goal seeking ..........................................................................................763
Introducing Solver ............................................................................................................764
Appropriate problems for Solver .............................................................................765
A simple Solver example .........................................................................................765
More about Solver ...................................................................................................770
Solver Examples ................................................................................................................771
Solving simultaneous linear equations .....................................................................771
Minimizing shipping costs.......................................................................................773
Allocating resources ................................................................................................776
Optimizing an investment portfolio .........................................................................778Chapter 38: Analyzing Data with the Analysis ToolPak . . . . . . . . . . . . . . 781
The Analysis ToolPak: An Overview..................................................................................781
Installing the Analysis ToolPak Add-in ..............................................................................782
Using the Analysis Tools ...................................................................................................783
Introducing the Analysis ToolPak Tools ............................................................................783
Analysis of Variance ................................................................................................784
Correlation ..............................................................................................................784
Covariance ..............................................................................................................785
Descriptive Statistics ................................................................................................785
Exponential Smoothing ...........................................................................................786
F-Test (two-sample test for variance) .......................................................................786
Fourier Analysis ......................................................................................................787
Histogram ...............................................................................................................787
Moving Average ......................................................................................................788
Random Number Generation ..................................................................................78902_474877-ftoc.indd xxxii02_474877-ftoc.indd xxxii 3/29/10 11:34 PM3/29/10 11:34 PM