Microsoft® Excel Data Analysis and Business Modeling

Table of contents

1. Microsoft® Excel Data Analysis and Business Modeling
2. A Note Regarding Supplemental Files
3. Introduction
   1. What You Should Know Before Reading This Book
   2. How to Use This Book
      1. Using the Companion CD
      2. System Requirements
   3. Support Information
   4. Acknowledgments
4. 1. Range Names
   1. How Can I Create Range Names?
      1. Using the Name Box to Create a Range Name
      2. Creating Range Names by Using the Name Create Command
      3. Creating Range Names by Using the Name Define Command
         1. I want to add up sales in Arizona, California, Montana, New York, and New Jersey. Can I use a formula to compute total sales in a form such as AZ+CA+MT+NY+NJ instead of SUM(A21:A25) and still get the right answer?
   2. How Do I Delete a Range Name?
   3. How Do I Change a Range Name?
   4. How Do I Name a Constant?
      1. 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?
      2. How can I easily select a cell range?
      3. How can I paste a list of all range names (and the cells they represent) into my spreadsheet?
   5. Remarks
   6. Problems
5. 2. Natural Language Range Names
   1. How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?
   2. 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?
   3. Problems
6. 3. Lookup Functions
   1. Syntax of the LOOKUP Functions
      1. VLOOKUP Syntax
   2. HLOOKUP Syntax
      1. How do I write a formula to compute tax rates based on income?
      2. Given a product ID, how can I look up the product’s price?
      3. Suppose that a product’s price changes over time. I know the date the product was sold. How can I write a formula to compute the product’s price?
   3. Problems
7. 4. The INDEX Function
   1. Syntax of the INDEX Function
      1. I have a list of distances between U.S. cities. How do I write a function that returns the distance between, say, Seattle and Miami?
      2. Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?
   2. Problems
8. 5. The MATCH Function
   1. 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?
   2. 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?
   3. 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?
   4. Problems
9. 6. 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. CONCATENATE and & Functions
      8. REPLACE Function
      9. VALUE Function
   2. Text Functions in Action
      1. I have a spreadsheet in which each cell contains a product description, a product ID, and a product price. How can I put all the product descriptions in column A, all the product IDs in column B, and all the prices in column C?
         1. Every day I receive data about total U.S. sales, which is computed in a cell as the sum of East, North, and South region sales. How can I extract East, North, and South sales to separate cells?
   3. Using the Text To Columns Command to Extract Data
   4. Problems
10. 7. Dates and Date Functions
    1. 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?
    2. Can I enter a formula that automatically displays today’s date?
    3. How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
    4. How do I determine the number of workdays between two dates?
    5. 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?
    6. Problems
11. 8. Evaluating Investments with Net Present Value Criteria
    1. What is net present value?
    2. How do I use the Excel NPV function?
    3. How can I compute NPV when cash flows are received at the beginning of a year or in the middle of the year?
    4. How can I compute NPV when cash flows are received at irregular intervals?
    5. Problems
12. 9. Internal Rate of Return
    1. How can I find the IRR of cash flows?
    2. Does a project always have a unique IRR?
    3. Are there conditions that guarantee a project will have a unique IRR?
    4. If two projects both have a single IRR, how do I use the projects’ IRRs?
    5. How can I find the IRR of irregularly spaced cash flows?
    6. Problems
13. 10. Functions for Personal Financial Decisions: The PV, FV, PMT, PPMT, and IPMT Functions
    1. Should I pay $11,000 today for a copier or $3,000 a year for 5 years?
    2. 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?
    3. 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?
    4. Problems
14. 11. Circular References
    1. I often get a circular reference message from Excel. Does this mean I’ve made an error?
    2. How can I resolve circular references?
    3. Problems
15. 12. IF Statements
    1. Problems
16. 13. The Paste Special Command
    1. How can I move the results of calculations (not the formulas) to a different part of a worksheet?
    2. I have a list of names in a single column. How can I make the list appear in one row instead of one column?
    3. Problems
17. 14. The Auditing Tool
    1. 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?
    2. 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?
    3. How does the auditing tool work when I’m working with data in more than one worksheet or workbook?
    4. Problems
18. 15. Sensitivity Analysis with Data Tables
    1. 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.
    2. Problems
19. 16. The Goal Seek Command
    1. For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?
    2. 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?
    3. I always had trouble with "story problems" in high-school algebra. Can Excel make solving story problems easier?
    4. Problems
