vertical/ horizontal/ dollar analysis

Project.xlsx

Home >Education homework help >vertical/ horizontal/ dollar analysis

Instructions

Instructions:

Merck IS:

Vertical Analysis - compute the percentage change for 2023 and 2022 for each row through row 31 - Net Income Attributable to Merck & Co., Inc.

Horizontal Analysis - Dollars - compute the dollar amount change for 2023 and 2022 for each row through row 31 - Net Income Attributable to Merck & Co., Inc.

Horizontal Analysis - % change - compute the percentage change for 2023 and 2022 for each row through row 31 - Net Income Attributable to Merck & Co., Inc.

Merck BS:

Vertical Analysis - compute the percentage change for 2023 and 2022 for each row

Horizontal Analysis - Dollars - compute the dollar amount change for 2023 and 2022 for each row

Horizontal Analysis - % change - compute the percentage change for 2023 and 2022 for each row

Ratios:

Compute the ratios for 2023 and 2022

Show your work by either using formulas to link to the data or hard coding the formulas in each cell.

Merck IS

Created by EDGAR Online, Inc.

MERCK & CO., INC.
INCOME_STATEMENT
Form Type: 10-K
Period End: Dec 31, 2023
Date Filed: Feb 26, 2024
Table Of Contents

	Consolidated Statement of Income
	Merck & Co., Inc. and Subsidiaries
	Years Ended December 31
	($ in millions except per share amounts)				Vertical Analysis		Horizontal Analysis - Dollars		Horizontal Analysis - % Change
		2023	2022	2021	2023	2022	2023	2022	2023	2022
	Sales	$ 60,115	$ 59,283	$ 48,704
	Costs, Expenses and Other
	Cost of sales	16,126	17,411	13,626
	Selling, general and administrative	10,504	10,042	9,634
	Research and development	30,531	13,548	12,245
	Restructuring costs	599	337	661
	Other (income) expense, net	466	1,501	(1,341)
		58,226	42,839	34,825
	Income from Continuing Operations Before Taxes	1,889	16,444	13,879
	Taxes on Income from Continuing Operations	1,512	1,918	1,521
	Net Income from Continuing Operations	377	14,526	12,358
	Less: Net Income Attributable to Noncontrolling Interests	12	7	13
	Net Income from Continuing Operations Attributable to Merck &
	Co., Inc.	365	14,519	12,345
	Income from Discontinued Operations, Net of Taxes and Amounts
	Attributable to Noncontrolling Interests	- 0	- 0	704
	Net Income Attributable to Merck & Co., Inc.	$ 365	$ 14,519	$ 13,049
	Basic Earnings per Common Share Attributable to Merck & Co.,
	Inc. Common Shareholders
	Income from Continuing Operations	$ 0.14	$ 5.73	$ 4.88
	Income from Discontinued Operations	- 0	- 0	0.28
	Net Income	$ 0.14	$ 5.73	$ 5.16
	Earnings per Common Share Assuming Dilution Attributable to
	Merck & Co., Inc. Common Shareholders
	Income from Continuing Operations	$ 0.14	$ 5.71	$ 4.86
	Income from Discontinued Operations	- 0	- 0	0.28
	Net Income	$ 0.14	$ 5.71	$ 5.14
	Consolidated Statement of Comprehensive (Loss) Income
	Merck & Co., Inc. and Subsidiaries
	Years Ended December 31
	($ in millions)
		2023	2022	2021
	Net Income Attributable to Merck & Co., Inc.	$ 365	$ 14,519	$ 13,049
	Other Comprehensive (Loss) Income Net of Taxes:
	Net unrealized (loss) gain on derivatives, net of
	reclassifications	(97)	(71)	410
	Benefit plan net (loss) gain and prior service (cost) credit,
	net of amortization	(385)	335	1,769
	Cumulative translation adjustment	89	(603)	(423)
		(393)	(339)	1,756
	Comprehensive (Loss) Income Attributable to Merck & Co., Inc.	$ (28)	$ 14,180	$ 14,805

Merck BS

Created by EDGAR Online, Inc.

