Book description
Well known consultant, statistician, and business professor Wayne Winston teaches by example the best ways to use Microsoft Excel for data analysis, modeling, and decision making within realworld business scenarios.
Publisher resources
Table of contents
 Microsoft® Excel Data Analysis and Business Modeling
 A Note Regarding Supplemental Files
 Introduction

1. Range Names
 How Can I Create Range Names?
 How Do I Delete a Range Name?
 How Do I Change a Range Name?

How Do I Name a Constant?
 To compute total sales for a year, I often average the 12 monthly sales below the current cell. Can I name my formula annualaverage so that when I enter annualaverage in a cell the appropriate average is computed?
 How can I easily select a cell range?
 How can I paste a list of all range names (and the cells they represent) into my spreadsheet?
 Remarks
 Problems

2. Natural Language Range Names
 How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?
 How do I use natural language range names to incorporate a relationship such as Month(t) Ending Inventory = Month(t) Beginning Inventory + Month(t) Production –Month(t) Demand in a spreadsheet formula?
 Problems
 3. Lookup Functions
 4. The INDEX Function

5. The MATCH Function
 Given monthly sales for several products, how do I write a formula that will calculate the sales of a product during a given month? For example, how much of product 2 did I sell during June?
 Given a list of baseball player salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth highest salary?
 Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project’s initial investment cost?
 Problems

6. Text Functions
 Text Function Syntax
 Text Functions in Action
 Using the Text To Columns Command to Extract Data
 Problems

7. Dates and Date Functions
 When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change 37625 to a normal date?
 Can I enter a formula that automatically displays today’s date?
 How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
 How do I determine the number of workdays between two dates?
 I have 500 different dates entered in an Excel worksheet. How do I write formulas that will extract from each date the month, year, day of the month, and day of the week?
 Problems
 8. Evaluating Investments with Net Present Value Criteria
 9. Internal Rate of Return

10. Functions for Personal Financial Decisions: The PV, FV, PMT, PPMT, and IPMT Functions
 Should I pay $11,000 today for a copier or $3,000 a year for 5 years?
 If I invest $2,000 a year for 40 years toward my retirement and earn 8 percent a year on my investments, how much will I have when I retire?
 I am borrowing $10,000 on a 10month loan with an annual interest rate of 8 percent. What will my monthly payments be? How much principal and interest am I paying each month?
 Problems
 11. Circular References
 12. IF Statements
 13. The Paste Special Command

14. The Auditing Tool
 I've just been handed a 5000row spreadsheet that computes the net present value (NPV) for a new car. In the spreadsheet, my financial analyst made an assumption about the annual percentage of the growth in the product's price. What cells in the spreadsheet are affected by this assumption?
 I think my financial analyst made an error in computing year 1 beforetax profit. What cells in the spreadsheet model were used for this calculation?
 How does the auditing tool work when I’m working with data in more than one worksheet or workbook?
 Problems
 15. Sensitivity Analysis with Data Tables

16. The Goal Seek Command
 For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?
 We want to pay off our mortgage in 15 years. The annual interest rate is 6 percent. The bank’s told us we can afford monthly payments of $2000. How much can we borrow?
 I always had trouble with "story problems" in highschool algebra. Can Excel make solving story problems easier?
 Problems
 17. Using the Scenario Manager for Sensitivity Analysis
 18. Creating and Using Spinners for Sensitivity Analysis

19. The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions
 How many songs were sung by each singer?
 How many songs were not sung by Eminem?
 How many songs lasted at least 4 minutes?
 How many songs were longer than average?
 How many songs were sung by a singer whose last name begins with S?
 How many songs were sung by a singer whose last name contains six letters?
 How many songs were sung after June 15, 2005?
 How many songs were sung before the beginning of 2009?
 How many songs lasted exactly 4 minutes?
 How do I count the number of cells in a range containing numbers?
 How do I count the number of blank cells in a range?
 How do I count the number of nonblank cells in a range?
 Remarks
 Problems

20. The SUMIF Function
 What was the total dollar amount of merchandise sold by each salesperson?
 How many units were returned?
 What was the total dollar volume sold in 2005 or later?
 How many units of lip gloss were sold? How much revenue did lip gloss sales bring in?
 What dollar amount of sales were not made by Jen?
 Remarks
 Problems

21. The OFFSET Function
 How can I create a reference to a rectangular range of cells that is a specified number of rows and columns from a cell or another range of cells?
 How can I perform a lookup operation keying off the rightmost column instead of the leftmost column in a table range?
 I run a small video store. In a spreadsheet, my accountant has listed the name of each movie in stock and the number of copies in stock. Unfortunately, for each movie this information is in a single cell. How can I extract the number of copies of each movie in stock to a separate cell?
 I am charting my company’s monthly unit sales. Each month I download the most recent month’s unit sales. I would like my chart to update automatically. Is there an easy way to accomplish this?
 Problems
 22. The INDIRECT Function

