## Book description

Master the business modeling and analysis techniques that help you transform data into bottom-line results. For more than a decade, Wayne Winston has been teaching corporate clients and MBA students the most effective ways to use Excel to solve business problems and make better decisions. Now this award-winning educator shares the best of his expertise in this hands-on, scenario-focused guide—fully updated for Excel 2010!

• Model investment risks and returns

• Analyze your sales team’s effectiveness

• Create best, worst, and most-likely case scenarios

• Compare lease vs. buy, and calculate loan terms

• See how price, advertising, and seasonality affect sales

• Manage inventory with precision

• Quantify the value of customer loyalty

• Calculate your break-even number and ROI

• Maximize scheduling efficiency

• Express “home-field advantage” in real numbers

• Project company growth, predict election results, and more!

• Plus—introduce yourself to PowerPivot for Excel

• Your companion web content includes:

• Hundreds of scenario-based practice problems

• All the book’s sample files—plus customizable templates

1. Microsoft® Excel® 2010: Data Analysis and Business Modeling
2. Introduction
3. 1. What’s New in Excel 2010
1. Customizable Ribbon
2. Sparklines
3. Slicers
4. PowerPivot
5. New Excel Solver
6. File Tab
7. New Excel Functions
8. New Equation Editor
9. Improved Data Bars
10. Paste Special Live Preview
4. 2. Range Names
1. How Can I Create Named Ranges?
2. Answers to This Chapter’s Questions
3. Problems
5. 3. Lookup Functions
1. Syntax of the Lookup Functions
2. Answers to This Chapter’s Questions
3. Problems
6. 4. The INDEX Function
7. 5. The MATCH Function
8. 6. Text Functions
1. Text Function Syntax
2. Answers to This Chapter’s Questions
3. Problems
9. 7. Dates and Date Functions
10. 8. Evaluating Investments by Using Net Present Value Criteria
11. 9. Internal Rate of Return
12. 10. More Excel Financial Functions
1. Answers to This Chapter’s Questions
2. Problems
13. 11. Circular References
14. 12. IF Statements
15. 13. Time and Time Functions
16. 14. The Paste Special Command
17. 15. Three-Dimensional Formulas
1. Answers to This Chapter’s Questions
18. 16. The Auditing Tool
19. 17. Sensitivity Analysis with Data Tables
20. 18. The Goal Seek Command
21. 19. Using the Scenario Manager for Sensitivity Analysis
1. Answer to This Chapter’s Question
2. Problems
22. 20. The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
1. Answers to This Chapter’s Questions
2. Problems
23. 21. The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
24. 22. The OFFSET Function
1. Answers to This Chapter’s Questions
2. Problems
25. 23. The INDIRECT Function
26. 24. Conditional Formatting
1. Answers to This Chapter’s Questions
2. Problems
27. 25. Sorting in Excel
28. 26. Tables
29. 27. Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes
30. 28. An Introduction to 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. Answer to This Chapter’s Questions
3. Problems
38. 36. Penalties and the Evolutionary Solver
1. Answers to This Chapter’s Questions
2. Problems
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
1. Answers to This Chapter’s Questions
2. Problems
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. Sparklines
47. 45. Summarizing Data with Database Statistical Functions
48. 46. Filtering Data and Removing Duplicates
49. 47. Consolidating Data
50. 48. Creating Subtotals
51. 49. Estimating Straight Line Relationships
52. 50. Modeling Exponential Growth
53. 51. The Power Curve
54. 52. Using Correlations to Summarize Relationships
1. Answer to This Chapter’s Question
2. Problems
55. 53. Introduction to Multiple Regression
56. 54. Incorporating Qualitative Factors into Multiple Regression
57. 55. Modeling Nonlinearities and Interactions
58. 56. Analysis of Variance: One-Way ANOVA
59. 57. Randomized Blocks and Two-Way ANOVA
60. 58. Using Moving Averages to Understand Time Series
61. 59. Winters’s Method
1. Time Series Characteristics
2. Parameter Definitions
3. Initializing Winters’s Method
4. Estimating the Smoothing Constants
5. Remarks
6. Problems
62. 60. Ratio-to-Moving-Average Forecast Method
63. 61. Forecasting in the Presence of Special Events
64. 62. An Introduction to Random Variables
65. 63. The Binomial, Hypergeometric, and Negative Binomial Random Variables
66. 64. The Poisson and Exponential Random Variable
67. 65. The Normal Random Variable
68. 66. Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
69. 67. Making Probability Statements from Forecasts
70. 68. Using the Lognormal Random Variable to Model Stock Prices
1. Answers to This Chapter’s Questions
2. Problems
71. 69. Introduction to Monte Carlo Simulation
1. Answers to This Chapter’s Questions
2. Problems
72. 70. Calculating an Optimal Bid
73. 71. Simulating Stock Prices and Asset Allocation Modeling
74. 72. Fun and Games: Simulating Gambling and Sporting Event Probabilities
75. 73. Using Resampling to Analyze Data
76. 74. Pricing Stock Options
77. 75. Determining Customer Value
78. 76. The Economic Order Quantity Inventory Model
79. 77. Inventory Modeling with Uncertain Demand
1. Answers to This Chapter’s Questions
2. Problems
80. 78. Queuing Theory: The Mathematics of Waiting in Line
81. 79. Estimating a Demand Curve
82. 80. Pricing Products by Using Tie-Ins
83. 81. Pricing Products by Using Subjectively Determined Demand
84. 82. Nonlinear Pricing
85. 83. Array Formulas and Functions
1. Answers to This Chapter’s Questions
2. Problems
86. 84. PowerPivot
1. Answers to This Chapter’s Questions