## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

## Book Description

Master business modeling and analysis techniques with Microsoft Excel 2016, and transform data into bottom-line results. Written by award-winning educator Wayne Winston, this hands on, scenario-focused guide helps you use Excel’s newest tools to ask the right questions and get accurate, actionable answers. This edition adds 150+ new problems with solutions, plus a chapter of basic spreadsheet models to make sure you’re fully up to speed.

Solve real business problems with Excel—and build your competitive advantage

• Quickly transition from Excel basics to sophisticated analytics

• Summarize data by using PivotTables and Descriptive Statistics

• Use Excel trend curves, multiple regression, and exponential smoothing

• Master advanced functions such as OFFSET and INDIRECT

• Delve into key financial, statistical, and time functions

• Leverage the new charts in Excel 2016 (including box and whisker and waterfall charts)

• Make charts more effective by using Power View

• Tame complex optimizations by using Excel Solver

• Run Monte Carlo simulations on stock prices and bidding models

• Work with the AGGREGATE function and table slicers

• Create PivotTables from data in different worksheets or workbooks

• Learn about basic probability and Bayes’ Theorem

• Automate repetitive tasks by using macros

• ## Table of Contents

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