0 1 Complete this spreadsheet and submit it to the D2L dropbox. Use formulas to complete all cells that require a calculation. Type in narrative answers in the shaded textboxes for the growth analysis part of the assignment, and in the Answer and Justification sections of the competitive analysis section of the assignment. 2 3 4 NAME SOLUTION S 6 7 Microsoft Corporation (MSFT) 8 9 Income Statement for fiscal year ended June/30/xxxx 0 ($-millions except per share data) 2016 2017 2018 2019 2020 2021 Sales 2 Cost of goods sold 3 Depreciation and amortization 84,695 89,395 0,175 125,502 143,015 168,088 26,833 26,354 29,016 31,310 33,778 41,332 5,947 7,855 9,954 11,600 12,300 10,900 A Gross profit 51,915 55,186 71,205 82,592 96,937 115,856 5 Selling, general, and administrative expense 31,248 30,718 36,332 39,974 43,978 45,940 Operating income (EBIT) 20,667 24,463 34,873 42,618 52,959 69,916 7 Non-operating income 2,202 4,165 4,568 3,159 2,545 2,488 8 Non-operating expense 1,875 3,262 234 -597 -123 -1,044 Interest expense 1,243 2,222 2,733 2,686 2,591 2,346 3 Pretax income 19,751 23,149 36,474 43,688 53,036 71,102 Income tax expense 2,953 1,945 19,903 4,448 8,755 9,831 2 Net income 16,798 21,204 16,571 39,240 44,281 61,271 35 Balance Sheet on June/30/xxxxx 36 ($-millions except per share data) 37 Cash 2016 2017 2018 2019 6,510 7,663 11,946 38 Short-term investments 106,730 125,318 121,822 11,356 122,463 2020 13,576 122,951 2021 14,224 116,110 39 Accounts receivables 40 Inventories 18,277 19,792 26,481 29,524 32,011 38,043 2,251 2,181 2,662 2,063 1,895 2,636 41 Other current assets 5,892 4,897 6,751 10,146 11,482 13,393 42 Current assets 139,660 159,851 169,662 175,552 181,915 184,406 43 Property, plant, and equipment - net 18,356 23,734 36,146 43,856 52,904 70,803 44 Long-term Investments 10,431 6,023 3,662 4,849 5,677 9,427 45 Intangible assets 21,605 45,228 43,736 49,776 50,389 57,511 46 Other assets 3,642 47 Total assets 193,694 6,250 241,086 5,642 258,848 12,523 10,426 11,632 286,556 301,311 333,779 48 49 Short-term debt and current portion of long-term debt 12,904 10,234 5,573 7,348 5,905 10,825 50 Accounts payable 6,898 7,390 8,617 9,382 12,530 15,163 51 Other current liabilities 39,555 46,903 44,298 52,690 53,875 62,669 52 Current liabilities 59,357 64,527 58,488 69,420 72,310 88,657 53 Long-term debt 62,340 104,165 117,642 114,806 110,697 103,134 54 Total liabilities 121,697 168,692 176,130 184,226 183,007 191,791 55 Total shareholder equity 71,997 72,394 82,718 102,330 118,304 141,988 56 Total liabilities and shareholder equity 193,694 241,086 258,848 286,556 301,311 333,779 57 58 59 Other Data: 2016 2017 2018 2019 2020 2021 60 WACC 5.60% 5.60% 5.60% 5.60% 5.60% 5.60% 61 Tax rate 15.0% 8.4% 54.6% 10.2% 16.5% 13.8% 62 Stock price 51.17 68.93 98.61 133.96 203.51 270.90 63 Shares common stock outstanding (millions) 7,808 7,708 7,677 7,643 7,571 7,519 64 Total dividends ($-millions) 11,244 12,024 12,897 14,063 15,445 16,843 65 67 68 1. Compute Microsoft's actual sales growth for 2017 through 2021 69 70 Actual Sales Growth Rate (%) 71 72 2. Compute Microsoft's sustainable growth rate for 2017 through 2021. 73 74 Net Profit Margin 75 Total Asset Turnover 76 Equity Multiplier 77 Retention Ratio Please use formulas to complete the blue shaded cells Type in your answers to all other questions in the textboxes below each question. 78 Sustainable Growth Rate 79 80 3. Between 2017-2021 Microsoft was not in a state of balanced growth. What kind of growth problem did Microsoft have during the majority of this period and what issue or problem would this have created for the company? 81 82 83 84 85 86 87 38 KR 89 4. How could Microsoft achieve balanced growth? Complete the following table, showing whether each ratio should be increased or decreased to bring sustainable growth in line 90 with actual growth. In the final column, briefly discuss (i.e. a sentence is sufficient) a possible action that Lowe's could take to change the ratio in the desired direction. 91 92 93 94 95 4A. Net Profit Margin 96 97 98 99 100 4B. Total Asset Turnover 101 102 103 104 105 4C. Equity Multiplier 106 107 108 09 10 4D. Dividend Payout Ratio 11 12 13 14 15 To achieve balanced growth, should the ratio be increased or decreased? What action could bring about the desired change in the ratio? perger what kind of firm should it look for in terms of the growth rate of the target firm? 18 19 20 21 22 23 15 13 14 45 16 5. If Microsoft's management was to consider a merger, what kind of firm should it look for in terms of the growth rate of the target firm? 17 24 25 Life-Cycle & Competitive Analysis: For this part of the assignment, provide your answers below. 26 For each of the following questions, provide one reason to justify your selection/