Problems

1. Suppose the price of a product will change at dates in the future, as follows:

   Open table as spreadsheet

   Date	Price
   On or before February 15, 2004	$8
   From February 16, 2004, through April 10, 2005	$9
   From April 11, 2005, through January 15, 2006	$10
   After January 15, 2006	$11

   Write a formula that will compute the price of the product based on the date the product is sold.

2. The 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 non-refundable. The variable cost of flying a passenger (mostly food costs and fuel costs) is $30 per passenger. If more than 250 people show up for the flight, the flight is over-booked and Blue Yonder must pay overbooking compensation of $350 per person to each overbooked passenger. Develop a worksheet that computes Blue Yonder’s profit based on the number of customers who show up for the flight.

3. 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 10-year profit given the chosen annual capacity level and the annual demand for the drug. We will assume demand for the drug is the same each year. You can ignore the time value of money in this problem.

4. Our 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, sales will drop at a rate of 10 percent per year.

   Develop a worksheet that takes values for year 1 sales, length of growth cycle, length of decline cycle, growth rate during growth cycle, and rate of decrease during decline cycle, and computes unit sales for years 1–11.

5. We are bidding on a construction project. The low bid will get the project. We estimate our project cost 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) computes our profit.

6. We are bidding on a valuable painting. The high bid will get 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 painting.

7. Our drug company believes a new drug will sell 10,000 units during 2004. They expect two competitors to enter the market. The year in which the first competitor enters, our company expects to lose 30 percent of its market share. The year in which the second competitor enters, the company expects to lose 15 percent of its market share. The size of the market is growing at 10 percent per year. Given values of the years in which the two competitors enter, develop a worksheet that computes the annual sales for the years 2004–2013.

8. A clothing store has ordered 100,000 swimsuits. It costs $22 to produce a swimsuit. They plan to sell them until August 31 at a price of $40 and then mark the price down to $30. Given values for demand through August 31 and after August 31, develop a worksheet to compute the profit from this order.

9. 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, we win the game. If the game has not ended and the current roll is a 7, we lose. Otherwise, the game continues. Develop a worksheet that tells us (given knowledge of the first four rolls) the status of the game after four dice rolls.

10. On our 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 5-month moving average. Is this strategy more profitable than selling when the current price is less than the 15-month moving average?

11. A European call option gives us 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 price midway between the low and high exercise prices. Here is an example of a butterfly spread: The current stock price is $60. We buy a $54 six-month European call option for $9, buy a $66 six-month European call option for $4, and sell two $60 European call options for the price of $6. Compute the profit (in dollars, not percentage) for this transaction as a function of six-month stock prices ranging from $40–$80. When a trader purchases a butterfly spread, which type of movement in the stock price during the next six months is the trader betting on?

12. Suppose a stock is currently selling for $32.00. We buy a six-month European call option with an exercise price of $30.00 for $2.50 and sell a six-month European call option with an exercise price of $35.00 for $1.00. Compute the profit of this strategy (in dollars) as a function of a six-month stock price ranging from $25.00–$45.00. Why is this strategy called a bull spread? How would you modify this strategy to create a bear spread?

13. 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 percent of EBIT and EBIT is positive, our interest rate on debt is 13 percent. Otherwise, our interest rate is 10 percent. Modify our pro forma to account for this variable interest rate.

14. 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 our pro forma? Hint: You must keep each year’s stock nonnegative and use stock and cash or marketable securities to balance assets and liabilities.

15. 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. Hint: First determine the day of the week for January 1 of the given year.

16. Thanksgiving occurs on the fourth Thursday in November. Write a formula that computes (given the year) the date of Thanksgiving. Hint: First determine the day of the week for November 1 of the given year.

17. The first quarter of the year is January–March; the second quarter, April–June; the third quarter, July–September; and the fourth quarter, October–December. Write a formula that returns (for any given date) the quarter of the year.

18. Write a formula that returns a person’s age, given his or her date of birth.

19. Labor Day is the first Monday in September. Write a formula that determines the date of Labor Day for a given year.

20. File 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 we have sorted the data on the company name, how would you determine the number of downgraded bonds?

21. The file 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?

22. The file FormattingDDAnum.xlsx gives a bunch of text strings such as DDA : D, DDA1250045, and so on. A cell is properly formatted if the first three characters are DDA and the last seven characters are a number 1 million or larger. Determine which cells are properly formatted.

23. Suppose the number of Group 1 members is listed in cell B1, the number of Group 2 members is listed in cell B2 and the number of Group 3 members is listed in cell B3. The total number of group members is always 100. Suppose that Group 1 has 50 members, Group 2 has 30 members, and Group 3 has 20 members. Efficiently place a “1” for each Group 1 member in column D, a “2” for each Group 2 member in column D, and a “3” for each Group 3 member in column D. Thus, column D should (in our example) have a “1” in D1:D50, a “2” in D51:D80 and a “3” in D81:D100.

24. The file Divideby0price.xlsx contains units sold of each product and total revenue. We want to determine the average price for each product. Of course, if units sold are 0, then there is no average price. Error-trap the file Divideby0price.xlsx to ensure that all products with 0 sales yield the message “no sales” instead of a #DIV!0 error.

25. The School of Fine Art has 100 lockers numbered 1–100. At present, all lockers are open. We begin by closing every locker whose number evenly divides by 3. Then we “toggle” (toggling means opening a closed locker and closing an open locker) each locker whose number is divisible by 4; then toggle each locker whose number is divisible by 5,…, and finally toggle each locker whose number is divisible by 100. How many lockers are now open?

26. The file Matchlist.xlsx contains a list of people who bought your product in February and a list of people who bought it in March. Determine how many of your February customers purchased your product in March.

27. Set up a “calendar worksheet” that takes a given month and year as inputs and tells you the day of the week on which each day of the month occurs.