Worksheet AD 717

/in /by

Name: ___________________________

When trying to determine the intrinsic value of a company, analysts use past financial data and assumptions about its future growth. One method of analysis is the so-called discounted free cash flow analysis. Using this worksheet, you will create a DCF valuation of Microsoft (MSFT).

For your analysis, consider the annual financial report of Microsoft, which is available in full on the website of the SEC as form 10-K. It contains the company’s income statement, balance sheet, and cash flow statement. Unless specified differently, compute results for the years 2017 and 2018.

Use an Excel spreadsheet to keep track of your results.

  1. The gross profit of a company is defined as its revenue minus the cost to generate that revenue. Find Microsoft’s gross profit, in US dollars and as a percentage.









  2. In order to keep Microsoft running and growing, the company incurs costs for research and development, sales and marketing, and other general administrative purposes. Once these costs are deducted from the gross profit, we find the operating profit. This result is important for our discounted cash flow analysis. What is Microsoft’s operating profit, in US dollars and as a percentage?










  3. Identify the tax rate at which Microsoft’s earnings, that is, what is left of its revenue after cost of revenue, operating expenses, and interest expenses, were taxed.
  • To calculate the free cash flow of Microsoft, we need to calculate how much of operating income (not EBIT) is left over after taxes. Compute this number.








  • As time passes, assets on the company’s balance sheet lose their value, which is charged as depreciation and amortization, depending on whether the assets are tangible or intangible. To account for this, companies take a non-cash charge on their income statement. While this reduces the tax burden of the company, it does not reduce the free cash flow. Using the cash flow statement, find the non-cash charge taken for depreciation and amortization (D&A).








  • We start from the net operating profit after taxes; then we introduce the contribution of D&A. Explain if the value for D&A contributes positively or negatively to free cash flow that we derive from the net operating profit after taxes.









  • When considering the balance sheet of Microsoft, we see that a distinction is made between current assets and non-current assets, and likewise for liabilities. Explain why this distinction is important.
  • The non-cash working capital of a company is defined as the difference between its current operating assets and its current operating liabilities, and it can be thought of as a measure of the liquidity to operate the business. That’s why we exclude things like cash or short-term debt – they are financial and not operational.

Identify current assets and current liabilities on the balance sheet and calculate Microsoft’s change in non-cash working capital from 2017 to 2018.















  • While negative working capital means that liabilities due within the next twelve months exceed the assets available in the same period of time, it need not be a bad sign. The same is true for changes in non-cash working capital.
    From a practical perspective, Microsoft needs to finance this shortcoming somehow. Explain if increasing the non-cash working capital increases or decreases the cash flow.













  • Companies use their funds to add new tangible assets or upgrade existing ones. Capital expenditure or capex summarize the amount of money spent for these purposes. Identify Microsoft’s capex.
  1. We have now assembled the most important building blocks for our discounted cash flow analysis: Operating income after taxes, depreciation and amortization, changes in the working capital, and capital expenditure. What was the free cash flow to the firm for Microsoft in the fiscal year that ended June 30, 2018?
  1. Assuming no growth and a discount rate of 8%, calculate the present value of all cash flows from 2019 to 2023.











  2. Obviously, the no-growth assumption is not realistic. Revenues and earnings change over time. We have to estimate these changes. Include parameters for growth in your model and update your calculations.








  3. What happens after the year 2023?