(I NEED HELP CREATING AN EXEL DOCUMENT FOR THIS QUESTION)

Horizon Water Beds was originally founded 20 years ago when owner Glen Fisher started making water beds but has since expanded to all bedroom furnishings. The company is considering expanding operations to include manufacturing sofas and love seats. They have spent $50,000 to produce prototypes for several potential designs, they also have an old factory that they used to use to manufacture the waterbeds. Glenn figures that he can use the vacant factory to produce the new product lines. The factory is currently valued at $1,000,000.

Glenn estimates that he can sell each sofa for $950 and each love seat for $650. While the cost per unit to produce each will be $600 for the sofas and $500 for each love seat, which includes the cost of materials and the cost of labor. Glen estimates that the cost per unit will increase by 3% per year while he will only be able to increase the price per unit by 2% per year. Fixed costs would include additionally utilities of $50,000. In order to produce the new product lines the company must invest in $1,000,000 in new equipment, which will be depreciated using the 5 years MACRS schedule. The project has a 6 year life at which time the equipment can be salvaged for $50,000 and the factory can be sold for $1,000,000. Net working capital will be 20% of the next years sales. Glen estimates that he will be able to sell 900 sofas and 1000 love seats, in year one and the units sold will increase by 10% each year for both sofas and love seats. The tax rate is 21% and the relevant discount rate is 13%.

1. What is the baseline NPV and IRR?
2. What is the NPVs sensitivity to the Sofa Price?
3. What is the NPVs sensitivity to Sofa Units sold?

Copy the Baseline Proforma you just made and create a best- and worst-case scenario.

Worst case: There is not as much demand as Glen had previously hoped. Units sold in year one are only 450 for the Sofa and 500 for the Love Seat, and only increase at 5% each year. Additionally, prices do not increase. Everything else is the same as the baseline case.

1. What is the NPV and IRR?

Best Case: Demand is much higher than expected. Units sold are 1500 for the Sofa and 1800 for the Love Seat in year one. Everything else is the same as the baseline case.

1. What is the NPV and IRR?

If there is a 50% chance that the baseline happens, a 30% chance of the worst case, and a 20% chance of the best case scenario.

1. What is the expected NPV?

Now assume that if the worst case scenario does indeed happen Glen has the option to sell the factory for $1,000,000 and the equipment for $500,000 at the end of year 2(so there are sales in year 2).

1. What is the NPV of the worst case with the option?
2. What is the expected overall NPV (updating your answer to question 6 to include the value of the option)?