Have I landed on a wrong blog by mistake? Why the heck we are talking about finance on a supply chain blog?

If you are thinking that, consider this harsh fact – supply chain is a cost center. Supply chain doesn’t sell anything. Heck, supply chain folks are often found in the alleyways of 5-star hotels screaming at the transporter in the choicest words, while inside sales guys are receiving performance awards for delivering their sales targets.

But jokes apart, any supply chain initiative must be backed by financials unless you want it to be shot-down in the crib. No one is going to give you money for extravagant warehouse robots  unless it makes for a solid business sense.

And to do that one must be aware of basic financial modeling and concepts. Let us illustrate it through an example.

Problem

Your organization is contemplating buying a new fancy line of warehouse automation systems. This includes an automated picking and a conveyer system. Though all your simulations show an improved performance of your warehouses, the system is expensive and your CEO has asked you to prepare a business case.

Sensing your cost consciousness, the vendor Buy-It-Already-Sucker (BIAS) Pvt. Ltd. has offered you three business options:

While the first two options are straight forward, the 3^rd option is “Rent & Buy” which means that you’d be paying $ 250,000 per annum and by the end of six years, you’ll own the asset. Additionally, there is a 4^th hidden option too (and default option for procrastinators): Maintain Status Quo, i.e. don’t do anything.

Now with these 4 options in your hand, your next objective is to:

One – Prepare a business case with financials that will convince all the stakeholders

Two – Select the most profitable option

On the face of it, renting cost seems high but to buy the asset, we’ve to pay $ 1 million right now, which might not be as easy. However, option #3 looks quite attractive where we’ve to pay same as rental but only for 6 years and then the asset will be ours. But NEVER FALL IN THIS TRAP of making an important decision on intuition without doing the numbers.

So how can you compare these options? We’ll use a technique called Net Present Value (NPV) or Net Present Worth (NPW) calculation. If you are already familiar with this concept, you can skip the next section.

NPV Calculation Basics

Would you rather have $10 dollar today or $10 dollar tomorrow?

Of course, you’d rather have it today. “A dollar today is worth more than the same dollar tomorrow.” And this is the basic fact around which the whole financial world revolves. If you expect people to accept money later you’ll have to pay a little bit extra, which is called interest.

Let’s say that you agree to pay someone $11 tomorrow in exchange for their $ 10 today. This extra $1 is the interest and you just paid 10% ($1/$10) as the rate of interest.

Similarly, $ 12 tomorrow is worth...$ 12/(1+rate of interest) = $ 10.9 today. This $10.9 is Net Present Value of $ 12 of tomorrow.

Comparing the above two examples, using the rate of interest as 10%, now let me ask you a slightly modified question - Would you rather have $ 10 today or $ 12 tomorrow?

Now, you’d choose $ 12 tomorrow, since we know that Net Present Value of $ 12 is $ 10.9.

Using this logic we can calculate the NPV of all the future cash flows to compare the possible options.

NPV Model

To build a NPV model, we’ll go through five simple steps:

Step 1: List all future inflows and outflows

In a strict sense, productivity savings and damage reduction are not inflows. However, relative to our base case of "do-nothing" (option 4), which we are assuming to have a zero NPV, these are cash inflows for the organization.

Step 2: Estimated value against all inflows and outflows

Pro-Tip: It’s always useful to keep input values dynamic in the model. This is because things change, and you don’t want to recreate the whole model when one of input changes. E.g. you may realize that life is asset is not 10 years but 2 years.

We highly recommend 13 Tips and Tricks for Excel modeling for more such Pro-Tips.

Step 3: Identify other external factors (e.g. inflation)

There can be scores of other external factors that impact your future financials. Prime example is inflation. For our current example, we estimate that inflation is going to remain at 5% and it will impact maintenance, operational costs, and reduction in value of damages (not because inflation impacts the damages directly but because we assume value of damage products to increase @inflation)

Step 4: Estimate Cost of Capital (interest value)

Organizations receive money from several sources, shareholders, banks etc. They expect a decent rate of return to compensate for the risk they are undertaking. We assume this to be 18%  for our example.

Step 5:  Bring all the cash flows to today’s value (NPV) and compare different options

As you can see below, we’ve shown cash flows for the first 6 years for the buy option. the light red  shaded cells show outflows and light green are inflows. The gray cells show the net.

Download the complete excel model here.

Few other things that you can observe above:

- Maintenance is free for first 5 years as given in the BIAL quote. It’s, however, applicable from sixth year onwards

- Maintenance and operational cost increase @rate of inflation

 RESULTS AND CONCLUSION

If you haven’t, download the entire model here.

Are you surprised with the results? I was.

After doing a detailed analysis, we find out that Option 1 “Buy” has a positive NPV of about $ 42k. Even better is Option 2 “Rent” that has a NPV of $ 112k. Hence, we should go ahead for option 2 and RENT the conveyer system.

Interestingly, our initial hunch for Option 3 “Rent & Buy” was gravely wrong which is not only worse than Buying or Renting, but has a negative NPV of $ 56 k. It demonstrates how these decisions shouldn’t be done on intuition but on the basis of logic.

We hope that this article enhanced your understanding of NPV and it’s role in decision making. Feel free to play with the excel workbook. Tweak the inputs and see how that changes the NPV of different options. Found an interesting combination of inputs that gives surprising results? Share with everyone in comments.