Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek, Data Tables, Scenarios, and SolverExcels 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
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 |
158 | |
160 | |
161 | |
164 | |
165 | |
166 | |
Other editions - View all
Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek ... Paul Cornell No preview available - 2005 |
Beginning Excel What-If Data Analysis Tools: Getting Started with Goal Seek ... Paul Cornell No preview available - 2008 |
Common terms and phrases
adjustable cells button cell B3 cell group Cell Reference box cell values Changing Cells box check box Click Add click cell Click Data Click OK click or type Click Reset Click Solve Click the Cell Click the Constraint Click the Set Click Tools Column Input Cell Compare your results Constraint box defined name error message example Excel Data Analysis follow these steps formula Goal Seek half marathon Input Cell box input values interest rate kilometers Manager dialog box Math Problems message appears Microsoft Office Excel Model check OLAP one-variable data table operator list Parameters dialog box PivotTable race paces results to Figure run/walk race cell Scenario Manager dialog Scenario Summary scenarios to forecast select cells Set Cell box Set Target Cell shown in Figure Solver Options dialog Solver Parameters dialog Solver to forecast specified Target Cell box target pace two-variable data table type or click Value box workbook
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...