MERCK & CO., INC.
BALANCE_SHEET
Form Type: 10-K
Period End: Dec 31, 2023
Date Filed: Feb 26, 2024
Table Of Contents

	Table of C o ntent s
	Consolidated Balance Sheet
	Merck & Co., Inc. and Subsidiaries
	December 31
	($ in millions except per share amounts)				Vertical Analysis		Horizontal Analysis - Dollars		Horizontal Analysis - % Change
		2023	2022	2021	2023	2022	2023	2022	2023	2022
	Assets
	Current Assets
	Cash and cash equivalents	$ 6,841	$ 12,694	$ 8,096
	Short-term investments	252	498	0
	Accounts receivable (net of allowance for doubtful accounts of $88 in
	2023
	and $72 in 2022)	10,349	9,450	9,230
	Inventories (excludes inventories of $3,348 in 2023 and $2,938 in 2022
	classified in Other assets - see Note 8)	6,358	5,911	5,953
	Other current assets	8,368	7,169	6,987
	Total current assets	32,168	35,722	30,266
	Investments	252	1,015	370
	Property, Plant and Equipment (at cost)
	Land	326	295	326
	Buildings	14,966	13,166	12,529
	Machinery, equipment and office furnishings	17,763	16,760	16,303
	Construction in progress	8,262	9,186	8,313
		41,317	39,407	37,471
	Less: accumulated depreciation	18,266	17,985	18,192
		23,051	21,422	19,279
	Goodwill	21,197	21,204	21,264
	Other Intangibles, Net	18,011	20,269	22,933
	Other Assets	11,996	9,528	11,582
		$ 106,675	$ 109,160	$ 105,694
	Liabilities and Equity
	Current Liabilities
	Loans payable and current portion of long-term debt	$ 1,372	$ 1,946	$ 2,412
	Trade accounts payable	3,922	4,264	4,609
	Accrued and other current liabilities	15,766	14,159	13,859
	Income taxes payable	2,649	1,986	1,224
	Dividends payable	1,985	1,884	1,768
	Total current liabilities	25,694	24,239	23,872
	Long-Term Debt	33,683	28,745	30,690
	Deferred Income Taxes	871	1,795	3,441
	Other Noncurrent Liabilities	8,792	8,323	9,434
	Merck & Co., Inc. Stockholders' Equity
	Common stock, $0.50 par value
	Authorized - 6,500,000,000 shares
	Issued - 3,577,103,522 shares in 2023 and 2022	1,788	1,788	1,788
	Other paid-in capital	44,509	44,379	44,238
	Retained earnings	53,895	61,081	53,696
	Accumulated other comprehensive loss	(5,161)	(4,768)	(4,429)
		95,031	102,480	95,293
	Less treasury stock, at cost: 1,045,470,249 shares in 2023 and
	1,039,269,638 shares in 2022	57,450	56,489	57,109
	Total Merck & Co., Inc. stockholders' equity	37,581	45,991	38,184
	Noncontrolling Interests	54	67	73
	Total equity	37,635	46,058	38,257
		$ 106,675	$ 109,160	$ 105,694

Merck Eq

Created by EDGAR Online, Inc.

