book

Microsoft® Excel Data Analysis and Business Modeling

Name: Microsoft® Excel Data Analysis and Business Modeling
Author: Wayne L. Winston
ISBN: 9780735619012

by Wayne L. Winston

February 2004

Beginner to intermediate

624 pages

18h 6m

English

Microsoft Press

Read now

Unlock full access

Microsoft® Excel Data Analysis and Business Modeling
A Note Regarding Supplemental Files
Introduction
What You Should Know Before Reading This BookHow to Use This BookUsing the Companion CDSystem RequirementsSupport InformationAcknowledgments
1. Range Names
How Can I Create Range Names?Using the Name Box to Create a Range NameCreating Range Names by Using the Name Create CommandCreating Range Names by Using the Name Define CommandI 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?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?RemarksProblems
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
Syntax of the LOOKUP FunctionsVLOOKUP SyntaxHLOOKUP SyntaxHow do I write a formula to compute tax rates based on income?Given a product ID, how can I look up the product’s price?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?Problems
4. The INDEX Function
Syntax of the INDEX FunctionI have a list of distances between U.S. cities. How do I write a function that returns the distance between, say, Seattle and Miami?Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?Problems
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 SyntaxThe LEFT FunctionThe RIGHT FunctionThe MID FunctionThe TRIM FunctionThe LEN FunctionThe FIND and SEARCH FunctionsCONCATENATE and & FunctionsREPLACE FunctionVALUE FunctionText Functions in ActionI 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?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?Using the Text To Columns Command to Extract DataProblems
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
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?Problems
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?Problems
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?Problems
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?Problems
12. IF Statements
Problems
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?Problems
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?Problems
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.Problems
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?Problems
17. Using the Scenario Manager for Sensitivity Analysis
RemarksProblems
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?Problems
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?RemarksProblems
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?RemarksProblems
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?Problems
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?Problems
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
Problems
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?Problems
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?Problems
27. Using Solver to Schedule Your Workforce
How can I efficiently schedule my workforce to meet labor demands?Problems
28. Using Solver for Capital Budgeting
How can a company use Solver to determine which projects it should undertake?Handling Other ConstraintsProblems
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?Problems
30. Using Solver to Rate Sports Teams
Can I use Excel to set NFL point spreads?Problems
31. Importing Text or Microsoft Word Data into Excel
Problems
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
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?Problems
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
Problems
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 glossLocate the ten largest transactions (by revenue)Locate the bottom 5 percent of transactions (by revenue) in which Colleen was the salespersonLocate all 2005 lipstick transactions in which Ashley was the salespersonLocate 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.20Problems
39. Consolidating Data
Problems
40. Creating Subtotals
Problems
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?Problems
42. Modeling Exponential Growth
How can I model the growth of a company’s revenue over time?Problems
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?Problems
44. Using Correlations to Summarize Relationships
How are monthly stock returns on Microsoft, GE, Intel, GM, and Cisco related?Problems
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
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?
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?Problems
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?Problems
50. Using Moving Averages to Understand Time Series
Problem
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?Problems
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?Problems
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?Problems
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?Problems
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?Problems
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?Problems
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?What is the probability that a machine will last at least 20 hours?What is the probability that a machine will last between 15 and 30 hours?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?Problems
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?The Impact of Risk on Our DecisionConfidence Interval for Mean ProfitProblems
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?Problems
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?Problems
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?Problems
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?Problems
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?Problems
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?Problems
65. The Economic Order Quantity Inventory Model
Problems
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?Problems
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
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?Problems
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?Problems
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?Problems
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?Problems
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
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?
About the Author
Index
About the Author
Copyright

Content preview from Microsoft® Excel Data Analysis and Business Modeling

Chapter 39. Consolidating Data

My company sells products in several regions of the United States. Each region keeps records of the number of units of each product sold during the months of January, February, and March. Is there an easy way to create a "master workbook" that always combines each region’s sales and gives a tally of the total amount sold in the U.S. of each product during each month?

A business analyst often receives worksheets that tally the same information (such as monthly product sales) from different affiliates or regions. To determine the company’s overall profitability, the analyst usually needs to combine or consolidate this information in a single workbook. PivotTables built from multiple consolidation ranges can be used ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Microsoft Excel 2016 Data Analysis and Business Modeling

Publisher Resources

ISBN: 0735619018Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® Excel Data Analysis and Business Modeling

by Wayne L. Winston

Chapter 39. Consolidating Data

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.