Interpreting a DCF model

In addition to financial models built around the three different major financial statements, we'll often to be asked to build financial models around stock price data and the associated financials. Now, two types of models that we might be asked to build are the DDM, or Dividend Discount Model, and the DCF model, or Discounted Cashflow Model. I'm in the 04_04_Begin Excel spreadsheet. Now what we have here is stock price data for Microsoft, along with different financial metrics for the firm, including various multiples and the firm's financials over the last few years. We want to figure out the intrinsic value of Microsoft stock. To do that, we'll use both a DDM and a DCF. 

To begin with, with the Dividend Discount Model, we're going to assume a payout ratio of 50%. So 50% of the firm's net income will be paid out as a dividend. We look at net income over the last 12 months and divide by the number of shares outstanding, then multiply by the payout ratio. In order to determine the value of the stock based on this, we'll need to pick a discount rate and a growth rate. Perhaps that should 11%, 13%, whatever we feel is appropriate, given the riskiness of Microsoft stock. To come up with our DDM figure, we are going to take the dividend that we expect to be paid out, multiply that by one plus our growth rate, and then divide the whole thing by the discount rate minus our growth rate. And in this case, the DDM indicates Microsoft should be valued at about $84.23. We could adjust this up or down based on the discount rate in question, but notice, small changes in the discount rate make a big change in the value of the stock. 

So how about the DCF? Maybe this will give us a more precise estimate. Well, we've gone through and projected the free cashflows over the next few years for Microsoft based on its historical net income. In order to get our DCF, in this case, we are going to use the NPV formula, or Net Present Value formula. This starts by picking a discount rate. We'll use 11% in this case. Ideally, we'd want it to be consistent between the two different models, but for simplicity, we'll use 11. Now we're going to go through and pick each of the values for the cash that can be paid out to us as shareholders. So in this case, that'll start initially with the net cash on the balance sheet, plus the free cashflows in each of the next few years, and then finally, the terminal value for the firm. Now notice, we've used the NPV formula here, because we have cashflows each and every year in equal increments. If we didn't have those increments, we've have to use the X NPV function. So when we get done, we'll have a discounted cashflow valuation on the firm as a whole; it's $895,000. We have one more thing left to do. We need to divide by the number of shares outstanding. When we do that, we get a DCF model price of $116.65. How is that compared to where Microsoft is trading at this point in time? Well, when the financial model was produced, Microsoft was trading at about $114.37. Pretty close to accurate, right? This would indicate that Microsoft was slightly undervalued based on these particular assumptions. Now you're all set to go through and build your own financial models around stock prices given financial data.