Problem #2 :

In the Excel template provided, there is data provided that a managerial accountant is analyzing for Agra Inc. The task is to analyze the data and make a recommendation to management as to which of the four projects they should choose.

Estimated income and net cash flow is given for five years as well as the required rate of return and minimum cash payback period desired by management.

1. Using the template calculate the cash payback period, annual rate of return, present value of the cash flows, net present value (NPV) and profitability index for each project.

2. Using your calculated data, recommend a project to management and explain the basis for it.

AutoSave OFF HES ACC 204 Projec... Home Insert Draw Tell me Share Q Comments A % Conditional Formatting Format as Table Clipboard Font Alignment Number Cells Cell Styles A1 fx Company Standards: Cash payback period 3 year maximum A B D E F 1 Company Standards: Cash payback period 3 year maximum 2 Required Rate of Return: 12% Year 3 Proposal A: 4 Investment $ 450,000 5 6 Income from Operations Net Cash Flow 1 $ 30,000 $ 120,000 2 30,000 120,000 3 20,000 110,000 4 10,000 100,000 5 (30,000) 60,000 $ 60,000 $ 510,000 7 8 9 10 $ 200,000 1 $ 2 3 11 Proposal B: 12 13 14 15 16 17 18 Proposal C: 19 60,000 $ 40,000 20,000 (10,000) (20,000) 90,000 $ 100,000 80,000 60,000 30,000 20,000 290,000 4 5 $ $ 320,000 1 $ 2 3 20 21 22 23 36,000 $ 26,000 26,000 16,000 16,000 120,000 $ 100,000 90,000 90,000 80,000 80,000 440,000 4 5 $ $ 540,000 1 $ 2 24 25 Proposal D: 26 27 28 29 3 92,000 $ 72,000 52,000 12,000 (8,000) 220,000 $ 200,000 180,000 160,000 120,000 100,000 760,000 4 5 30 $ 31 32 Cash Payback Period Annual Rate of Present Value of Cash Return Flows Profitability Net Present Value lindex 33 Proposal 34 A 35 B 36 C ACC 204 Project Sheet2 Sheet3 + Average: 90139.72222 Count: 92 Sum: 6490060