Problems

1. You’ve been assigned to analyze the profitability of Bill Clinton’s new autobiography. The following assumptions have been made:

   • Bill 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 each hardcover book is $4.

   • The publisher’s net from book sales per hardcover unit sold is $15.

   • The publisher expects to sell 1 million hardcover copies.

   • The fixed cost of producing the paperback is $100,000.

   • The variable cost of producing each paperback book is $1.

   • The publisher’s net from book sales per paperback unit sold is $4.

   • Paperback sales will be double hardcover sales.

   Use this information to answer the following questions.

   • Determine how the publisher’s before-tax profit will vary as hardcover sales vary from 100,000 through 1 million copies.

   • Determine how the publisher’s before-tax profit varies as hardcover sales vary from 100,000 through 1 million copies and the ratio of paperback to hardcover sales varies from 1 through 2.4.

2. 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 price (within $10) and amount of advertising (within $100) that maximizes profit.

3. Reconsider our hedging example in Chapter 11, “IF Statements.” For stock prices in six months that range from $20.00 through $65.00 and the number of puts purchased varying from 0 through 100 (in increments of 10), determine the percentage return on your portfolio.

4. For our mortgage example, suppose you know the annual interest rate will be 5.5 percent. Create a table that shows for amounts borrowed from $300,000 through $600,000 (in $50,000 increments) the difference in payments between a 15-year, 20-year, and 30-year mortgage.

5. Currently, we sell 40,000 units of a product for $45 each. The unit variable cost of producing the product is $5. We are thinking of cutting the product price by 30 percent. We are sure this will increase sales by an amount from 10 percent through 50 percent. Perform a sensitivity analysis to show how profit will change as a function of the percentage increase in sales. Ignore fixed costs.

6. Let’s assume that at the end of each of the next 40 years, we will put the same amount in our retirement fund and earn the same interest rate each year. Show how the amount of money we will have at retirement changes as we vary our annual contribution from $5,000 through $25,000 and the rate of interest varies from 3 percent through 15 percent.

7. 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 million. Annual cash flow growth will be from 5 percent through 25 percent a year. How does the project payback depend on the year 1 cash flow and cash flow growth rates?

8. 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.00. The fixed cost of translation is $5 million. Translating the product into Swahili will increase sales during each of the next three years by some unknown percentage over the current level of 200,000 units. Show how the change in profit resulting from the translation depends on the percentage increase in product sales. You can ignore the time value of money and taxes in your calculations.

9. The file Citydistances.xlsx gives latitude and longitude for several U.S. cities. There is also a formula that determines the distance between two cities by using a given latitude and longitude. Create a table that computes the distance between any pair of of cities listed.

10. You have begun saving for your child’s college education. You plan to save $5,000 per year, and want to know for annual rates of return on your investment from 4 percent through 12 percent the amount of money you will have in the college fund after saving for 10–15 years.

11. If we earn interest at percentage rate r per year and compound our interest n times per year, then in y years $1 will grow to (1+(r/n))^ny dollars. Assuming a 10 percent annual interest rate, create a table showing the factor by which $1 will grow in 5–15 years for daily, monthly, quarterly, and semiannual compounding.

12. Assume I have $100 in the bank. Each year, I will withdraw x percent (ranging from 4 percent through 10 percent) of my original balance. For annual growth rates of 3 percent through 10 percent per year, determine how many years it will take before I run out of money. Hint: You should use the IFERROR function (discussed in Chapter 11, “IF Statements”) because if my annual growth rate exceeds the withdrawal rate, I will never run out of money.

13. If we earn interest at an annual rate of x percent per year, then in n years $1 will become (1+x)ⁿ dollars. For annual rates of interest from 1 percent through 20 percent, determine the exact time (in years) in which $1 will double.

14. We are borrowing $200,000 and making payments at the end of each month. For an annual interest rate ranging from 5 percent through 10 percent and loan durations of 10, 15, 20, 25, and 30 years, determine the total interest paid on the loan.

15. You are saving for your child’s college fund. You are going to contribute the same amount of money in 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 through 15 percent and number of years investing varying from 5–15 years, determine your required annual contribution.

16. The file Antitrustdata.xlsx gives the starting and ending years for many court cases. Determine the number of court cases active during each year.