Question

Destaurant Group operates a chain of restaurants. The company is considering two altero Marion plans, either opening up 8 smaller restaurants at a cost of $7,740,000 (Plan Alor onenie expansion p chans at a cost of $6,680,000 (Plan B). Each plan has an expected life of 9 years. The working larger shops sal would be released at the end for use elsewhere. Other information for the two plans appear capital wou below: Plan A Plan B Annual cash revenue $4,000,000 $3,500,000 Annual cash expenses 2,450,000 2,350,000 Working capital needed at start up 600,000 500,000 Residual value in 9 years 1,275,000 Barden requires a 10% rate of return and uses straight line depreciation. Round answers to the nearest dollar. 1. Calculate the NPV of each plan. Round cash flows to the nearest dollar. 2. Calculate the profitability index for each plan. 3. Calculate the IRR for each. Ignore the salvage value on new assets. 4. Calculate the payback for each plan. Barden desires a payback of 6 years or less. 9-11

Answer 1

6) Which plan would you choose and why?
Plan A would be better choice because its NPV, IRR, PI and Payback period are better than Plan B.

Plan A
Year	0	1	2	3	4	5	6	7	8	9
Annual cash Revenues		$ 4,000,000.00	$ 4,000,000.00	$             4,000,000.00	$    4,000,000.00	$    4,000,000.00	$    4,000,000.00	$    4,000,000.00	$    4,000,000.00	$    4,000,000.00
Annual cash Expenses		$ 2,450,000.00	$ 2,450,000.00	$             2,450,000.00	$    2,450,000.00	$    2,450,000.00	$    2,450,000.00	$    2,450,000.00	$    2,450,000.00	$    2,450,000.00
Operating Cash flow		$ 1,550,000.00	$ 1,550,000.00	$             1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00
Machinery Purchased	$ (7,740,000.00)
Add : Working Capital	$    (600,000.00)									$       600,000.00
Salvage Value										$                      -
Net Cash Flow	$ (8,340,000.00)	$ 1,550,000.00	$ 1,550,000.00	$             1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    1,550,000.00	$    2,150,000.00
PV Discount @ 10%	1	0.909090909	0.826446281	0.751314801	0.683013455	0.620921323	0.56447393	0.513158118	0.46650738	0.424097618
Present Value	$ (8,340,000.00)	$ 1,409,090.91	$ 1,280,991.74	$             1,164,537.94	$    1,058,670.86	$       962,428.05	$       874,934.59	$       795,395.08	$       723,086.44	$       911,809.88
NPV	$      840,945.49
IRR	12.41%
PI = (NPV + Initial investment) ÷ Initial Investment	1.10
Pay Back = 8340000/15500000	5.38	Years
ARR = (Operating Cash Flow - Deprecitaion)/Initial Investment	7.47%
Plan B
Year	0	1	2	3	4	5	6	7	8	9
Annual cash Revenues		$ 3,500,000.00	$ 3,500,000.00	$             3,500,000.00	$    3,500,000.00	$    3,500,000.00	$    3,500,000.00	$    3,500,000.00	$    3,500,000.00	$    3,500,000.00
Annual cash Expenses		$ 2,350,000.00	$ 2,350,000.00	$             2,350,000.00	$    2,350,000.00	$    2,350,000.00	$    2,350,000.00	$    2,350,000.00	$    2,350,000.00	$    2,350,000.00
Operating Cash flow		$ 1,150,000.00	$ 1,150,000.00	$             1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00
Machinery Purchased	$ (6,680,000.00)
Add : Working Capital	$    (500,000.00)									$       500,000.00
Salvage Value										$    1,275,000.00
Net Cash Flow	$ (7,180,000.00)	$ 1,150,000.00	$ 1,150,000.00	$             1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    1,150,000.00	$    2,925,000.00
PV Discount @ 10%	1	0.909090909	0.826446281	0.751314801	0.683013455	0.620921323	0.56447393	0.513158118	0.46650738	0.424097618
Present Value	$ (7,180,000.00)	$ 1,045,454.55	$    950,413.22	$                864,012.02	$       785,465.47	$       714,059.52	$       649,145.02	$       590,131.84	$       536,483.49	$    1,240,485.53
NPV	$      195,650.66
IRR	10.61%
PI = (NPV + Initial investment) ÷ Initial Investment	1.03
Pay Back = 7180000/11500000	6.24	Years
ARR = (Operating Cash Flow - Deprecitaion)/Initial Investment	13.00%

