*Figure 1 Part of Microsoft’s Income statements over last 5 years, as is available on GlobeInvestor Gold. Other databases you could use are those of Canadian Shareowner or Yahoo-Finance. Click on this figure to magnify.*

Our first goal is to create an investment cash flow and
price appreciation forecast. To do so, we need not only to know earnings
growth, dividend payments and share buybacks in the past, but we also need to
estimate how this data extends into the future, say over the coming five years. From
our earlier discussions it should be clear that the wider Microsoft’s moat
around its business is, the more predictable and reliable this forecast will
become.

About Microsoft’s moat you can all learn into today’s media
commentaries. As said on this blog many times, you will have to be critical of what you’re
being told and separate the hype from reality. Ask yourself, 'do you see
Microsoft being in business 100 years from now,?'; similar to companies like GE or Proctor & Gamble have done? That really is the type of company we’re looking for in our Dividend
Portfolio (one of our four portfolio strategies).
Here is a screen shot from Excel that shows how you could arrange
our 5 year data and our five year forecast.

*Figure 2. Earnings forecast - click on this figure to magnify.*

Column A3 to A12 shows the years from 5 years ago (years1-5) to 5 years into the future (years 6-10). Column B shows the fiscal year end date of the company who’s data we’re analyzing; Column C holds the EBIT and Column D the Net Earnings data for the corresponding years. So in our Microsoft example, Year 1-5 rows contain EBIT and Net Earnings from 2008 to 2012 as released by Microsoft. They are plotted in the adjacent graph versus time. Trend line analysis was used to determine slope and intercept for this earnings data shown in columns C and D, rows 15 and 16. The slope and intercept were used to extrapolate these earnings into the next 5 years (2013-2017) as shown in column A (years 6 to 10). The extrapolated earnings are shown in the earnings graph for 2013-2017 and are reported in Columns C and D, rows 8 to 10 (colored orange).

To estimate the stock price in year 10 (June 30, 2017) I
make the assumption that market emotions regarding Microsoft five years
from now are not really that different from today. So the P/E should be the same as
it averaged over the past 5 years. To calculate the average P/E over the last five years determine from historic stock
price data what Microsoft’s common share price was on June 30 for each of the
past 5 years (Column E) and determine the P/E for these dates (Column F) by dividing this price by the corresponding net earnings in Column D. Next
these P/Es are averaged in cell F13.

Using our earnings forecast for 2017 multiplied with the
average P/E, we can estimate that Microsoft’s share 2017 price will be around $39.09 (cell J7).
The dividends combined with this estimate of the potential share price 5 years
from now will let us construct the cash flow stream of our Microsoft investment for
the coming five years and thus estimate its intrinsic value. This we’ll do in
one of our near future blog posts.