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

Front Cover
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.

Selected pages

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
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

Other editions - View all

Common terms and phrases

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...

About the author (2006)

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.

Bibliographic information