J K L M N A B C D E F G H 7 8 9 Discount rate 8% 10 Tax rate 21% 11 Useful life (years) 12 13 Rent Machine Year 0 14 Rent Year 1 -$50,000 Year 2 Year 3 -$50,000 -$50,000 Year 4 -$50,000 Year 5 -$50,000 Year 6 -$50,000 Year 7 -$50,000 Year 8 -$50,000 Year 9 -$50,000 Year 10 -$50,000 15 FCF (rent) 16 NPV 17 18 19 Purchase Current Machine Maintenance 20 Depreciation 21 Capital Expenditures -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$20,000 -$150,000 22 FCF (purchase current) 23 NPV 24 25 Purchase Advanced Machine 26 Maintenance -$15,000 -$15,000 -$15,000 -$15,000 -$15,000 -$15,000 -$15,000 27 Other Costs -$35,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 -$15,000 $10,000 -$15,000 $10,000 -$15,000 $10,000 28 Depreciation 29 Capital Expenditures -$250,000 30 FCF (purchase advanced) 31 NPV 32 33 Beryl's Iced Tea should 34 35 37 Requirements 38 1 In cell E15, by using cell references, calculate the free cash flow for year 1 in case of renting the machine (1 pt.). 2 To calculate the free cash flows for years 2 through 10 in case of renting the machine, copy cell E15 and paste it onto cells F15:N15 (1 pt.). 39 40 3 In cell D16, by using cell references and the function NPV, calculate the net present value of renting the machine (1 pt.). 41 4 In cell E20, by using cell references, calculate the depreciation of the current machine for year 1 (1 pt.). 5 To calculate the depreciation of the current machine for years 2 through 7, copy cell E20 and paste it onto cells F20:K20 (1 pt.). 42 42 6 In cell D22, by using a cell reference, calculate the free cash flow for year 0 in case of purchasing the current machine (1 pt.). 43 7 In cell E22, by using cell references, calculate the free cash flow for year 1 in case of purchasing the current machine (1 pt.). 47 10 In cell E28, by using cell references, calculate the depreciation of the advanced machine for year 1 (1 pt.). 44 45 45 46 445 8 9 To calculate the free cash flows for years 2 through 10 in case of purchasing the current machine, copy cell E22 and paste it onto cells F22:N22 (1 pt.). In cell D23, by using cell references and the function NPV, calculate the net present value of purchasing the current machine (1 pt.). 11 To calculate the depreciation of the advanced machine for years 2 through 7, copy cell E28 and paste it onto cells F28:K28 (1 pt.). 48 12 In cell D30, by using cell references, calculate the free cash flow for year 0 in case of purchasing the advanced machine (1 pt.). 49 49 50 51 52 53 54 13 14 15 To calculate the free cash flows for years 2 through 10 in case of purchasing the advanced machine, copy cell D30 and paste it onto cells E30:N30 (1 pt.). In cell D31, by using cell references and the function NPV, calculate the net present value of purchasing the advanced machine (1 pt.). In cell D33, type whether Beryl's Iced Tea should continue to rent, purchase the current machine, or purchase the advanced machine. (1 pt.).