Posts Tagged ‘Cost Containment’

Calculating the Correct Net Present Value using Excel

Monday, June 20th, 2011

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



System Reformers of the Health Care Industry

Tuesday, December 8th, 2009

cost cuttingI just finished a reading an article titled “The “Third School” for Controlling Health Care Costs?”[1] and I found it very exciting to read. In it, he talks about “System Reformers” that once were focused on improving Quality and now are focused on Quality and controlling costs. I have worked in manufacturing for years and our Quality Organizations were just that focused on “Improving Quality”. In today’s world that has changed for the better with the coming of Six Sigma a process focused on improving quality and reducing costs. I believe these reformers Altman talks about are the same.

I know some people do not like the words Six Sigma and that is because what they were told was Six Sigma did not work. There maybe several definition out there but the one I know that is working is Six Sigma is a 5 step process based on facts and data focused on customer value to grow the business. Six Sigma Belts are change agents/ System Reformers trying to create value for the customer/ patient by reducing costs (which speaks to management) and improving value (Quality). Even in manufacturing Quality Improvement never got a high priority until Quality Professionals started talking money.

I agree with Altman real cost containment and control never really comes from outside the box through regulations by the “Regulators” nor from competing health care plans and informed consumers per the “Marketeers”. It has to come from inside the box through what he calls the “System Reformer”. The True Reformer/ Change agent will be focused on creating value for all stakeholders (Stockholders, Employees/ Care Givers, and Customers/ Patients). They have to create value, NOT costs, and it can be done one area/ company at a time. The big issue will be working these change across different organizations. Again, though, manufacturing did this by working with its suppliers and customers to help them apply the same to their groups.

I believe that all of the serious questions he mentions about the System Reformers success can be addressed. Will the System Reformer approach be successful? I would answer YES! At least to all that embrace its approach. The results, from these companies, will drive others to do the same. That is how Six Sigma became as successful as it has. At first many company did not embraces Six Sigma but with time and successes at their competition, many now do. And a lot of those are in the Fortune 500.

I see the System Reformer as the only true way to get cost under control.

Peter Bersbach

Six Sigma Master Black Belt

Bersbach Consulting

From Process to Profits


[1] Drew Altman, PhD, “The “Third School” for Controlling Health Care Costs?”, The Henry J. Kaiser Family Foundation , Oct 29 2009,