Question

You are tasked to perform an analysis of the manufacturing plant and to present your recommendation on whether the company should open the new plant or not. If the new plant is opened, it will cost R500 million today and the expected cash flows are shown in the table below. The company's required rate of return is 12% Year 0 1 WN 2 3 4 5 6 7 8 9 Cash Flow -500 000 000 60 000 000 90 000 000 170 000 000 230 000 000 205 000 000 140 000 000 110 000 000 70 000 000 -80 000 000 a. Estimate the normal payback period of the proposed plan b. Estimate the discounted payback period of the proposed plan C. Estimate the net present value of the proposed plan. d. Based on your analysis, should the company open the new plant? (3) (5) (10) (2)

Answer 1

Excel Formulation:

А B С D E F G H K 1 2 3 YO Y1 Y2 Y3 Y4 Y5 Y6 Y7 Y8 Y9 4 60 90 170 230 205 140 110 70 All figures in mn (expect discount factor) Given Cash Inflow Cash Outflow Discount Rtae Net Cash Flow 5 -500 -80 6 0.12 =C4+C5 =D4+D5 =E4+E5 =F4+F5 =G4+G5 =H4+H5 =14+15 =J4+25 =K4+K5 L4+L5 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Net present value Year 0 1 =D10+1 =E10+1 =F10+1 =G10+1 =H10+1 =110+1 =J10+1 =K10+1 Discount Factor = (1+$C$6)^C10 =(1+$C$6)^D10 =(1+$C$6)^E10 =(1+$C$6)^F10 =(1+$C$6)^G10 =(1+$C$6)^H10 =(1+$C$6)^110 =(1+$C$6)^J10 =(1+$C$6)^K10 =(1+$C$6)^L10 PV of Net Cash Flow =C7/C11 =D7/D11 =E7/E11 =F7/F11 =G7/G11 =H7/H11 =17/111 =J7/J11 =K7/K11 =L7/L11 NPV =SUM(C12:L12) Return of Investment Total Cash Inflow =SUM(C4:L4) Total Cash Outflow =SUM(C5:L5) Return of investment =C15/C16*-1 Payback Period Cummulative Cash Inflow =C4 =C19+D4 =D19+E4 =E19+F4 =F19+G4 =G19+H4 =H19+14 =119+J4 =J19+K4 =K19+L4 Cummulative Cash Outflow =C5 =C20+D5 =D20+E5 =E20+F5 =F20+G5 =G20+H5 =H20+15 =120+J5 =J20+K5 =K20+L5 Cummulative Cash Inflow at Y5 =H19 Payback Period (Yrs) 5 Discounted Payback Period Discounted Cash Inflow =C4/C11 =D4/D11 =E4/E11 =F4/F11 =G4/G11 =H4/H11 =14/111 =J4/J11 =K4/K11 =L4/L11 Cummulative Discounted Cash Inflow =C24 =D24+C25 =E24+D25 =F24+E25 =G24+F25 =H24+G25 =124+H25 =J24+125 =K24+J25 =L24+K25 Discounted Cash Outflow =C5/C11 =D5/D11 =E5/E11 =F5/F11 =G5/G11 =H5/H11 =15/111 =J5/J11 =K5/K11 =L5/L11 Cummulative Discounted Cash Outflow =C26 =D26+C27 =E26+D27 =F26+E27 =G26+F27 =H26+G27 =126+H27 =J26+127 =K26+27 =L26+K27 Discounted Payback Period (Yrs) 6

A - С D E F G H K 2 3 YO Y1 Y2 Y3 Y4 Y5 Y6 47 Y9 B All figures in mn (expect discount factor) Given Cash Inflow Cash Outflow Discount Rtae Net Cash Flow Y8 70.000 4 60.000 90.000 170.000 230.000 205.000 140.000 110.000 5 -80.000 6 -500 12% -500 7 60.000 90.000 170.000 230.000 205.000 140.000 110.000 70.000 -80.000 8 9 0 1.000 4.000 5.000 8.000 10 11 Net present value Year Discount Factor PV of Net Cash Flow 1 2.000 1.254 71.747 3.000 1.405 121.003 1.120 53.571 1.574 1.762 6.000 1.974 70.928 7.000 2.211 49.758 2.476 28.272 9.000 2.773 -28.849 12 -500 146.169 116.323 128.92 13 14 15 16 17 1075 -580 1.853448276 18 19 0 60.000 -500.000 150.000 -500.000 320.000 -500.000 550.000 -500.000 755.000 -500.000 895.000 -500.000 1005.000 -500.000 1075.000 -500.000 1075.000 -580.000 NPV Return of Investment Total Cash Inflow Total Cash Outflow Return of investment Payback Period Cummulative Cash Inflow Cummulative Cash Outflow Cummulative Cash Inflow at Y5 Payback Period (Yrs) Discounted Payback Period Discounted Cash Inflow Cummulative Discounted Cash Inflow Discounted Cash Outflow Cummulative Discounted Cash Outflow Discounted Payback Period (Yrs) 20 21 22 23 24 25 26 27 28 29 -500 755 5 0 0 -500 -500 6 53.571 53.571 0.000 -500.000 71.747 125.319 0.000 -500.000 121.003 246.322 0.000 -500.000 146.169 392.491 0.000 -500.000 116.323 508.813 0.000 -500.000 70.928 579.742 0.000 -500.000 49.758 629.500 0.000 -500.000 28.272 657.772 0.000 -500.000 0.000 657.772 -28.849 -528.849

Answers: Based on above workout worksheet

1. Normal Payback period:

The timeline where all invested amount get paid back, disregarding time value of money is 5 yrs

1. Discounted Normal Payback period:

The timeline where all invested amount get paid back, considering time value of money is 6 yrs

1. Net Present Value:

NPV is present value of net cash flow over project period. NPV of project is $128.92 mn

1. Based on analysis, following things are implied

1. Project has positive NPV
2. Payback period if 5 yrs, out total duration of 9 yrs
3. Return on investment is 1.85

Hence, company should open new plant.

If you like the answer please give an up-vote, this will be quite encouraging for me, thank you.

If you have further suggestions in answer, please write below on the comment section, I will certainly look into it.