1. I am providing an excel sheet with past balance sheet and the income statement of Coke and Pepsi. The name of the file is Assignment 3 Financial Analysis of Coke and Pepsi.xls. You have to do the following in the same excel sheet: a. Do the ratio analysis using the framework provided b. Prepare common-size statements in the framework provided c. Do the Du Pont System analysis of ROE

	Coca-Cola						Pepsi
	2012	2013	2014	2015	2016		2012	2013	2014	2015	2016
Sales	48.07	46.70	45.99	44.29	41.87		65.49	66.42	66.68	63.06	62.80
Cost of Revenue	18.92	18.27	17.89	17.48	16.47		31.29	31.24	30.88	28.38	28.21
Gross Profit	29.15	28.43	28.10	26.81	25.40		34.20	35.18	35.80	34.67	34.59
Operating Expenses	17.77	17.37	18.40	18.08	16.77		24.62	25.48	26.70	26.32	24.81
Operating Profit	11.38	11.00	9.70	8.73	8.63		9.58	9.70	9.10	8.35	9.79
Non-operating income	0.82	0.88	0.10	1.73	0.24		-0.38	0.10	0.57	0.06	0.11
EBIT	12.20	11.88	9.80	10.46	8.87		9.20	9.80	9.67	8.41	9.68
Interest	0.39	0.40	0.48	0.86	0.73		0.90	0.91	0.91	0.97	1.12
EBT	11.81	11.48	9.32	9.61	8.14		8.30	8.89	8.76	7.44	8.55
Taxes	2.79	2.90	1.46	2.24	1.61		2.12	2.15	2.25	1.94	2.22
Net Income	9.02	8.58	7.10	7.37	6.53		6.18	6.74	6.51	5.50	6.33
	2012	2013	2014	2015	2016		2012	2013	2014	2015	2016
ASSETS
Cash and Cash Equivalents	16.55	20.27	21.68	19.90	22.20		6.62	9.68	8.73	12.01	16.13
Accounts Receivables	4.76	4.87	4.47	3.94	3.86		7.04	6.95	6.65	6.44	5.71
Inventory	3.26	3.28	3.10	2.90	2.68		3.58	3.41	3.14	2.72	2.72
Other Current Assets	5.75	2.88	3.74	6.65	5.28		1.39	2.10	2.14	1.87	2.53
Total Current Assets	30.32	31.30	32.99	33.40	34.01		18.63	22.14	20.66	23.03	27.09
Net Fixed Assets	55.85	58.76	59.03	56.70	53.26		56.01	55.34	49.85	46.64	47.04
Total Assets	86.17	90.06	92.02	90.09	87.27		74.64	77.48	70.51	69.67	74.13
LIABILITIES +EQUITY
Accounts Payable	9.15	9.89	9.63	9.99	2.68		12.27	12.53	13.02	13.51	6.16
Short-term debt	17.87	17.93	22.68	15.81	16.03		4.82	5.31	5.07	4.07	6.89
Other Current Liabilities	0.80	0.00	0.06	1.13	7.83		0.00	0.00	0.00	0.00	8.09
Total Current Liabilities	27.82	27.82	32.37	26.93	26.53		17.09	17.84	18.09	17.58	21.14
Long-term Debt	25.19	29.06	29.33	37.61	37.68		35.03	35.12	34.98	40.17	41.75
Total Liabilities	53.01	56.88	61.70	64.54	64.21		52.12	52.96	53.07	57.74	62.88
Common Stock-Treasury	-24.89	-28.22	-33.09	-39.46	-42.44		-20.64	-21.90	-31.51	-38.55	-41.27
Retained Earnings	58.05	61.66	63.41	65.02	65.50		43.16	46.42	49.09	50.47	52.52
Total Stockholder Equity	33.16	33.44	30.32	25.55	23.06		22.52	24.52	17.58	11.92	11.25
Total Liab + Total SH Equity	86.17	90.06	92.02	90.09	87.27		74.64	77.48	70.51	69.67	74.13
FINANCIAL ANALYSIS OF COCA COLA AND PEPSI
RATIO ANALYSIS
	2012	2013	2014	2015	2016		2012	2013	2014	2015	2016
