Questions and Answers of Microsoft Excel Data Analysis 7th

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 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
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.
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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

Showing 500 - 600 of 914