$($include directions on how to solve with exel$)$

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\%.$

What is the baseline NPV and IRR?

What is the NPVs sensitivity to the Sofa Price?

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.

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.

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.

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).$

What is the NPV of the worst case with the option?

What is the expected overall NPV $($updating your answer to question $6$ to include the value of the option$)?$