Question

1. Learning Objectives (a) Develop proforma Project Income Statement Using Excel Spreadsheet (b) Compute Net Project Cash flows, NPV, IRR and PayBack Period (c) Develop Problem-Solving and Critical Thinking Skills 1) Life Period of the Equipment = 4 years 8) Sales for first year (1) 200000 2) New equipment cost -200000 9) Sales increase per year 0.05 3) Equipment ship & install cost -35000 10) Operating cost: -120000 4) Related start up cost -5000 (60 Percent of Sales) -0.6 5) Inventory increase 25000 11) Depreciation (Straight Line)/YR -60000 6) Accounts Payable increase 5000 12) Tax rate 0.35 7) Equip. Salvage Value Estimated 15000 13) Cost of Capital (WACC) 0.1 End of Year 4 (fully depreciated )

Answer 1

(a) Proforma Project Income Statement Using Excel Spreadsheet

Particulars	Year 0	Year 1	Year 2	Year 3	Year 4
Sales
Costs
Depreciation
EBIT
Tax
Net income

(b) Net Project Cash flows, NPV, IRR and PayBack Period is calculated as follows,

i) Net Project Cash flows

Particulars	Year 0	Year 1	Year 2	Year 3	Year 4
Sales	-	200,000.00	210,000.00	220,500.00	231,525.00
Costs (60% of sales)	-	120,000.00	126,000.00	132,300.00	138,915.00
Depreciation	-	60,000.00	60,000.00	60,000.00	60,000.00
EBIT	-	20,000.00	24,000.00	28,200.00	32,610.00
Tax @35%	-	7,000.00	8,400.00	9,870.00	11,413.50
Net income	-	13,000.00	15,600.00	18,330.00	21,196.50
Depreciation	-	60,000.00	60,000.00	60,000.00	60,000.00
Initial investment
New equipment Cost	(200,000.00)	-	-	-	-
Equipment ship & install cost	(35,000.00)	-	-	-	-
Related start up cost	(5,000.00)	-	-	-	-
Change in Net Working Capital	(20,000.00)	-	-	-	20,000.00
After tax Salvage value	-	-	-	-	9,750.00
Free cash flows	(260,000.00)	73,000.00	75,600.00	78,330.00	110,946.50

ii)NPV

Net Present Value = Present value of Cash inflows - Present value of Cash outflows

Year	Cash Flows	PV factor @ 10%	PV of Cash Flows
0	(260,000.00)	1.000	(260,000.00)
1	73,000.00	0.909	66,363.64
2	75,600.00	0.826	62,479.34
3	78,330.00	0.751	58,850.49
4	110,946.50	0.683	75,777.95
NPV @ 10%	3,471.42

Net Present Value @ 10% =3,471.42

iii)IRR

IRR=L+((NPV_L/(NPV_L-NPV_H)*(H-L))

Where,

L means Lower discount rate taken

H means Higher discount rate taken

NPV_L means NPV at Lower discount rate taken

NPV_H means NPV at Higher discount rate taken

Year	Cash Flows	PV factor @10%	PV of Cash Flows	PV factor @15%	PV of Cash Flows
0	(260,000.00)	1.000	(260,000.00)	1.000	(260,000.00)
1	73,000.00	0.909	66,363.64	0.870	63,478.26
2	75,600.00	0.826	62,479.34	0.756	57,164.46
3	78,330.00	0.751	58,850.49	0.658	51,503.25
4	110,946.50	0.683	75,777.95	0.572	63,434.02
NPV @ 10%	3,471.42	NPV @ 15%	(24,420.01)

IRR=L+((NPV_L/(NPV_L-NPV_H)*(H-L))

IRR=10+((3,471.42/(3,471.42-(-24,420.01))*(15-10))

IRR=10.62%

iv)PayBack Period

Payback Period = A + (B/C)

Where,

A means Last period with a negative cumulative cash flow

B means Absolute value of cumulative cash flow at the end of the period A

C means cash flow during the period after A

Year	Cash flows	Cumulative CF
0	(260,000.00)	(260,000.00)
1	73,000.00	(187,000.00)
2	75,600.00	(111,400.00)
3	78,330.00	(33,070.00)
4	110,946.50	77,876.50

Payback Period = A + (B/C)

Payback Period =3 + (33,070/110,946.50)

Payback Period = 3.3 years

(c) Problem-Solving and Critical Thinking

A project is accepted when NPV is greater than zero and rejected when NPV is less than zero. Since NPV of this project is greater than zero i.e., positive NPV firm should accept this project.

If IRR of the project is greater than or equal to project’s cost of capital, then accept the project. However If IRR of the project is less than project’s cost of capital, then reject the project.IRR of the project is also greater than cost of capital of the firm. From point of IRR, the firm should accept this project.

Longer the payback period higher the risk. Shorterer the payback period lower the risk So project having shorter payback period is accepted.So also from point of payback period, the firm should accept this project.