20. 17. Using the Scenario Manager for Sensitivity Analysis
    1. Remarks
    2. Problems
21. 18. Creating and Using Spinners for Sensitivity Analysis
    1. 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?
    2. Problems
22. 19. The COUNTIF, COUNT, COUNTA, and COUNTBLANK Functions
    1. How many songs were sung by each singer?
    2. How many songs were not sung by Eminem?
    3. How many songs lasted at least 4 minutes?
    4. How many songs were longer than average?
    5. How many songs were sung by a singer whose last name begins with S?
    6. How many songs were sung by a singer whose last name contains six letters?
    7. How many songs were sung after June 15, 2005?
    8. How many songs were sung before the beginning of 2009?
    9. How many songs lasted exactly 4 minutes?
    10. How do I count the number of cells in a range containing numbers?
    11. How do I count the number of blank cells in a range?
    12. How do I count the number of nonblank cells in a range?
    13. Remarks
    14. Problems
23. 20. The SUMIF Function
    1. What was the total dollar amount of merchandise sold by each salesperson?
    2. How many units were returned?
    3. What was the total dollar volume sold in 2005 or later?
    4. How many units of lip gloss were sold? How much revenue did lip gloss sales bring in?
    5. What dollar amount of sales were not made by Jen?
    6. Remarks
    7. Problems
24. 21. The OFFSET Function
    1. 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?
    2. How can I perform a lookup operation keying off the right-most column instead of the left-most column in a table range?
    3. 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?
    4. 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?
    5. Problems
25. 22. The INDIRECT Function
    1. 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?
    2. Problems
26. 23. Conditional Formatting
    1. 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?
    2. 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?
    3. Given a list of dates, how can I highlight the weekend dates in a specific color?
    4. 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?
    5. Problems
27. 24. An Introduction to Optimization with the Excel Solver
    1. Problems
28. 25. Using Solver to Determine the Optimal Product Mix
    1. How can I determine the monthly product mix for our plant that maximizes corporate profitability?
    2. Does a Solver model always have a solution?
    3. What does is mean if a Solver model yields the result Set Values Do Not Converge?
    4. Problems
29. 26. Using Solver to Solve Transportation or Distribution Problems
    1. How can a drug company determine the locations at which they should produce drugs and from which they should ship drugs to customers?
    2. Problems
30. 27. Using Solver to Schedule Your Workforce
    1. How can I efficiently schedule my workforce to meet labor demands?
    2. Problems
31. 28. Using Solver for Capital Budgeting
    1. How can a company use Solver to determine which projects it should undertake?
    2. Handling Other Constraints
    3. Problems
32. 29. Using Solver for Financial Planning
    1. Can I use Solver to check out the accuracy of the Excel PMT function or to determine mortgage payments for a variable interest rate?
    2. Can I use Solver to determine how much money I need to save for retirement?
    3. Problems
33. 30. Using Solver to Rate Sports Teams
    1. Can I use Excel to set NFL point spreads?
    2. Problems
34. 31. Importing Text or Microsoft Word Data into Excel
    1. Problems
35. 32. Importing Data from the Web into Excel
    1. The Web site for MSN Money Central provides analyst ratings (buy, sell, and hold) for stocks. How can I import this information into Excel?
    2. 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?
    3. Is there a way I can download current stock prices into Excel?
    4. Problems
36. 33. Validating Data
    1. 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?
    2. 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?
    3. I’m entering a long list of numbers. Can I have Excel warn me if I enter a nonnumeric value?
    4. 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?
    5. Problems
37. 34. Summarizing Data with Histograms
    1. 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?
    2. What are some common shapes of histograms?
    3. What can I learn by comparing histograms from different data sets?
    4. Problems
38. 35. Summarizing Data with Descriptive Statistics
    1. What defines a typical value for a data set?
    2. How can I measure how much a data set spreads out from its typical value?
    3. Together, what do the mean and standard deviation of a data set tell me about the data?
    4. How can I use descriptive statistics to compare data sets?
    5. 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?
    6. Problems
39. 36. Using PivotTables to Describe Data
    1. Problems
40. 37. Summarizing Data with Database Statistical Functions
    1. How many dollars worth of lip gloss did Jen sell?
    2. What was the average number of units of lipstick sold each time Jen made a sale in the East region?
    3. How many dollars of sales were made by Emilee or in the East region?
    4. How many dollars worth of lipstick were sold by Colleen or Zaret in the East region?
    5. How many lipstick transactions were not in the East?
    6. How many dollars worth of lipstick did Jen sell during 2004?
    7. How many units of makeup were sold for a price of at least $3.20?
    8. What is the total dollar amount each salesperson sold of each product?
    9. What cute tricks can I use to set up criteria ranges?
    10. 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?
    11. Problems
