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 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. Each chapter advances your data analysis and modeling expertise using real-world examples and learn-by-doing exercises. You also get all the book’s problem-and-solution files on CD—for all the practice you need to solve complex problems and work smarter with Excel.
Learn how to solve real business problems with Excel!
Create best, worst, and most-likely scenarios for sales
Calculate how long it would take to recoup a project’s startup costs
Plan personal finances, such as computing loan terms or saving for retirement
Estimate a product’s demand curve
Simulate stock performance over a year
Determine which product mix will yield the greatest profits
Interpret the effects of price and advertising on sales
Assign a dollar value to customer loyalty
Manage inventory and order quantities with precision
Create customer service queues with short wait times
Estimate the probabilities of equipment failure
Model business uncertainties
Get new perspectives on data with PivotTable dynamic views
Help predict quarterly revenue, outcomes of sporting events, presidential elections, and more!
On the CD:
Practice files for all the book’s exercises
Solutions for problem sets
Fully searchable eBook
A Note Regarding the CD or DVD
The print version of this book ships with a CD or DVD. For those customers purchasing one of the digital formats in which this book is available, we are pleased to offer the CD/DVD content as a free download via O'Reilly Media's Digital Distribution services. To download this content, please visit O'Reilly's web site, search for the title of this book to find its catalog page, and click on the link below the cover image (Examples, Companion Content, or Practice Files). Note that while we provide as much of the media content as we are able via free download, we are sometimes limited by licensing restrictions. Please direct any questions or concerns to firstname.lastname@example.org.
Chapter 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?
Chapter 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?
Chapter 3 Lookup Functions
Syntax of the LOOKUP Functions
Chapter 4 The INDEX Function
Syntax of the INDEX Function
Chapter 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?
Chapter 6 Text Functions
Text Function Syntax
Text Functions in Action
Using the Text To Columns Command to Extract Data
Chapter 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?
Chapter 8 Evaluating Investments with Net Present Value Criteria
What is net present value?
How do I use the Excel NPV function?
How can I compute NPV when cash flows are received at the beginning of a year or in the middle of the year?
How can I compute NPV when cash flows are received at irregular intervals?
Chapter 9 Internal Rate of Return
How can I find the IRR of cash flows?
Does a project always have a unique IRR?
Are there conditions that guarantee a project will have a unique IRR?
If two projects both have a single IRR, how do I use the projects’ IRRs?
How can I find the IRR of irregularly spaced cash flows?
Chapter 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 10-month 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?
Chapter 11 Circular References
I often get a circular reference message from Excel. Does this mean I’ve made an error?
How can I resolve circular references?
Chapter 12 IF Statements
Chapter 13 The Paste Special Command
How can I move the results of calculations (not the formulas) to a different part of a worksheet?
I have a list of names in a single column. How can I make the list appear in one row instead of one column?
Chapter 14 The Auditing Tool
I've just been handed a 5000-row 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 before-tax 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?
Chapter 15 Sensitivity Analysis with Data Tables
I’m thinking of starting a store to sell gourmet lemonade in the local mall. Before opening the store, I’m curious about how my profit, revenue, and variable costs will depend on the price I charge and the unit variable cost.
Chapter 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 high-school algebra. Can Excel make solving story problems easier?
Chapter 17 Using the Scenario Manager for Sensitivity Analysis
Chapter 18 Creating and Using Spinners for Sensitivity Analysis
I need to run a sensitivity analysis that has many key inputs, such as year 1 sales, annual sales growth, year 1 price, and year 1 unit cost. Is there a way I can quickly vary these inputs and see the effect of the variation on the calculation of net present value, for example?
Chapter 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?
Chapter 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?
Chapter 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 right-most column instead of the left-most 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?
Chapter 22 The INDIRECT Function
Each worksheet in a workbook lists monthly sales of a product in cell D1. Is there an easy way to write and copy a formula that lists each month’s product sales in a single worksheet?
Chapter 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?
Chapter 24 An Introduction to Optimization with the Excel Solver
Chapter 25 Using Solver to Determine the Optimal Product Mix
How can I determine the monthly product mix for our plant that maximizes corporate profitability?
Does a Solver model always have a solution?
What does is mean if a Solver model yields the result Set Values Do Not Converge?
Chapter 26 Using Solver to Solve Transportation or Distribution Problems
How can a drug company determine the locations at which they should produce drugs and from which they should ship drugs to customers?
Chapter 27 Using Solver to Schedule Your Workforce
How can I efficiently schedule my workforce to meet labor demands?
Chapter 28 Using Solver for Capital Budgeting
How can a company use Solver to determine which projects it should undertake?
Handling Other Constraints
Chapter 29 Using Solver for Financial Planning
Can I use Solver to check out the accuracy of the Excel PMT function or to determine mortgage payments for a variable interest rate?
Can I use Solver to determine how much money I need to save for retirement?
Chapter 30 Using Solver to Rate Sports Teams
Can I use Excel to set NFL point spreads?
Chapter 31 Importing Text or Microsoft Word Data into Excel
Chapter 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?
Chapter 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?
Chapter 34 Summarizing Data with Histograms
Wise people often say that a picture is worth a thousand words. Can I use Excel to create a picture (called a histogram) that summarizes the values in a data set?
What are some common shapes of histograms?
What can I learn by comparing histograms from different data sets?
Chapter 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?
Chapter 36 Using PivotTables to Describe Data
Chapter 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?
Chapter 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
Chapter 39 Consolidating Data
Chapter 40 Creating Subtotals
Chapter 41 Estimating Straight Line Relationships
How well does my relationship explain the monthly variation in plant operating cost?
How accurate are my predictions likely to be?
When estimating a straight line relationship, which Excel functions can I use to give me the slope and intercept of the line that best fits the data?
Chapter 42 Modeling Exponential Growth
How can I model the growth of a company’s revenue over time?
Chapter 43 The Power Curve
As a company produces more of a product, it learns how to make the product more efficiently. Can we model the relationship between units produced and the time needed to produce a unit?
Chapter 44 Using Correlations to Summarize Relationships
How are monthly stock returns on Microsoft, GE, Intel, GM, and Cisco related?
Chapter 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?
Chapter 46 Incorporating Qualitative Factors into Multiple Regression
How can I predict quarterly U.S. auto sales?
How can I predict U.S. presidential elections?
Is there an Excel function I can use to easily make forecasts from a multiple regression equation?
Chapter 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 48 Analysis of Variance: One-Way 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 one-way ANOVA for forecasting?
Chapter 49 Randomized Blocks and Two-Way 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?
Chapter 50 Using Moving Averages to Understand Time Series
Chapter 51 Forecasting with Moving Averages
What are the shortcomings of using moving averages in forecasting?
How can I optimize moving average forecasts?
Can I modify moving average forecasting to incorporate trend and seasonality?
Chapter 52 Forecasting in the Presence of Special Events
When predicting the number of customers who enter a bank each day, customer traffic is influenced by seasonality (in the form of the month of the year or the day of the week). How can I determine how this influences customer traffic?
How can I check if my forecast errors are random?
Chapter 53 An Introduction to Random Variables
What is a random variable?
What is a discrete random variable?
What are the mean, variance, and standard deviation of a random variable?
What is a continuous random variable?
What is a probability density function?
What are independent random variables?
Chapter 54 The Binomial and Hypergeometric Random Variables
What is the binomial random variable?
How do we use the BINOMDIST function to compute binomial probabilities?
What is the hypergeometric random variable?
Chapter 55 The Poisson and Exponential Random Variable
What is the Poisson random variable?
How do I use Excel to compute probabilities for the Poisson random variable?
Suppose the number of customers arriving at a bank is governed by a Poisson random variable. What random variable governs the time between arrivals?
Chapter 56 The Normal Random Variable
How do I use Excel to find probabilities for the normal random variable?
Can I use Excel to find percentiles for the normal random variable?
Why is the normal random variable appropriate in many real-world situations?
Chapter 57 Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
I want to know the probability that a machine will work without failing for at least 20 hours. How would I estimate this probability?
I want to know the probability that installing dry wall on a building will take more than 200 hours. How would I estimate this probability?
Chapter 58 Introduction to Monte Carlo Simulation
Who uses Monte Carlo simulation?
What happens when I enter =RAND() in a cell?
How can I simulate values of a discrete random variable?
How can I simulate values of a normal random variable?
How should a greeting card company determine how many cards to produce?
Chapter 59 Calculating an Optimal Bid
How do I simulate a binomial random variable?
How can I determine whether a continuous random variable should be modeled as a normal random variable?
How can I use simulation to determine the optimal bid for a construction project?
Chapter 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 5-year planning horizon will yield an expected return of at least 10 percent and minimize risk?
Chapter 61 Fun and Games: Simulating Gambling and Sporting Event Probabilities
What is the probability of winning at craps?
In five-card 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?
Chapter 62 Using Resampling to Analyze Data
I’ve produced nine batches of a product using a high temperature and seven batches of a product using a low temperature. What is the probability that the process yield is better at the high temperature?
Chapter 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 Black-Scholes formula?
How do changes in key parameters change the value of a put or call option?
How can I use the Black-Scholes formula to estimate a stock’s volatility?
I don’t want somebody messing up my neat option-pricing 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?
Chapter 64 Determining Customer Value
A credit-card 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 long-distance phone company gives the competition’s customers an incentive to switch. How large an incentive should they give?
Chapter 65 The Economic Order Quantity Inventory Model
Chapter 66 Determining the Reorder Point: How Low Should I Let My Inventory Level Go Before I Reorder?
At what inventory level (called the reorder point) should I place an order if my goal is to minimize the sum of annual holding, ordering, and shortage costs?
What does the term 95 percent service level mean?
Chapter 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?
Chapter 68 Estimating a Demand Curve
What do I need to know to price a product?
What is the meaning of elasticity of demand?
Is there any easy way to estimate a demand curve?
What does a demand curve tell us about a customer’s willingness to pay for our product?
Chapter 69 Pricing Products with Tie-Ins
How does the fact that customers buy razor blades as well as razors affect the profit-maximizing price of razors?
Chapter 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 profit-maximizing price?
How can a small drugstore determine the profit-maximizing price for lipstick?
Chapter 71 Nonlinear Pricing
What is linear pricing?
What is nonlinear pricing?
What is bundling, and how can it increase profitability?
How can I find a profit-maximizing nonlinear pricing plan?
Chapter 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?
Chapter 73 Picking Your Fantasy Football Team
What is fantasy football?
What information do I need to pick a good team?
How can I use the Excel Solver to pick my team?
Appendix About the Author
Microsoft® Excel Data Analysis and Business Modeling
Wayne L. Winston is a professor of Decision Sciences at Indiana University's Kelley School of Business and has earned numerous MBA teaching awards. For 20+ years, he has taught clients at Fortune 500 companies how to use Excel to make smarter business decisions. Wayne and his business partner Jeff Sagarin developed the player-statistics tracking and rating system used by the Dallas Mavericks professional basketball team. He is also a two time Jeopardy! champion.