## Book Description

Master business modeling and analysis techniques with Microsoft Excel 2019 and Office 365 and transform data into bottom-line results. Written by award-winning educator Wayne Winston, this hands-on, scenario-focused guide helps you use Excel to ask the right questions and get accurate, actionable answers. New coverage ranges from Power Query/Get & Transform to Office 365 Geography and Stock data types. Practice with more than 800 problems, many based on actual challenges faced by working analysts.

• Quickly transition from Excel basics to sophisticated analytics
• Use PowerQuery or Get & Transform to connect, combine, and refine data sources
• Leverage Office 365's new Geography and Stock data types and six new functions
• Illuminate insights from geographic and temporal data with 3D Maps
• Summarize data with pivot tables, descriptive statistics, histograms, and Pareto charts
• Use Excel trend curves, multiple regression, and exponential smoothing
• Delve into key financial, statistical, and time functions
• Master all of Excel's great charts
• Quickly create forecasts from historical time-based data
• Use Solver to optimize product mix, logistics, work schedules, and investments—and even rate sports teams
• Run Monte Carlo simulations on stock prices and bidding models
• Learn about basic probability and Bayes' Theorem
• Use the Data Model and Power Pivot to effectively build and use relational data sources inside an Excel workbook
• Automate repetitive analytics tasks by using macros

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

## Product Information

• Title: Microsoft Excel 2019 Data Analysis and Business Modeling, Sixth Edition
• Author(s): Wayne Winston
• Release date: April 2019
• Publisher(s): Microsoft Press
• ISBN: 9781509306091