Table of Contents
1 Basic worksheet modeling
2 Range names
3 Lookup functions
4 The INDEX function
5 The MATCH function
6 Text functions and Flash Fill
7 Dates and date functions
8 Evaluating investments by using net present value criteria
9 IRR, XIRR, and MIRR functions
10 More Excel financial functions
11 Circular references
12 IF, IFERROR, IFS, CHOOSE, and SWITCH functions
13 Time and time functions
14 The Paste Special command
15 Three-dimensional formulas and hyperlinks
16 The auditing tool and the Inquire add-in
17 Sensitivity analysis with data tables
18 The Goal Seek command
19 Using the Scenario Manager for sensitivity analysis
20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions
21 The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions
22 The OFFSET function
23 The INDIRECT function
24 Conditional formatting
25 Sorting in Excel
26 Excel tables and table slicers
27 Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes
28 The analytics revolution
29 An introduction to optimization with Excel Solver
30 Using Solver to determine the optimal product mix
31 Using Solver to schedule your workforce
32 Using Solver to solve transportation or distribution problems
33 Using Solver for capital budgeting
34 Using Solver for financial planning
35 Using Solver to rate sports teams
36 Warehouse location and the GRG Multistart and Evolutionary Solver engines
37 Penalties and the Evolutionary Solver
38 The traveling salesperson problem
39 Importing data from a text file or document
40 Get & Transform (or s/b Get & Transform?)
41 Geography and Stock data types
42 Validating data
43 Summarizing data by using histograms and Pareto charts
44 Summarizing data by using descriptive statistics
45 Using pivot tables and slicers to describe data
46 The Data Model
47 Power Pivot
48 Filled and 3D Power Maps
49 Sparklines
50 Summarizing data with database statistical functions
51 Filtering data and removing duplicates
52 Consolidating data
53 Creating subtotals
54 Charting tricks
55 Estimating straight-line relationships
56 Modeling exponential growth
57 The power curve
58 Using correlations to summarize relationships
59 Introduction to multiple regression
60 Incorporating qualitative factors into multiple regression
61 Modeling nonlinearities and interactions
62 Analysis of variance: One-way ANOVA
63 Randomized blocks and two-way ANOVA
64 Using moving averages to understand time series
65 Winters method and the Forecast Sheet
66 Ratio-to-moving-average forecast method
67 Forecasting in the presence of special events
68 Introduction to Probability
69 An introduction to random variables
70 The binomial, hypergeometric, and negative binomial random variables
71 The Poisson and exponential random variables
72 The normal random variable and Z-scores
73 Weibull and beta distributions: Modeling machine life and project duration
74 Making probability statements from forecasts
75 Using the lognormal random variable to model stock prices
76 Importing Historical stock data into Excel
77 Introduction to Monte Carlo simulation
78 Calculating an optimal bid
79 Simulating stock prices and asset-allocation modeling
80 Fun and games: Simulating gambling and sporting event probabilities
81 Using resampling to analyze data
82 Pricing stock options
83 Determining customer value
84 The economic order quantity inventory model
85 Inventory modeling with uncertain demand
86 Queueing theory: The mathematics of waiting in line
87 Estimating a demand curve
88 Pricing products by using tie-ins
89 Pricing products by using subjectively determined demand
90 Nonlinear pricing
91 Array formulas and functions
92 Recording macros
93 Advanced Sensitivity Analysis