Instructions: Calculate the NPV for each proposal. For Proposal A & B, please use the NPV function to calculate the PV of Future Cash Flows under a 10% and 18% discount rate. 20 points Note: All gray boxes should include an entry. Proposal A Discount Rate Proposal A Year 1 10% Cash Flow $ 150,000.00 Discount Rate Year 18% Cash Flow 1 $ 150,000.00 2 $ 125,000.00 2 $ 125,000.00 3 $ 75,000.00 3 $ 75,000.00 4 5 $ 37,500.00 4 $ 37,500.00 $ 25,000.00 5 $ 25,000.00 $ 12,500.00 6 $ 12,500.00 PV of Future Cash Flows Year 0 Investment Cost Net Present Value of Proposal A Proposal B Year 1 $ $344,209.99 (250,000.00) PV of Future Cash Flows Year 0 Investment Cost $297,439.22 Please see https://youtu.be/LKitww9sFM0f $ (250,000.00) $ Please see https://youtu.be/LKitww9sFM0 for an 94,209.99 example to calculate this cell. Net Present Value of Proposal A Proposal B $ 47,439.22 Cash Flow Year Cash Flow $ (37,500.00) 1 $ (37,500.00) 2 $ (25,000.00) 3 $ (12,500.00) 4 $ 212,500.00 5 $ 275,000.00 6 2 $ (25,000.00) 3 $ (12,500.00) 4 $ 212,500.00 5 $ 275,000.00 $ 338,760.00 Hint: This call should factor in the after-tax cash flows (per table) and after-tax consequence of loss PV of Future Cash Flows $ 338,760.00 Hint: This call should factor in the after-tax cash flows (per table) and after-tax consequence of loss $ 297,955.40 Please see https://youtu.be/LKitww9sFM0 PV of Future Cash Flows Year 0 Investment Cost Net Present Value of Proposal B $ $ 442,971.41 (312,500.00) Year 0 Investment Cost $ Please see https://youtu.be/LKltww9sFMO for an 130,471.41 example to calculate this cell. Net Present Value of Proposal B Instructions: Based on the NPV calculations above, which Project and Discount rate should be used? 2 points [Key essay answer here] $ (312,500.00) $ (14,544.60) Proposal A Proposal B Year 1 $ 2 $ 3 $ 4 $ 37,500.00 $137,500.00 5 6 $ 25,000.00 $162,500.00 $ 12,500.00 $ 175,000.00 Payback in Years 1.80 Cash Flow 150,000.00 $ (100,000.00) Payback Calculations Year Cash Flow 1 $ Payback Calculations (37,500.00) $ (350,000.00) 125,000.00 $25,000.00 2 $ (25,000.00) $ (375,000.00) 75,000.00 $100,000.00 3 $ (12,500.00) $(387,500.00) $ 212,500.00 $ (175,000.00) 5 6 $ 275,000.00 $ 100,000.00 $ 338,760.00 $ 438,760.00 Payback in Years 4.64 Instructions: Please set up the problem (in the gray area) and calculate the IRR for Proposal A and Proposal B. Please use the IRR function. Please refer to https://youtu.be/qAhV3xG0i8s for further guidance. 10 points Proposal A IRR Year Cash Flows 0 $ (250,000.00) 1 $ 150,000.00 2 $ 125,000.00 3 $ 75,000.00 4 $ 37,500.00 5 $ 25,000.00 6 $ 12,500.00 IRR for Proposal A 29% Proposal B IRR Year IRR for Proposal B Cash Flows 0 $ (312,500.00) 1 $ (37,500.00) 2 $ (25,000.00) 3 $ (12,500.00) 4 $ 212,500.00 5 $ 275,000.00 6 $ 338,760.00 17% Instructions: According to your analysis (NPV, IRR and Payback Period) which proposal(s) should be accepted assuming a discount rate of 10%? Would your answer change if the discount rate was 18% Explain. 10 points [Key essay answer here]