# Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and Solver

Apress, Nov 9, 2006 - Computers - 192 pages

Excels what-if data analysis tools let you experiment with your data to project future results. In turn, these predictions will lead to better decision making and unlock the mystery of many business analysis scenarios. For example, what-if data analysis tools will enable you to forecast how lowering the price per unitwhile increasing projected unit salesmight affect your profit margins.

Beginning Excel What-If Data Analysis Tools explores the use of Goal Seek, Data Tables, Scenarios, and Solver to help you get insight on your data. This book is focused and to the point, and it provides tutorial treatment of what-if tools in a practical, hands-on manner.

### What people are saying -Write a review

We haven't found any reviews in the usual places.

### Contents

 Goal Seek 6 How Do I Use Goal Seek? 7 Use Goal Seek to Solve Simple Math Problems 9 Circle Radius Diameter Circumference and Area Math Problems 10 Algebraic Equation Math Problem 12 Use Goal Seek to Forecast Interest Rates 14 Home Mortgage Interest Rate 15 Car Loan Interest Rate 16
 Object Velocity Problem 84 Use Solver to Forecast Auction Prices 85 Average Daily Bid Increase for One Item 86 Average Daily Auction Bid Increase for All Items 87 Use Solver to Determine a Home Sales Price 89 Use Solver to Forecast the Weather 91 Minimum Yearly Precipitation Total for Seattle 92 Average December Precipitation Total for All Cities 93

 Savings Account Interest Rate 18 Use Goal Seek to Determine Optimal Ticket Prices 19 Number of Tickets Sold 21 Ticket Prices 23 Troubleshooting Goal Seek 24 Summary 25 Data Tables 27 When Would I Use Data Tables? 28 How Do I Create Data Tables? 30 Working with TwoVariable Data Tables 32 Clearing Data Tables 33 Adjusting Data Table Calculation Options 34 OneVariable Data Table to Forecast Savings Account Details 35 TwoVariable Data Table to Forecast Savings Account Details 36 Use Data Tables to Determine Royalty Payments 37 OneVariable Data Table to Determine Royalty Payments 38 TwoVariable Data Table to Determine Royalty Payments 39 Use Data Tables to Calculate Stock Dividend Payments 41 TwoVariable Data Table to Calculate Stock Dividend Payments 42 Troubleshooting Data Tables 43 Summary 44 Scenarios 45 When Would I Use Scenarios? 46 How Do I Use Scenarios? 47 Creating a New Scenario 48 Displaying a Scenario 49 Editing an Existing Scenario 50 Merging Scenarios from Another Worksheet 51 Preventing Changes to a Scenario 53 Use Scenarios to Forecast Development Costs 54 BestCase Scenario 55 Scenario Results 56 Use Scenarios to Forecast Sales 57 Summer Scenario 58 Scenario Results 59 Use Scenarios to Forecast Rental Volumes 60 Regular Week Scenario 61 Scenario Results 62 Troubleshooting Scenarios 63 Summary 64 Solver 65 When Would I Use Solver? 66 How Do I Use Solver? 67 Installing Solver 69 Adding and Changing Constraints 71 Setting Solver Options 72 Saving and Loading Solver Models 75 Working with the Solver Results 77 Working with the Show Trial Solution Dialog Box 79 Use Solver to Solve Math Problems 83
 Experiment with the Default Solver Samples 95 Product Mix 97 Shipping Routes 98 Staff Scheduling 100 Maximizing Income 102 Portfolio of Securities 105 Engineering Design 106 Troubleshooting Solver 109 Solver Dialog Box Error Messages 110 General Troubleshooting Tips 114 Case Study Using Excel WhatIf Tools 116 Use Goal Seek to Forecast Membership Dues 117 New Lifetime Family Club Membership Dues 118 New Annual Family Club Memberships 119 Use Data Tables to Forecast Race Paces 120 Time for Multiple Race Paces 121 Use Scenarios to Forecast RaceDay Cash Flow 123 Cash Flow for a Rainy Weather Race Day 125 Cash Flow for a Normal Weather Race Day 126 Cash Flow for a Perfect Weather Race Day 127 Report to Display RaceDay CashFlow Forecasts Side by Side 128 Report to Display RaceDay CashFlow Forecasts in PivotTable Format 129 Use Solver to Forecast RaceDay Finish Times 130 RaceDay Finish Times with Distance and Target Pace 132 RaceDay Finish Times with Distance and Elapsed Time 133 RaceDay Finish Times with a Pacer 134 Use Solver to Pair Up Race Relay Teams 135 Summary 137 Excel WhatIf Tools Quick Start 138 Using Data Tables 139 Data Table Examples 140 Using Scenarios 142 Using Solver 143 Solver Example 144 Summary of Other Helpful Excel Data Analysis Tools 146 Consolidating Data 147 Consolidating Data by Position or Category 149 Sorting Data 150 Sorting in Custom Order 151 Filtering Data 152 Filtering Data with the Advanced Filter Feature 153 Using Conditional Cell Formatting 155 Working with OLAP Data 156 Summary of Common Excel Data Analysis Functions 158 Mathematical Functions 160 Financial Functions 161 Additional Excel Data Analysis Resources 164 Newsgroups 165 INDEX 166 Copyright

### Popular passages

Page 162 - PPMT function returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
Page 161 - Calculates the payment For a loan based on constant payments and a constant interest rate, Pv is the present value: the total amount that a series of future payments is worth now.
Page 162 - PV function returns the present value of an investment. The present value is the total amount that a series of future payments is worth now.
Page xv - I hope you enjoy reading and using this book, as much as I did putting it together.
Page 158 - ... other scores and is not in the middle of the distribution. It would be more interesting to researchers if 2 occurred five times, for example, in this set of data. We don't use the mode often, because it doesn't tell us much about the data. Median The second measure of central tendency is the median. The median is the number in the middle of a set of ordered data. An equal number of scores are found below and above it. For example, in the set of scores of a communication skills test taken by a...