Microsoft® Office Excel® 2007: Data Analysis and Business Modeling, Second Edition

Microsoft® Office Excel® 2007: Data Analysis and Business Modeling, Second Edition

by Wayne L. Winston
Released May 2007
Publisher(s): Microsoft Press
ISBN: 9780735623965

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master the techniques that business analysts at leading companies use to transform data into bottom-line results. For more than a decade, well-known consultant and business professor Wayne Winston has been teaching corporate clients and MBA students the most effective ways to use Microsoft Office Excel for data analysis, modeling, and decision making. Now this award-winning educator shares the best of his classroom experience in this practical, business-focused guide—updated and expanded for Excel 2007. Each chapter advances your data analysis and modeling expertise using real-world examples and learn-by-doing exercises. You’ll learn how to create best, worst, and most-likely scenarios for sales, estimate a product’s demand curve, forecast using trend and seasonality, and determine which product mix will yield the greatest profit. You’ll even discover how to interpret the effects of price and advertising on sales and how to assign a dollar value to customer loyalty.

Publisher resources

Download Example Code

Table of contents

  1. Microsoft® Office Excel® 2007: Data Analysis and Business Modeling
  2. A Note Regarding Supplemental Files
  3. Preface
    1. What You Should Know Before Reading this Book
    2. How to Use this Book
    3. Using the Companion CD
    4. Support Information
    5. Acknowledgments
  4. Introduction to Excel 2007: What’s New?
  5. 1. Range Names
    1. How Can I Create Named Ranges?
      1. Using the Name Box to Create a Range Name
      2. Creating Named Ranges by Using the Create from Selection Option
      3. Creating Range Names by Using the Name Manager Option
      4. The Name Manager
    2. Remarks
    3. Problems
  6. 2. Lookup Functions
    1. Syntax of the Lookup Functions
      1. VLOOKUP Syntax
      2. HLOOKUP Syntax
    2. Problems
  7. 3. The INDEX Function
    1. Syntax of the INDEX Function
    2. Problems
  8. 4. The MATCH Function
    1. Problems
  9. 5. Text Functions
    1. Text Function Syntax
      1. The LEFT Function
      2. The RIGHT Function
      3. The MID Function
      4. The TRIM Function
      5. The LEN Function
      6. The FIND and SEARCH Functions
      7. The REPT Function
      8. The CONCATENATE and & Functions
      9. The REPLACE Function
      10. The VALUE Function
      11. The UPPER and LOWER Functions
      12. The CHAR Function
    2. Text Functions in Action
    3. Extracting Data by Using the Text To Columns Wizard
    4. Problems
  10. 6. Dates and Date Functions
    1. Problems
  11. 7. Evaluating Investments by Using Net Present Value Criteria
    1. Problems
  12. 8. Internal Rate of Return
    1. Problems
  13. 9. More Excel Financial Functions
    1. CUMPRINC and CUMIPMT Functions
    2. Problems
  14. 10. Circular References
    1. Problems
  15. 11. IF Statements
    1. Problems
  16. 12. Time and Time Functions
    1. Problems
  17. 13. The Paste Special Command
    1. Problems
  18. 14. The Auditing Tool
    1. Problems
  19. 15. Sensitivity Analysis with Data Tables
    1. Problems
  20. 16. The Goal Seek Command
    1. Problems
  21. 17. Using the Scenario Manager for Sensitivity Analysis
    1. Remarks
    2. Problems
  22. 18. The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
    1. Remarks
    2. Problems
  23. 19. The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
    1. Problems
  24. 20. The OFFSET Function
    1. Remark
    2. Problems
  25. 21. The INDIRECT Function
    1. Problems
  26. 22. Conditional Formatting
    1. Problems
  27. 23. Sorting in Excel
    1. Problems
  28. 24. Tables
    1. Problems
  29. 25. Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes
    1. Spin Buttons and Scroll Bars
    2. Problems
  30. 26. An Introduction to Optimization with Excel Solver
    1. Problems
  31. 27. Using Solver to Determine the Optimal Product Mix
    1. Problems
  32. 28. Using Solver to Schedule Your Workforce
    1. Problems
  33. 29. Using Solver to Solve Transportation or Distribution Problems
    1. Problems
  34. 30. Using Solver for Capital Budgeting
    1. Handling Other Constraints
      1. Solving Binary and Integer Programming Problems
    2. Problems
  35. 31. Using Solver for Financial Planning
    1. Problems
  36. 32. Using Solver to Rate Sports Teams
    1. Problems
  37. 33. Importing Data from a Text File or Document
    1. Problems
  38. 34. Importing Data from the Internet
    1. Problems
  39. 35. Validating Data
    1. Remarks
    2. Problems
  40. 36. Summarizing Data by Using Histograms
    1. Problems
  41. 37. Summarizing Data by Using Descriptive Statistics
    1. Mean
    2. Median
    3. Mode
    4. Kurtosis
    5. Sample variance and sample standard deviation
    6. Range
      1. Using Conditional Formatting to Highlight Outliers
    7. Problems
  42. 38. Using PivotTables to Describe Data
    1. Remarks About Grouping
    2. Problems
  43. 39. Summarizing Data with Database Statistical Functions
    1. Problems
  44. 40. Filtering Data and Removing Duplicates
    1. Problems
  45. 41. Consolidating Data
    1. Problems
  46. 42. Creating Subtotals
    1. Problems
  47. 43. Estimating Straight Line Relationships
    1. Problems
  48. 44. Modeling Exponential Growth
    1. Problems
  49. 45. The Power Curve
    1. Problems
  50. 46. Using Correlations to Summarize Relationships
    1. Filling in the correlation matrix
    2. Using the CORREL function
    3. Relationship between correlation and R2
    4. Correlation and regression towards the mean
    5. Problems
  51. 47. Introduction to Multiple Regression
    1. What is the best prediction equation?
  52. 48. Incorporating Qualitative Factors into Multiple Regression
  53. 49. Modeling Nonlinearities and Interactions
    1. Problems for Chapters 47 Through 49
  54. 50. Analysis of Variance: One-Way ANOVA
    1. Problems
  55. 51. Randomized Blocks and Two-Way ANOVA
    1. Problems
  56. 52. Using Moving Averages to Understand Time Series
    1. Problem
  57. 53. Winter’s Method
    1. Time Series Characteristics
    2. Parameter Definitions
    3. Initializing Winter’s Method
    4. Estimating the Smoothing Constants
    5. Remarks
    6. Problems
  58. 54. Forecasting in the Presence of Special Events
    1. Problems
  59. 55. An Introduction to Random Variables
    1. Problems
  60. 56. The Binomial, Hypergeometric, and Negative Binomial Random Variables
    1. Coke or Pepsi
    2. Elevator Rails
    3. Airline Overbooking
    4. Problems
  61. 57. The Poisson and Exponential Random Variable
    1. Problems
  62. 58. The Normal Random Variable
    1. What fraction of people have an IQ of less than 90?
    2. What fraction of all people have IQs from 95 through 120?
    3. What fraction of all people have IQs of at least 130?
    4. Problems
  63. 59. Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
    1. What is the probability that a machine will last at least 20 hours?
    2. What is the probability that a machine will last from 15 through 30 hours?
    3. Problems
  64. 60. Introduction to Monte Carlo Simulation
    1. Problems
  65. 61. Calculating an Optimal Bid
    1. Problems
  66. 62. Simulating Stock Prices and Asset Allocation Modeling
    1. Problems
  67. 63. Fun and Games: Simulating Gambling and Sporting Event Probabilities
    1. Problems
  68. 64. Using Resampling to Analyze Data
    1. Problems
  69. 65. Pricing Stock Options
    1. Problems
  70. 66. Determining Customer Value
    1. Problems
  71. 67. The Economic Order Quantity Inventory Model
    1. Problems
  72. 68. Inventory Modeling with Uncertain Demand
    1. The back-order case
    2. The lost-sales case
    3. Problems
  73. 69. Queuing Theory: The Mathematics of Waiting in Line
    1. Problems
  74. 70. Estimating a Demand Curve
    1. Problems
  75. 71. Pricing Products by Using Tie-Ins
    1. Problems
  76. 72. Pricing Products by Using Subjectively Determined Demand
    1. Problems
  77. 73. Nonlinear Pricing
    1. Problems
  78. 74. Array Formulas and Functions
    1. How many units of makeup did Jen sell?
    2. How many units of lipstick did Jen sell?
    3. How many units were sold by Jen or were lipstick?
    4. Can I summarize the number of units of each product sold by each salesperson?
    5. Problems
  79. A. About the Author
  80. Index
  81. About the Author
  82. Copyright

Product information

  • Title: Microsoft® Office Excel® 2007: Data Analysis and Business Modeling, Second Edition
  • Author(s): Wayne L. Winston
  • Release date: May 2007
  • Publisher(s): Microsoft Press
  • ISBN: 9780735623965