Microsoft Excel Data Analysis And Business Modeling 7th Edition Wayne Winston - Solutions

The file named Lookuptwocolumns.xlsx gives the model, year, and price for each of a series of cars. Set up formulas that enable you to enter the model and year of a car and return its price.
The file named Moviedata.xlsx contains the names of several movies followed by the number of copies of the movie DVD purchased by a local video store. Extract the title of each movie from this data.
Again with Moviedata.xlsx, for each movie, extract the number of copies purchased from this data. You probably want to use the SUBSTITUTE function. The syntax of the SUBSTITUTE function is SUBSTITUTE(text,old_text,new_text,instance_number). If instance_number is omitted, every occurrence of
The file named Problem16data.xlsx contains the number of people who responded 1 – 5 on a marketing questionnaire (1 = very unlikely to buy product, ..., 5 = very likely to buy product). Summarize this data graphically by using the asterisk symbol. To make your summary look more appealing, you can
The file named Problem17data.xlsx contains people’s names (such as Mr. John Doe). Use text functions to extract each person’s title and first name to separate columns.
The file named Weirddata.xls contains three numbers imported from an internet site. When you try adding the numbers, you will see the total is incorrect. Modify the data so that the SUM function can obtain the correct sum of the three numbers.
Use Flash Fill to change the names in the worksheet named Flashfilltemplate.xlsx (in the Templates folder) to lowercase.
Use Flash Fill to extract the number from each row in the file named Movienumbers.xlsx.
For the data in the file named Movienumbers.xlsx, use Flash Fill to create a column that inserts the number in the movie title at the end of the phrase The number in the title of this movie is.
Each row of the file named Problem22data.xlsx gives the name of a city, the city’s state, and population of the city. Use text functions to extract the state to a separate column.
The file named Problem23data.xlsx gives the name of a quarterback (QB), followed by the player’s QB rating, followed by the number of completed passes thrown by the QB. Use text functions to extract each QB’s number of completed passes.
In each row of the file named Problem24data.xlsx, you will find a city’s zip code, city name, state (always Texas!), latitude, and longitude. Use formulas to extract the name of the city in each row. You may assume that each zip code is five characters long.
Each row of the file named Problem25data.xlsx contains between the hyphen and comma a QB’s number of completions. Use text functions to extract each QB’s number of completions.
The file named Problem26data.xlsx contains sales data on the world’s 2,000 largest companies. If an integer between 1 and 2000 is entered in cell B3, then cell C3 should return a sentence such as The company ranking #1 in revenue has revenue of $328.21. Use the TEXT function to ensure the dollar
Consider the following set of cash flows over a four-year period. Determine the NPV of these cash flows if r = 0.15 and cash flows occur at the end of the year. Year 1 2 3 4 Cash flow -$600 $550 -$680 $1,000
Beginning one month from now, a customer will pay $25 per month to her internet provider for the next five years. Assuming all revenue for a month is received at the beginning of the month, use the XNPV function to obtain the exact NPV of these revenues. Use r = 0.15.
Beginning one month from now, a customer will pay his internet provider $25 per month for the next five years. Assuming all revenue for a year is received at the middle of a year, estimate the NPV of these revenues. Use r = 0.15.
Using the definitions of quarters given in Problem 19, write a formula that computes for any given date the last day of the previous quarter.Data from in Problem 19Suppose the first quarter of each year is January–March, the second quarter is April–June, and so on. Write a formula that returns
Given any date, find a way to have Excel compute the last day of the date’s month.
Given any date, find a way to have Excel compute the day of the week of the first day of the date’s month.
The file named Machinedates.xlsx contains dates on which several machines were bought and sold. Determine how many months and years each machine was kept.
For the file Datep.xlsx, express each date in serial format.
For the file Datep.xlsx, determine the month, year, day of the month, and day of the week for each date.
To what date does a serial format of 45000 correspond?
To what date does a serial format of 4526 correspond?
The workbook Problem27data.xlsx contains phone number, zip code, and Social Security number data. Apply the correct formats to this data. 12345 123 3 Zip code +4 A 11 07039-4123 6 7 8 Social Security Numbers 9 156-43-7899 10 666-77-8888 16 17 47401-5678 18 19 20 21 14 always 000 15
Given the latitude and longitude of any two cities, the file Citydistances.xlsx computes the distance between the two cities. Define range names for the latitude and longitude of each city and ensure that these names show up in the formula for total distance.
Open a worksheet and name the range containing the cells A1:B3 and A6:B8 Red.
You own six local coffee shops. The revenues and customer count for each coffee shop are given in the following table.Set up a workbook that makes it easy to enter the revenue and customer count for each store. Then create a summary worksheet (using three-dimensional formulas) that computes the
Assume that at the end of each of the next 40 years, you put the same amount in your retirement fund and earn the same interest rate each year. Show how the amount of money you have at retirement would change at different annual contribution levels (from $5,000 to $25,000) and with different annual
The payback period for a project is the number of years needed for a project’s future profits to pay back the project’s initial investment. A project requires a $300 million investment at Time 0. The project yields profit for 10 years, and Time 1 cash flow will be between $30 million and $100
A software development company is thinking of translating a software product into Swahili. Currently, 200,000 units of the product are sold per year at a price of $100 each. Unit variable cost is $20. The fixed cost of translation is $5 million. Translating the product into Swahili will increase
Currently, you sell 40,000 units of a product for $45 each. The unit variable cost of producing the product is $5. You are thinking of cutting the product price by 30 percent. You are sure this will increase sales by 10 percent to 50 percent. Perform a sensitivity analysis to show how profit will
The annual demand for a product equals 500 – 3p + 10a.5, where p is the price of the product in dollars and a is hundreds of dollars spent on advertising the product. The annual fixed cost of selling the product is $10,000, and the unit variable cost of producing the product is $12. Determine a
You are saving for your child’s college fund. You are going to contribute the same amount of money to the fund at the end of each year. Your goal is to end up with $100,000. For annual investment returns ranging from 4 percent to 15 percent and the number of years investing varying from five to
You’ve been assigned to analyze the profitability of Bill Clinton’s autobiography. The following assumptions have been made:Clinton is receiving a one-time royalty payment of $12 million.The fixed cost of producing the hardcover version of the book is $1 million.The variable cost of producing
Use the file named Makeup2007.xlsxHow many units of makeup were sold from January 15, 2004, through February 15, 2005? G H 4 Trans Number Name 456789 10 11 12 13 14 15 16 17 18 19 222 20 21 1 Betsy 2 Hallagan 3 Ashley 4 Hallagan 5 Zaret 6 Colleen 7 Cristina 8 Colleen 9 Ashley 10 Betsy 11 Ashley 12
Using the lemonade example of a data table, create spin buttons that allow the inputs to vary within the following ranges: Input Price Unit cost Low value $2 $0.20 High value $5 $1
A square matrix has the same number of rows and columns. Given a square matrix A, suppose there exists a matrix B whereby AB equals a matrix in which each diagonal entry equals 1, and all other entries equal 0. You can then say that B is the inverse of A. The Excel array function MINVERSE finds the
An m-by-n matrix is a rectangular array of numbers containing m rows and n columns. The following, for example, is a 3-by-3 matrix:Consider two matrices, A and B. Suppose that the number of columns in matrix A equals the number of rows in matrix B. Then, multiply matrix A by matrix B. (The product
Find the average dollar amount per transaction by region. B 4 Trans Number Name 10 Betsy 11 Ashley 15 Zaret 16 Emilee 567 7 8 68 9 10 11 12 13 14 15 16 17 18 19 20 21 22222 23 24 25 45 Emilee 46 Ashley 58 Cristina 60 Jen 69 Cristina 77 Cristina 81 Jen 86 Jen 87 Emilee 98 Jen 108 Cici 114 Betsy 116
For each salesperson, display a breakdown of units sold as well as revenue, showing the results for each product by region. B 4 Trans Number Name 10 Betsy 11 Ashley 15 Zaret 16 Emilee 567 7 8 68 9 10 11 12 13 14 15 16 17 18 19 20 21 22222 23 24 25 45 Emilee 46 Ashley 58 Cristina 60 Jen 69
Find the largest transaction (in terms of revenue) for each product. B 4 Trans Number Name 10 Betsy 11 Ashley 15 Zaret 16 Emilee 45 Emilee 46 Ashley 58 Cristina 60 Jen 69 Cristina 77 Cristina 81 Jen 86 Jen 87 Emilee 98 Jen 108 Cici 114 Betsy 116 Zaret 119 Colleen 131 Jen 133 Betsy 140
Find the units sold and revenue for each salesperson. B 4 Trans Number Name 10 Betsy 11 Ashley 15 Zaret 16 Emilee 45 Emilee 46 Ashley 58 Cristina 60 Jen 69 Cristina 77 Cristina 81 Jen 86 Jen 87 Emilee 98 Jen 108 Cici 114 Betsy 116 Zaret 119 Colleen 131 Jen 133 Betsy 140
The following table contains performance reviews for five employees. Use a radar chart to summarize the performance reviews. Person Hard-working Wayne 1 Vivian 5 Greg 10 Collegial 2 6 9.5 Completes tasks on time 3 7 9 Punctual 4 8 8.5
Each year, 250,000 seniors play high-school football, 16,000 college seniors play college football, and 256 seniors are drafted by the NFL. Summarize this data with the appropriate chart.
A company’s profit, revenue, and cost breakdown for a year are given below. Summarize this data in a waterfall chart. Item Revenue Profit Labor Costs Material Costs Overhead Costs Amount $300,000 $65,000 $100,000 $80,000 $55,000
A project consists of five activities. The start time and duration of each activity are given below. Summarize this data in a Gantt chart. Activity A B C D E Start time 0 3 in 5 6 3 Duration 4 6 7 8 6
During its first year of operation, a firm’s revenue was $5 million and expenses were $6 million. For different assumed growth rates in expenses and revenues, plot 10 years of revenues and expenses. Ensure that the graph title shows the year that the revenue first exceeds expenses.
Plot the data in Problem 1 on a column chart in which good defensive games are highlighted in green, bad games in red, and other games in orange.Data from in problem 1The Indiana University basketball team considers at least 50 deflections per game a good performance and 30 or fewer deflections a
A marketing director estimates that the total sales of a product as a function of price will be as shown in the following table. Estimate the relationship between the price and the demand and predict the demand for a $46 price. A 1 percent increase in price will reduce the demand by what
The brand manager for a new drug believes that the annual sales of the drug, as a function of the number of sales calls on doctors, will be as shown in the following table. Estimate the sales of the drug if 80,000 sales calls are made on doctors. Sales calls 50,000 100,000 150,000 200,000 Units
For an airplane manufacturer, the time needed to produce each of the first 10 airplanes in a year is as follows:Estimate the total number of hours needed to produce the next 10 airplanes. Unit 1 2 3 4 5 сл 6 7 8 9 10 Hours 1,000 800 730 630 600 560 560 500 510 510
For 12 straight weeks, you have observed sales (in the number of cases) of canned tomatoes at Mr. D’s Supermarket. Each week, you keep track of the following:Was a promotional notice for canned tomatoes placed in all the shopping carts?Was a coupon for canned tomatoes given to each customer?Was a
If you toss two dice, find the probability of each possible total.
If you throw three dice, what is the chance the total is 9?
Of Carver High School students, 20 percent play baseball, 15 percent play basketball, and 5 percent play both. What fraction of students play baseball or basketball?
Let A = Event a Die Comes Up Even and B = Event Die Shows One or Two Dots. Are Events A and B independent?
In the game of craps, you roll two dice. A total of 7 or 11 on the first roll wins. What is the chance you win a game on the first roll?
The following table shows admission statistics for men and women at a large US university. The total number applying for admittance is in parentheses, and the number admitted is not in parentheses. You can see the percentage of men admitted is more than the percentage of women admitted. Using
A roulette wheel contains the numbers 0, 00, 1, 2, ..., 36. Suppose you bet on each of 25 spins that 00 will show. After 25 spins, what is the chance you have won at least once?
What fraction of men and women watch The Bachelor?
A chest has two drawers. Each drawer contains two coins. You know one drawer has two gold coins and the other drawer has one gold and one silver coin. You randomly pick a drawer and a coin; the selected coin is gold. What is the chance that the other coin in the drawer is also gold?
Of all the cabs, 85 percent are blue and the rest are green. A cab identified in a hit-and-run accident is identified as green. People can correctly identify the color of a cab 80 percent of the time. When surveyed, most Stanford students thought there was an 80 percent chance the cab is actually
An urn contains nine balls. Each ball has one of the numbers 1, 2, ..., 9 painted on it. Pull two balls (with replacement) from the urn. What is the chance the two numbers are the same?
Toss a die and let A = Event You Roll an Odd Number and B = Event You Roll a Number > =4. Find P(A|B).
Two engines each have a 0.9 chance of working. The success or failure of the engines is independent. If the engines are in a series, both engines need to work for the system to work. If the engines are in parallel, the system works if at least one engine works. What is the probability that a series
A company has one plant in Houston and one in Dallas. Seventy percent of the employees work in Houston and 30 percent work in Dallas. Each year, 3 percent of the Houston employees are involved in an accident and 5 percent of the Dallas employees are involved in an accident. If you randomly choose
An MBA student is studying finance and marketing. Assume the student has a 90 percent chance of getting an A in finance and an 80 percent chance of getting an A in marketing. If her performance in each of the two courses is independent of the other, what is the probability that she gets at least
A bowl contains four red balls and six blue balls. Two balls are drawn (without replacement) from the bowl. Given that the second ball is blue, what is the chance the first ball is blue?
Suppose you toss two dice. Let A = Event First Die Shows a 3 and B = Event Total of the Two Dice Is 8. Are events A and B independent?
Of an insurance company’s policyholders, 80 percent are high risk and 20 percent are low risk. Assume nobody has more than one accident in a year. Also assume 10 percent of highrisk people have an accident during a year and 3 percent of low-risk people have an accident during a year. If you
In each year’s NCAA basketball tournament, there are four #16 seeds with a 3 percent chance of beating a #1 seed. Before the tournament started, what is the chance that at least one #16-seed team beats a #1-seed team?
Assume one in every 1,000 people is a liar (incapable of telling the truth). Also suppose a lie-detector test is 98 percent accurate. That is, if a person is lying, there is a 98 percent chance the test will indicate that the person is lying. Also, if the person is not lying, there is a 98 percent
After throwing two dice, define Event A = Total of the Dice Is Even and Event B = First Die Shows a Five. Are Events A and B independent?
During 40 percent of all weeks, a supermarket cuts the price on macaroni and cheese. During 20 percent of all weeks, the supermarket puts macaroni and cheese on display. Finally, during 15 percent of all weeks, the supermarket cuts the price of macaroni and cheese and puts macaroni and cheese on
You are told two cards have been drawn from a deck of cards and both are hearts. What is the probability that the first card was the two of hearts?
In a drawer, there are two normal quarters and one quarter with two heads. With your eyes closed, you choose one of the coins. Your friend then flips the coin you chose and reports that the coin came up heads. What is the probability you selected the two-headed coin?
Of an insurance company’s policyholders, 20 percent are high risk, 40 percent are low risk, and 40 percent are intermediate risk. Of the low-risk policyholders, 2 percent have an accident during a year, 4 percent of intermediate-risk policyholders have an accident during a year, and 20 percent of
You toss two dice. Let Event A = Total Is 10 and Event B = Event First Die Shows an Odd Number. Are Events A and B independent?
Beer drinkers order an average of 40 pitchers of beer per hour at Nick’s Pub in Bloomington, Indiana. Answer the following questions:What is the probability that at least 100 pitchers are ordered in a two-hour period?What is the chance that the time between ordered pitchers will be 30 seconds or
Suppose that teenage drivers have an average of 0.3 accidents per year. Answer the following questions:What is the probability that a teenager will have no more than one accident during a year?What is the probability that the time between accidents will be six months or less?
I am next in line at a fast-food restaurant in which customers wait in a single line, and the time to serve a customer follows an exponential distribution with a mean of three minutes. What is the chance that I will have to wait at least five minutes to be served?
Since 1900, a fraction (0.00124) of all Major League Baseball games have resulted in nohitters. A team plays 162 games in a season. What is the chance that a team pitches two or more no-hitters during a season?
An average of 80 customers arrive each hour at the Central Forest coffee shop. What is the probability that at least 150 customers arrive in a two-hour period?
Suppose 10 percent of all staplers are defective. If 60 staplers are sold, what is the chance that at least 10 staplers are defective?
In each year’s NCAA basketball tournament there are four #1 seeds. For a #1 seed to make the Final Four, a team needs to win its first four games. Assume each #1 seed has a 0.7 chance to win each of the first four games. What is the chance that all #1 seeds make the Final Four?
A factory has five assembly lines. Each assembly line is down (not working) p percent of the time. What must p equal so that there is a 95 percent chance that at least one assembly line is working?
Suppose that each time the Houston Astros play the New York Yankees, the Astros have a 35 percent chance of winning the game. If the Astros play the Yankees 10 times, what is the chance the Astros win at least half the games?
In the 17th century, French mathematicians Pierre de Fermat and Blaise Pascal were inspired to formulate the modern probability theory after trying to solve the problem of points. Here is a simple example: Pascal and Fermat take turns tossing coins. Fermat wins a point if a head is tossed, and
A student is selling magazines for a school fundraiser. There is a 20 percent chance that a given homeowner will buy a magazine. The student needs to sell five magazines. Determine the probability that he will need to visit 5, 6, 7,..., 100 houses to sell five magazines.
When tested for extra sensory perception (ESP), participants are asked to identify the shape on a card. There are 25 cards in all. These 25 cards are divided into five groups of five, with each group featuring a different shape. If a person identifies 12 cards correctly, what would you conclude?
Suppose that in a group of 100 people, 20 have the flu and 80 do not. If you randomly select 30 people, what is the chance that at least 10 people have the flu?
Professional basketball player Kevin Durant is a 90 percent foul shooter. Answer the following questions:If he shoots 100 free throws, what is the probability that he will miss more than 15 shots?How good a foul shooter would Kevin Durant be if he had only a 5 percent chance of making fewer than 90
Suppose that during each year, a given mutual fund has a 50 percent chance of beating the Standard & Poor’s 500 Stock Index (S&P Index). In a group of 100 mutual funds, what is the probability that at least 10 funds will beat the S&P Index during at least eight out of 10 years?
Use the airline overbooking data to do the following:Determine how the probability of overbooking changes as the number of tickets sold varies from 100 through 115.Show how the probability of overbooking changes as the number of tickets sold varies from 100 through 115 and the probability that a
Suppose that, on average, 4 percent of all CD drives received by a computer company are defective. The company has adopted the following policy: Sample 50 CD drives in each shipment, and accept the shipment if none are defective. Using this information, determine the following:What fraction of
Identify the following random variables as discrete or continuous:Number of games the Seattle Seahawks win next seasonNumber that comes up when spinning a roulette wheelUnit sales of tablet PCs next yearLength of time that a light bulb lasts before it burns out
Compute the mean, variance, and standard deviation of the number of dots showing when a die is tossed.
Determine whether the following random variables are independent:Daily temperature and sales at an ice cream storeSuit and number of a card drawn from a deck of playing cardsInflation and return on the stock marketThe number of units sold of a car and the price charged for each unit

Showing 500 - 600 of 914

Microsoft Excel Data Analysis And Business Modeling 7th Edition Wayne Winston - Solutions

Step by Step Answers