Book Description
Master business modeling and analysis techniques with Microsoft Excel 2013, and transform data into bottomline results. Written by awardwinning educator Wayne Winston, this handson, scenariofocused 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 addin
Table of Contents
 Microsoft Excel 2013: Data Analysis and Business Modeling
 Introduction
 1. Range names
 2. Lookup functions
 3. INDEX function
 4. MATCH function

5. Text functions

Text function syntax
 The LEFT function
 The RIGHT function
 The MID function
 The TRIM function
 The LEN function
 The FIND and SEARCH functions
 The REPT function
 The CONCATENATE and & functions
 The REPLACE function
 The VALUE function
 The UPPER, LOWER, and PROPER functions
 The CHAR function
 The CLEAN Function
 The SUBSTITUTE FUNCTION
 Answers to this chapter’s questions
 Problems

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