Excel Homework 1 [10 points]

Benchmark case:

In June 2020, Microsoft had reported sales of $143.02 billion in fiscal year 2020.

You expect its sales to grow at a rate of 20% in 2021, at a rate of 18% in 2022, at a rate of 16% in 2023.

Thereafter, the growth will level off at its long-run growth rate of 3.4%. Use the following forecasts to estimate Microsofts free cash flows:

• Given the companys recent operating margin, you estimate that Earnings Before Interest and Taxes (EBIT) is 45% of sales every year.
• The depreciation expense is expected to be 6.5% of sales every year.
• Increases in net working capital requirements will be 2.5% of any increase in sales.
• Capital expenditures will be 10% of the sales every year.

The other assumptions:

• Tax rate is 16%.
• The firms WACC is 7%.
• Microsoft has $82.28 billion in debt, $130.26 billion in cash, and 7.51 billion shares outstanding in 2020.

1. By using the free cash flow model, what is the intrinsic value of Microsofts stock in June 2020? (Benchmark case) [4 points]

For the following questions, all assumptions remain the same as the benchmark case. The only change to the problem is given in each of the questions. And, please write down your student ID: ______.

2. Suppose the firms WACC is 9.8 (the last digit of your student ID/2) %. What is the intrinsic value of Microsofts stock in June 2020? [2 points]

3. Suppose the increase in net working capital will be 1.6 + (the last digit of your student ID/4) % of any increase in sales. What is the intrinsic value of Microsofts stock in June 2020? [2 points]

4. Suppose the firms long-run growth rate is 2.5 + (the last digit of your student ID/4) %. What is the intrinsic value of Microsofts stock in June 2020? [2 points]

Input EBIT as a percent of sales ANWC as a percent of Asales Depreciation as a percent of sales Capx as a percent of sales Long-term Growth Tax Rate WACC 0.45 0.025 0.065 0.1 The following numbers are in the billions. Debt 82.28 Cash 130.26 Shares Outstanding 7.51 0.034 0.16 0.07 2020 2021 2022 2023 143.02 20.00% 18.00% 16.00% Year FCF Forecast ($billion) Sales Growth From Prior Year EBIT Less: Income Tax Net Income Plus: Depreciation Less: Capital Expenditure ASales Less: Increase in NWC Free Cash Flow Terminal Value Enterprise Value Price per Share Input EBIT as a percent of sales ANWC as a percent of Asales Depreciation as a percent of sales Capx as a percent of sales Long-term Growth Tax Rate 0.45 0.025 0.065 The following numbers are in the billions. Debt 82.28 Cash 130.26 Shares Outstanding 7.51 0.1 0.034 0.16 WACC 2020 2021 2022 2023 143.02 20.00% 18.00% 16.00% Year FCF Forecast ($billion) Sales Growth From Prior Year EBIT Less: Income Tax Net Income Plus: Depreciation Less: Capital Expenditure ASales Less: Increase in NWC Free Cash Flow Terminal Value Enterprise Value Price per Share 0.45 The following numbers are in the billions. Debt 82.28 Cash 130.26 Shares Outstanding 7.51 Input EBIT as a percent of sales ANWC as a percent of Asales Depreciation as a percent of sales Capx as a percent of sales Long-term Growth Tax Rate WACC 0.065 0.1 0.034 0.16 0.07 2020 2021 2022 2023 143.02 20.00% 18.00% 16.00% Year FCF Forecast ($billion) Sales Growth From Prior Year EBIT Less: Income Net Income Plus: Depreciation Less: Capital Expenditure ASales Less: Increase in NWC Free Cash Flow Terminal Value Enterprise Value Price per Share Input EBIT as a percent of sales ANWC as a percent of Asales Depreciation as a percent of sales Capx as a percent of sales Long-term Growth Tax Rate WACC The following numbers are in the billions. Debt 82.28 Cash 130.26 Shares Outstanding 7.51 0.45 0.025 0.065 0.1 0.16 0.07 2020 2021 2022 2023 Year FCF Forecast ($billion) Sales Growth From Prior Year EBIT 143.02 20.00% 18.00% 16.00% Less: Income Tax Net Income Plus: Depreciation Less: Capital Expenditure ASales Less: Increase in NWC Free Cash Flow Terminal Value Enterprise Value Price per Share