60.1K

Verified Solution

Link Copied!

PLEASE PROVIDE THE EXCEL SHEETS:

Using the annual report and excel file of financial statements for AutoNation, prepare the following items. Use the spreadsheet provided and modify it with your calculations. Just be sure to change the file name to something with your name in it. You must use spreadsheets (and formulas) for this assignment.

Common Size Balance Sheet

Prepare a spreadsheet with common size balance sheets for the years ended December 31, 2019 and 2018. For 2019, each line item should be shown as a percentage of total assets on December 31, 2019. Likewise, for 2018, each item should be shown as a percentage of total assets on December 31, 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%. 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 2019 and 2018 fiscal years. You do not need to do 2017. In this case, your denominator will be Revenues. 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 2018 to 2019 (the dollar amounts, not the percentages you calculated in the common size statements). This is calculated as follows:

(2019 amount 2018 amount)/ 2018 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.

Ratios

Calculate all* of the ratios listed in exhibit 14-26, "Summary of Analytical Measures," for AutoNation in 2019 (you do not need to do ratios for 2018).

* You also do not need to calculate EPS. It is shown on the income statement

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

The listed items should be submitted in an Excel file.

Auto Nation simplified financial statements.xlsx

Finally, a similar ratio analysis will be provided after the first chat for Penske Automotive Group. Compare your results from AutoNation with Penske on a separate Word document. Your analysis must address at similarities ad differences between the companies related to liquidity, credit risk, and profitability, as well as what the differences mean. You will not receive full credit if you discuss dollars instead of ratios, and you must interpret, not just list, your results.

alle des bowl w h h Hornal Map 5 ht. Coco SH Oximetri PDISC DTS dasb. WOLWIN Der var: Intex CNH Van Fans Cam Chat 18 e IS-21:51 15:2 Ciwill Arth E V. Dehir UX H:Inuill I CITIE THI TE 46 PY 505 RESULS from loca Dar Cocc:coural FAHIYHISH Er:Trans LIV Besar H., SALE 5 Panier InterCx .WE 177 CeCod 05 214 1 521 Cu Var hit Laul CH ) Dell | . 2 13 4517-30517 12120-77182 Nc. Cura JDT 718 M.1984 KHMHION IT RECIATACH W Great 2 13 3113-00100 MERCO CE11 Wars Touran. Hal: hocytrup 13 21 15 0 28 0-0 00:5956 20 Ha mark Insuran Hamak Isuran Vs. Horton Natha service 2 13 12: 19-30-6220202041-8 4. Huva Kukic WELKutin 13 IN ITALIHY: U hech Chic 11 dis 104 1418 218 20 TEN M 2 Hry CC: 512 wg with che Ha 2 Draw SHIRTS whe Coco Socie Linda Corrie | ralaaltilalin Colla Van Abano Court Cased 2 S18-50:51ISSTICO Grain Japan In.. Dobive E. Erasy E Command 1523582.37PF rulla - CISEXCEL 3 T- 11 FI --- ce ANNO 10 plan Corralp CEFIED NURAE TOIALLISIMIES pedig a bitter Bigtown 326 alle des bowl w h horwal Norte 5 10 Ceca SH CNH Van Fans Cam Chat 18 e IS-21:51 15:2 Ciwill Arth E V. Dehir UX H:Inuill I CITIE THI 2317 65 46 $234.5 505 Read Tem luca Dar Cocc:coural 1. 5.6 LIU Besar 77 CC 273 214 .2 Mens End Cordununla of Cushikams - USU ISIS in Allt Dec. 31. 2019 20.5

Answer & Explanation Solved by verified expert

See Answer