Question

The company has been growing steadily over the past 5 years, and the financials and future prospects look good. Your CEO has asked you to run the numbers. After doing some digging into the business, you have gathered information on the following:

• The estimated purchase price for the equipment required to move the operation in-house would be $750,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 $35,000 per year starting in year 0 and through all years of the project to support production as raw materials will be required in year o and all years to run the new equipment and produce components to replace those purchased from the vendor..

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

• 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 the end of the project (the last year of generated cash flow) for $50,000. (i.e. the terminal value).

Question:

As part of your research, you have sought input from a number of stakeholders. Each has raised important points to consider in your analysis and recommendation. Some of the points and assumptions are purely financial. Others touch on additional concerns and opportunities.

Question:

Stanley from Sales is convinced that this capability would create a new revenue stream that could significantly offset operating expenses. He recommends savings that grow each year: 5-year project lif e, 10% discount rate, and a 10% annual savings growth in years 2 through 5. In other words, instead of assuming savings stay flat, assume that they will grow by 10%% in year 2, and then grow another10%overyear2inyear3,andsoon. Stanleyfeelsthatthestatedterminalvalueis reasonable and used it in his calculations.

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):

• Nominal Payback

• Discounted Payback

• Net Present Value

• Internal Rate of Return

Answer 1

From the above question we take out the following information and arrange it in the following way:

	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Purchase price	-750000
Net Working Capital	-35000	-35000	-35000	-35000	-35000	-35000
Cost Reduction benefit		240000	240000	240000	240000	240000
Salvage Value of Equipment						50000
Net Inflow/Outflow	-785000	205000	205000	205000	205000	255000
-ve is outflow
+ve is inflow
Cumulative Cash Flows	-785000	-580000	-375000	-170000	35000	290000
Discounted Cash Flows	-785000	186363.6	169421.5	154019.5	140017.8	158334.9
Discounted Cumulative CF	-785000	-598636	-429215	-275195	-135178	23157.35
Discount Rate	10%

a. Nominal Payback Period = Full Years until recovery + (Unrecovered Cost at the beginning of                                                                                                Recovery year / CF during the recovery Year)

                          = 3 + (170000 / 205000)

                         = 3.83 years

b. In Discounted cash flow the formula above remains same but here we use the values of the Discounted Cumulative Cash flows.

= 4 + (135178 / 255000)

= 4.53 years

c. Net Present Value (NPV) = Present Value of Inflows – Present Value of Outflows

PV Inflows =

                  = $808,157

PV Outflows = $785,000.35

NPV = $808,157.35 - $785,000

        = $23,157.35

IRR

Since you have not mentioned to solve the question on Excel which makes IRR Solving matter of seconds but since it’s not the case, we will do it via Trial and Error Method.

IRR is the rate at which PV Inflows = PV Outflows , that is NPV = 0

At 10% rate we get a positive NPV of $23157.23

So we need to discount the Inflows at a rate greater than 10%.

Let’s take 12%

PV Inflows =

                  = $ 767,350.46

NPV = $767,350.46- $785,000

         = -$17,649.54

So now we see that we get a negative NPV, we now know that the IRR lies between 10% and 12%. Now we will use interpolation (formula given) to get the IRR value.

Interpolated IRR=

= 11.1349 %

Remember that this is the closest we can get the IRR value. Excel and Financial Calculators give more accurate and exact answers. But the difference will be very less.

As per Excel calculation the IRR is 11.1146%

If you need the Excel formula for the same please comment below. Thank you