41. 38. Filtering Data
    1. Locate all transactions in which Jen sold lip gloss
    2. Locate the ten largest transactions (by revenue)
    3. Locate the bottom 5 percent of transactions (by revenue) in which Colleen was the salesperson
    4. Locate all 2005 lipstick transactions in which Ashley was the salesperson
    5. 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
    6. Problems
42. 39. Consolidating Data
    1. Problems
43. 40. Creating Subtotals
    1. Problems
44. 41. Estimating Straight Line Relationships
    1. How well does my relationship explain the monthly variation in plant operating cost?
    2. How accurate are my predictions likely to be?
    3. 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?
    4. Problems
45. 42. Modeling Exponential Growth
    1. How can I model the growth of a company’s revenue over time?
    2. Problems
46. 43. The Power Curve
    1. 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?
    2. Problems
47. 44. Using Correlations to Summarize Relationships
    1. How are monthly stock returns on Microsoft, GE, Intel, GM, and Cisco related?
    2. Problems
48. 45. Introduction to Multiple Regression
    1. 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?
    2. How accurate are our forecasts for predicting monthly cost from units produced?
    3. 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?
49. 46. Incorporating Qualitative Factors into Multiple Regression
    1. How can I predict quarterly U.S. auto sales?
    2. How can I predict U.S. presidential elections?
    3. Is there an Excel function I can use to easily make forecasts from a multiple regression equation?
50. 47. Modeling Nonlinearities and Interactions
    1. What does it mean when we say that an independent variable has a nonlinear effect on a dependent variable?
    2. What does it mean when we say that the effects of two independent variables on a dependent variable interact?
    3. How can we test for the presence of nonlinearity and interaction in a regression?
    4. Problems for Chapter 45–
51. 48. Analysis of Variance: One-Way ANOVA
    1. 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?
    2. If we are determining whether populations have significantly different means, why is the technique called analysis of variance?
    3. How can we use the results of a one-way ANOVA for forecasting?
    4. Problems
52. 49. Randomized Blocks and Two-Way ANOVA
    1. Based on a knowledge of sales rep and district, how can I forecast sales? How accurate are my sales forecasts?
    2. 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?
    3. How can I interpret the effects of price and advertising on sales when there is a significant interaction between price and advertising?
    4. Problems
53. 50. Using Moving Averages to Understand Time Series
    1. Problem
54. 51. Forecasting with Moving Averages
    1. What are the shortcomings of using moving averages in forecasting?
    2. How can I optimize moving average forecasts?
    3. Can I modify moving average forecasting to incorporate trend and seasonality?
    4. Problems
55. 52. Forecasting in the Presence of Special Events
    1. 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?
    2. How can I check if my forecast errors are random?
    3. Problems
56. 53. An Introduction to Random Variables
    1. What is a random variable?
    2. What is a discrete random variable?
    3. What are the mean, variance, and standard deviation of a random variable?
    4. What is a continuous random variable?
    5. What is a probability density function?
    6. What are independent random variables?
    7. Problems
57. 54. The Binomial and Hypergeometric Random Variables
    1. What is the binomial random variable?
    2. How do we use the BINOMDIST function to compute binomial probabilities?
    3. What is the hypergeometric random variable?
    4. Problems
58. 55. The Poisson and Exponential Random Variable
    1. What is the Poisson random variable?
    2. How do I use Excel to compute probabilities for the Poisson random variable?
    3. Suppose the number of customers arriving at a bank is governed by a Poisson random variable. What random variable governs the time between arrivals?
    4. Problems
59. 56. The Normal Random Variable
    1. How do I use Excel to find probabilities for the normal random variable?
    2. Can I use Excel to find percentiles for the normal random variable?
    3. Why is the normal random variable appropriate in many real-world situations?
    4. Problems
60. 57. Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
    1. I want to know the probability that a machine will work without failing for at least 20 hours. How would I estimate this probability?
       1. What is the probability that a machine will last at least 20 hours?
       2. What is the probability that a machine will last between 15 and 30 hours?
    2. 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?
    3. Problems
