Question

Perform a Financial Analysis for a project XY.

Assume the projected costs and benefits for this project are spread over five years as follows:

•   Estimated costs are $225,000 in Year 1, $50,000 in Year 2 , 52,500 in Year 3, 55,000 in Year 4 and 57,500 in Year 5
•   Estimated benefits are $0 in Year 1 and $182,500 each year in Years 2, 3, 4 and 5
•   Use a 9 percent discount rate, and round the discount factors to two decimal places.

Create a spreadsheet to calculate and clearly display the following:

•   NPV
•   ROI
•   The year in which payback occurs.

In addition, write a paragraph explaining whether you would recommend investing in this project, based on your financial analysis

Answer 1

1)

a) Calculate the net present value:

Net present value is nothing but net off of the present value of cash inflows and outflows by discounting the flows at a specified rate.

Year	Estimated benefits	Estimated costs	Net Cash Flow	Present value @ 9%	Present value
1	-	2,25,000	-2,25,000	1	-2,25,000
2	1,82,500	50,000	1,32,500	0.92	1,21,900
3	1,82,500	52,500	1,30,000	0.84	1,09,200
4	1,82,500	55,000	1,27,500	0.77	98,175
5	1,82,500	57,500	1,25,000	0.71	88,750
				NPV	1,93,025

NPV is 1,93,025

b) Calculate the ROI:

ROI = Net Profit / Total Investment

Year	Estimated benefits	Estimated costs
1	-	2,25,000
2	1,82,500	50,000
3	1,82,500	52,500
4	1,82,500	55,000
5	1,82,500	57,500
Total	7,30,000	4,40,000

Net Profit = Estimated benefits - Estimated costs

=   7,30,000 - 4,40,000

= 2,90,000

ROI = Net Profit / Total Investment

= (290000 / 4,40,000) *100

= 66%

c) Calculate the year when payback occurs:

Year	Estimated benefits	Estimated costs	Net Cash Flow	Accumulated CF
1	-	2,25,000	-2,25,000	-2,25,000
2	1,82,500	50,000	1,32,500	-92,500
3	1,82,500	52,500	1,30,000	37,500
4	1,82,500	55,000	1,27,500	1,65,000
5	1,82,500	57,500	1,25,000	2,90,000

Payback will occurs in year 3.

2)

Since the NPV is positive, the project would potentially generate the profit if the expected cash inflows and outflows are accurate. Thus, the project should be accepted regardless the fact that the payback period occurs at the year 3 of the project.