Assignment 3: Chapter 3: Financial Analysis; Debt/Leverage, Dividends, & the DuPont Identity Using the Financial Statements for Merck (MRK) and Pfizer that are provided complete the following problems. Complete this assignment using an Excel spreadsheet. Please show your work (if using Excel, make sure the formulas, not just the numbers are in the cells). Merck & Company (MRK) 1. Debt and Leverage: Calculate the total debt ratio, debt to equity ratio, equity multiplier, and times interest earned ratio for each of the last five years. 2. Analyze the debt/leverage trend (by individual ratio and in total) and determine whether the asset efficiency has been volatile, improved, declined, or stayed the same during this period. 3. Dividend Policy: Calculate the dividend per share amount, dividend yield, payout ratio, and retention ratio for each of the last five years. 4. Analyze the dividend ratio trends and determine the trend (by individual ratio and in total) and determine whether the asset efficiency has been volatile, improved, declined, or stayed the same during this period. Does the company appear to have a specific dividend strategy? If yes, what is it? 5. DuPont Identity: Calculate the DuPont Identity and the ratio components for each of the last five years. 6. Analyze the changes from year to year and determine what factors had the most significant effects on the change in return on equity each year. Pfizer (PFE) 7. Debt and Leverage: Calculate the total debt ratio, debt to equity ratio, equity multiplier, and times interest earned ratio for each of the last five years. 8. Analyze the debt/leverage trend (by individual ratio and in total) and determine whether the asset efficiency has been volatile, improved, declined, or stayed the same during this period. 9. Dividend Policy: Calculate the dividend per share amount, dividend yield, payout ratio, and retention ratio for each of the last five years. 10. Analyze the dividend ratio trends and determine the trend (by individual ratio and in total) and determine whether the asset efficiency has been volatile, improved, declined, or stayed the same during this period. Does the company appear to have a specific dividend strategy? If yes, what is it? 11. DuPont Identity: Calculate the DuPont Identity and ratio components for each of the last five years. 12. Analyze the changes from year to year and determine what factors had the most significant effects on the change in return on equity each year.

Merck & Co., Inc. (MRK)
Summary Balance Sheet
As of December 31st, 2008, 2009, 2010, 2011 and 2012
($000,000)
		2009		2010		2011		2012		2013
Assets
Current Assets
	Cash & Investments	9,604	8.6%	12,201	11.5%	14,972	14.2%	16,141	15.2%	17,486	16.6%
	Accounts Receivable	6,603	5.9%	10,995	10.4%	11,955	11.4%	12,181	11.5%	11,973	11.3%
	Inventory	8,055	7.2%	5,868	5.5%	6,254	5.9%	6,535	6.2%	6,226	5.9%
	Other Current Assets	4,166	3.7%	-	0.0%	-	0.0%	-	0.0%	-	0.0%
Total Current Assets		28,428	25.4%	29,064	27.5%	33,181	31.6%	34,857	32.8%	35,685	33.8%
Net Fixed Assets		18,274	16.3%	17,082	16.1%	16,297	15.5%	16,030	15.1%	14,973
Other Assets
	Long Term Investments	432	0.4%	2,175	2.1%	3,458	3.3%	7,305	6.9%	9,770	9.2%
	Goodwill	11,923	10.6%	12,378	11.7%	12,155	11.6%	12,134	11.4%	12,301	11.6%
	Intangible Assets	47,656	42.5%	39,456	37.3%	34,302	32.6%	29,083	27.4%	23,801	22.5%
	Other	5,376	4.8%	5,626	5.3%	5,735	5.5%	6,723	6.3%	9,115	8.6%
Total Other Assets		65,387	58.3%	59,635	56.4%	55,650	52.9%	55,245	52.1%	54,987	52.0%
Total Assets		112,089	100.0%	105,781	100.0%	105,128	100.0%	106,132	100.0%	105,645	100.0%
Liabilities & Equity
Current Liabilities
	Accounts Payable	11,691	10.4%	13,241	12.5%	14,255	13.6%	14,033	13.2%	13,347	12.6%
	Short/Current Long Term Debt	1,379	1.2%	2,400	2.3%	1,990	1.9%	4,315	4.1%	4,521	4.3%
	Other Current Liabilities	2,681	2.4%	0	0.0%	0	0.0%	0	0.0%	-	0.0%
Total Current Liabilities		15,751	14.1%	15,641	14.8%	16,245	15.5%	18,348	17.3%	17,868	16.9%
Long Term Liabilities
	Long Term Debt	16,075	14.3%	15,482	14.6%	15,525	14.8%	16,254	15.3%	20,539	19.4%
	Deferred Long Term Liabilities	18,772	16.7%	17,853	16.9%	16,415	15.6%	16,067	15.1%	14,912	14.1%
	Other Liabilities	0	0.0%	2,429	2.3%	2,426	2.3%	2,443	2.3%	2,561	2.4%
Total Long Term Liabilities		34,847	31.1%	35,764	33.8%	34,366	32.7%	34,764	32.8%	38,012	36.0%
Total Liabilities		50,598	45.1%	51,405	48.6%	50,611	48.1%	53,112	50.0%	55,880	52.9%
Stockholders' Equity
	Paid in Common Stock	1,781	1.6%	1,788	1.7%	1,788	1.7%	1,788	1.7%	1,788	1.7%
	Capital Surplus	39,683	35.4%	40,701	38.5%	40,663	38.7%	40,646	38.3%	40,508	38.3%
	Retained Earnings	41,405	36.9%	37,536	35.5%	38,990	37.1%	39,985	37.7%	39,257	37.2%
	Treasury Stock	(21,044)	-18.8%	(22,433)	-21.2%	(23,792)	-22.6%	(24,717)	-23.3%	(29,591)	-28.0%
	Other	(332)	-0.3%	(3,216)	-3.0%	(3,132)	-3.0%	(4,682)	-4.4%	(2,197)	-2.1%
Total Stockholders' Equity		61,493	54.9%	54,376	51.4%	54,517	51.9%	53,020	50.0%	49,765	47.1%
Total Liabilities & Equity		112,091	100.0%	105,781	100.0%	105,128	100.0%	106,132	100.0%	105,645	100.0%