23. Conditional Formatting
 How can I highlight monthly stock returns so that every good month is highlighted in one color and every bad month is highlighted in another?
 Given quarterly corporate revenues, how can I highlight quarters in which revenues increased over the previous quarter in one color and quarters in which revenues decreased from the previous quarter in another?
 Given a list of dates, how can I highlight the weekend dates in a specific color?
 Our basketball coach has given each player a rating between 1 and 10 for her ability to play guard, forward, or center. Can I set up a spreadsheet that highlights the ability of each player to play the position to which she’s assigned?
 Problems
 24. An Introduction to Optimization with the Excel Solver
 25. Using Solver to Determine the Optimal Product Mix
 26. Using Solver to Solve Transportation or Distribution Problems
 27. Using Solver to Schedule Your Workforce
 28. Using Solver for Capital Budgeting
 29. Using Solver for Financial Planning
 30. Using Solver to Rate Sports Teams
 31. Importing Text or Microsoft Word Data into Excel

32. Importing Data from the Web into Excel
 The Web site for MSN Money Central provides analyst ratings (buy, sell, and hold) for stocks. How can I import this information into Excel?
 I need to gather data about monthly price changes in the U.S. over the past 80 years. I know this data is available on the Web. How can I import this data into Excel so that I can incorporate it into other business analyses?
 Is there a way I can download current stock prices into Excel?
 Problems

33. Validating Data
 I’m entering scores of professional basketball games into Excel. I know that a team scores between 50 and 200 points a game. I once entered 1000 points instead of 100 points, which messed up my analysis. Is there a way to have Excel prevent me from making this type of error?
 I’m entering the date and amount of my business expenses for a new year. Early in the year, I often enter the previous year in the date by mistake. Is there a way I can set up Excel to prevent me from making this type of error?
 I’m entering a long list of numbers. Can I have Excel warn me if I enter a nonnumeric value?
 My assistant needs to enter state abbreviations as she enters dozens and dozens of sales transactions. Can we set up a list of state abbreviations to minimize the chance that she’ll enter an incorrect abbreviation?
 Problems
 34. Summarizing Data with Histograms

35. Summarizing Data with Descriptive Statistics
 What defines a typical value for a data set?
 How can I measure how much a data set spreads out from its typical value?
 Together, what do the mean and standard deviation of a data set tell me about the data?
 How can I use descriptive statistics to compare data sets?
 How can I find the 90th percentile of a data set? For a given data point, can I easily find its percentile ranking within the data set?
 Problems
 36. Using PivotTables to Describe Data

37. Summarizing Data with Database Statistical Functions
 How many dollars worth of lip gloss did Jen sell?
 What was the average number of units of lipstick sold each time Jen made a sale in the East region?
 How many dollars of sales were made by Emilee or in the East region?
 How many dollars worth of lipstick were sold by Colleen or Zaret in the East region?
 How many lipstick transactions were not in the East?
 How many dollars worth of lipstick did Jen sell during 2004?
 How many units of makeup were sold for a price of at least $3.20?
 What is the total dollar amount each salesperson sold of each product?
 What cute tricks can I use to set up criteria ranges?
 I have a database that lists for each sales transaction the revenue, date, and product ID code. Given the date and ID code for a transaction, is there an easy way to pick off the transaction’s revenue?
 Problems

38. Filtering Data
 Locate all transactions in which Jen sold lip gloss
 Locate the ten largest transactions (by revenue)
 Locate the bottom 5 percent of transactions (by revenue) in which Colleen was the salesperson
 Locate all 2005 lipstick transactions in which Ashley was the salesperson
 Locate all foundation transactions in the first six months of 2005 for which Emilee or Jen was the salesperson and the average per unit price was more than $3.20
 Problems
 39. Consolidating Data
 40. Creating Subtotals
 41. Estimating Straight Line Relationships
 42. Modeling Exponential Growth
 43. The Power Curve
 44. Using Correlations to Summarize Relationships

45. Introduction to Multiple Regression
 A factory produces three products (A, B, and C). How can we predict the cost of running the factory based on knowing the number of units produced?
 How accurate are our forecasts for predicting monthly cost from units produced?
 I know how to use the Data Analysis command to run a multiple regression. Is there a way to "run the regression" without using this command and place the regression’s results in the same worksheet as the data?
 46. Incorporating Qualitative Factors into Multiple Regression

47. Modeling Nonlinearities and Interactions
 What does it mean when we say that an independent variable has a nonlinear effect on a dependent variable?
 What does it mean when we say that the effects of two independent variables on a dependent variable interact?
 How can we test for the presence of nonlinearity and interaction in a regression?
 Problems for Chapter 45–

48. Analysis of Variance: OneWay ANOVA
 Microsoft Press wants to know whether the position of its books in the computer book section of bookstores influences sales. More specifically, does it really matter whether the books are placed in the front, back, or middle of the computer book section?
 If we are determining whether populations have significantly different means, why is the technique called analysis of variance?
 How can we use the results of a oneway ANOVA for forecasting?
 Problems