Liquidity Ratios
Current Ratio	1.09	?	?	?	?		1.09	?	?	?	?
Quick Ratio	0.97	?	?	?	?		0.88	?	?	?	?
Asset Management Ratios
Inventory Turnover	14.75	?	?	?	?		18.29	?	?	?	?
Days Sales Outstanding	36.14	?	?	?	?		39.24	?	?	?	?
Fixed Assets Turnover	0.86	?	?	?	?		1.17	?	?	?	?
Total Assets Turnover	0.56	?	?	?	?		0.88	?	?	?	?
Debt Ratios
Debt Ratio/Leverage	0.62	?	?	?	?		0.70	?	?	?	?
Times Interest Earned	31.68	?	?	?	?		10.22	?	?	?	?
Profitability Ratios
Profit Margin	0.19	?	?	?	?		0.09	?	?	?	?
Basic Earning Power	0.14	?	?	?	?		0.12	?	?	?	?
Return-on-Assets	0.10	?	?	?	?		0.08	?	?	?	?
Return-on-Equity	0.27	?	?	?	?		0.27	?	?	?	?
Market Value Ratios
Year end Share Price	$36.25	$41.31	$42.22	$42.63	$41.46		$68.43	$82.94	$94.56	$99.21	$104.63
No. of shares (billions)	4.5	4.509	4.45	4.367	4.355		1.58	1.56	1.52	1.49	1.45
Earnings per share	2.00	?	?	?	?		3.91	?	?	?	?
Price-to-earnings	18.08	?	?	?	?		17.50	?	?	?	?
Price-to-cash flow
Market-to-Book	4.92	?	?	?	?		4.80	?	?	?	?
Du Pont System
PM	0.19	?	?	?	?		0.09	?	?	?	?
* TA turnover	0.56	?	?	?	?		0.88	?	?	?	?
* Equity Multiplier	2.60	?	?	?	?		3.31	?	?	?	?
= ROE	0.27	?	?	?	?		0.27	?	?	?	?
FINANCIAL ANALYSIS OF COCA COLA AND PEPSI
COMMON SIZE STATEMENTS
	2012	2013	2014	2015	2016		2012	2013	2014	2015	2016
Sales	100.00%	?	?	?	?		100.00%	?	?	?	?
Cost of Revenue	39.36%	?	?	?	?		47.78%	?	?	?	?
Gross Profit	60.64%	?	?	?	?		52.22%	?	?	?	?
Operating Expenses	36.97%	?	?	?	?		37.59%	?	?	?	?
Operating Profit	23.67%	?	?	?	?		14.63%	?	?	?	?
Non-operating income	1.70%	?	?	?	?		-0.58%	?	?	?	?
EBIT	25.37%	?	?	?	?		14.05%	?	?	?	?
Interest	0.80%	?	?	?	?		1.37%	?	?	?	?
EBT	24.57%	?	?	?	?		12.67%	?	?	?	?
Taxes	5.80%	?	?	?	?		3.24%	?	?	?	?
Net Income	18.76%	?	?	?	?		9.44%	?	?	?	?
	2012	2013	2014	2015	2016		2012	2013	2014	2015	2016
Assets
Cash and Cash Equivalents	19.21%	?	?	?	?		8.87%	?	?	?	?
Accounts Receivables	5.52%	?	?	?	?		9.43%	?	?	?	?
Inventory	3.78%	?	?	?	?		4.80%	?	?	?	?
Other Current Assets	6.67%	?	?	?	?		1.86%	?	?	?	?
Total Current Assets	35.19%	?	?	?	?		24.96%	?	?	?	?
Net Fixed Assets	64.81%	?	?	?	?		75.04%	?	?	?	?
Total Assets	100.00%	?	?	?	?		100.00%	?	?	?	?
Liab + Equity
Accounts Payable	10.62%	?	?	?	?		16.44%	?	?	?	?
Short-term debt	20.74%	?	?	?	?		6.46%	?	?	?	?
Other Current Liabilities	0.93%	?	?	?	?		0.00%	?	?	?	?
Total Current Liabilities	32.29%	?	?	?	?		22.90%	?	?	?	?
Long-term Debt	29.23%	?	?	?	?		46.93%	?	?	?	?
Total Liabilities	61.52%	?	?	?	?		69.83%	?	?	?	?
Common Stock	-28.88%	?	?	?	?		-27.65%	?	?	?	?
Retained Earnings and Capital Surplus	67.37%	?	?	?	?		57.82%	?	?	?	?
Total Stockholder Equity	38.48%	?	?	?	?		30.17%	?	?	?	?
Total Liab + Total SH Equity	100.00%	?	?	?	?		100.00%	?	?	?	?