Please explain what exact excel formulas need to be typed in to the yellow boxes.

This problem set uses Hawaiian Airlines for ALL questions. To start, go to its investor relations page and get its latest annual report $(10-$K$).$ Make sure to use the last $3$ full years! All your responses will be in the YELLOW areas only!

$1.$ Using the firm's financials:

a$.$ Fill the income statement $($in million $\backslash$$$).$ Expenses without their own line should be grouped under "All other expenses". The net income lines must tie using formulas

b$.$ Create the common size income statement $($as a $\backslash (\backslash \%\backslash )$ of operating revenues for that year$)$

c$.$ Show year$-$on$-$year $($YoY$,$ in $\backslash \%)$ growth

d$.$ What happened to the profit margins?

$2.$ Using the selected consolidated statistical data:

a$.$ Yield?

b$.$ Operating expense per available seat mile $($CASM$)?$

c$.$ Revenue passenger miles $($RPMs$)?$

d$.$ Fuel price $\backslash ((\backslash$$ $/\backslash )$ gallon $\backslash ()\backslash )?$

$3.$ Using the firm's balance sheets, show:

a$.$ Balance sheets as shown $($in million $\backslash$$$)$; show only net property $\backslash$& equipment lines.

b$.$ Common size balance sheets.

c$.$ Are there any outliers in the balance sheets that you think merit mentioning?

d$.$ For these years, what is the net working capital position? Current ratio? Quick ratio? Days receivable? Return on assets $($ROA$)?$ Return on equity $($ROE$)?$ What is the latest p$/$e ratio? $($use Yahoo Finance$)$

$4.$ Using the Statement of Cash Flows, which items stand out to you and why?

$5.$ Go to the carrier snapshot section in the Bureau of Transportation Statistics of the U$.$S$.$ DOT:

a$.$ What are the top three markets by share?

b$.$ What happened to passenger yields?

c$.$ What happened to costs per ASM?

d$.$ What do you think this means for the bottom line?

e$.$ What are the top three city pairs?

A

B

C

D

E

F

G

H

I

J

K

L

M

YoY Growth $2021\_2022\_$

Total Operating Revenues

Operating Expenses:

Wages and benefits

Aircraft fuel

Maintenance and repairs

Other rentals and landing fees

Aircraft and passenger servicing

All other expenses

Total Operating Expenses

Operating Income

Total other income$/($expense$)$

$($Loss$)/$Income before taxes

Income tax$/($benefit$)$ expense

Net $($Loss$)$ Income

EPS

$3$ All cash and short term investments

All receivables, net

Parts and supplies, net

Prepaids and other

Total Current Assets

Property & Equipment, net

Operating lease assets

Other assets

Total Assets

Working capital

Current ratio

Quick ratio

Days receivable

ROA

ROE

p$//$e ratio today

Air traffic liability

Accounts payable

Accruals

Other current liabilities

Total Current Liabilities

Long term debt

Other liabilities and deferred credits

Stockholder's equity

Total Liab. and equity

Put your text answer in the yellow box.