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

A stock is selling today for $42. The stock has an annual volatility of 40 percent, and the annual risk-free rate is 10 percent. Answer the following questions:What is a fair price for a six-month European call option with an exercise price of $40?How much does the current stock price have to
Mr. D’s Supermarket is determined to please its customers with a customer-advantage card. Currently, 30 percent of all their shoppers are loyal to Mr. D’s. A loyal Mr. D’s customer shops at Mr. D’s 85 percent of the time. A disloyal Mr. D’s customer shops at Mr. D’s 10 percent of the
Whirlswim Appliance is considering giving each of its customers free maintenance on each TV purchased. The company estimated that this proposal would require it to pay an average of $2.50 for each TV sold (the cost in dollars). The market consisted of 72,000 consumers whose last purchase was from
Suppose the Waterford Crystal company can produce up to 100 iced-tea pitchers per day. Now suppose that the plant is open 250 days per year and that the annual demand is for 20,000 pitchers. The cost to hold a pitcher in inventory for a year might be $10, and the cost to set up the facility to
An appliance store sells plasma TVs. The annual demand is estimated at 1,000 units. The cost to carry a TV in inventory for one year is $500, and the cost to place an order for plasma TVs is $400. Answer the following questions:How many TVs should be ordered each time an order is placed?How many
Using the same wine example as in Problem 1, determine an inventory policy for wine that yields a 99 percent service level.Problem 1Suppose a restaurant serves an average of 5,000 bottles of wine per year. The standard deviation of the annual demand for wine is 1,000 bottles. The annual holding
A reorder-point policy is often referred to as a two-bin policy. How can a reorder-point policy be implemented in a situation in which two bins are used to store inventory?
Suppose a restaurant serves an average of 5,000 bottles of wine per year. The standard deviation of the annual demand for wine is 1,000 bottles. The annual holding cost for a bottle of wine is $1. It costs $10 to place an order for wine, and it takes an average of three weeks (with a standard
Thirty women work on the fifth floor of a business school. Assume that each woman uses the restroom three times a day. (The office is open from 8 a.m. to 5 p.m.) Each visit takes 180 seconds on average, with a standard deviation of 90 seconds. Assume the standard deviation of time between arrivals
A bank has six tellers.Mean service time equals one minute.Mean interarrival time equals 25 seconds.Standard deviation of service times equals one minute.Standard deviation of interarrival times equals 10 seconds.Determine the average time a customer waits in line.
A bank has six tellers.Mean service time equals one minute.Mean interarrival time equals 25 seconds.Standard deviation of service times equals one minute.Standard deviation of interarrival times equals 10 seconds.On average, how many customers are present in the bank line?
A bank has six tellers.Mean service time equals one minute.Mean interarrival time equals 25 seconds.Standard deviation of service times equals one minute.Standard deviation of interarrival times equals 10 seconds.Would you recommend adding more tellers?
A bank has six tellers.Mean service time equals one minute.Mean interarrival time equals 25 seconds.Standard deviation of service times equals one minute.Standard deviation of interarrival times equals 10 seconds.Suppose it costs $20 per hour to have a teller working, and you value a customer’s
You are trying to determine the profit-maximizing price for a video-game console. Currently, you charge $180 and sell 2 million consoles per year. It costs $150 to produce a console, and the price elasticity of demand for consoles is 3. What price should you charge for a console?
Currently, your local sports-car dealer sells 200 cars per year at a price of $150,000. The dealer’s cost of purchasing a car is $60,000. Assume that during a car’s lifetime, the dealer makes $25,000 in profits from repair. The annual demand for cars follows a linear demand curve with an
Assume the demand for suits at a men’s clothing store is linear with a price elasticity of 5. At the current price of $500, 60 suits per week are sold. The cost per suit is $200. Assume that with each suit, a man buys two ties and one shirt. The profit per tie is $15, and the profit per shirt is
A prescription drug is produced in the United States and sold internationally. Each unit of the drug costs $60 to produce. In the German market, the drug sells for €150 per unit. The current exchange rate is 0.667 U.S. dollars per euro. Current demand for the drug is 100 units, and the estimated
In the razor and blade example, suppose the cost to produce a blade is $0.20. If you charge $0.35 for a blade, a customer buys an average of 50 blades. Assume the price elasticity of demand for blades is 3. What price should you charge for a razor and for a blade?
Assume that a purchaser of your video-game console buys, on average, 10 video games, and you earn $10 profit on each video game. What is the correct price for consoles?
You manage a movie theater that can handle up to 8,000 patrons per week. The current demand, price, and elasticity for ticket sales, popcorn, soda, and candy are given in Figure 90-4. The theater keeps 45 percent of the ticket revenues. The unit cost per ticket, popcorn sales, candy sales, and soda
Suppose it costs $250 to produce a video-game console. A price from $200 to $400 is under consideration. The estimated demand for the game console is shown in the following table:What price should you charge for a game console? Price Demand (in millions)
This problem uses the demand information given in Problem 1. Each game owner buys an average of 10 video games. You earn $10 profit per video game. What price should you charge for the game console?Data from in Problem 1Suppose it costs $250 to produce a video-game console. A price from $200 to
You are trying to determine the correct price for a new weekly magazine. The variable cost of printing and distributing a copy of the magazine is $0.50. You are thinking of charging from $0.50 to $1.30 per copy. The estimated weekly sales of the magazine are shown in the following table:In addition
Your local women’s clothing store pays $200 for a suit. The weekly demand for suits is estimated as follows:Assume each woman who buys a suit purchases $80 of additional garments, on which the store earns a 50 percent profit margin. What price should the store set for a suit? Price Demand
A drugstore is trying to determine what price to charge for a candy bar. The drugstore pays 60 cents for each candy bar and is considering charging between $1 and $2 for a candy bar. The estimated weekly demand for three prices is given in the following table. What price should the store charge for
Subscribers to my recommendation service get access to recommendations about local workers, such as doctors, plumbers, and so on. I believe the number of new subscribers I obtain each month depends on price, as follows:Assume each subscriber remains a subscriber for one year. In addition to
The number of ink-jet printers sold in a week by your local electronics store is estimated to have the following dependence on price:The store pays $50 for each ink-jet printer. On average, each person who buys a printer buys 10 cartridges and the store earns $8 profit per cartridge. What printer
Using the information given for Problem 1, suppose you are going to offer a pure bundle. For example, a member can play up to five rounds of golf for $60 per month. The member has no option to choose from other than the pure bundle. What pure bundle maximizes your profit?Data from in problem
Suppose you own a small country club and have three types of customers who value each round of golf they play during a month, as shown in the following table:Find a profit-maximizing two-part tariff. Round no. 1 2 3 4 5₁ 01 6 Customer type 1 $60 $50 $40 $30 $20 $10 Customer type
The file Customers.xlsx computes ending year-10 customers based on three inputs: starting customers, annual number of new customers, and churn rate. Create a spider plot that shows how ending year-10 customers varies as each input varies up to 40 percent from its base level. F 4 Customer
Write a formula that will return a time 18 hours after the current time.
If my price for a product is at least $3 higher than the competitor’s price, we sell 500 units. If my price is at least $3 lower than the competitor’s price we sell 1,500 units. Otherwise, we sell 1,000 units. Assuming all prices are integers between $1 and $10 inclusively, write formulas that
At the beginning of Year 1, your auto-insurance premium for the next year is $300. If you have zero accidents in a year, your premium for the next year drops 10 percent; if you have one accident in a year, your premium for the next year increases10 percent; and if you have two accidents in a year,
Using the data in the file named Catsanddogs.xlsx, determine how many times the text string cat appears and how many times the text string dog occurs. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 A B C D E F G dog cat H | J K doggie katie cat is dog is racoon doggie katie cat is
Thanksgiving occurs on the fourth Thursday in November. Write a formula that computes (given the year) the date of Thanksgiving.
The first quarter of the year is January to March; the second quarter, April to June; the third quarter, July to September; and the fourth quarter, October to December. Write a formula that returns (for any given date) the quarter of the year.
Write a formula that returns a person’s age, given his or her date of birth. Use this method in order to calculate age of person if date of birth is 1944 if age is the name of function, print(age(1944)).
Labor Day is the first Monday in September. Write a formula that determines the date of Labor Day for a given year.
The file named Addresses.xlsx gives people’s names on one line, their street address on the next line, and their city, state, and zip code on the following line. How could you put each person’s information on one line? 123 45 6 7 9 10 11 12 13 14 A- B C D Wayne Winston 3600 Winston
The file named Nancybonds.xlsx gives the bond rating for several bonds in a previous and future month. You want to efficiently count how many bonds were downgraded. Unfortunately, each company is listed in more than one row. Assuming you have sorted the data on the company name, how would you
Martin Luther King Day occurs on the third Monday in January. Write a formula that computes (given the year) the date of Martin Luther King Day. First determine the day of the week for January 1 of the given year. A major drug company is trying to determine the correct plant capacity for a new
Let’s reconsider our pro forma example. Suppose the interest rate on our debt depends on our financial well-being. More specifically, suppose that if our earnings before interest and taxes (EBIT) are negative, our interest rate on debt is 16 percent. If our interest expense is more than 10
Suppose a stock is currently selling for $32. You buy a six-month European call option with an exercise price of $30 for $2.50 and sell a six-month European call option with an exercise price of $35 for $1. Compute the profit of this strategy (in dollars) as a function of a six month stock price
A European call option gives you the right to buy at a specified future date a share of stock for a given exercise price. A butterfly spread involves buying one call option with a low exercise price, buying one call option with a high exercise price, and selling two call options with an exercise
On the S&P moving-average example, suppose we still buy shares if the current price exceeds the 15-month moving average, but we sell if the current price is less than the five month moving average. Is this strategy more profitable than selling when the current price is less than the 15-month
In a game of craps, on each roll of the dice after the first roll, the rules are as follows: If the game has not ended and the current roll matches the first roll, you win the game. If the game has not ended and the current roll is a 7, you lose. Otherwise, the game continues. Develop a worksheet
A drug company believes its new drug will sell 10,000 units in 2004. They expect two competitors to enter the market. The year in which the first competitor enters, the company expects to lose 30 percent of its market share. The year in which the second competitor enters, the company expects to
We are bidding on a valuable painting. The high bid gets the painting. We estimate the painting’s value at $10,000. Four companies are bidding against us. It costs $400 to prepare the bid. Write a formula that (given the bids of our four competitors and our bid) determines whether we get the
You are bidding on a construction project. The low bid gets the project. You estimate the project cost at $10,000. Four companies are bidding against you. It costs $400 to prepare the bid. Write a formula that (given the bids of your four competitors and your bid) computes your profit (or loss if
The same drug company is producing a new drug. The company has made the following assumptions:During Year 1, 100,000 units will be sold. Sales will grow for three years and then decline for seven years.During the growth period, sales will grow at a rate of 15 percent per year. During the decline,
Do this problem independently of Problem 13. Suppose our firm wants a debt-to-equity ratio of 50 percent during each year. How would you modify the pro forma statement?Data from in Problem 13Let’s reconsider our pro forma example. Suppose the interest rate on our debt depends on our financial
Suppose the price of a product will change at dates in the future, as follows:Write a formula that computes the price of the product based on the date the product is sold. Date On or before February 15, 2004 From February 16, 2004, through April 10, 2005 From April 11, 2005, through January 15,
A Blue Yonder Airline flight from Seattle to New York has a capacity of 250 people. The airline sold 270 tickets for the flight at a price of $300 per ticket. Tickets are nonrefundable. The variable cost of flying a passenger (mostly food costs and fuel costs) is $30 per passenger. If more than 250
A major drug company is trying to determine the correct plant capacity for a new drug. A unit of annual capacity can be built for a cost of $10. Each unit of the drug sells for $12 and incurs variable costs of $2. The drug will be sold for 10 years. Develop a worksheet that computes the company’s
An NBA player is receiving $15 million at the end of each of the next seven years. He can earn 6 percent per year on his investments. What is the present value of his future salary payments?
You have just won the lottery. At the end of each of the next 20 years, you will receive a payment of $50,000. If the cost of capital is 10 percent per year, what is the present value of your lottery winnings?
I now have $250,000 in the bank. At the end of each of the next 20 years, I withdraw $15,000. If I earn 8 percent per year on my investments, how much money will I have in 20 years?
I plan to deposit $2,000 per month at the end of each month over the next 10 years. My investments earn 0.8 percent per month. I would like to have $1 million in 10 years. To achieve this, I will also need to deposit a lump sum now. How much should that lump sum be?
A perpetuity is an annuity that is received forever. If I rent out my house, and at the beginning of each year I receive $14,000, what is the value of this perpetuity? Assume an annual 10 percent cost of capital.
Answer each question in Problem 7 assuming beginning-of-month payments.Data from in Problem 7You are borrowing $200,000 on a 30-year mortgage, with an annual interest rate of 10 percent. Assuming end-of-month payments, determine the monthly payment, the monthly interest payment, and the
At the end of each of the next 20 years, I will receive the following amounts:Years 1–5 .............................. $200Years 6–10 ............................ $300Years 11–20 .......................... $400Use the PV function to find the present
You are borrowing $200,000 on a 30-year mortgage, with an annual interest rate of 10 percent. Assuming end-of-month payments, determine the monthly payment, the monthly interest payment, and the amount paid toward the principal each month.
Use the FV function to determine the value to which $100 accumulates in three years if you earn 7 percent per year.
You are going to buy a new car. The cost of the car is $50,000. You have been offered two payment plans:A 10 percent discount on the sales price of the car, followed by 60 monthly payments financed at 9 percent per year.No discount on the sales price of the car, followed by 60 monthly payments
You have a liability of $1,000,000 due in 10 years. The cost of capital is 10 percent per year. What amount of money do you need to set aside at the end of each of the next 10 years to meet this liability?
Suppose you have borrowed money at a 14.4 percent annual rate, and you make monthly payments. If you have missed four consecutive monthly payments, how much should next month’s payment be to catch up?
A balloon mortgage requires you to pay off part of a loan during a specified time period and then make a lump sum payment at the end of that period to pay off the remaining balance of the loan. Suppose you borrow $400,000 on a 20-year balloon mortgage and the interest rate is 0.5 percent per month.
An adjustable-rate mortgage (ARM) ties monthly payments to a rate index (say, the US T-bill rate). Suppose you borrow $60,000 on an ARM for 30 years (360 monthly payments). The first 12 payments are governed by the current T-bill rate of 8 percent. In years 2–5, monthly payments are set at the
I presently have $10,000 in the bank. At the beginning of each of the next 20 years, I plan to invest $4,000, and I expect to earn 6 percent per year on my investments. How much money will I have in 20 years?
You want to replace a machine in 10 years, and you estimate the cost will be $80,000. If you can earn 8 percent annually on your investments, how much money should you put aside at the end of each year to cover the cost of the machine?
You are considering borrowing $50,000 on a 180-month loan. The annual interest rate on the loan depends on your credit score, as shown in Figure 10-8. Write a formula that gives your monthly payments as a function of your credit score. D Annual Rate 8.15 0.0815 ($482.17) 8.45 0.0845
You are buying a motorcycle. You pay $1,500 today and $182.50 a month for three years. If the annual rate of interest is 18 percent, what was the original cost of the motorcycle?
Suppose the annual rate of interest for your motorcycle loan is 10 percent. You pay $200 a month for two years, $300 a month for one year, and $400 a month for two years. What is the present value of all your payments?
You can invest $500 at the end of each six-month period for five years. If you want to have $6,000 after five years, what is the annual rate of return you need on your investments?
I borrow $2,000 and make quarterly payments for two years. The annual rate of interest is 24 percent. What is the size of each payment?
I have borrowed $15,000. I am making 48 monthly payments, and the annual rate of interest is 9 percent. What is the total interest paid over the course of the loan?
I am borrowing $5,000 and plan to pay back the loan with 36 monthly payments. The annual rate of interest is 16.5 percent. After one year, I pay back $500 extra and shorten the period of the loan to two years total. What will my monthly payment be during the second year of the loan?
With an ARM, you make monthly payments that vary depending on the interest rate at the beginning of each year. You have borrowed $60,000 on a 30-year ARM. For the first year, monthly payments are based on the current annual T-bill rate of 9 percent. In years 2–5, monthly payments will be based on
You have a choice of receiving $8,000 each year beginning at age 62 and ending when you die or receiving $10,000 each year beginning at age 65 and ending when you die. If you think you can earn an 8 percent annual return on your investments, which will net the largest amount? You may assume that
You have just won the lottery and will receive $50,000 a year for 20 years. What rate of interest would make these payments the equivalent of receiving $500,000 today?
A bond pays a $50 coupon at the end of each of the next 30 years and pays $1,000 face value in 30 years. If you discount cash flows at an annual rate of 6 percent, what would be a fair price for the bond?
You have borrowed $100,000 on a 40-year mortgage with monthly payments. The annual interest rate is 16 percent. How much will you pay over the course of the loan? With four years left on the loan, how much will you still owe?
I need to borrow $12,000. I can afford payments of $500 per month, and the annual rate of interest is 4.5 percent. How many months will it take to pay off the loan?
You are going to borrow $40,000 for a new car. Calculate the monthly payments and total interest paid for the following situations:48-month loan, 6.85 percent annual rate60-month loan, 6.59 percent annual rate
Consider a machine that costs $50,000 and will be depreciated over 10 years to a final salvage value of $5,000. Determine the depreciation during each year for the SLN, SYD, and DDB depreciation methods.
You have invested $10,000 today, which will compound daily at an annual rate of 6 percent. How much money will you have in a year?
I plan to put $200 in the bank at the end of each month for 60 months. The annual rate is 8 percent, compounded monthly. How much money will I have in 60 months?
Suppose $100 grows to $106 when interest is compounded monthly. What annual rate of interest would yield this result?
Use LOOKUP functions to compute for a given date and day of the week the date of the next date that occurs on the listed day of the week. For example, for 4/30/2021 and Monday, your formula should return 5/3/2021.
Write formulas that return the first and last days of the month for the months 12 months before and after 2/28/2025.
Given any date, find a way to compute which day of the year it is. For example, what day of the year will 4/15/2020 be? Hint: The formula DATE(2020,1,0) will return the serial number for day 0 of January 2020, and Excel will treat this as December 31, 2019.
Memorial Day is always the last Monday in May. Set up a worksheet in which you can enter the year, and the worksheet determines the date of Memorial Day.
Use LOOKUP functions to compute for a given date and day of the week the date of the most recent day of the week on or before the given date. For example, for 4/30/2021 and Wednesday, your formula should return 4/28/2021.
In Fredonia, employees have Tuesdays and Wednesdays off. What date is 200 workdays after 5/3/2013?
Create a worksheet that always lists the next 50 workdays (assuming no holidays and that Monday–Friday are the workdays).
Suppose the first quarter of each year is January–March, the second quarter is April–June, and so on. Write a formula that returns for a given date the first day of the quarter.
In Lower Ampere, employees have Fridays and Saturdays off. Also, Valentine’s Day is a holiday. How many workdays are there between 1/10/2014 and 5/31/2015?
Determine the day that occurs 74 workdays after today’s date (including holidays).
How many workdays (including holidays but excluding Christmas, New Year’s Day, and Independence Day) are there between July 10, 2005, and August 15, 2006?
What is the serial format for January 25, 2006?

Showing 700 - 800 of 914

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

Step by Step Answers