4000000 2450000 =C4-C5 4000000 2450000 =D4-D5 4000000 2450000 =E4-E5 4000000 2450000 =F4-F5 4000000 2450000 =G4-65 400000 245000 =H4-15 3 Year 4 Annual cash Revenues 5 Annual cash Expenses 6 Operating Cash flow 7 Machinery Purchased -7740000 8 Add: Working Capital -600000 9 Salvage Value 10 Net Cash Flow =B7+B8 11 PV Discount @ 10% =1/1.1^B3 12 Present Value =B10 B11 13 NPV =SUM(B12:K12) 14 IRR =IRR(B10:10) 15 PI = (NPV + Initial investment) = Initial Investmerl=(B13+-B12)/-312 =SUM(C6:09) =1/1.1^C3 =C10*c11 =SUM(D6:09) =1/1.1^D3 =D10*D11 =SUM(E6:09) =1/1.14E3 =E10 E11 =SUM(F6:F9) =1/1.1^F3 =F10 F11 =SUM(G6:59) =1/1.1^G3 =G10-G11 =SUM =1/1.14 =H10H Years 16 Pay Back = 8340000/15500000 --B10/C10 17 ARR = (Operating Cash Flow - Deprecitaion)/Initi: = (C6-(-B10/9)}/-B10 18 4 3500000 2350000 =C21-C22 3500000 2350000 =D21-D22 3500000 2350000 =E21-E22 3500000 2350000 =F21-F22 3500000 2350000 =G21-G22 350000 235000 =H21-H 19 Plan B 20 Year 21 Annual cash Revenues 22 Annual cash Expenses 23 Operating Cash flow 24 Machinery Purchased 25 Add: Working Capital 26 Salvage Value 27 Net Cash Flow 28 PV Discount @ 10% 29 Present Value 30 NPV 31 IRR -6680000 -500000 =B24+B25 =1/1.1^B20 =B27*B28 =SUM(B29:29) =IRR(B27:K27) =SUM(C23:C26) =1/1.1^C20 =C27*C28 =SUM(D23:D26) =1/1.1^D20 =D27*D28 =SUM(E23:26) =1/1.1"E20 =E27 E28 =SUM(F23:F26) =1/1.1^F20 =F27°F28 =SUM(G23:26) =1/1.1"G20 =G27*G28 =SUMI =1/1.1" =H27*H PL = (NPV + Initial investment) = Initial 32 Investment =(B30+-B29)/-B29 33 Pay Back = 7180000/11500000 --B27/027 Years ARR = (Operating Cash Flow - 34 Deprecitaion)/Initial Investment =(C23-((-B24-K26)/9))/((-B27+K26)/

4000000 2450000 =C4-C5 4000000 2450000 =D4-D5 4000000 2450000 =E4-E5 4000000 2450000 =F4-F5 4000000 2450000 =G4-65 400000 245000 =H4-15 3 Year 4 Annual cash Revenues 5 Annual cash Expenses 6 Operating Cash flow 7 Machinery Purchased -7740000 8 Add: Working Capital -600000 9 Salvage Value 10 Net Cash Flow =B7+B8 11 PV Discount @ 10% =1/1.1^B3 12 Present Value =B10 B11 13 NPV =SUM(B12:K12) 14 IRR =IRR(B10:10) 15 PI = (NPV + Initial investment) = Initial Investmerl=(B13+-B12)/-312 =SUM(C6:09) =1/1.1^C3 =C10*c11 =SUM(D6:09) =1/1.1^D3 =D10*D11 =SUM(E6:09) =1/1.14E3 =E10 E11 =SUM(F6:F9) =1/1.1^F3 =F10 F11 =SUM(G6:59) =1/1.1^G3 =G10-G11 =SUM =1/1.14 =H10H Years 16 Pay Back = 8340000/15500000 --B10/C10 17 ARR = (Operating Cash Flow - Deprecitaion)/Initi: = (C6-(-B10/9)}/-B10 18 4 3500000 2350000 =C21-C22 3500000 2350000 =D21-D22 3500000 2350000 =E21-E22 3500000 2350000 =F21-F22 3500000 2350000 =G21-G22 350000 235000 =H21-H 19 Plan B 20 Year 21 Annual cash Revenues 22 Annual cash Expenses 23 Operating Cash flow 24 Machinery Purchased 25 Add: Working Capital 26 Salvage Value 27 Net Cash Flow 28 PV Discount @ 10% 29 Present Value 30 NPV 31 IRR -6680000 -500000 =B24+B25 =1/1.1^B20 =B27*B28 =SUM(B29:29) =IRR(B27:K27) =SUM(C23:C26) =1/1.1^C20 =C27*C28 =SUM(D23:D26) =1/1.1^D20 =D27*D28 =SUM(E23:26) =1/1.1"E20 =E27 E28 =SUM(F23:F26) =1/1.1^F20 =F27°F28 =SUM(G23:26) =1/1.1"G20 =G27*G28 =SUMI =1/1.1" =H27*H PL = (NPV + Initial investment) = Initial 32 Investment =(B30+-B29)/-B29 33 Pay Back = 7180000/11500000 --B27/027 Years ARR = (Operating Cash Flow - 34 Deprecitaion)/Initial Investment =(C23-((-B24-K26)/9))/((-B27+K26)/