All of the turmoil and volatility in the market these days makes understanding the value of a stock extremely important. While valuation is not an exact science (and can get fairly complex) a basic model using Free Cash Flow is useful to get a general idea of whether a stock is properly valued.
Getting Started
First off, you’ll need to understand the time value of money. Second, you’ll need to find the statement of cash flows for a company you want to value. I typically use Yahoo Finance for this, since it is easy to use. In addition, remember that it is important to be conservative when performing this exercise. That is, if a company has grown cash flow 10% every year, we want to assume they will grow cash flows at a much lower rate moving forward. Also, remember that all numbers we will use below are in thousands. So, if you see $1,000 this actually represents $1,000,000.
Step 1
Fire up Excel and let’s get started. You’ll probably want to set up your spreadsheet as shown below (click on the thumbnail). Note the positions of each cell (i.e., the upper table begins in B2). Also, make sure the numbers representing the years in each column remain formatted as numbers, as we will use them in our calculations.
Next, go to Yahoo Finance and type the symbol of the stock you want to value in the “Get Quotes” box (I’ll use JNJ). Scroll down and under Financials click on Balance Sheet. Make sure “Annual Data” is selected at the top. Under Stockholder’s Equity look for Common Stock (for JNJ it shows 3,120,000 for the end of 2006). Put this value in cell C10 of your spreadsheet, keeping in mind that this number is in thousands.
Now click on Cash Flow under Financials (again, make sure “Annual Data” appears at top). Take the most current Total Cash Flow From Operating Activities number (enter it into C11) as well as Capital Expenditures (enter it into C12). Then, in C13, subtract C12 from C11 to calculate current Free Cash Flow (FCF). FCF represents the actual cash the company has on hand to invest in new projects, pay out to investors as a dividend, or retain for the future. This is the number we are interested in because cash is a liquid asset that has immediate value.
Step 2
Now we need to estimate the rate of FCF growth in subsequent years, and forecast these amounts into the future. If I calculate FCF for the last 2 years, I see that JNJ grew about 3% year over year in ‘05 vs. ‘04, and 25% in ‘06 vs. ‘05. That would give us an average of about 14%. However, being conservative, let’s use 4% as the growth rate for each subsequent year and 3% as the perpetuity value (perpetuity value is just the present value of cash flows for all years beyond year 10 - unless you like calculus, let’s leave it at that).
Next, we need to choose a discount rate (if you need a refresher on present value, see my article on the time value of money). We’ll choose 9%, which is appropriate for a lower risk company such as JNJ (note that the riskier the company, the higher the discount rate) and enter this value in C4 through M4. At this point, your spreadsheet should look like the following:
Step 3
Next, calculate the FCF that JNJ will generate in each year as well as in perpetuity by applying a basic growth formula. For Year 1 FCF in cell C5 use =$C$13*(1+C3). However, for Year 2 in D5 use =C5*(1+D3). Next, copy your formula from D5 and paste it into E5 through L5. Finally, in M5 use =L5*(1+M3)/(M4-M3).
Now we need to calculate the present value of each of these cash flows. So, in C6 enter =C5/(1+C4)^C2. Now copy this formula in D6 through L6. Finally, in M6 use =M5/(1+M4)^L2. Almost there…just one more step. Here’s the table again below.
Step 4
In the final step, we simply sum cells C6 through M6 in cell C17 (this is the sum of the present value of FCF in each year). Then, in C18 we divide this by the current number of outstanding shares (in C10) to get an estimate of the value per share. Based on our estimates, we get a current valuation of $68.78 per share. At the time of this post, JNJ is trading at $67.75 which is within 1% of our valuation. Even though this is just an estimate, I would conclude that the company is about fairly valued. Here is a look at the final table:
1 Trackback(s)