Question

To please be done in excel: Based on a market survey (which cost $100 000), the probable demand for “Tab” in the first year has been estimated. New plant with a maximum annual capacity of 10 000 units can be purchased for $3.5 million. The plant has an estimated useful life for four years, at the end of which the residual value is expected to be $350 000. Variable production costs are estimated at $1 000 per unit, while additional fixed costs, based on a capacity of 10 000 units and before allowing for depreciation, are expected to amount to $250 per unit. These fixed costs will be avoidable if local manufacture does not take place. The “Tab” would sell for $1 700 each, some $500 less than the selling price of the cheap imported tablets currently sold by ABC. This would mean that the current sales of 7 000 units per annum of the cheap imported machines would cease. The contribution generated by sale of these imported machines amounts to $250 per unit. Existing fixed costs amount to $1,75 million per annum, and will still be incurred if local manufacture takes place. The new plant would be written off over 5 years on a straight-line basis for tax purposes. The corporate tax rate is 30% and ABC uses a discount rate of 18% for all projects of this type. There are no expected changes to the working capital of the business. Question: Assuming that all four years have the same net incremental cash flows as calculated, and taking into account any other relevant cash flows, show that the NPV, IRR and Payback Period of the project as a whole are: $274 805.27, 21.8% and 2.66 years respectively.

Answer 1

Answer:-

Year	0	1	2	3	4	4
Initial Investment	-3500000
Sales		17000000	17000000	17000000	17000000
Less:- Variable Cost		10000000	10000000	10000000	10000000
Less :- Fixed Cost		2500000	2500000	2500000	2500000
EBIT		4500000	4500000	4500000	4500000
Less:- Reduction of sales		-1750000	-1750000	-1750000	-1750000
Less:- Fixed Cost existing		-1750000	-1750000	-1750000	-1750000
Actual Profit		1000000	1000000	1000000	1000000
( Taxes ) @30%		-300000	-300000	-300000	-300000
EAT		700000	700000	700000	700000
New Dep		787500	787500	787500	787500
Salvage Value						350000
( Tax on Salvage )						-105000
TOTAL FREE CF	-3500000	1487500	1487500	1487500	1487500	245000
PVF @ 18%	1	0.8474576	0.7181844	0.6086309	0.5157889	0.5157889
Present Value	-3500000	1260593.2	1068299.3	905338.42	767235.95	126368.28
	NPV	=	627835.22

IRR :-

Year	Cash Flow	PVF@ 40%	PV	PVF@ 45%	PV
0	-3500000	1	-3500000	1	-3500000
1	1487500	0.714	1062500	0.689655	1025862
2	1487500	0.621	923913	0.475624	707491.1
3	1487500	0.540	803402.6	0.328017	487924.9
4	1487500	0.470	698611	0.226218	336499.9
4	245000	0.470	115065.3	0.226218	55423.52
	NPV		103492		-886799
	IRR	=	45 + (103492/990290.6)*5			=	45.523

PBP :-

Year	Cash Flow
0	-3500000
Year	Cash Flow	CF
1	1260593	1260593
2	1068299	2328893
3	905338.4	3234231
4	767236	4001467
5	126368.3	4127835
Now PBP =	1 + (3500000-1260593)/1068299			=	2.15	Years