| Introduction |
|
xxi | |
| Chapter 1 Basic spreadsheet modeling |
|
1 | (8) |
|
Answers to this chapter's questions |
|
|
1 | (7) |
|
|
|
8 | (1) |
| Chapter 2 Range names |
|
9 | (12) |
|
How can I create named ranges? |
|
|
9 | (5) |
|
Using the Name box to create a range name |
|
|
10 | (1) |
|
Creating named ranges by using the Create From Selection option |
|
|
11 | (1) |
|
Creating range names by using the Define Name option |
|
|
12 | (1) |
|
|
|
13 | (1) |
|
Answers to this chapter's questions |
|
|
14 | (5) |
|
|
|
18 | (1) |
|
|
|
19 | (2) |
| Chapter 3 Lookup functions |
|
21 | (8) |
|
Syntax of the lookup functions |
|
|
21 | (1) |
|
|
|
21 | (1) |
|
|
|
22 | (1) |
|
Answers to this chapter's questions |
|
|
22 | (3) |
|
|
|
25 | (4) |
| Chapter 4 The INDEX function |
|
29 | (4) |
|
Syntax of the INDEX function |
|
|
29 | (1) |
|
Answers to this chapter's questions |
|
|
29 | (2) |
|
|
|
31 | (2) |
| Chapter 5 The MATCH function |
|
33 | (6) |
|
Syntax of the MATCH function |
|
|
33 | (2) |
|
Answers to this chapter's questions |
|
|
35 | (3) |
|
|
|
38 | (1) |
| Chapter 6 Text functions |
|
39 | (16) |
|
|
|
40 | (3) |
|
|
|
41 | (1) |
|
|
|
41 | (1) |
|
|
|
41 | (1) |
|
|
|
41 | (1) |
|
|
|
41 | (1) |
|
The FIND and SEARCH functions |
|
|
41 | (1) |
|
|
|
41 | (1) |
|
The CONCATENATE and & functions |
|
|
42 | (1) |
|
|
|
42 | (1) |
|
|
|
42 | (1) |
|
The UPPER, LOWER, and PROPER functions |
|
|
42 | (1) |
|
|
|
42 | (1) |
|
|
|
43 | (1) |
|
|
|
43 | (1) |
|
Answers to this chapter's questions |
|
|
43 | (8) |
|
Extracting data by using the Convert Text To Columns Wizard |
|
|
47 | (4) |
|
|
|
51 | (4) |
| Chapter 7 Dates and date functions |
|
55 | (8) |
|
Answers to this chapter's questions |
|
|
56 | (5) |
|
|
|
61 | (2) |
| Chapter 8 Evaluating investments by using net present value criteria |
|
63 | (6) |
|
Answers to this chapter's questions |
|
|
64 | (4) |
|
|
|
68 | (1) |
| Chapter 9 Internal rate of return |
|
69 | (6) |
|
Answers to this chapter's questions |
|
|
70 | (3) |
|
|
|
73 | (2) |
| Chapter 10 More Excel financial functions |
|
75 | (14) |
|
Answers to this chapter's questions |
|
|
75 | (8) |
|
CUMPRINC and CUMIPMT functions |
|
|
80 | (3) |
|
|
|
83 | (6) |
| Chapter 11 Circular references |
|
89 | (6) |
|
Answers to this chapter's questions |
|
|
89 | (3) |
|
|
|
92 | (3) |
| Chapter 12 IF statements |
|
95 | (18) |
|
Answers to this chapter's questions |
|
|
96 | (12) |
|
|
|
108 | (5) |
| Chapter 13 Time and time functions |
|
113 | (6) |
|
Answers to this chapter's questions |
|
|
114 | (4) |
|
|
|
118 | (1) |
| Chapter 14 The Paste Special command |
|
119 | (6) |
|
Answers to this chapter's questions |
|
|
119 | (4) |
|
|
|
123 | (2) |
| Chapter 15 Three-dimensional formulas and hyperlinks |
|
125 | (6) |
|
Answers to this chapter's questions |
|
|
125 | (3) |
|
|
|
128 | (3) |
| Chapter 16 The auditing tool |
|
131 | (8) |
|
Answers to this chapter's questions |
|
|
134 | (4) |
|
|
|
138 | (1) |
| Chapter 17 Sensitivity analysis with data tables |
|
139 | (12) |
|
Answers to this chapter's questions |
|
|
140 | (6) |
|
|
|
146 | (5) |
| Chapter 18 The Goal Seek command |
|
151 | (6) |
|
Answers to this chapter's questions |
|
|
151 | (3) |
|
|
|
154 | (3) |
| Chapter 19 Using the Scenario Manager for sensitivity analysis |
|
157 | (6) |
|
Answer to this chapter's question |
|
|
157 | (4) |
|
|
|
161 | (1) |
|
|
|
161 | (2) |
| Chapter 20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions |
|
163 | (8) |
|
Answers to this chapter's questions |
|
|
165 | (3) |
|
|
|
168 | (1) |
|
|
|
168 | (3) |
| Chapter 21 The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions |
|
171 | (6) |
|
Answers to this chapter's questions |
|
|
172 | (3) |
|
|
|
175 | (2) |
| Chapter 22 The OFFSET function |
|
177 | (14) |
|
Answers to this chapter's questions |
|
|
178 | (9) |
|
|
|
187 | (1) |
|
|
|
187 | (4) |
| Chapter 23 The INDIRECT function |
|
191 | (8) |
|
Answers to this chapter's questions |
|
|
192 | (6) |
|
|
|
198 | (1) |
| Chapter 24 Conditional formatting |
|
199 | (28) |
|
Answers to this chapter's questions |
|
|
201 | (21) |
|
|
|
222 | (5) |
| Chapter 25 Sorting in Excel |
|
227 | (8) |
|
Answers to this chapter's questions |
|
|
227 | (7) |
|
|
|
234 | (1) |
| Chapter 26 Tables |
|
235 | (16) |
|
Answers to this chapter's questions |
|
|
235 | (13) |
|
|
|
248 | (3) |
| Chapter 27 Spin buttons, scroll bars, option buttons, check boxes, combo boxes, and group list boxes |
|
251 | (12) |
|
Answers to this chapter's questions |
|
|
252 | (9) |
|
|
|
261 | (2) |
| Chapter 28 The analytics revolution |
|
263 | (6) |
|
Answers to this chapter's questions |
|
|
263 | (6) |
| Chapter 29 An introduction to optimization with Excel Solver |
|
269 | (4) |
|
|
|
272 | (1) |
| Chapter 30 Using Solver to determine the optimal product mix |
|
273 | (12) |
|
Answers to this chapter's questions |
|
|
273 | (9) |
|
|
|
282 | (3) |
| Chapter 31 Using Solver to schedule your workforce |
|
285 | (6) |
|
Answers to this chapter's question |
|
|
285 | (6) |
|
|
|
288 | (3) |
| Chapter 32 Using Solver to solve transportation or distribution problems |
|
291 | (6) |
|
Answer to this chapter's question |
|
|
291 | (3) |
|
|
|
294 | (3) |
| Chapter 33 Using Solver for capital budgeting |
|
297 | (8) |
|
Answer to this chapter's question |
|
|
297 | (5) |
|
Handling other constraints |
|
|
300 | (1) |
|
Solving binary and integer programming problems |
|
|
301 | (1) |
|
|
|
302 | (3) |
| Chapter 34 Using Solver for financial planning |
|
305 | (6) |
|
Answers to this chapter's questions |
|
|
305 | (4) |
|
|
|
309 | (2) |
| Chapter 35 Using Solver to rate sports teams |
|
311 | (6) |
|
Answer to this chapter's question |
|
|
312 | (2) |
|
Why is our model not a linear Solver model? |
|
|
314 | (1) |
|
|
|
314 | (3) |
| Chapter 36 Warehouse location and the GRG Multistart and Evolutionary Solver engines |
|
317 | (10) |
|
Understanding the GRG Multistart and Evolutionary Solver engines |
|
|
317 | (4) |
|
How does Solver solve linear model problems? |
|
|
317 | (1) |
|
How does the GRG Nonlinear engine solve nonlinear optimization models? |
|
|
318 | (3) |
|
How does the Evolutionary Solver engine tackle nonsmooth optimization problems? |
|
|
321 | (1) |
|
Answer to this chapter's questions |
|
|
321 | (5) |
|
|
|
326 | (1) |
| Chapter 37 Penalties and the Evolutionary Solver |
|
327 | (6) |
|
Answers to this chapter's questions |
|
|
327 | (3) |
|
Using conditional formatting to highlight each employee's ratings |
|
|
330 | (1) |
|
|
|
330 | (3) |
| Chapter 38 The traveling salesperson problem |
|
333 | (4) |
|
Answers to this chapter's questions |
|
|
333 | (3) |
|
|
|
336 | (1) |
| Chapter 39 Importing data from a text file or document |
|
337 | (6) |
|
Answers to this chapter's question |
|
|
337 | (4) |
|
|
|
341 | (2) |
| Chapter 40 Validating data |
|
343 | (8) |
|
Answers to this chapter's questions |
|
|
343 | (5) |
|
|
|
347 | (1) |
|
|
|
348 | (3) |
| Chapter 41 Summarizing data by using histograms and Pareto charts |
|
351 | (14) |
|
Answers to this chapter's questions |
|
|
351 | (11) |
|
|
|
362 | (3) |
| Chapter 42 Summarizing data by using descriptive statistics |
|
365 | (18) |
|
Answers to this chapter's questions |
|
|
366 | (14) |
|
Using conditional formatting to highlight outliers |
|
|
371 | (9) |
|
|
|
380 | (3) |
| Chapter 43 Using PivotTables and slicers to describe data |
|
383 | (46) |
|
Answers to this chapter's questions |
|
|
384 | (40) |
|
|
|
412 | (12) |
|
|
|
424 | (5) |
| Chapter 44 The Data Model |
|
429 | (10) |
|
Answers to this chapter's questions |
|
|
429 | (8) |
|
|
|
437 | (2) |
| Chapter 45 Power Pivot |
|
439 | (12) |
|
Answers to this chapter's questions |
|
|
440 | (9) |
|
|
|
449 | (2) |
| Chapter 46 Power View and 3D Maps |
|
451 | (14) |
|
Questions answered in this chapter |
|
|
452 | (12) |
|
|
|
464 | (1) |
| Chapter 47 Sparklines |
|
465 | (6) |
|
Answers to this chapter's questions |
|
|
465 | (4) |
|
|
|
469 | (2) |
| Chapter 48 Summarizing data with database statistical functions |
|
471 | (10) |
|
Answers to this chapter's questions |
|
|
473 | (5) |
|
|
|
478 | (3) |
| Chapter 49 Filtering data and removing duplicates |
|
481 | (14) |
|
Answers to this chapter's questions |
|
|
483 | (11) |
|
|
|
494 | (1) |
| Chapter 50 Consolidating data |
|
495 | (6) |
|
Answer to this chapter's question |
|
|
495 | (4) |
|
|
|
499 | (2) |
| Chapter 51 Creating subtotals |
|
501 | (6) |
|
Answers to this chapter's questions |
|
|
501 | (4) |
|
|
|
505 | (2) |
| Chapter 52 Charting tricks |
|
507 | (36) |
|
Answers to this chapter's questions |
|
|
508 | (32) |
|
|
|
540 | (3) |
| Chapter 53 Estimating straight-line relationships |
|
543 | (8) |
|
Answers to this chapter's questions |
|
|
544 | (5) |
|
|
|
549 | (2) |
| Chapter 54 Modeling exponential growth |
|
551 | (4) |
|
Answers to this chapter's question |
|
|
551 | (3) |
|
|
|
554 | (1) |
| Chapter 55 The power curve |
|
555 | (6) |
|
Answer to this chapter's question |
|
|
557 | (2) |
|
|
|
559 | (2) |
| Chapter 56 Using correlations to summarize relationships |
|
561 | (6) |
|
Answer to this chapter's question |
|
|
563 | (3) |
|
Filling in the correlation matrix |
|
|
564 | (1) |
|
Using the CORREL function |
|
|
565 | (1) |
|
Relationship between correlation and R-squared |
|
|
565 | (1) |
|
Correlation and regression toward the mean |
|
|
566 | (1) |
|
|
|
566 | (1) |
| Chapter 57 Introduction to multiple regression |
|
567 | (8) |
|
Answers to this chapter's questions |
|
|
567 | (8) |
| Chapter 58 Incorporating qualitative factors into multiple regression |
|
575 | (10) |
|
Answers to this chapter's questions |
|
|
575 | (10) |
| Chapter 59 Modeling nonlinearities and interactions |
|
585 | (8) |
|
Answers to this chapter's questions |
|
|
585 | (4) |
|
Problems for
Chapters 57 through 59 |
|
|
589 | (4) |
| Chapter 60 Analysis of variance: One-way ANOVA |
|
593 | (6) |
|
Answers to this chapter's questions |
|
|
594 | (3) |
|
|
|
597 | (2) |
| Chapter 61 Randomized blocks and two-way ANOVA |
|
599 | (10) |
|
Answers to this chapter's questions |
|
|
600 | (7) |
|
|
|
607 | (2) |
| Chapter 62 Using moving averages to understand time series |
|
609 | (4) |
|
Answers to this chapter's question |
|
|
609 | (2) |
|
|
|
611 | (2) |
| Chapter 63 Winters method |
|
613 | (6) |
|
Time-series characteristics |
|
|
613 | (1) |
|
|
|
613 | (1) |
|
Initializing Winters method |
|
|
614 | (1) |
|
Estimating the smoothing constants |
|
|
615 | (2) |
|
|
|
617 | (1) |
|
|
|
617 | (2) |
| Chapter 64 Ratio-to-moving-average forecast method |
|
619 | (4) |
|
Answers to this chapter's questions |
|
|
619 | (3) |
|
|
|
622 | (1) |
| Chapter 65 Forecasting in the presence of special events |
|
623 | (8) |
|
Answers to this chapter's questions |
|
|
623 | (7) |
|
|
|
630 | (1) |
| Chapter 66 An introduction to probability |
|
631 | (10) |
|
Answers to this chapter's questions |
|
|
631 | (6) |
|
|
|
637 | (4) |
| Chapter 67 An introduction to random variables |
|
641 | (6) |
|
Answers to this chapter's questions |
|
|
641 | (4) |
|
|
|
645 | (2) |
| Chapter 68 The binomial, hypergeometric, and negative binomial random variables |
|
647 | (8) |
|
Answers to this chapter's questions |
|
|
648 | (5) |
|
|
|
653 | (2) |
| Chapter 69 The Poisson and exponential random variable |
|
655 | (4) |
|
Answers to this chapter's questions |
|
|
655 | (3) |
|
|
|
658 | (1) |
| Chapter 70 The normal random variable and Z-scores |
|
659 | (10) |
|
Answers to this chapter's questions |
|
|
659 | (7) |
|
|
|
666 | (3) |
| Chapter 71 Weibull and beta distributions: Modeling machine life and duration of a project |
|
669 | (6) |
|
Answers to this chapter's questions |
|
|
669 | (4) |
|
|
|
673 | (2) |
| Chapter 72 Making probability statements from forecasts |
|
675 | (4) |
|
Answers to this chapter's questions |
|
|
675 | (2) |
|
|
|
677 | (2) |
| Chapter 73 Using the lognormal random variable to model stock prices |
|
679 | (4) |
|
Answers to this chapter's questions |
|
|
679 | (3) |
|
|
|
682 | (1) |
|
|
|
682 | (1) |
| Chapter 74 Introduction to Monte Carlo simulation |
|
683 | (10) |
|
Answers to this chapter's questions |
|
|
683 | (7) |
|
The impact of risk on your decision |
|
|
689 | (1) |
|
Confidence interval for mean profit |
|
|
690 | (1) |
|
|
|
690 | (3) |
| Chapter 75 Calculating an optimal bid |
|
693 | (6) |
|
Answers to this chapter's questions |
|
|
693 | (3) |
|
|
|
696 | (3) |
| Chapter 76 Simulating stock prices and asset-allocation modeling |
|
699 | (10) |
|
Answers to this chapter's questions |
|
|
699 | (8) |
|
|
|
707 | (2) |
| Chapter 77 Fun and games: Simulating gambling and sporting-event probabilities |
|
709 | (8) |
|
Answers to this chapter's questions |
|
|
709 | (6) |
|
|
|
715 | (2) |
| Chapter 78 Using resampling to analyze data |
|
717 | (4) |
|
Answer to this chapter's question |
|
|
717 | (2) |
|
|
|
719 | (2) |
| Chapter 79 Pricing stock options |
|
721 | (14) |
|
Answers to this chapter's questions |
|
|
722 | (10) |
|
|
|
732 | (3) |
| Chapter 80 Determining customer value |
|
735 | (6) |
|
Answers to this chapter's questions |
|
|
735 | (4) |
|
|
|
739 | (2) |
| Chapter 81 The economic order quantity inventory model |
|
741 | (6) |
|
Answers to this chapter's questions |
|
|
741 | (4) |
|
|
|
745 | (2) |
| Chapter 82 Inventory modeling with uncertain demand |
|
747 | (6) |
|
Answers to this chapter's questions |
|
|
748 | (4) |
|
|
|
748 | (1) |
|
|
|
749 | (3) |
|
|
|
752 | (1) |
| Chapter 83 Queuing theory: The mathematics of waiting in line |
|
753 | (6) |
|
Answers to this chapter's questions |
|
|
753 | (4) |
|
|
|
757 | (2) |
| Chapter 84 Estimating a demand curve |
|
759 | (6) |
|
Answers to this chapter's questions |
|
|
759 | (4) |
|
|
|
763 | (2) |
| Chapter 85 Pricing products by using tie-ins |
|
765 | (6) |
|
Answer to this chapter's question |
|
|
765 | (3) |
|
|
|
768 | (3) |
| Chapter 86 Pricing products by using subjectively determined demand |
|
771 | (6) |
|
Answer to this chapter's questions |
|
|
771 | (3) |
|
|
|
774 | (3) |
| Chapter 87 Nonlinear pricing |
|
777 | (8) |
|
Answers to this chapter's questions |
|
|
777 | (7) |
|
|
|
784 | (1) |
| Chapter 88 Array formulas and functions |
|
785 | (18) |
|
Answers to this chapter's questions |
|
|
786 | (14) |
|
|
|
800 | (3) |
| Chapter 89 Recording macros |
|
803 | (12) |
|
Answers to this chapter's questions |
|
|
803 | (9) |
|
|
|
812 | (3) |
| Index |
|
815 | |
