Question

11. Company Y is developing a new product line. The product line will be a new gaming system to compete with XBOX. The product requires an investment of $567,000 for inventory and $22,000 for shipping. The Company has a WACC of 7% and an effective tax rate of 40%. This product is expected to generate the following results: Sales Year # of Units 3,000 4,365 4,900 5,800 Price $228 per unit $231 per unit $240 per unit $250 per unit Costs Year WN Type of Cost Direct Costs Direct Costs Direct Costs Direct Costs Aniount $125 per unit $130 per unit $135 per unit $140 per unit The Company has to pay rent of $125,000 each year for its facility (rent), $40,000 for utilities and pays 5% of total revenue in annual bonuses. What is the NPV, IRR, MIRR and Payback period for the project? Would you do the project or not?

Answer 1

NPV, IRR and MIRR are calculated using the respective functions in Excel.

Payback period is the time taken for the cumulative cash flows to equal zero

Payback period = 3 + (cash flow required in year 3 for cumulative cash flows to equal zero / year 3 cash flow) = 3 + ($213,430 / $240,300) = 3.89 years.

This project would not be accepted as the NPV is negative, and the IRR and MIRR are less than the WACC.

fiNPV(7%,C19:F19) +B 19 B21 A C D E 1 0 1 2 Initial Investment 3 Investment in inventory $589,000 4 5 OCF Number of units 5,800 6 3,000 4,365 4,900 7 Sale price per unit $228 $231 $240 $250 8 Cost per unit $125 $130 $135 $140 $684,000 $1,008,315 $1,176,000 $1,450,000 9 Revenues $375,000 $567,450 $661,500 $812,000 10-Costs $125,000 $125,000 $125,000 $125,000 11 Rent 12 -Utilities $40,000 $40,000 $40,000 $40,000 13 Bonus $34,200 $50,416 $58,800 $72,500 $109,800 $225,449 $290,700 $400,500 14 Earnings before tax $43,920 $90,180 $116,280 $160,200 15-Taxes 16 Income after tax $65,880 $135,270 $174,420 $240,300 17 18 NPV Analysis 19 Project Cash Flows ($589,000) $65,880 $135,270 $174,420 $240,300

А C E 2 1 0 4 Initial Investment 2 Investment in inventory 567000+22000 4 OCF Number of units 3000 4365 5800 6 4900 Sale price per unit 7 228 231 240 250 Cost per unit 125 130 135 140 E6*E7 -Сб*с7 -D6*D7 F6*F7 9 Revenues -Сб*с8 D6*D8 -E6*E8 -F6*F8 10 Costs 125000 125000 11 Rent 125000 125000 12 Utilities 40000 40000 40000 40000 13 Bonus C9*5% D9*5% E9*5% F9*5% 14 Earnings before tax C9-C10-C11-C12-C13 D9-D10-D11-D12-D13 E9-E10-E11-E12-E13 F9-F10-F11-F12-F13 C14*40 % D14*40% -E14*40 % F14*40% 15-Taxes -D14- D15 16 Income after tax =C14-C15 E14-E15 F14-F15 17 18 NPV Analysis 19 Project Cash Flows 3с16 --B3 -D16 E16 F16 20 =NPV(7%, C19:F19)+B19 21 NPV =IRR(B19:F19) 22 IRR =MIRR (B19: F19,7%,7%) 23 MIRR 24 Cumulative Project Cash Flows -B24+C19 -B19 C24+D19 D24+E19 -E24+F19 25 Payback Period =3+-E24/F19) L