Friday, December 16, 2011

Discounted Cash Flow Sample Excel for AAPL

Here is a simple sample two stage Discounted Cash Flow exercise for AAPL, using data from 2011-09-24. The yellow boxes require you to enter values from financial statements, which are currently filled with AAPL's data. The green boxes require you to make some kind of estimate. These estimates will be based on how aggressive or conservative you are. The white boxes either have formulas or hard text which do not need to be changed. You can download it at the end of the post.



I will go through all of the yellow and green boxes. The first section deals with how much money a company brings in. The numbers in the Income column are not used for calculations, but instead, are used to verify against free cash flow. The total income and total free cash should be in the vicinity of each other. If this is not the case, you need to figure out why. For AAPL, we see that free cash is higher than income. This is due to accrual accounting items like depreciation. You should get suspicious if income is higher than free cash.

I wanted to take the 4 most recent quarters and take the total of those to get the 1 year cash flow. If you are confused with why the Q4 Op Cash and Cap Ex are so much larger, it's because those numbers are cumulative (sum of Q1 to Q4). These were taken from google finance and I was too lazy to break it down. I'm not sure if there's a reason to since that's how it's reported in the statements. But the most important number is the 30,077 (30bn), which represents the how much AAPL has received from selling all of its goodies. I subtract the full value of capital expenditures, which is a conservative way to do it. Realistically, you need to estimate the portion of the cap ex which goes to keeping the business running and the portion attributed to expansion. This would lead to the Free Cash flow being higher. You can't do harm to yourself adding extra laters of safety by using a smaller cash flow number.

Next, is the Balance Sheet section. Cash is cash. For AAPL, I gave it a weight of 1, a dollar for dollar credit. I gave goodwill and intangibles a weight of 0.75, to give myself some room for error. The "Total minus above" is the total amount of assets aside from cash, goodwill, and intangibles. I get this total so that I can give it a weight also. I gave it a fairly conservative weight of 0.8. For liabilities, I gave it a full weight of 1. If I wanted to be even more conservative, I could assign it a higher number, like 1.10. The outstanding shares comes straight from the balance sheet. The final output, the Adjusted Shareholder Equity, is what the company is worth in terms of what it has an owes. The idea is to get an estimate of what you can fetch for the company in a liquidation sale. Giving a weight of 0 to both goodwill and intangibles will get exactly that, but it's not realistic for a company that is not about to be liquidated.

All of the numbers right now have been in millions, which I keep track of using the multiplier later. If you get this wrong, you'll get some extremely inaccurate outputs.

I've given AAPL a super conservative growth rate of 10%. Considering AAPL's shareholder equity went from 22bn to 76bn from 2008 to 2011, 10% is well, obviously wrong. But, it's better to be wrong on the safe side instead of the aggressive side.

The terminal growth rate is what you the income to grow every year by after AAPL cannot get any bigger. It is reasonable to assume that the terminal rate is the same as the rate of inflation, which is about 1.5% or so. For my conservative nature, I have it set to 0%, which lowers the valuation of AAPL.

You'll notice the Free Cash number for the chart is in green. You can choose to put in the value that was obtained in the income section, or you can lower it just to be safe. Based on the growth rate, the Free Cash column contains estimates of the cash flows for the next 10 years. Then, in the NPV column, we discount the Free Cash numbers to present value. The Cumulative NPV is just a running total of the previous NPV column. Finally the terminal value is the an annuity calculation which is then discounted back to present. The Total Value is the sum of the Cumulative NPV and the Terminal Value, giving us the total valuation for AAPL's income generating portion. Once again, you get to pick a number based on how aggressive/conservative you feel. If you think AAPL will still be growing for the next 10 years, you 376bn as your valuation. I felt that AAPL will be growing for another 5 years, so I picked 298bn.

The discount rate has many definitions, but in our case, it is our desired rate of return. As mentioned in other posts, the market returns around 8%-12% per year. To ask for anything less means you should just buy an index fund. I refuse to lower my discount rate below 15%, but you might see differently.

Finally, the multiplier is just how much you multiply all the numbers by. This is just to have less clutter on the screen. Most financial statements report their numbers this way as well.

The last section is what we're waiting for. Shareholder equity is from above. DCF Valuation is taken from the "Total Value" column in the DCF chart, based on how many years you think AAPL will continue to grow for. In this worksheet, I've picked the value from year 5. The fair market value of a company is the sum of its assets plus the valuation of its income generating ability.

With the estimates that I used, the worksheet shows that the intrinsic value of AAPL is 383.67. This part is very important to understand. Intrinsic value is based largely on what you feel is a reasonable rate of return, the discount rate. This is a very sensitive number. Current market price of AAPL right now is 380. Given how conservative I've been in order to obtain the 383, AAPL at 380 is a pretty safe bet.

You can download the worksheet as an excel file from google docs here:
Discounted Cash Flow Excel sheet

If there are any questions on how to use it, feel free to ask in the comments below!

3 comments:

  1. Could you update this analysis using AAPL's latest numbers? I'm curious as to what you are using for the 1-year totals in this case, as well as the DCF valuation estimate. Thanks!

    ReplyDelete
  2. Can you give a little explanation on the terminal value column? Where did you get the numbers from, and how did it progress as time passed?

    ReplyDelete
  3. The terminal value is obtained by using the dividend discount model. http://en.wikipedia.org/wiki/Dividend_discount_model I plug in the FCF as the dividend, and then get the net present value of the terminal value. The intrinsic value is the sum of the "Cumulative NPV" and "Terminal value". The rows signify the number of years of growth that you're trying to model. Notice that the Cumulative NPV increases as the Terminal Value decreases based on the number of years of growth.

    ReplyDelete