happypups speadsheet

/in /by

Using Excel, complete the following:

HappyPups makes pet cameras and will be introducing their newest product, the Furbo. The Furbo is a camera that allows dog owners to check in on their pup, give their pup a treat, and talk to them while they are away. HappyPups spent $375,000 on consumer demand studies and an additional $40,000 on research and development to get the perfect product for all pet lovers. Each Furbo will sell for $195. It is expected that Furbo product will generate additional revenue from the sale of treats that are compatible with device. It is expected that 50% of Furbo purchases will result in an additional $15 net profit from treat sales at time of purchase. The Furbo has a variable cost of $125.00, and the project will incur an annual fixed cost of $1,400,000 each year. HappyPups will need to purchase a new machine to manufacture the Furbo for $2,000,000, and will be depreciated using the 3-yr MACRS schedule. HappyPups anticipates it will be able to sell the machine for $750,000 at the end of the project in three years. In order to promote the product, HappyPups will initially set aside $1,000,000 worth of inventory at the beginning of the project and will readjust NWC levels to reflect 10% of Furbo Sales (not including the treats). All inventory will be liquidated at the end of project. However, by introducing the Furbo, HappyPups anticipate that will take away roughly $1,000,000 in revenue each year from its existing pet camera line. The required return for the project is 20%, and the tax rate is 21%

  • Spent $375,000 and $40,000 on consumer demand studies and R&D
  • Sales: 80,000 units, 70,000 units, 60,000 units.
  • Retail price: $195, Variable Costs $125, and $1,400,000 in fixed costs
  • 50% of Furbo purchases will result an additional $15 in revenue from Treat sales
  • Machine to manufacture the product: $2,000,000, depreciated using 3yr MACRS schedule. Will be able to sell for $750,000 at the end of the 3rd year.
  • $1,000,000 will be set aside in inventory, and then adjust to 10% of Furbo sales (just the camera), and will be liquidated and recouped at the end of the project
  • $1,000,000 in revenue will be lost each year from HappyPup’s existing pet camera line.

Year

MACRS

1

33.33%

2

44.45%

3

14.81%

4

7.41%
  • Calculate the Operating Cash flows for the project.
  • Calculate the Cash Flow From Assets for this Project.
  • What is the net present value of this project? Should the project be accepted or rejected?
  • What is the IRR of this project (round to nearest number)?
  • HappyPups does not have enough cash for the machine. HappyPups current has a debt-to-equity ratio of .7, and does not want to alter its capital structure. If HappyPups will be charged 8% to raise debt and 10% to raise equity, how much will the machine effectively cost HappyPups?
  • Using the information in number 5, what is the new net present value of the project?
  • One call option contract is worth 100 units of the underlying stock. The call option has an exercise price of $40, and option premium of $1. If you buy one call option contract, what is your net profit when the stock price is $20 at expiration? What is your net profit if the stock price is at $55 at expiration?
  • One put option contract is worth 100 units of the underlying stock. The put option has an exercise price of $40, and option premium of $1. If you buy one put option contract, what is your net profit when the stock price is $20 at expiration? What is your net profit if the stock price is at $55 at expiration?