Question

Calculate Microsoft's 2019 and 2020 financial ratios in all the five categories (Liquidity: New Working Capital, Current Ratio, and Quick Ratio. Efficiency: Total Asset Turnover, Fixed Asset Turnover, Inventory Turnover, Average Age of Inventory, Accounts Receivable Turnover, Average Collection Period, and Operating Cycle. Leverage: Debt Ratio, Interest Earned Ratio. Profitability: Net Profit Margin, Return on Asset, and Return on Equity. Market: Price Earning Ratio, Earning per Share, Dividend per Share, and Dividend Payout Ratio). A spreadsheet such as EXCEL is useful. Please show calculations.

INCOME STATEMENT

(In millions, except per share amounts)
Year Ended June 30,	2020		2019		2018
Revenue:
Product	$68,041		$66,069		$64,497
Service and other	74,974		59,774		45,863
Total revenue	143,015		125,843		110,360
Cost of revenue:
Product	16,017		16,273		15,420
Service and other	30,061		26,637		22,933
Total cost of revenue	46,078		42,910		38,353
Gross margin	96,937		82,933		72,007
Research and development	19,269		16,876		14,726
Sales and marketing	19,598		18,213		17,469
General and administrative	5,111		4,885		4,754
Operating income	52,959		42,959		35,058
Other income, net	77		729		1,416
Income before income taxes	53,036		43,688		36,474
Provision for income taxes	8,755		4,448		19,903
Net income	$44,281		$39,240		$16,571
Earnings per share:
Basic	$5.82		$5.11		$2.15
Diluted	$5.76		$5.06		$2.13
Weighted average shares outstanding:
Basic	7,610		7,673		7,700
Diluted	7,683		7,753		7,794

BALANCE SHEET

(In millions)
June 30,	2020		2019
Assets
Current assets:
Cash and cash equivalents	$13,576		$11,356
Short-term investments	122,951		122,463
Total cash, cash equivalents, and short-term investments	136,527		133,819
Accounts receivable, net of allowance for doubtful accounts of $788 and $411	32,011		29,524
Inventories	1,895		2,063
Other current assets	11,482		10,146
Total current assets	181,915		175,552
Property and equipment, net of accumulated depreciation of $43,197 and $35,330	44,151		36,477
Operating lease right-of-use assets	8,753		7,379
Equity investments	2,965		2,649
Goodwill	43,351		42,026
Intangible assets, net	7,038		7,750
Other long-term assets	13,138		14,723
Total assets	$301,311		$286,556
Liabilities and stockholders’ equity
Current liabilities:
Accounts payable	$12,530		$9,382
Current portion of long-term debt	3,749		5,516
Accrued compensation	7,874		6,830
Short-term income taxes	2,130		5,665
Short-term unearned revenue	36,000		32,676
Other current liabilities	10,027		9,351
Total current liabilities	72,310		69,420
Long-term debt	59,578		66,662
Long-term income taxes	29,432		29,612
Long-term unearned revenue	3,180		4,530
Deferred income taxes	204		233
Operating lease liabilities	7,671		6,188
Other long-term liabilities	10,632		7,581
Total liabilities	183,007		184,226
Commitments and contingencies
Stockholders’ equity:
Common stock and paid-in capital – shares authorized 24,000; outstanding 7,571 and 7,643	80,552		78,520
Retained earnings	34,566		24,150
Accumulated other comprehensive income (loss)	3,186		(340)
Total stockholders’ equity	118,304		102,330
Total liabilities and stockholders’ equity	$301,311		$286,556

Answer 1

Attached are images of two excel sheets, one with the solution and the other showcasing the formulas used.