Question

Problem Four :

Kaplan Incorporated currently uses an older model BAII plus milling machine that was

purchased two years ago for $10,000. The machine would be sold today for $3,000. If the old

machine is not replaced, it is expected to operate for another 4 years at which time its salvage

value will be $1,200. The BAII plus currently generates revenues of $13,000 per year. The

machine has annual operating costs of $8,000 per year. The company maintains an investment of

$1,750 in operating net working capital with the old machine.

Kaplan Incorporated is considering acquiring a new milling machine to replace its old milling

machine. The new milling machine has a capital cost of $12,000 and an estimated useful life of 4

years and an expected salvage value of $2,000. The company expects the new machine to

generate total revenues of $25,000 per year. The new machine would have operating costs of

$11,000 per year. With the new machine, the investment in operating net working capital would

be increased to $2,500 and remain at this level until the end of the project.

Both machines have a CCA rate of 30%. The company’s tax rate is 35%. The project has a

required rate of return of 15% per year compounded annually.

What is the NPV of the project, which replaces the old machine with the new machine?

Please show me how to do this question on excel showing all formulas. The answer was also provided as $9,694.16. However I want to know how the prof got to this answer.  

Answer 1

1) In case the machine is installed today, the cash outflow would be as follows:

Particulars	$
Cost	12000
Disposal of old machine (After Tax)(Working Note 1)	-1950
Net cash outflow	10050
Net working capital of old machine	-1750
Net working capital of new machine	2500
Total	10800

Formula used here is the sum formula in excel.

2) Now we will calculate the incremental cash inflow after tax to determine NPV of the project.

Cash inflow after tax of old machine (All amounts in $):

Year	Revenue	Operating cost excluding CCA	CCA(Working Note 2)	Net Revenue before tax	Tax	PAT	Cash flow after tax
1	13000	8000	1470	3530	1235.5	2294.5	3764.5
2	13000	8000	1029	3971	1389.85	2581.15	3610.15
3	13000	8000	720.3	4279.7	1497.895	2781.805	3502.105
4	13000	8000	504.21	4495.79	1573.527	2922.264	3426.4735

Cash inflow after tax of new machine (All amounts in $):

Year	Revenue	Operating cost excluding CCA	CCA	Net Revenue before tax	Tax	PAT	Cash flow after tax
1	25000	11000	3600	10400	3640	6760	10360
2	25000	11000	2520	11480	4018	7462	9982
3	25000	11000	1764	12236	4282.6	7953.4	9717.4
4	25000	11000	1234.8	12765.2	4467.82	8297.38	9532.18
4	2000*				700		1300

Incremental Cash Flow After Tax	^D.F.@15%	PVAF
6595.5	0.869565	5735.217
6371.85	0.756144	4818.034
6215.295	0.657516	4086.657
6105.7065	0.571753	3490.958
1300	0.571753	743.2792
Total		18874.15

Therefore, NPV of the project is $8074.15 (i.e. $18874.15-$10800)

*$2000 is the salvage value of the new machine at the end of year 4.

^D.F., i.e. Discounting Factor is calculated in excel by this formula: year 1: 1/1.15, year 2:1/(1.15^2), year 3:1/(1.15^3) & year 4: 1/(1.15^4)

Working Notes:

1] If we are to sell old machine today, the proceeds will be received after tax which will be calculated as $3000 - ($3000*0.35) which comes to $1950.

2]CCA of old & new machine:

(a) CCA of old machine:

Year	Opening BV ($)	Rate	CCA ($)	Closing BV($)
1	4900	30%	1470	3430
2	3430	30%	1029	2401
3	2401	30%	720.3	1680.7
4	1680.7	30%	504.21	1176.49

(b) CCA of new machine:

Year	Opening BV($)	Rate	CCA($)	Closing BV($)
1	12000	30%	3600	8400
2	8400	30%	2520	5880
3	5880	30%	1764	4116
4	4116	30%	1234.8	2881.2