Question

Give calculation for Historical and competitors Ratio Analysis.

FIN611 Excel Project

American Airlines Group, Inc

1 Skyview Drive, Fort Worth, TX 76155

Anatoly Denisov

Company Name: American Airlines Group, Inc

Ticker Symbol: (NASDAQ) AAL

Industry : Airline Transportation

Products or Services Offered: Freight and mail services, airfaire, different pricing options, interconnected loyalty programs, credit card, rewards programs such as miles

Major Competitors: JetBlue Airways, United Airlines, Southwest Airlines, Delta Airlines, Air France KLM, etc

American Airlines Group, Inc.
Consolidated Statements of Operations
As of December 31, 2020, December 31, 2019, and December 31, 2018
$s in Millions
	2020	2019	2018
Operating revenues:
Passenger	$ 14,518	$ 42,010	$ 40,676
Cargo	769	863	1,013
Other	2,050	2,895	2,852
Total operating revenues	17,337	45,768	44,541
Operating expenses:
Aircraft fuel and related taxes	2,581	7,526	8,053
Salaries, wages and benefits	10,960	12,609	12,251
Regional expenses	4,892	7,501	7,133
Maintenance, materials and repairs	1,583	2,380	2,050
Other rent and landing fees	1,536	2,055	1,900
Aircraft rent	1,341	1,326	1,264
Selling expenses	513	1,602	1,520
Depreciation and amortization	2,040	1,982	1,839
Special items, net	-657	635	787
Other	2,969	5,087	5,088
Total operating expenses	27,758	42,703	41,885
Operating income (loss)	-10,421	3,065	2,656
Nonoperating income (expense):
Interest income	41	127	118
Interest expense, net	-1,227	-1,095	-1,056
Other income, net	154	159	166
Total nonoperating expense, net	-1,032	-809	-772
Income (loss) before income taxes	-11,453	2,256	1,884
Income tax provision (benefit)	-2,568	570	472
Net income (loss)	$ (8,885)	$ 1,686	$ 1,412
Earnings (loss) per common share:
Basic	$ (18.36)	$ 3.80	$ 3.04
Diluted	$ (18.36)	$ 3.79	$ 3.03
Weighted average shares outstanding (in thousands):
Basic	483,888	443,363	464,236
Diluted	483,888	444,269	465,660
Cash dividends declared per common share	$ 0.10	$ 0.40	$ 0.40

 

American Airlines Group
Consolidated Balance Sheets
As of December 31, 2020 and December 31, 2019
$s in Millions
	December 31, 2020	December 31, 2019	December 31, 2018
Assets
Current assets
Cash	$ 245	$ 280	$ 275
Short-term investments	6,619	3,546	4,485
Restricted cash and short-term investments	609	158	154
Accounts receivables, net	1,342	1,750	1,706
Aircraft fuel, spare parts and supplies, net	1,614	1,851	1,522
Prepaid expenses and other	666	621	495
Total current assets	11,095	8,206	8,637
Operating property and equipment
Flight equipment	37,816	42,537	4,199
Ground property and equipment	9,194	9,443	8,764
Equipment purchase deposits	1,446	1,674	1,278
Total property and equipment, at cost	48,456	53,654	14,241
Less accumulated depreciation and amortization	-16,757	-18,659	-17,443
Total property and equipment, net	31,699	34,995	-3,202
Operating lease right-of-use assets	8,039	8,737	9,151
Other assets
Goodwill	4,091	4,091	4,091
Intangibles, net of accumulated amortization of $745 and $704, respectively	2,029	2,084	2,137
Deferred tax asset	3,239	645	1,145
Other assets	1,816	1,237	1,321
Total assets	11,175	8,057	8,694
Total assets	$ 62,008	$ 59,995	$ 60,580
Liabilities and Stockholders' Equity (Deficit)
Current liabilities
Current maturities of long-term debt and finance leases	$ 2,797	$ 2,861	$ 3,294
Accounts payable	1,196	2,062	1,773
Accrued salaries and wages	1,716	1,541	1,427
Air traffic liability	4,757	4,808	4,339
Loyalty program liability	2,033	3,193	3,267
Operating lease liabilities	1,651	1,708	1,654
Other accrued liabilities	2,419	2,138	2,342
Total current liabilities	16,569	18,311	18,096
Noncurrent liabilities
Long-term debt and finance leases, net of current maturities	29,796	21,454	21,179
Pension and postretirement benefits	7,069	6,052	6,907
Loyalty program liability	7,162	5,422	5,272
Operating lease liabilities	6,777	7,421	7,902
Other liabilities	1,502	1,453	1,393
Total noncurrent liabilities	52,306	41,802	42,653
Commitments and contingencies (Note 12)
Stockholders' equity (deficit)
Common stock, $0.01 par value; 1,750,000,000 shares authorized, 621,479,522 shares issued and outstanding at December 31, 2020; 428,202,506 shares issued and outstanding at December 31, 2019	6	4	5
Additional paid-in capital	6,894	3,945	4,964.00
Accumulated other comprehensive loss	(7103)	(6331)	-5,896
Retained earnings (deficit)	(6664)	2,264	758
Total stockholders' deficit	(6867)	(118)	-169
Total liabilities and stockholders' equity (deficit)	$ 62,008	$ 59,995	$ 60,580

 

