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 real-world business scenarios.
Table of Contents
- Microsoft® Excel Data Analysis and Business Modeling
- A Note Regarding Supplemental Files
1. Range Names
How Can I Create Range Names?
- Using the Name Box to Create a Range Name
- Creating Range Names by Using the Name Create Command
- Creating Range Names by Using the Name Define Command
- 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?
- How Can I Create Range Names?
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?
3. Lookup Functions
- Syntax of the LOOKUP Functions
- HLOOKUP Syntax
- 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?
6. Text Functions
- Text Function Syntax
Text Functions in Action
- 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?
- Using the Text To Columns Command to Extract Data
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?
- 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 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?
- 11. Circular References
- 12. IF Statements
- 13. The Paste Special Command
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?
- 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 high-school algebra. Can Excel make solving story problems easier?
- 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?
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?
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?
- 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?
- 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?
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?
- 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?
- 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?
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
- 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: 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?
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?
- 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
- 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?
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?
- 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 5-year planning horizon will yield an expected return of at least 10 percent and minimize risk?
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?
- 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 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?
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?
- 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?
- 68. Estimating a Demand Curve
- 69. Pricing Products with Tie-Ins
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?
- 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?
- 73. Picking Your Fantasy Football Team
- About the Author
- About the Author
- Title: Microsoft® Excel Data Analysis and Business Modeling
- Release date: February 2004
- Publisher(s): Microsoft Press
- ISBN: 0735619018