Excel Exercise #3 Cost of Capital Due: Sunday, November 21, 10 pm For this assignment you will calculate the cost of capital for as Microsoft as of October 31, 2021, and use that to evaluate a new project that it is (hypothetically) considering. 1. Considerations Microsoft is considering the sale of X-glasses, which give Xbox gamers a significant new way to interact with games. It has been developing the product in secret for the past 3 years. Because of the secrecy, the company did no market research, but based on competitor sales, e.g., Oculus Quest 2 glasses, Microsoft believes it could be an instant success and a significant new product line. The product team believes first-year sales would be 5 million units, which would grow at an annual rate of 50% over five years. The price per unit would be $375 in the first year and would increase each year by 7% as features, games, and functionality are added. To make the X-glasses, Microsoft would need to purchase a nearby manufacturing site at a cost of $350 million and invest $1.6 billion in new equipment. The site is expected to appreciate at 3.5% each year and the equipment will be straight-line depreciated over five years, after which the equipment will be worthless. The per unit manufacturing costs are estimated at $300 in the first year and would increase each year by 9%. Net working capital To make the X-glasses, Microsoft would need to purchase a nearby manufacturing site at a cost of $350 million and invest $1.6 billion in new equipment. The site is expected to appreciate at 3.5% each year and the equipment will be straight-line depreciated over five years, after which the equipment will be worthless. The per unit manufacturing costs are estimated at $300 in the first year and would increase each year by 9%. Net working capital needs to be maintained at 6% of sales, starting in year zero (based on year 1 sales). At the end of 5 years the glasses will be retired and replaced with a different product line. Operations will shut entirely. Microsoft currently has $67.7 Billion of debt on the balance sheet paying a 3.4% annual coupon on average. The debt is rated at AA+ which sells to yield 1.82% at current rates. Microsoft's market value of equity of $2.53 Trillion. The current yield on the 10-year treasury is 1.59%. Assume a market risk premium of 8.5%. Microsoft pays a corporate tax rate of 21%. II. Estimate Microsoft's weighted average cost of capital (WACC) In the worksheet given to you, calculate the Microsoft's WACC given the above information and the three years of monthly closing prices for Microsoft and the S&P500 (the market portfolio). To do this, estimate beta (using the slope formula in excel). You will first need to convert the prices to returns. Use the capital asset pricing model (CAPM) to calculate the firm's equity cost of capital (Re). Then use Microsoft's capital structure to estimate it's WACC. In the spreadsheet, using only formulas in the green cells (i.e., calculations you must perform). In the yellow cells, list the inputs you will needs for your formulas. Format the cells according to their proper units (percent, fraction, dollars, etc.) Fill in all cells highlighted yellow and green. Q1. What is Microsoft's cost of equity compared to its cost of debt? Q2. According to what you have learned in class, does Microsoft have an optimal capital structure? Explain. . III. Build pro forma cash flows In the excel spreadsheet, and like the second case study assigned to you, follow the step-by- step method of calculating free cash flow showing: Revenues and costs EBIDTDA and depreciation EBIT and taxes Net income Operating cash flows Free cash flows . . . . Q1. What is the project NPV and IRR? Should you take it? Q2. What is the minimum sales growth required to achieve a zero NPV (hint: change the sales growth rate until NPV=0). Turn in: An excel file with two worksheets. Use the excel file that I give you as a template. There is no need to change any of the row or column headings, but you must properly fill and format all values and formulas. Yellow cells are for you input. Green cells are for your output (answers). Formulas should contain only references to other cells. Format each cell so that dollar values rounded to the nearest dollar and percentages have no more than two decimal places. . Year 0 Unit sales Revenue/unit Revenue cost/unit less operating cost EBITDA less depreciation EBIT less taxes (21%) Operating Profit add back deprecation from cost of capital spreasheet OCE NWC Change in NWC Equipment Building less capital expenditures FCF NPV IRR growth at which NPV=O Monthly Returns MSFT SP500 Beta (Slope) Rf MRP Toal Debt (D) MV Equity (E) tc Rd Re Equity Ratio Debt Ratio Wacc Q2 answer Monthly Prices Date MSFT SP500 9/30/2018 103.03 2711.74 ######### 106.96 2760.17 12/1/2018 98.40 2506.85 1/1/2019 101.17 2704.1 2/1/2019 108.53 2784.49 3/1/2019 114.74 2834.4 3/31/2019 127.06 2945.83 4/30/2019 120.33 2752.06 5/31/2019 130.81 2941.76 6/30/2019 133.07 2980.38 7/31/2019 134.62 2926.46 8/31/2019 136.22 2976.74 9/30/2019 140.47 3037.56 ######### 148.32 3140.98 12/1/2019 155.03 3230.78 1/1/2020 167.35 3225.52 2/1/2020 159.27 2954.22 3/1/2020 155.47 2584.59 3/31/2020 176.66 2912.43 4/30/2020 180.64 3044.31 5/31/2020 201.17 3100.29 6/30/2020 202.66 3271.12 7/31/2020 222.94 3500.31 8/31/2020 208.42 3363 9/30/2020 200.63 3269.96 ######### 212.12 3621.63 12/1/2020 220.97 3756.07 1/1/2021 230.45 3714.24 2/1/2021 230.87 3811.15 3/1/2021 234.78 3972.89 3/31/2021 251.12 4181.17 4/30/2021 248.63 4204.11 5/31/2021 270.38 4297.5 6/30/2021 284.37 4395.26 7/31/2021 301.30 4522.68 8/31/2021 281.92 4307.54 9/30/2021 331.62 4605.38 Excel Exercise #3 Cost of Capital Due: Sunday, November 21, 10 pm For this assignment you will calculate the cost of capital for as Microsoft as of October 31, 2021, and use that to evaluate a new project that it is (hypothetically) considering. 1. Considerations Microsoft is considering the sale of X-glasses, which give Xbox gamers a significant new way to interact with games. It has been developing the product in secret for the past 3 years. Because of the secrecy, the company did no market research, but based on competitor sales, e.g., Oculus Quest 2 glasses, Microsoft believes it could be an instant success and a significant new product line. The product team believes first-year sales would be 5 million units, which would grow at an annual rate of 50% over five years. The price per unit would be $375 in the first year and would increase each year by 7% as features, games, and functionality are added. To make the X-glasses, Microsoft would need to purchase a nearby manufacturing site at a cost of $350 million and invest $1.6 billion in new equipment. The site is expected to appreciate at 3.5% each year and the equipment will be straight-line depreciated over five years, after which the equipment will be worthless. The per unit manufacturing costs are estimated at $300 in the first year and would increase each year by 9%. Net working capital To make the X-glasses, Microsoft would need to purchase a nearby manufacturing site at a cost of $350 million and invest $1.6 billion in new equipment. The site is expected to appreciate at 3.5% each year and the equipment will be straight-line depreciated over five years, after which the equipment will be worthless. The per unit manufacturing costs are estimated at $300 in the first year and would increase each year by 9%. Net working capital needs to be maintained at 6% of sales, starting in year zero (based on year 1 sales). At the end of 5 years the glasses will be retired and replaced with a different product line. Operations will shut entirely. Microsoft currently has $67.7 Billion of debt on the balance sheet paying a 3.4% annual coupon on average. The debt is rated at AA+ which sells to yield 1.82% at current rates. Microsoft's market value of equity of $2.53 Trillion. The current yield on the 10-year treasury is 1.59%. Assume a market risk premium of 8.5%. Microsoft pays a corporate tax rate of 21%. II. Estimate Microsoft's weighted average cost of capital (WACC) In the worksheet given to you, calculate the Microsoft's WACC given the above information and the three years of monthly closing prices for Microsoft and the S&P500 (the market portfolio). To do this, estimate beta (using the slope formula in excel). You will first need to convert the prices to returns. Use the capital asset pricing model (CAPM) to calculate the firm's equity cost of capital (Re). Then use Microsoft's capital structure to estimate it's WACC. In the spreadsheet, using only formulas in the green cells (i.e., calculations you must perform). In the yellow cells, list the inputs you will needs for your formulas. Format the cells according to their proper units (percent, fraction, dollars, etc.) Fill in all cells highlighted yellow and green. Q1. What is Microsoft's cost of equity compared to its cost of debt? Q2. According to what you have learned in class, does Microsoft have an optimal capital structure? Explain. . III. Build pro forma cash flows In the excel spreadsheet, and like the second case study assigned to you, follow the step-by- step method of calculating free cash flow showing: Revenues and costs EBIDTDA and depreciation EBIT and taxes Net income Operating cash flows Free cash flows . . . . Q1. What is the project NPV and IRR? Should you take it? Q2. What is the minimum sales growth required to achieve a zero NPV (hint: change the sales growth rate until NPV=0). Turn in: An excel file with two worksheets. Use the excel file that I give you as a template. There is no need to change any of the row or column headings, but you must properly fill and format all values and formulas. Yellow cells are for you input. Green cells are for your output (answers). Formulas should contain only references to other cells. Format each cell so that dollar values rounded to the nearest dollar and percentages have no more than two decimal places. . Year 0 Unit sales Revenue/unit Revenue cost/unit less operating cost EBITDA less depreciation EBIT less taxes (21%) Operating Profit add back deprecation from cost of capital spreasheet OCE NWC Change in NWC Equipment Building less capital expenditures FCF NPV IRR growth at which NPV=O Monthly Returns MSFT SP500 Beta (Slope) Rf MRP Toal Debt (D) MV Equity (E) tc Rd Re Equity Ratio Debt Ratio Wacc Q2 answer Monthly Prices Date MSFT SP500 9/30/2018 103.03 2711.74 ######### 106.96 2760.17 12/1/2018 98.40 2506.85 1/1/2019 101.17 2704.1 2/1/2019 108.53 2784.49 3/1/2019 114.74 2834.4 3/31/2019 127.06 2945.83 4/30/2019 120.33 2752.06 5/31/2019 130.81 2941.76 6/30/2019 133.07 2980.38 7/31/2019 134.62 2926.46 8/31/2019 136.22 2976.74 9/30/2019 140.47 3037.56 ######### 148.32 3140.98 12/1/2019 155.03 3230.78 1/1/2020 167.35 3225.52 2/1/2020 159.27 2954.22 3/1/2020 155.47 2584.59 3/31/2020 176.66 2912.43 4/30/2020 180.64 3044.31 5/31/2020 201.17 3100.29 6/30/2020 202.66 3271.12 7/31/2020 222.94 3500.31 8/31/2020 208.42 3363 9/30/2020 200.63 3269.96 ######### 212.12 3621.63 12/1/2020 220.97 3756.07 1/1/2021 230.45 3714.24 2/1/2021 230.87 3811.15 3/1/2021 234.78 3972.89 3/31/2021 251.12 4181.17 4/30/2021 248.63 4204.11 5/31/2021 270.38 4297.5 6/30/2021 284.37 4395.26 7/31/2021 301.30 4522.68 8/31/2021 281.92 4307.54 9/30/2021 331.62 4605.38