Prepare a spreadsheet with common size balance sheets for the years ended 2017 and 2018. For 2017, each line item should be shown as a percentage of total assets on December 31, 2017. Likewise, for 2018, each item should be shown as a percentage of total assets on 2018. In your calculations, round each percentage to three digits. For example if cash as a percentage of total assets is .054029834, your answer should be shown as 5.4%. Remember, these numbers on the financial statements are rounded to the nearest million, so it is a bit silly to show calculations with more precision than the underlying data.

Common Size Income Statement

Prepare a spreadsheet with common size income statements for the 2018 and 2017 fiscal years. In this case, your denominator will be Net Sales. Again, remember to round.

Horizontal Analysis of both financial statements

Prepare a horizontal analysis of the balance sheet and income statement, showing the percentage change from 2017 to 2018 (the dollar amounts, not the percentages you calculated in the common size statements). This is calculated as follows:

2018 amount 2017 amount

2017 amount

Thus, each line item on the balance sheet and income statement should show a percentage increase or decrease. A negative number, or decrease, can be formatted to show in parentheses, which is the preferable notation in accounting circles.

Solvency Ratios

Calculate all of the solvency ratios listed in on the Ratio sheet in Royal Caribbean 10-k data analyzed -2.xls

Calculate Accounts Receivable Turnover (note: this one is missing from your textbook but is calculated as Net Sales / Average Accounts Receivable)

In any place where the formula calls for an average, use the 2017 number plus the 2018 number divided by 2.

Helpful Hint: There are no marketable securities, so the acid test ratio is just (cash + receivables) / current liabilities. Use both lines of receivables in any ratio involving receivables.

Performance Ratios

Calculate the following performance ratios from Chapter 14:

• Asset Turnover
• Return on Sales contains a typo in the book. The formula should say (Net Income + Net-of-tax interest expense)/Sales.
• Return on Assets
• Return on Equity
• Average Interest Rate
• Also calculate Gross Margin (this is also missing from your book but is a widely used ratio. It is gross profit divided by Net Sales)

For the tax rate used in the ROA, assume their effective income tax rate is 35% because tax reform did not take effect until 2018.