61. 58. Introduction to Monte Carlo Simulation
    1. Who uses Monte Carlo simulation?
    2. What happens when I enter =RAND() in a cell?
    3. How can I simulate values of a discrete random variable?
    4. How can I simulate values of a normal random variable?
    5. How should a greeting card company determine how many cards to produce?
       1. The Impact of Risk on Our Decision
       2. Confidence Interval for Mean Profit
    6. Problems
62. 59. Calculating an Optimal Bid
    1. How do I simulate a binomial random variable?
    2. How can I determine whether a continuous random variable should be modeled as a normal random variable?
    3. How can I use simulation to determine the optimal bid for a construction project?
    4. Problems
63. 60. Simulating Stock Prices and Asset Allocation Modeling
    1. I recently bought 100 shares of GE. What is the probability that during the next year this investment will return more than 10 percent?
    2. 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?
    3. Problems
64. 61. Fun and Games: Simulating Gambling and Sporting Event Probabilities
    1. What is the probability of winning at craps?
    2. In five-card draw poker, what is the probability of getting three of a kind?
    3. Before the 2003 Super Bowl, Oakland was favored by 3 points. What was the probability that Tampa Bay would beat Oakland?
    4. Going into the 2003 NCAA men’s basketball Final Four, what was the probability of each team winning the tournament?
    5. Problems
65. 62. Using Resampling to Analyze Data
    1. 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?
    2. Problems
66. 63. Pricing Stock Options
    1. What are put and call options?
    2. What are American and European options?
    3. As a function of the stock price on the exercise date, what do the payoffs look like for European puts and calls?
    4. What parameters determine the value of an option?
    5. How do we estimate the volatility of a stock based on historical data?
    6. How can I use Excel to implement the Black-Scholes formula?
    7. How do changes in key parameters change the value of a put or call option?
    8. How can I use the Black-Scholes formula to estimate a stock’s volatility?
    9. 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?
    10. How can option pricing be used to help companies make better investment decisions?
    11. Problems
67. 64. Determining Customer Value
    1. 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?
    2. A long-distance phone company gives the competition’s customers an incentive to switch. How large an incentive should they give?
    3. Problems
68. 65. The Economic Order Quantity Inventory Model
    1. Problems
69. 66. Determining the Reorder Point: How Low Should I Let My Inventory Level Go Before I Reorder?
    1. 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?
    2. What does the term 95 percent service level mean?
    3. Problems
70. 67. Queuing Theory: The Mathematics of Waiting in Line
    1. What factors affect the time we spend waiting in line and the number of people waiting in line?
    2. 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?
    3. Why does variability degrade the performance of a queuing system?
    4. Can I easily determine the average time a person spends at airport security or waiting in line at a bank?
    5. Problems
71. 68. Estimating a Demand Curve
    1. What do I need to know to price a product?
    2. What is the meaning of elasticity of demand?
    3. Is there any easy way to estimate a demand curve?
    4. What does a demand curve tell us about a customer’s willingness to pay for our product?
    5. Problems
72. 69. Pricing Products with Tie-Ins
    1. How does the fact that customers buy razor blades as well as razors affect the profit-maximizing price of razors?
    2. Problems
73. 70. Pricing Products Using Subjectively Determined Demand
    1. 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?
    2. How can a small drugstore determine the profit-maximizing price for lipstick?
    3. Problems
74. 71. Nonlinear Pricing
    1. What is linear pricing?
    2. What is nonlinear pricing?
    3. What is bundling, and how can it increase profitability?
    4. How can I find a profit-maximizing nonlinear pricing plan?
    5. Problems
75. 72. Array Formulas and Functions
    1. What is an array formula?
    2. How do we interpret formulas such as (D2:D7)*(E2:E7) and SUM(D2:D7*E2:E7)?
    3. 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?
    4. 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?
    5. Can I write a single formula that will sum up the second digit of a list of integers?
    6. Is there a way to look at two lists of names and determine which names occur on both lists?
    7. Can I write a formula that averages all numbers in a list that are greater than or equal to the list’s median value?
    8. What are array constants and how can I use them?
    9. How do I edit array formulas?
    10. Given quarterly revenues for a toy store, can I estimate the trend and seasonality of the store’s revenues?
    11. Problems
76. 73. Picking Your Fantasy Football Team
    1. What is fantasy football?
    2. What information do I need to pick a good team?
    3. How can I use the Excel Solver to pick my team?
77. About the Author
78. Index
79. About the Author
80. Copyright