American Airlines Group, Inc.
Consolidated Statements of Cash Flows
As of December 31, 2020, December 31, 2019, and December 31, 2018
$s in Millions
	2020	2019	2018
Cash flows from operating activities:
Net income (loss)	$ (8,885)	$ 1,686

Answer 1

American Airlines Group, Inc
Historical Ratios					Competitor Ratio Analysis
For the years ended December 31, 2020, December 31, 2019, December 31, 2018
	Formulas	2020	2019	2018	Southwest Airlines	United Airlines	Industry Average
Liquidity Ratios
Current Ratio	Current Assets/Current liabilities	0.670	0.448	0.477	2.021449507	1.163064833	1.56
Quick Ratio	Cash+Short term investments+Receivables/Current liabilties	0.495	0.305	0.357	1.92699174	1.019882122	0.54
Leverage Ratios
Debt to Total Assets Ratio	Total Liabilities/ Total Assets	1.110743775	1.001966831	1.0027897	0.74	0.899912675	4.9
Debt Equity Ratio	Total Liabilities/Stockholders'equity	-10.030	-509.432	-359.462	2.896800361	8.991275168	0.23
Long-Term Debt to Equity	Long term Debt/Stockholders' equity	-4.339	-181.814	-125.320	1.139139252	5.041275168	2
Times Interest Earned Ratio	Operating Income /Interest expense	-8.493	2.799	2.515	-10.93409742	-5.982126058	1.455
Activity Ratio
Inventory Turnover	Net Sales/Average Inventory	10.01	27.14	29.26	19.18981972	15.3243513	34.59
Fixed Assets Turnover	Net Sales/Fixed Assets	0.547	1.308	-13.910	0.550766983	0.498247777	0.5
Total Assets Turnover	Net Sales/Total Assets	0.5199	2.8791	-13.9104	0.261593616	0.273807719	0.4925
Accounts Receivable Turnover	Net Sales/Accounts Receivable	11.21410091	26.48611111	26.1084408	8.166064982	11.54945468	18.9
Average Collection Period	365/Accounts receivable turnover	32.54830709	13.78080755	13.9801531	44.69717065	31.60322371	19.31216931
Profitability Ratio
Gross Profit Margin	Gross profits/Sales	77.38%	80.66%	79.08%	79.56%	78.18%	68.23%
Operating Profit Margin	Operating profit/Sales	-60.11%	6.70%	5.96%	-42.18%	-41.41%	9.25%
Net Profit Margin	Net profit/Sales	-51.25%	3.68%	3.17%	-33.97%	-46.04%	5.77%
Return on Total Assets (ROA)	Net income/Total assets	-79.51%	20.93%	16.24%	-8.89%	-11.87%	4.15%
Return on Stockholders' Equity (ROE)	Net income/Stockholders' equity		-1429%	-836%	-34.63%	-118.61%	15.70%
Earnings Per Share (EPS)	Net income/Number of shares	-18.36	3.80	3.04	-5.44	-25.3	$ 0.04
Price Earnings Ratio	Market price of share/EPS	-1.55	8.37	17.11	$ -8.53	$ -1.76	8.99
Market price of share	Market price of share	$ 28.44	$ 31.83	$ 52.03	$ 46.41	$ 44.65
Source: Industry Ratios
https://csimarket.com/Industry/industry_Financial_Strength_Ratios.php?ind=1102
https://www.macrotrends.net/stocks/charts/AIRI/air-industries-group/current-ratio

 

