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