Beginning Excel What-If Data Analysis Tools (eBook)

Paul Cornell works at Microsoft on the documentation team for Microsoft Visual Studio Tools for the Microsoft Office system. He worked as an editor, writer, and columnist on the MSDN Office Developer Center and edited the Microsoft Office Visual Basic Language Reference. Cornell also founded the Power User Corner, on Microsoft Office Online, where he was a frequent contributor.

Contents at a Glance 4
Contents 6
Preface 12
About the Author 14
About the Technical Reviewer 16
Acknowledgments 18
Introduction 20
CHAPTER 1 Goal Seek 24
What Is Goal Seeking? 24
When Would I Use Goal Seek? 24
How Do I Use Goal Seek? 25
Try It: Use Goal Seek to Solve Simple Math Problems 27
Speed,Time, and Distance Math Problems 27
Circle Radius, Diameter, Circumference, and Area Math Problems 28
Algebraic Equation Math Problem 30
Try It: Use Goal Seek to Forecast Interest Rates 32
Home Mortgage Interest Rate 33
Car Loan Interest Rate 34
Savings Account Interest Rate 36
Try It: Use Goal Seek to Determine Optimal Ticket Prices 37
Number of Tickets Sold 38
Ticket Prices 40
Troubleshooting Goal Seek 41
Summary 42
CHAPTER 2 Data Tables 44
What Are Data Tables? 44
When Would I Use Data Tables? 45
How Do I Create Data Tables? 47
Working with One-Variable Data Tables 47
Working with Two-Variable Data Tables 49
Clearing Data Tables 50
Converting Data Tables 50
Adjusting Data Table Calculation Options 51
Try It: Use Data Tables to Forecast Savings Account Details 51
One-Variable Data Table to Forecast Savings Account Details 52
Two-Variable Data Table to Forecast Savings Account Details 53
Try It: Use Data Tables to Determine Royalty Payments 54
One-Variable Data Table to Determine Royalty Payments 55
Two-Variable Data Table to Determine Royalty Payments 56
Try It: Use Data Tables to Calculate Stock Dividend Payments 58
One-Variable Data Table to Calculate Stock Dividend Payments 58
Two-Variable Data Table to Calculate Stock Dividend Payments 59
Troubleshooting Data Tables 60
Summary 61
CHAPTER 3 Scenarios 62
What Are Scenarios? 62
When Would I Use Scenarios? 63
How Do I Use Scenarios? 64
Creating a New Scenario 65
Displaying a Scenario 66
Editing an Existing Scenario 67
Deleting a Scenario 67
Creating a Scenario Summary Report 67
Merging Scenarios from Another Worksheet 68
Preventing Changes to a Scenario 70
Try It: Use Scenarios to Forecast Development Costs 71
Worst-Case Scenario 71
Best-Case Scenario 72
Scenario Results 73
Try It: Use Scenarios to Forecast Sales 74
Summer Scenario 75
Winter Scenario 75
Scenario Results 76
Try It: Use Scenarios to Forecast Rental Volumes 77
Blockbuster Week Scenario 77
Regular Week Scenario 78
Scenario Results 79
Troubleshooting Scenarios 80
Summary 81
CHAPTER 4 Solver 82
What Is Solver? 82
When Would I Use Solver? 83
How Do I Use Solver? 84
Installing Solver 86
Setting Solver Parameters 86
Adding and Changing Constraints 88
Setting Solver Options 89
Saving and Loading Solver Models 92
Working with the Solver Results 94
Working with the Show Trial Solution Dialog Box 96
Creating Solver Reports 96
Try It: Use Solver to Solve Math Problems 100
Cube Volume Problem 100
Object Velocity Problem 101
Try It: Use Solver to Forecast Auction Prices 102
Average Daily Bid Increase for One Item 103
Average Daily Auction Bid Increase for All Items 104
Try It: Use Solver to Determine a Home Sales Price 106
Try It: Use Solver to Forecast the Weather 108
Minimum Yearly Precipitation Total for Seattle 109
Average December Precipitation Total for All Cities 110
Try It: Experiment with the Default Solver Samples 112
Quick Tour 112
Product Mix 114
Shipping Routes 115
Staff Scheduling 117
Maximizing Income 119
Portfolio of Securities 122
Engineering Design 123
Troubleshooting Solver 125
General Excel Error Messages 125
Solver Dialog Box Error Messages 126
General Troubleshooting Tips 130
Summary 130
CHAPTER 5 Case Study: Using Excel What-If Tools 132
About the Ridge Running Cooperative 132
Use Goal Seek to Forecast Membership Dues 133
New Lifetime Family Club Membership Dues 134
New Annual Family Club Memberships 135
Use Data Tables to Forecast Race Paces 136
Time for a Single Race Pace 136
Time for Multiple Race Paces 137
Use Scenarios to Forecast Race-Day Cash Flow 139
Cash Flow for a Rainy Weather Race Day 141
Cash Flow for a Normal Weather Race Day 142
Cash Flow for a Perfect Weather Race Day 143
Report to Display Race-Day Cash-Flow Forecasts Side by Side 144
Report to Display Race-Day Cash-Flow Forecasts in PivotTable Format 145
Use Solver to Forecast Race-Day Finish Times 146
Race-Day Finish Times with Distance and Target Pace 148
Race-Day Finish Times with Distance and Elapsed Time 149
Race-Day Finish Times with a Pacer 150
Use Solver to Pair Up Race Relay Teams 151
Summary 153
APPENDIX A Excel What-If Tools Quick Start 154
Using Goal Seek 154
Goal Seek Procedure 154
Goal Seek Example 154
Using Data Tables 155
Data Table Procedure 155
Data Table Examples 156
Using Scenarios 158
Scenario Procedure 158
Scenario Example 158
Using Solver 159
Solver Procedure 159
Solver Example 160
APPENDIX B Summary of Other Helpful Excel Data Analysis Tools 162
Subtotaling and Outlining Data 162
Consolidating Data 163
Consolidating Using 3-D References in Formulas 163
Consolidating Data by Position or Category 164
Sorting Data 165
Sorting in Ascending or Descending Order 165
Sorting by Multiple Columns 165
Sorting by Months or Weekdays 165
Sorting in Custom Order 166
Sorting by Rows 166
Filtering Data 167
Filtering Data with the AutoFilter Feature 167
Filtering Data with the Advanced Filter Feature 168
Using Conditional Cell Formatting 169
Working with OLAP Data 170
Working with PivotTables and PivotCharts 170
APPENDIX C Summary of Common Excel Data Analysis Functions 172
Statistical Functions 172
Mathematical Functions 174
Financial Functions 175
APPENDIX D Additional Excel Data Analysis Resources 178
Books 178
Periodicals 178
Web Sites 178
Newsgroups 179
Index 180