o. Home Review View AutoSave OFF D OO Insert Page Layout Formulas Calibri (Body) - 11 19 Copy Format BI U S 2 . Cut Data A- A- A Wrap Text Percentage $ % Paste 3 :53 Merge & Center , Conditional Form Formatting as Tal Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. F14 x fx =(B14-D14)/D14 Dec. 31, 2018 Dec. 31, 2017 Trend Vertical 2017 1% Vertical 2018 1% 1% 1% 2% 1% 0% 1% 0% 0% $ 287,852 324,507 153,573 456,547 19,565 1,242,044 23,466,163 1,378,353 1,611,710 27.698,270 $ 120,112 318,641 111,393 258,171 99,320 907,637 19,735,180 288,512 1,429,597 22.360.926 140% 2% 38% 77% -80% 37% 19% 378% 13% 24% 4% 85% 5% 6% 100% 4% 88% 1% 6% 100% 1,188,514 5% 39% 6% 3% 2% 0% 0% 2% 0% D CONSOLIDATED BALANCE SHEETS - USD ($) $ in Thousands 2 Current assets 3 Cash and cash equivalents 4 Trade and other receivables, net 5 Inventories Prepaid expenses and other assets Derivative financial instruments 8 Total current assets 9 Property and equipment, net 10 Goodwill 11 Other assets 12 Total assets 13 Current liabilities 14 Current portion of long-term debt 15 Commercial paper 16 Accounts payable 17 Accrued interest Accrued expenses and other liabilities 19 Derivative financial instruments 20 Customer deposits 21 Total current liabilities 22 Long-term debt 23 Other long-term liabilities TC 24 Total liabilities 25 Commitments and contingencies 26 Redeemable noncontrolling interest 27 Shareholders' equity 28 Preferred stock ($0.01 par value; 20,000,000 shares authorized; none outstanding) Common stock ($0.01 par value; 500,000,000 shares authorized; 235,847,683 and 235,198,901 shares 29 issued, December 31, 2018 and December 31, 2017, respectively) 30 Paid-in capital 31 Retained earnings 32 Accumulated other comprehensive loss Treasury stock (26,830,765 and 21,861,308 common shares at cost, December 31, 2018 and December 33 31, 2017, respectively) 34 Total shareholders' equity 35 Total liabilities, redeemable noncontrolling interest and shareholders' equity 1,646,841 775,488 488,212 74,550 899,761 78,476 3,148,837 7,112,165 8,355,370 583,254 16,050,789 3% 0% 11% 26% 30% 2% 58% 360,113 47,469 903,022 47,464 2.308, 291 4,854.873 6,350,937 452,813 11,658,623 0% 10% 22% 28% 2% 52% 36% 57% 0% 65% 36% 46% 32% 29% 38% 542,020 2% 0% 0% 0% 2,358 0% 2,352 0% 1% 14% 3,420,900 10,263,282 (627,734) (1,953,345) 12% 37% -2% -7% 3,390,117 9,022,405 (334,265) (1,378,306) 15% 40% -1% -6% 88% 11,105,461 $27.698,270 40% 100% 10,702,303 $22.360,926 48% 100% 42% 4% 24% CONSOLIDATED BALANCE SHEETS CONSOLIDATED STATEMENTS OF CASH Ratios General Summary of Significant Accounti Ready Find me a tutor CARNIVAL CORPORATION & PLC HORIZONTAL ANALYSIS OF CONSOLIDATED BALANCE SHEETS AS ON NOVEMBER 30, 2018 & 2017 (Amount in USD ($) and $ in millions) Nov 30, 2018 Nov 30, 2017 INCREASE (DECREASE) AMT % 982 358 450 436 2,225 35,336 2,925 1,176 738 42,401 395 312 387 502 1,596 34,430 2,967 1,200 585 40,778 587 4 6 63 (66) 629 906 (42)| 148.61% 14.74% 16.28% (13.15%) 39.41% 2.63% (1.42%) (2.00%) 26.15% 3.98% (24) 153 1,623 Current Assets Cash and Cash Equivalents Trade and Other Receivables, Net Inventories Prepaid expenses and other Total Current Assets Property and Equipment, Net Goodwill Other Intangibles Other Assets Assets Current Liabilities Short-Term Borrowings Current Portion of Long Term Debt Accounts Payable Accrued liabilities and Other Customer Deposits Total Current Liabilities Long term Debt Other Long Term Liabilities Commitments & Contingencies Shareholder's Equity Additional Paid in capital Retained Earnings 848 1,578 730 1,654 4,395 9,204 7,897 856 485 1,717 762 1,877 3,958 8,800 6,993 769 363 (139) (32) (223) 437 404 904 87 74.85% (8.10%) (4.20%) (11.88%) 11.04% 4.59% 12.93% 11.31% 2959 8,756 25,066 8,690 23,292 66 1,774 0.76% 7.62% o. Home Review View AutoSave OFF D OO Insert Page Layout Formulas Calibri (Body) - 11 19 Copy Format BI U S 2 . Cut Data A- A- A Wrap Text Percentage $ % Paste 3 :53 Merge & Center , Conditional Form Formatting as Tal Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. F14 x fx =(B14-D14)/D14 Dec. 31, 2018 Dec. 31, 2017 Trend Vertical 2017 1% Vertical 2018 1% 1% 1% 2% 1% 0% 1% 0% 0% $ 287,852 324,507 153,573 456,547 19,565 1,242,044 23,466,163 1,378,353 1,611,710 27.698,270 $ 120,112 318,641 111,393 258,171 99,320 907,637 19,735,180 288,512 1,429,597 22.360.926 140% 2% 38% 77% -80% 37% 19% 378% 13% 24% 4% 85% 5% 6% 100% 4% 88% 1% 6% 100% 1,188,514 5% 39% 6% 3% 2% 0% 0% 2% 0% D CONSOLIDATED BALANCE SHEETS - USD ($) $ in Thousands 2 Current assets 3 Cash and cash equivalents 4 Trade and other receivables, net 5 Inventories Prepaid expenses and other assets Derivative financial instruments 8 Total current assets 9 Property and equipment, net 10 Goodwill 11 Other assets 12 Total assets 13 Current liabilities 14 Current portion of long-term debt 15 Commercial paper 16 Accounts payable 17 Accrued interest Accrued expenses and other liabilities 19 Derivative financial instruments 20 Customer deposits 21 Total current liabilities 22 Long-term debt 23 Other long-term liabilities TC 24 Total liabilities 25 Commitments and contingencies 26 Redeemable noncontrolling interest 27 Shareholders' equity 28 Preferred stock ($0.01 par value; 20,000,000 shares authorized; none outstanding) Common stock ($0.01 par value; 500,000,000 shares authorized; 235,847,683 and 235,198,901 shares 29 issued, December 31, 2018 and December 31, 2017, respectively) 30 Paid-in capital 31 Retained earnings 32 Accumulated other comprehensive loss Treasury stock (26,830,765 and 21,861,308 common shares at cost, December 31, 2018 and December 33 31, 2017, respectively) 34 Total shareholders' equity 35 Total liabilities, redeemable noncontrolling interest and shareholders' equity 1,646,841 775,488 488,212 74,550 899,761 78,476 3,148,837 7,112,165 8,355,370 583,254 16,050,789 3% 0% 11% 26% 30% 2% 58% 360,113 47,469 903,022 47,464 2.308, 291 4,854.873 6,350,937 452,813 11,658,623 0% 10% 22% 28% 2% 52% 36% 57% 0% 65% 36% 46% 32% 29% 38% 542,020 2% 0% 0% 0% 2,358 0% 2,352 0% 1% 14% 3,420,900 10,263,282 (627,734) (1,953,345) 12% 37% -2% -7% 3,390,117 9,022,405 (334,265) (1,378,306) 15% 40% -1% -6% 88% 11,105,461 $27.698,270 40% 100% 10,702,303 $22.360,926 48% 100% 42% 4% 24% CONSOLIDATED BALANCE SHEETS CONSOLIDATED STATEMENTS OF CASH Ratios General Summary of Significant Accounti Ready Find me a tutor CARNIVAL CORPORATION & PLC HORIZONTAL ANALYSIS OF CONSOLIDATED BALANCE SHEETS AS ON NOVEMBER 30, 2018 & 2017 (Amount in USD ($) and $ in millions) Nov 30, 2018 Nov 30, 2017 INCREASE (DECREASE) AMT % 982 358 450 436 2,225 35,336 2,925 1,176 738 42,401 395 312 387 502 1,596 34,430 2,967 1,200 585 40,778 587 4 6 63 (66) 629 906 (42)| 148.61% 14.74% 16.28% (13.15%) 39.41% 2.63% (1.42%) (2.00%) 26.15% 3.98% (24) 153 1,623 Current Assets Cash and Cash Equivalents Trade and Other Receivables, Net Inventories Prepaid expenses and other Total Current Assets Property and Equipment, Net Goodwill Other Intangibles Other Assets Assets Current Liabilities Short-Term Borrowings Current Portion of Long Term Debt Accounts Payable Accrued liabilities and Other Customer Deposits Total Current Liabilities Long term Debt Other Long Term Liabilities Commitments & Contingencies Shareholder's Equity Additional Paid in capital Retained Earnings 848 1,578 730 1,654 4,395 9,204 7,897 856 485 1,717 762 1,877 3,958 8,800 6,993 769 363 (139) (32) (223) 437 404 904 87 74.85% (8.10%) (4.20%) (11.88%) 11.04% 4.59% 12.93% 11.31% 2959 8,756 25,066 8,690 23,292 66 1,774 0.76% 7.62%