MERCK & CO., INC.
TABLE35
Form Type: 10-K
Period End: Dec 31, 2023
Date Filed: Feb 26, 2024
Table Of Contents

	Table of C o ntent s
	Consolidated Statement of Equity
	Merck & Co., Inc. and Subsidiaries
	Years Ended December 31
	($ in millions except per share amounts)
					Accumulated
			Other		Other		Non-
		Common	Paid-In	Retained	Comprehensive	Treasury	controlling
		Stock	Capital	Earnings	Loss	Stock	Interests	Total
	Balance January 1, 2021	$ 1,788	$ 39,588	$ 47,362	$ (6,634)	$ (56,787)	$ 87	$ 25,404
	Net income attributable to Merck & Co.,
	Inc.	- 0	- 0	13,049	- 0	- 0	- 0	13,049
	Other comprehensive income, net of
	taxes	- 0	- 0	- 0	1,756	- 0	- 0	1,756
	Cash dividends declared on common stock
	($2.64 per share)	- 0	- 0	(6,715)	- 0	- 0	- 0	(6,715)
	Treasury stock shares purchased	- 0	- 0	- 0	- 0	(840)	- 0	(840)
	Spin-off of Organon & Co.	- 0	4,643	- 0	449	- 0	(1)	5,091
	Net income attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	16	16
	Distributions attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	(29)	(29)
	Share-based compensation plans and
	other	- 0	7	- 0	- 0	518	- 0	525
	Balance December 31, 2021	1,788	44,238	53,696	(4,429)	(57,109)	73	38,257
	Net income attributable to Merck & Co.,
	Inc.	- 0	- 0	14,519	- 0	- 0	- 0	14,519
	Other comprehensive loss, net of taxes	- 0	- 0	- 0	(339)	- 0	- 0	(339)
	Cash dividends declared on common stock									2528
	($2.80 per share)	- 0	- 0	(7,134)	- 0	- 0	- 0	(7,134)	(2,547.86)	2538
	Net income attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	7	7
	Distributions attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	(13)	(13)
	Share-based compensation plans and
	other	- 0	141	- 0	- 0	620	- 0	761
	Balance December 31, 2022	1,788	44,379	61,081	(4,768)	(56,489)	67	46,058
	Net income attributable to Merck & Co.,
	Inc.	- 0	- 0	365	- 0	- 0	- 0	365
	Other comprehensive loss, net of taxes	- 0	- 0	- 0	(393)	- 0	- 0	(393)
	Cash dividends declared on common stock									2538
	($2.96 per share)	- 0	- 0	(7,551)	- 0	- 0	- 0	(7,551)	(2,551.01)	2532
	Treasury stock shares purchased	- 0	- 0	- 0	- 0	(1,346)	- 0	(1,346)
	Net income attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	12	12
	Distributions attributable to
	noncontrolling interests	- 0	- 0	- 0	- 0	- 0	(25)	(25)
	Share-based compensation plans and
	other	- 0	130	- 0	- 0	385	- 0	515
	Balance December 31, 2023	$ 1,788	$ 44,509	$ 53,895	$ (5,161)	$ (57,450)	$ 54	$ 37,635

Merck CF

Created by EDGAR Online, Inc.

MERCK & CO., INC.
CASH_FLOW
Form Type: 10-K
Period End: Dec 31, 2023
Date Filed: Feb 26, 2024
Table Of Contents

	Table of C o ntent s
	Consolidated Statement of Cash Flows
	Merck & Co., Inc. and Subsidiaries
	Years Ended December 31
	($ in millions)
		2023	2022	2021
	Cash Flows from Operating Activities of Continuing Operations
	Net income from continuing operations	$ 377	$ 14,526	$ 12,358
	Adjustments to reconcile net income from continuing operations
	to net cash provided by operating activities of continuing
	operations:
	Amortization	2,044	2,085	1,636
	Depreciation	1,828	1,824	1,578
	Intangible asset impairment charges	792	1,749	302
	(Income) loss from investments in equity securities, net	(340)	1,419	(1,940)
	Charge for the acquisition of Prometheus Biosciences, Inc.	10,217	- 0	- 0
	Charge for the acquisition of Imago BioSciences, Inc.	1,192	- 0	- 0
	Charge for the acquisition of Pandion Therapeutics, Inc.	- 0	- 0	1,556
	Deferred income taxes	(1,899)	(1,568)	187
	Share-based compensation	645	541	479
	Other	355	1,301	805
	Net changes in assets and liabilities:
	Accounts receivable	(1,148)	(644)	(2,033)
	Inventories	(816)	(161)	(674)
	Trade accounts payable	(380)	(289)	405
	Accrued and other current liabilities	1,783	(50)	277
	Income taxes payable	214	380	(540)
	Noncurrent liabilities	456	(545)	484
	Other	(2,314)	(1,473)	(1,758)
	Net Cash Provided by Operating Activities of Continuing
	Operations	13,006	19,095	13,122
	Cash Flows from Investing Activities of Continuing Operations
	Capital expenditures	(3,863)	(4,388)	(4,448)
	Purchases of securities and other investments	(955)	(1,204)	(1)
	Proceeds from sale of Seagen Inc. common stock	1,145	- 0	- 0
	Proceeds from sales of securities and other investments	1,658	721	1,026
	Acquisition of Prometheus Biosciences, Inc., net of cash
	acquired	(10,705)	- 0	- 0
	Acquisition of Imago BioSciences Inc., net of cash acquired	(1,327)	- 0	- 0
	Acquisition of Acceleron Pharma Inc., net of cash acquired	- 0	- 0	(11,174)
	Acquisition of Pandion Therapeutics, Inc., net of cash
	acquired	- 0	- 0	(1,554)
	Other acquisitions, net of cash acquired	- 0	(121)	(179)
	Other	(36)	32	(91)
	Net Cash Used in Investing Activities of Continuing Operations	(14,083)	(4,960)	(16,421)
	Cash Flows from Financing Activities of Continuing Operations
	Net change in short-term borrowings	- 0	- 0	(3,986)
	Payments on debt	(1,755)	(2,251)	(2,319)
	Proceeds from issuance of debt	5,939	- 0	7,936
	Distribution from Organon & Co.	- 0	- 0	9,000
	Purchases of treasury stock	(1,346)	- 0	(840)
	Dividends paid to stockholders	(7,445)	(7,012)	(6,610)
	Proceeds from exercise of stock options	125	384	202
	Other	(328)	(240)	(286)
	Net Cash (Used in) Provided by Financing Activities of
	Continuing Operations	(4,810)	(9,119)	3,097
	Cash Flows from Discontinued Operations
	Net cash provided by operating activities	- 0	- 0	987
	Net cash used in investing activities	- 0	- 0	(134)
	Net cash used in financing activities	- 0	- 0	(504)
	Net Cash Flows Provided by Discontinued Operations	- 0	- 0	349
	Effect of Exchange Rate Changes on Cash, Cash Equivalents and
	Restricted Cash	23	(410)	(133)
	Net (Decrease) Increase in Cash, Cash Equivalents and
	Restricted Cash	(5,864)	4,606	14
	Cash, Cash Equivalents and Restricted Cash at Beginning of
	Year (includes $79, $71 and $103 of restricted cash at January
	1, 2023, 2022 and 2021, respectively, included in Other
	current assets)	12,773	8,167	8,153
	Cash, Cash Equivalents and Restricted Cash at End of Year
	(includes $68, $79 and $71 of restricted cash at December 31,
	2023, 2022 and 2021, respectively, included in Other current
	assets)	$ 6,909	$ 12,773	$ 8,167

