Question

Based on the following:

• The estimated purchase price for the equipment required to move the operation in-house would be $500,000. Additional net working capital to support production (in the form of cash used in Inventory, AR net of AP) would be needed in the amount of $25,000 per year starting in year 0 and through all 5 years of the project to support production.

• The current spending on this component (i.e. annual spend pool) is $875,000. The estimated cash flow savings of bringing the process in-house is 20% or annual savings of $175,000. This includes the additional labor and overhead costs required.

• Your company has access to a credit line and could borrow the funds at a rate of 6%.

• Finally, the equipment required is anticipated to have a somewhat short useful life, as a new wave of technology is on the horizon. Therefore, it is anticipated that the equipment will be sold after five years for $25,000. (i.e. the terminal value).

Your colleague from Accounting, recommends using the base assumptions above: 5-year project life, flat annual savings, and 10% discount rate. She does not feel the equipment will have any terminal value due to advancements in technology.

Using the data presented above (and ignoring the extraneous information), for this profit and supply chain improvement project, calculate each of the following (where applicable): Show Calculations

o  Nominal Payback

o  Discounted Payback

o  Net Present Value

o  Internal Rate of Return

Answer 1

Initial outlay

Equipment cost	$500,000
Working Capital	$25,000
Initial Outlay	$525,000

Discount Rate is 10%
Salvage Value = Nil


Calculation of NPV

	Year	CF	PV @ 10%	DCF
Equipment cost	0	-$500,000	1	-$500,000
Working Capital	0	-$25,000	1	-$25,000
		-$525,000
Year 1-5
Cost savings 175000+ working Capital 25000	1	$150,000	0.9091	$136,364
Cost savings 175000+ working Capital 25001	2	$150,000	0.8264	$123,967
Cost savings 175000+ working Capital 25002	3	$150,000	0.7513	$112,697
Cost savings 175000+ working Capital 25003	4	$150,000	0.6830	$102,452
Cost savings 175000+ working Capital 25004	5	$150,000	0.6209	$93,138
NPV				$43,618

Calculation of Payback period

Year	Cash Savings	cumulative Cash Flow
1	$150,000	$150,000
2	$150,000	$300,000
3	$150,000	$450,000
4	$150,000	$600,000
5	$150,000	$750,000

The initial outlay is 525000, the combined cash flow in year 4 is 600,000 and in year 3 is 450,000. by analysing this we know that payback period is between year 3 and 4

  

Payback period= 3 + (525000-450000)/(600000-450000) = 3+ 75000/15000

Payback period = 3.5 Years

Discounted Cash Back   

Year	Discounted Cash Savings	cumulative Discounted Cash Flow
1	$136,364	$136,364
2	$123,967	$260,331
3	$112,697	$373,028
4	$102,452	$475,480
5	$93,138	$568,618

The initial outlay is 525000, the combined cash flow in year 4 is $475,480 and in year 5 is $568,618. by analysing this we know that payback period is between year 4 and 5

Discounted Payback Period = 4.53 years

Calculation of IRR using Trial and Error Method
Since the NPV is positive, we know that rate of return on project is more than cost of capital, i.e 10% so lets calculated NPV of project at 12 and 15%

	Year	CF	PV @ 12%	DCF @ 12%	PV @ 15%	DCF @15%
Equipment cost	0	-$500,000	1.0000	-$500,000	1.0000	-$500,000
Working Capital	0	-$25,000	1.0000	-$25,000	1.0000	-$25,000
		-$525,000				$0
Year 1-5						$0
Cost savings 175000+ working Capital 25000	1	$150,000	0.8929	$133,929	0.8696	$130,435
Cost savings 175000+ working Capital 25001	2	$150,000	0.7972	$119,579	0.7561	$113,422
Cost savings 175000+ working Capital 25002	3	$150,000	0.7118	$106,767	0.6575	$98,627
Cost savings 175000+ working Capital 25003	4	$150,000	0.6355	$95,328	0.5718	$85,763
Cost savings 175000+ working Capital 25004	5	$150,000	0.5674	$85,114	0.4972	$74,577
NPV				$15,716		-$22,177

IRR = 13.26%
​​​​​​​(this IRR calculated using the trial and error approximation, it gives a good approximation but not 100% accurate, you would need a financial calculator or Excel to get the exact answer)

Part 2 :The solution is used using financial calculator

Step 1

Press CF button  

CF0 as -525000 press ENTER then down Button

CF1 as 150000 press ENTER then Down Button (annual savings 175000 - working capital cost 25000)

set F01 as 5 press ENTER

Step 2

Press NPV button

Set I as 10 press ENTER press DOWN button

NPV will appear on screen, now press CPT button you will get NPV of 43618

Press DOWN 2 times PB will appear on Screen Press CPT you will get PB of 3.5 years (Nominal payback period)

Press DOWN button DPB will appear on Screen Press CPT you will get DPB of 4.53 years (Discounted payback period)

Step 3

Calculation of IRR

Press IRR button , Press CPT you will get IRR of 13.147 (this is in percentage terms)