Microsoft Excel 2010: Data Analysis and Business Modeling, 3rd Edition

Customer Service Note: We are sorry for the inconvenience, but the voucher with the online access code is missing from the first printing of this title. However, we do have the code, available upon request. Please contact O’Reilly Media Customer Service at [email protected] or call 800-889-8969 to obtain your 24-character access code. This code will give you access to your online edition of this Microsoft Press book that is hosted on Safari Books Online! Once you have obtained your unique access code: Go to http://microsoftpress.oreilly.com/​safarienabled and enter your unique 24-character access code, including the hyphens. Set up your free Safari account (required), following the instructions on-screen. Enjoy unlimited access to your online edition of the Web! PLEASE NOTE: Each access code may only be redeemed once. Again, for assistance, please contact O’Reilly Media Customer Service at [email protected] or call 800-889-8969 or 707-827-7019.

Master the business modeling and analysis techniques that help you transform data into bottom-line results. Award-winning business professor and corporate consultant Wayne Winston shares the best of his real-world experience in this practical, scenario-focused guide—fully updated for Excel 2010. Use Wayne’s proven practices and hands-on examples to help you work smarter, make better decisions, and gain the competitive edge.

Table of Contents

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