Ratios

REQUIRED RATIOS/METRICS	TYPE	FORMULA	Merck
			2023	2022	Comments
Working Capital	Liquidity	Current Assets -
		Current Liabilities
Current Ratio	Liquidity	Current Assets ÷
		Current Liabilities
Quick Ratio	Liquidity	Quick Assets ÷
		Current Liabilities
Accounts Receivable Turnover	Liquidity	Sales ÷
		Average Accounts Rec.
Number of Days' Sales in Receivables	Liquidity	Average Accounts Rec. ÷
		Average Daily Sales
Inventory Turnover	Liquidity	Cost of Goods Sold ÷
		Average Inventory
Number of Days' Sales in Inventory	Liquidity	Average Inventory ÷
		Average Daily COGS
Ratio of Fixed Assets to Long-Term Liabilities	Solvency	Fixed Assets (net) ÷
		Long-Term Liabilities
Ratio of Liabilities to Stockholders' Equity	Solvency	Total Liabilities ÷
		Total Stockholders' Equity
Times Interest Earned	Solvency	(Income Before Income
		Tax + Interest Expense) ÷
		Interest Expense
Asset Turnover		Sales ÷
		Average Total Assets
		(excl. long-term investments)
Return on Total Assets	Profitability	(Net Income + Interest Expense) ÷
		Average Total Assets
Return on Stockholders' Equity	Profitability	Net Income ÷
		Average Total Stockholders' Equity
Return on Common Stockholders' Equity	Profitability	(Net Income - Preferred Dividends) ÷
		Average Common Stockholders' Equity
Earnings Per Share on Common Stock-Basic	Profitability	(Net Income - Preferred Dividends) ÷
Price Earnings Ratio	Profitability	Market Price per Share of Common Stock ÷
Fiscal Year End Market Price Per Share		Earnings per Share on Common Stock	$ 109.02	$ 110.95	Stock price (year-end)
Dividends Per Share	Profitability	Dividends on Common Stock ÷
Dividend Yield	Profitability	Dividends per Share of Common Stock ÷
		Market Price per Share of Common Stock