In Six Sigma we are always wanting to be able to show a return on investment to management. Most of the time in Dollars. To do that we need to consider both the cost and the benefits the project will obtain. But costs and benefits do not happen in one lump sum. Usually they fluctuate over time. This is referred to as a cash flow or a cash flow stream. The best tool to look at both the cost and benefits to see what the return would be is calculation what is called “Net Present Value” (NPV).
Definition of Net Present Value (NPV): The difference between the present value of cash inflows and the present value of cash outflows. NPV is used in budgeting to analyze the profitability of an investment or project[1].
In Excel there is a function [NPV(rate,value1,value2, ...)] to calculate NPV, but this formula can be misleading if you do not understand what it is doing. The function help on this function explains it all be rarely do we read these unless we really don’t know what goes where. In this functions case most people need to read it. In the above formula the rate is applied to every value listed. That is OK but in many projects the first value is costs/benefits obtained before the end of the first year (or period of the rate) so the rate should not be applied. That why in many projects we list the first year as “year 0”. These cost/benefits should be added to the above formula thus eliminating the rate from being applied.
Example: Lets say you have a project that will cost you $10,000 this year and $2000 next year to implement, but next year you will see a benefit of $500 and the year after that 5,000, Then in the third year $10,000 benefit and in the last two years $15,000 each year in benefits. Lets also say management want to see a rate of return of 10% over a five year period. You want to know if you can meet that with these figures. Below is the table in Excel I would create to show these figures and calculate the NPV.
You can see that the formula calculating NPV has year 0 being added to the formula so the interest rate is not applied to calculating the present value of it this year (year 0 is the present). But year 1-5 we did have the rate applied as we what to make sure that the expected rate of return is met by year five. This show we have meet managements expectations because it is a positive number.
The reason for doing this is because not all investments have cost/benefits in year 0. Take for instance the purchase of a new piece of equipment that the purchase price is not paid for 12 months. In this case year 0 maybe to delivery and setup of the machine which is included in the cost of $12000. Let say on this example we have the same benefits and expected return. Here is the table and calculations for this one:
You can see that this can be big if you do it wrong, so make sure that you apply this formula correctly. In most, not all, Six Sigma projects you will use the first way. But if you make sure to include always a year 0 and put it in the formula even the lower table will be correct using “=NPV(D2,D4;D8) + D3”.
Well there you have what to look out for in using the NPV formula in Excel. If you have questions or comments please feel free to contact me by leaving a comment below, emailing me, calling me, or leaving a comment on my website.
Bersbach Consulting
Peter Bersbach
Six Sigma Master Black Belt
http://sixsigmatrainingconsulting.com
peter@bersbach.com
1.520.829.0090
[1] http://www.investopedia.com/terms/n/npv.asp#axzz1PrAMAMip
Tags: cost benefit, Cost Containment, Leadership, measurement, Method, Six Sigma Success