49. Randomized Blocks and TwoWay ANOVA
 Based on a knowledge of sales rep and district, how can I forecast sales? How accurate are my sales forecasts?
 How can I determine whether varying the price and the amount of advertising affects the sales of a video game? How can I determine whether price and advertising interact significantly?
 How can I interpret the effects of price and advertising on sales when there is a significant interaction between price and advertising?
 Problems
 50. Using Moving Averages to Understand Time Series
 51. Forecasting with Moving Averages
 52. Forecasting in the Presence of Special Events
 53. An Introduction to Random Variables
 54. The Binomial and Hypergeometric Random Variables
 55. The Poisson and Exponential Random Variable
 56. The Normal Random Variable
 57. Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
 58. Introduction to Monte Carlo Simulation
 59. Calculating an Optimal Bid

60. Simulating Stock Prices and Asset Allocation Modeling
 I recently bought 100 shares of GE. What is the probability that during the next year this investment will return more than 10 percent?
 I’m trying to determine how to allocate my investment portfolio between stocks, Treasury bills, and bonds. What asset allocation over a 5year planning horizon will yield an expected return of at least 10 percent and minimize risk?
 Problems

61. Fun and Games: Simulating Gambling and Sporting Event Probabilities
 What is the probability of winning at craps?
 In fivecard draw poker, what is the probability of getting three of a kind?
 Before the 2003 Super Bowl, Oakland was favored by 3 points. What was the probability that Tampa Bay would beat Oakland?
 Going into the 2003 NCAA men’s basketball Final Four, what was the probability of each team winning the tournament?
 Problems
 62. Using Resampling to Analyze Data

63. Pricing Stock Options
 What are put and call options?
 What are American and European options?
 As a function of the stock price on the exercise date, what do the payoffs look like for European puts and calls?
 What parameters determine the value of an option?
 How do we estimate the volatility of a stock based on historical data?
 How can I use Excel to implement the BlackScholes formula?
 How do changes in key parameters change the value of a put or call option?
 How can I use the BlackScholes formula to estimate a stock’s volatility?
 I don’t want somebody messing up my neat optionpricing formulas. How can I protect the formulas in my worksheet so that nobody can mess them up?
 How can option pricing be used to help companies make better investment decisions?
 Problems

64. Determining Customer Value
 A creditcard company currently has an 80 percent retention rate. How will the company’s profitability improve if the retention rate increases to 90 percent or higher?
 A longdistance phone company gives the competition’s customers an incentive to switch. How large an incentive should they give?
 Problems
 65. The Economic Order Quantity Inventory Model
 66. Determining the Reorder Point: How Low Should I Let My Inventory Level Go Before I Reorder?

67. Queuing Theory: The Mathematics of Waiting in Line
 What factors affect the time we spend waiting in line and the number of people waiting in line?
 Under what conditions can we talk about the average time spent in a queuing system or the average number of people present in a queuing system?
 Why does variability degrade the performance of a queuing system?
 Can I easily determine the average time a person spends at airport security or waiting in line at a bank?
 Problems
 68. Estimating a Demand Curve
 69. Pricing Products with TieIns

70. Pricing Products Using Subjectively Determined Demand
 Sometimes I don’t know the price elasticity for a product. In other situations, I don’t believe a linear or power demand curve is relevant. Can I still estimate a demand curve and use Solver to determine a profitmaximizing price?
 How can a small drugstore determine the profitmaximizing price for lipstick?
 Problems
 71. Nonlinear Pricing

72. Array Formulas and Functions
 What is an array formula?
 How do we interpret formulas such as (D2:D7)*(E2:E7) and SUM(D2:D7*E2:E7)?
 I have a list of names in a single column. These names change often. Is there any easy way to transpose the listed names to a single row so that changes in the original column of names are reflected in the new row of names?
 I have a list of monthly stock returns. Is there a way to determine the number of returns between 30 percent and 20 percent, 10 percent and 0 percent, and so on that will automatically update if I change the original data?
 Can I write a single formula that will sum up the second digit of a list of integers?
 Is there a way to look at two lists of names and determine which names occur on both lists?
 Can I write a formula that averages all numbers in a list that are greater than or equal to the list’s median value?
 What are array constants and how can I use them?
 How do I edit array formulas?
 Given quarterly revenues for a toy store, can I estimate the trend and seasonality of the store’s revenues?
 Problems
 73. Picking Your Fantasy Football Team
 About the Author
 Index
 About the Author
 Copyright
Product information
 Title: Microsoft® Excel Data Analysis and Business Modeling
 Author(s):
 Release date: February 2004
 Publisher(s): Microsoft Press
 ISBN: 9780735619012
You might also like
book
Excel® Data Analysis: Your visual blueprint™ for creating and analyzing data, charts, and PivotTables, 3rd Edition
Advanced techniques for Excel power users Crunch and analyze Excel data the way the professionals do …
book
Microsoft® Office Excel® 2007: Data Analysis and Business Modeling, Second Edition
Master the techniques that business analysts at leading companies use to transform data into bottomline results. …
book
Business Analysis  Third edition
Business analysts must respond to the challenges of today's highly competitive global economy by developing practical, …
book
Microsoft® Excel® 2010: Data Analysis and Business Modeling
Master the business modeling and analysis techniques that help you transform data into bottomline results. For …