Question

Comparable Analysis

Below is data on 6 different companies

Please answer the questions in yellow using an Excel Formula:

A B C F G H | J D American Airlines AAL E Archer Daniels Midland ADM Boeing ВА CVS CVS Revenues 2015 Revenues 2016 Revenues 2017 229 230 231 232 233 234 235 236 237 238 239 40,990 40,180 42,622 44,541 67,702 62,346 60,828 64,341 60,160 153,290 177,526 184,786 194,579 163,041 Electronic Arts Ralph Lauren EA RL 4,396 7,405 4,845 6,653 5,150 6,182 4,950 6,313 1,322 2,427 1,433 1,199 3,324 996 562 Revenues 2018 Cost of Revenues 2018 96,114 93,496 94,005 101,127 81,490 3,269 4,381 11,987 11,604 1,144 10,460 4,101 R&D 2018 SG&A 2018 EBIT 2018 41,885 2,656 1,884 2,336 1,845 Pretax Income 2018 583 2,060 245 27,517 4,021 1,406 2,002 (596) 2,088 1,079 60 240 Taxes 2018 472 152 Net Income 2018 1,815 1,019 431 241 242 1,412 186 Dividends Declared 2018 758 202 243

Answer must be written using an Excel Formula

Thank You

Answer 1

CAGR (Combined annual Growth Rate)= (Revenue at the end of T period/ Revenue for starting period)^ (1/ T) - 1

Gross Margin Ratio = Gross Profit / Sales

and all other ratios are also expressable as percentage on sales.

	AAL	ADM	BA	CVS	EA	RL
Revenue Growth (2017-18)	(44541-42622)/42622	(64341-60828)/60828	(101127-94005)/94005	(194579-184786)/184786	(4950-5150)/5150	(6313-6182)/6182
	4.50%	5.78%	7.58%	5.30%	-3.88%	2.12%
Revenue Gowth (CAGR) (2015-18)	2.10%	-1.26%	1.28%	6.14%	3.01%	-3.91%
Gross Margin	100.00%	6.50%	19.42%	16.21%	73.29%	61.56%
In your Excel sheet (D234-D235)/D234
R & D Percent	0.00%	0.00%	3.23%	0.00%	28.95%	0.00%
In your Excel sheet D236/D234
SG&A Percent	94.04%	3.63%	4.33%	14.14%	24.22%	52.65%
In your Excel sheet D237/D234
EBIT Margin %	5.96%	2.87%	11.85%	2.07%	20.12%	8.90%
In your Excel sheet D238/D234
Tax Rate	25.05%	11.89%	9.86%	142.39%	5.56%	26.07%
In your Excel sheet D240/D239
Net Margin	3.17%	2.82%	10.34%	-0.31%	20.59%	6.83%
In your Excel sheet D241/D234
Payout Ratio	13.17%	41.76%	39.21%	-350.34%	0.00%	46.87%
In your Excel sheet D242/D241