## 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 2013, and transform data into bottom-line results. Written by award-winning educator Wayne Winston, this hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables—and how to effectively build a relational data source inside an Excel workbook.

Solve real business problems with Excel—and sharpen your edge

• Summarize data with PivotTables and Descriptive Statistics

• Explore new trends in predictive and prescriptive analytics

• Use Excel Trend Curves, multiple regression, and exponential smoothing

• Master advanced Excel functions such as OFFSET and INDIRECT

• Delve into key financial, statistical, and time functions

• Make your charts more effective with the Power View tool

• Tame complex optimization problems with Excel Solver

• Run Monte Carlo simulations on stock prices and bidding models

• Apply important modeling tools such as the Inquire add-in

• ## Table of Contents

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