American Airlines Group, Inc
Historical Ratios					Competitor Ratio Analysis
For the years ended December 31, 2020, December 31, 2019, December 31, 2018
	Formulas	2020	2019	2018	Southwest Airlines	United Airlines	Industry Average
Liquidity Ratios
Current Ratio	Current Assets/Current liabilities	='Historical Balance Sheets'!B14/'Historical Balance Sheets'!B39	='Historical Balance Sheets'!C14/'Historical Balance Sheets'!C39	='Historical Balance Sheets'!D14/'Historical Balance Sheets'!D39	='Southwest Balance Sheet'!B13/'Southwest Balance Sheet'!B34	='United Balance Sheet'!B14/'United Balance Sheet'!B45	1.56
Quick Ratio	Cash+Short term investments+Receivables/Current liabilties	=('Historical Balance Sheets'!B8+'Historical Balance Sheets'!B9+'Historical Balance Sheets'!B11)/'Historical Balance Sheets'!B39	=('Historical Balance Sheets'!C8+'Historical Balance Sheets'!C9+'Historical Balance Sheets'!C11)/'Historical Balance Sheets'!C39	=('Historical Balance Sheets'!D8+'Historical Balance Sheets'!D9+'Historical Balance Sheets'!D11)/'Historical Balance Sheets'!D39	=('Southwest Balance Sheet'!B8+'Southwest Balance Sheet'!B9+'Southwest Balance Sheet'!B10)/'Southwest Balance Sheet'!B34	=('United Balance Sheet'!B8+'United Balance Sheet'!B9+'United Balance Sheet'!B11)/'United Balance Sheet'!B45	0.54
Leverage Ratios
Debt to Total Assets Ratio	Total Liabilities/ Total Assets	=('Historical Balance Sheets'!B39+'Historical Balance Sheets'!B46)/'Historical Balance Sheets'!B29	=('Historical Balance Sheets'!C39+'Historical Balance Sheets'!C46)/'Historical Balance Sheets'!C29	=('Historical Balance Sheets'!D39+'Historical Balance Sheets'!D46)/'Historical Balance Sheets'!D29	=('Southwest Balance Sheet'!B34+'Southwest Balance Sheet'!B42)/'Southwest Balance Sheet'!B26	=('United Balance Sheet'!B45+'United Balance Sheet'!B60)/'United Balance Sheet'!B33	4.9
Debt Equity Ratio	Total Liabilities/Stockholders'equity	=('Historical Balance Sheets'!B39+'Historical Balance Sheets'!B46)/'Historical Balance Sheets'!B53	=('Historical Balance Sheets'!C39+'Historical Balance Sheets'!C46)/'Historical Balance Sheets'!C53	=('Historical Balance Sheets'!D39+'Historical Balance Sheets'!D46)/'Historical Balance Sheets'!D53	=('Southwest Balance Sheet'!B34+'Southwest Balance Sheet'!B42)/'Southwest Balance Sheet'!B49	=('United Balance Sheet'!B45+'United Balance Sheet'!B60)/'United Balance Sheet'!B69	0.23
Long-Term Debt to Equity	Long term Debt/Stockholders' equity	='Historical Balance Sheets'!B41/'Historical Balance Sheets'!B53	='Historical Balance Sheets'!C41/'Historical Balance Sheets'!C53	='Historical Balance Sheets'!D41/'Historical Balance Sheets'!D53	='Southwest Balance Sheet'!B36/'Southwest Balance Sheet'!B49	=SUM('United Balance Sheet'!B47:B49)/'United Balance Sheet'!B69	2
Times Interest Earned Ratio	Operating Income /Interest expense	='Historcial Income Statement'!B24/-'Historcial Income Statement'!B27	='Historcial Income Statement'!C24/-'Historcial Income Statement'!C27	='Historcial Income Statement'!D24/-'Historcial Income Statement'!D27	='Southwest Income statement'!B22/'Southwest Income statement'!B25	='United Income statement '!B23/-'United Income statement '!B26	1.455
Activity Ratio
Inventory Turnover	Net Sales/Average Inventory	='Historcial Income Statement'!B11/(('Historical Balance Sheets'!B12+'Historical Balance Sheets'!C12)/2)	='Historcial Income Statement'!C11/(('Historical Balance Sheets'!C12+'Historical Balance Sheets'!D12)/2)	='Historcial Income Statement'!D11/(('Historical Balance Sheets'!D12))	='Southwest Income statement'!B10/AVERAGE('Southwest Balance Sheet'!B11:C11)	='United Income statement '!B10/AVERAGE('United Balance Sheet'!B12:C12)	34.59
Fixed Assets Turnover	Net Sales/Fixed Assets	='Historcial Income Statement'!B11/'Historical Balance Sheets'!B21	='Historcial Income Statement'!C11/'Historical Balance Sheets'!C21	='Historcial Income Statement'!D11/'Historical Balance Sheets'!D21	='Southwest Income statement'!B10/AVERAGE('Southwest Balance Sheet'!B22:C22)	='United Income statement '!B10/AVERAGE('United Balance Sheet'!B21:C21)	0.5
Total Assets Turnover	Net Sales/Total Assets	='Historcial Income Statement'!B11/(('Historical Balance Sheets'!B21+'Historical Balance Sheets'!C21)/2)	='Historcial Income Statement'!C11/(('Historical Balance Sheets'!C21+'Historical Balance Sheets'!D21)/2)	='Historcial Income Statement'!D11/'Historical Balance Sheets'!D21	='Southwest Income statement'!B10/'Southwest Balance Sheet'!B26	='United Income statement '!B10/AVERAGE('United Balance Sheet'!B33:C33)	0.4925
Accounts Receivable Turnover	Net Sales/Accounts Receivable	='Historcial Income Statement'!B11/(('Historical Balance Sheets'!B11+'Historical Balance Sheets'!C11)/2)	='Historcial Income Statement'!C11/(('Historical Balance Sheets'!C11+'Historical Balance Sheets'!D11)/2)	='Historcial Income Statement'!D11/'Historical Balance Sheets'!D11	='Southwest Income statement'!B10/AVERAGE('Southwest Balance Sheet'!B10:C10)	='United Income statement '!B10/AVERAGE('United Balance Sheet'!B11:C11)	18.9
Average Collection Period	365/Accounts receivable turnover	=365/C17	=365/D17	=365/E17	=365/F17	=365/G17	=365/H17
Profitability Ratio
Gross Profit Margin	Gross profits/Sales	=('Historcial Income Statement'!B11-'Historcial Income Statement'!B13-'Historcial Income Statement'!B18)/'Historcial Income Statement'!B11	=('Historcial Income Statement'!C11-'Historcial Income Statement'!C13-'Historcial Income Statement'!C18)/'Historcial Income Statement'!C11	=('Historcial Income Statement'!D11-'Historcial Income Statement'!D13-'Historcial Income Statement'!D18)/'Historcial Income Statement'!D11	=('Southwest Income statement'!B10-'Southwest Income statement'!B15)/'Southwest Income statement'!B10	=('United Income statement '!B10-'United Income statement '!B13-'United Income statement '!B19)/'United Income statement '!B10	0.6823
Operating Profit Margin	Operating profit/Sales	='Historcial Income Statement'!B24/'Historcial Income Statement'!B11	='Historcial Income Statement'!C24/'Historcial Income Statement'!C11	='Historcial Income Statement'!D24/'Historcial Income Statement'!D11	='Southwest Income statement'!B22/'Southwest Income statement'!B10	='United Income statement '!B23/'United Income statement '!B10	0.0925
Net Profit Margin	Net profit/Sales	='Historcial Income Statement'!B32/'Historcial Income State

28.44