July 5, 2007 by Joe Ponzio

Tim wanted a little clarification on how I came up with a value for JNJ’s future cash of $201.7 billion using Excel^{®} (See the full valuation here). The following is a semi-intermediate discussion for Excel^{®} users.

*All numbers in $ Millions unless otherwise specified*

Enter the Free Cash Flow (FCF) for JNJ from 1997 through 2006 into Excel^{®}. Then, have Excel^{®} calculate the growth rate for various timeframes (I use 5- and 7-year periods to get medium and longer-term performance):

- 1997-2004
- 1998-2005
- 1999-2006
- 1997-2002
- 1998-2003
- 1999-2004
- 2000-2005
- 2001-2006

Doing that, you’ll be able to see how JNJ performed in various economic conditions and situations for an entire decade. Then, you simply get the median (=MEDIAN(values)) of those values-the median will give you the *typical* growth rate taking into account any overly high or low values.

In JNJ’s case, the median growth rate is 16.1%-and that is the basis for the next step.

Based on the past ten years of steady, consistent growth, we can reasonably expect JNJ to grow FCF at 16.1% for the next ten. After that, to be conservative, we project that JNJ will grow FCF at 5% from years 11-20.

Tell Excel^{®} to grow the FCF at 16.1%, using $11,582 in 2006 as your starting value. 2007 FCF would be $13,444; 2008 would be $15,605-and so on through 2016 (ten years). Then, tell Excel^{®} to grow it at 5% from 2017-2026. You should now have a list of the projected FCF for the next twenty years.

(Once you set up your spreadsheet, this will all happen automatically.)

You can’t just buy that future cash at face value-you’d end up with a 0% return. You have to buy that cash at a discount. For example: we estimate that JNJ’s FCF in 2015 will be $44,304; so, we have to figure out what price to pay today to earn 15% a year on our money and end up with $44,304 in 2015. In Excel^{®}, there are two ways to do this:

- Use the Present Value formula for each year: =PV (15%, 2015-2006, 0, -$44,304). Then, add up all twenty years of data;
- Use the Net Present Value formula for the entire series of cash flow data-from 2007-2026-and let Excel
^{®}do all of the leg work: =NPV (15%, [FCF from 2007-2026]).

However you choose to do it, you end up with $201,680-the price you could pay today to get 15% on your money assuming the future cash is as we project it. Of course, you aren’t *just* buying future cash-the company has a net worth of $39,318 in 2006 and you are buying your share of that as well. $201,680 + $39,318 = $240,998. That figure is in millions-translate it to billions and you end up with $241 billion.

What if the 2007 free cash flow isn’t exactly $13,444? No problem-we have to look at businesses over various timeframes and not sweat over the actual year-to-year performance. Think about it: **Does it make sense to analyze ten years’ worth of data in 5- and 7-year blocks, project twenty years into the future, and then worry about the daily stock price or one-year performance**?

Relax. You’re not gambling in stocks anymore-you are buying businesses. It is a whole different world.

This section is for comments from F Wall Street visitors. Do not assume that Joe Ponzio agrees with or otherwise endorses any particular comment just because it appears or remains on this website.

Before implementing the Facebook comment system below, visitors would register and post comments directly on F Wall Street. This article had 23 comments which you can view here.