Answer To: Excel Project: Part III Instructions Common Sized Balance Sheets (Tabs 12), Common Sized Income...
Himanshu answered on Mar 01 2021
Company Information
ACCT 370 Excel Project
Advance Auto Parts
Company Information
Derek Crans
Company Name Advance Auto Parts
Ticker Symbol AAP
Industry Automotive Aftermarket Parts Provider
Projects and Services Offered Automotive Aftermarket Parts and Accessories Provider serving both do it yourself (DIY), and professional installers (Professionals)
Major Competitors CarMax, Autozone, and O'Reilly Automotive
Historical Income Statements
Advance Auto Parts
Income Statements
December 30, 2017
December 29, 2018
December 28, 2019
USD ($) Shares in Thousands
$ in Thousands
CONSOLIDATED STATEMENTS OF OPERATIONS - USD ($) shares in Thousands, $ in Thousands 3 Months Ended 4 Months Ended 12 Months Ended
Dec. 28, 2019 Oct. 05, 2019 Jul. 13, 2019 Dec. 29, 2018 Oct. 06, 2018 Jul. 14, 2018 Apr. 20, 2019 Apr. 21, 2018 Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Net sales $ 2,112,614 $ 2,312,106 $ 2,332,246 $ 2,105,072 $ 2,274,982 $ 2,326,652 $ 2,952,036 $ 2,873,848 $ 9,709,003 $ 9,580,554 $ 9,373,784
Cost of sales, including purchasing and warehousing costs 5,454,257 5,361,141 5,288,735
Gross profit 928,769 1,011,926 1,009,438 928,643 1,006,927 1,011,559 1,304,612 1,272,284 4,254,746 4,219,413 4,085,049
Selling, general and administrative expenses 3,577,566 3,615,138 3,514,837
Operating income 677,180 604,275 570,212
Other, net:
Interest expense (39,898) (56,588) (58,801)
Other income, net 464 7,577 8,848
Total other, net (39,434) (49,011) (49,953)
Income before provision for income taxes 637,746 555,264 520,259
Provision for income taxes 150,850 131,417 44,754
Net income $ 95,907 $ 123,669 $ 124,820 $ 53,441 $ 115,843 $ 117,836 $ 142,500 $ 136,727 $ 486,896 $ 423,847 $ 475,505
Basic earnings per common share $ 1.39 $ 1.76 $ 1.74 $ 0.74 $ 1.57 $ 1.59 $ 1.99 $ 1.85 $ 6.87 $ 5.75 $ 6.44
Weighted average common shares outstanding 70,869 73,728 73,846
Diluted earnings per common share $ 1.38 $ 1.75 $ 1.73 $ 0.74 $ 1.56 $ 1.59 $ 1.98 $ 1.84 $ 6.84 $ 5.73 $ 6.42
Weighted average common shares outstanding 71,165 73,991 74,110
EBITDTA $ 717,195 $ 498,676 $ 461,458
CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME - USD ($) $ in Thousands 3 Months Ended 4 Months Ended 12 Months Ended
Dec. 28, 2019 Oct. 05, 2019 Jul. 13, 2019 Dec. 29, 2018 Oct. 06, 2018 Jul. 14, 2018 Apr. 20, 2019 Apr. 21, 2018 Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Net income $ 95,907 $ 123,669 $ 124,820 $ 53,441 $ 115,843 $ 117,836 $ 142,500 $ 136,727 $ 486,896 $ 423,847 $ 475,505
Other comprehensive income (loss):
Changes in net unrecognized other postretirement benefit costs, net of tax of $67, $103 and $126 (142) (294) (194)
Currency translation adjustments 9,766 (18,945) 14,941
Total other comprehensive income (loss) 9,624 (19,239) 14,747
Comprehensive income $ 496,520 $ 404,608 $ 490,252
CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME (Parenthetical) - USD ($) $ in Thousands 12 Months Ended
Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Statement of Comprehensive Income [Abstract]
Changes in net unrecognized other postretirement benefit costs, tax $ 67 $ 103 $ 126
Historical Balance Sheets
Advance Auto Parts
Balance Sheets
December 30, 2017
December 29, 2018
December 28, 2019
USD ($) Shares in Thousands
$ in Thousands
CONSOLIDATED BALANCE SHEETS - USD ($) $ in Thousands Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Current assets:
Cash and cash equivalents $ 418,665 $ 896,527 $ 546,937 2019 2018 2017
Receivables, net 689,469 624,972 606,357
Inventories 4,432,168 4,362,547 4,168,492
Other current assets 155,241 198,408 105,106
Total current assets 5,695,543 6,082,454 5,426,892
Property and equipment, net of accumulated depreciation of $2,037,849 and $1,918,502 1,433,213 1,368,985 1,394,138 2019 2018 2017 Deprecition for the year 119347
Operating lease right-of-use assets 2,365,325 0 0 Average FIXED ASSETS 1401099 1381561.5 697,069
Goodwill 992,240 990,237 994,293
Intangible assets, net 709,756 550,593 597,674
Other assets 52,448 48,379 69,304 Average Total Assets 20,289,173 13281798.5 4,241,151
Assets, Total 11,248,525 9,040,648 8,482,301
Current liabilities:
Accounts payable 3,421,987 3,172,790 2,894,582
Accrued expenses 535,863 623,141 533,548
Other current liabilities 519,852 90,019 51,967
Total current liabilities 4,477,702 3,885,950 3,480,097
Long-term debt 747,320 1,045,720 1,044,327
Noncurrent operating lease liabilities 2,017,159 0 0
Deferred income taxes 334,013 318,353 303,620
Other long-term liabilities 123,250 239,812 239,061
Commitments and Contingencies
Stockholders' equity:
Preferred stock, nonvoting, $0.0001 par value, 10,000 shares authorized; no shares issued or outstanding 0 0 0
Common stock, voting, $0.0001 par value, 200,000 shares authorized; 76,051 shares issued and 69,232 outstanding at December 28, 2019 and 75,831 shares issued and 72,460 outstanding at December 39, 2018 8 8 8 N0 0f share outsandings 69232 75831 75831
Additional paid-in capital 735,183 694,797 664,646 Share price per share 0.1155535013 0.1104057411 0.1104057411
Treasury stock, at cost, 6,819 and 3,371 shares (924,389) (425,954) (144,600) 3371 3371
Accumulated other comprehensive loss (34,569) (44,193) (24,954) 72603 79202
Retained earnings 3,772,848 3,326,155 $ 2,920,096
Total stockholders' equity 3,549,081 3,550,813 3,415,196
Liabilities and Stockholders' Equity, Total $ 11,248,525 $ 9,040,648 $8,482,301
CONSOLIDATED BALANCE SHEETS (Parenthetical) - USD ($) shares in Thousands, $ in Thousands Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Accumulated Depreciation, Property and Equipment $ 2,037,849 $ 1,918,502 $ 1,783,383
Preferred stock, non-voting, par value 0.0001 0.0001 0.0001
Preferred Stock, Shares Authorized 10,000 10,000 10,000
Preferred Stock, Shares Issued 0 0 0
Preferred Stock, Shares Outstanding 0 0 0
Common stock, voting, par value 0.0001 0.0001 0.0001
Common Stock, Shares Authorized 200,000 200,000 200,000
Common Stock, Shares, Issued 76,051 75,831 75,569
Common Stock, Shares, Outstanding 69,232 72,460 73,936
Treasury Stock, Shares $6,819 $3,371 $1,633
Total stock 76051 75831 75569
Average Inventory 4,397,358 4265519.5 2,084,246
Average Recivables 657220.5 615664.5 303178.5
HistoricalStatementofCashFlows
Advance Auto Parts
Cash Flow Statements
December 30, 2017
December 29, 2018
December 28, 2019
USD ($) Shares in Thousands
$ in Thousands
CONSOLIDATED STATEMENTS OF CASH FLOWS - USD ($) $ in Thousands 12 Months Ended
Dec. 28, 2019 Dec. 29, 2018 Dec. 30, 2017
Cash flows from operating activities:
Net income $ 486,896 $ 423,847 $ 475,505
Adjustments to reconcile net income to net cash provided by operating activities:
Depreciation and amortization 238,371 238,184 249,260
Share-based compensation 37,438 27,760 35,267
Loss and impairment of long-lived assets 6,671 15,956 17,106
Other, net 12,437 2,195 3,123
Provision (benefit) for deferred income taxes 23,148 15,956 (151,263)
Net change in:
Receivables, net (62,837) (21,471) 36,047
Inventories (63,130) (206,125) 167,548
Accounts payable 245,785 285,493 (197,168)
Accrued expenses (72,288) 93,940 (13,295)
Other assets and liabilities, net 14,418 (64,707) (21,325)
Net cash provided by operating activities 866,909 811,028 600,805
Cash flows from investing activities:
Purchases of property and equipment (270,129) (193,715) (189,758)
Purchase of an indefinite-lived intangible asset (201,519) 0 0
Proceeds from sales of property and equipment 8,709 1,888 11,099
Other, net 0 0 20
Net cash used in investing activities (462,939) (191,827) (178,639)
Cash flows from financing activities:
(Decrease) increase in bank overdrafts (59,339) 32,014 14,004
Redemption of senior unsecured note (310,047) 0 0
Borrowings under credit facilities 0 0 534,400
Payments on credit facilities 0 0 (534,400)
Dividends paid (17,185) (17,819) (17,854)
Proceeds from the issuance of common stock 3,334 3,200 4,076
Repurchases of common stock (498,435) (281,354) (6,498)
Other, net (481) 44 (8,600)
Net cash used in financing activities (882,153) (263,915) (14,872)
Effect of exchange rate changes on cash 321 (5,696) 4,465
Net (decrease) increase in cash and cash equivalents (477,862) 349,590 411,759
Cash and cash equivalents, beginning of period 896,527 546,937 135,178
Cash and cash equivalents, end of period 418,665 896,527 546,937
Supplemental cash flow information:
Interest paid 41,099 45,322 53,509
Income tax payments 108,163 143,213 192,116
Non-cash transactions:
Accrued purchases of property and equipment $ 26,201 $ 15,365 $ 14,335
CarMax Income Statements
CarMax
Income Statements
February 28, 2018
February 28, 2019
February 29, 2020
USD ($) Shares in Thousands
$ in Thousands
Consolidated Statements Of Earnings - USD ($) shares in Thousands, $ in Thousands 12 Months Ended
Feb. 29, 2020 Feb. 28, 2019 Feb. 28, 2018
SALES AND OPERATING REVENUES:
NET SALES AND OPERATING REVENUES $ 20,319,987 $ 18,173,100 $ 17,120,209
TOTAL COST OF SALES 17,597,647 15,692,509 14,791,350
GROSS PROFIT 2,722,340 2,480,591 2,328,859 ERROR:#NAME?
CARMAX AUTO FINANCE INCOME 456,030 438,690 421,182
Selling, general and administrative expenses 1,940,067 1,730,275 1,617,051
Interest expense 83,007 75,792 70,745
Other (income) expense (5,690) 408 (1,363)
Earnings before income taxes 1,160,986 1,112,806 1,063,608
Income tax provision 272,553 270,393 399,496 Operating Income $ 782,273
NET EARNINGS $ 888,433 $ 842,413 $ 664,112
WEIGHTED AVERAGE COMMON SHARES: EBITDA $ 1,243,993
Basic (in shares) 164,836 174,463 182,660
Diluted (in shares) 166,820 175,884 184,470
NET EARNINGS PER SHARE:
Basic (in dollars per share) $ 5.39 $ 4.83 $ 3.64
Diluted (in dollars per share) $ 5.33 $ 4.79 $ 3.60
Used vehicle sales
SALES AND OPERATING REVENUES:
NET SALES AND OPERATING REVENUES $ 17,169,462 $ 15,172,772 $ 14,392,360
TOTAL COST OF SALES 15,349,401 13,544,033 12,824,741
Wholesale vehicle sales
SALES AND OPERATING REVENUES:
NET SALES AND OPERATING REVENUES 2,500,042 2,392,992 2,181,156
TOTAL COST OF SALES 2,045,680 1,961,959 1,788,704
Other sales and revenues
SALES AND OPERATING REVENUES:
NET SALES AND OPERATING REVENUES 650,483 607,336 546,693
TOTAL COST OF SALES $ 202,566 $ 186,517 $ 177,905
NET SALES AND OPERATING REVENUES
Percentage of Sales
Item as a percent of net sales and operating revenues 100.00% 100.00% 100.00%
TOTAL COST OF SALES
Percentage of Sales
Item as a percent of net sales and operating revenues 86.60% 86.40% 86.40%
GROSS PROFIT
Percentage of Sales
Item as a percent of net sales and operating revenues 13.40% 13.60% 13.60%
CARMAX AUTO FINANCE INCOME
Percentage of Sales
Item as a percent of net sales and operating revenues 2.20% 2.40% 2.50%
Selling, general and administrative expenses
Percentage of Sales
Item as a percent of net sales and operating revenues 9.50% 9.50% 9.40%
Interest expense
Percentage of Sales
Item as a percent of net sales and operating revenues 0.40% 0.40% 0.40%
Other (income) expense
Percentage of Sales
Item as a percent of net sales and operating revenues 0.00% 0.00% 0.00%
Earnings before income taxes
Percentage of Sales
Item as a percent of net sales and operating revenues 5.70% 6.10% 6.20%
Income tax provision
Percentage of Sales
Item as a percent of net sales and operating revenues 1.30% 1.50% 2.30%
NET EARNINGS
Percentage of Sales
Item as a percent of net sales and operating revenues 4.40% 4.60% 3.90%
NET SALES AND OPERATING REVENUES | Used vehicle sales
Percentage of Sales
Item as a percent of net sales and operating revenues 84.50% 83.50% 84.10%
NET SALES AND OPERATING REVENUES | Wholesale vehicle sales
Percentage of Sales
Item as a percent of net sales and operating revenues 12.30% 13.20% 12.70%
NET SALES AND OPERATING REVENUES | Other sales and revenues
Percentage of Sales
Item as a percent of net sales and operating revenues 3.20% 3.30% 3.20%
TOTAL COST OF SALES | Used vehicle sales
Percentage of Sales
Item as a percent of net sales and operating revenues 75.50% 74.50% 74.90%
TOTAL COST OF SALES | Wholesale vehicle sales
Percentage of Sales
Item as a percent of net sales and operating revenues 10.10% 10.80% 10.40%
TOTAL COST OF SALES | Other sales and revenues
Percentage of Sales
Item as a percent of net sales and operating revenues 1.00% 1.00% 1.00%
Consolidated Statements Of Comprehensive Income - USD ($) $ in Thousands 12 Months Ended
Feb. 29, 2020 Feb. 28, 2019 Feb. 28, 2018
Statement of Comprehensive Income [Abstract]
NET EARNINGS $ 888,433 $ 842,413 $ 664,112
Other comprehensive (loss) income, net of taxes:
Net change in retirement benefit plan unrecognized actuarial losses (50,824) (1,981) (1,371)
Net change in cash flow hedge unrecognized losses (31,237) (11,717) 14,194
Other comprehensive (loss) income, net of taxes (82,061) (13,698) 12,823
TOTAL COMPREHENSIVE INCOME $ 806,372 $ 828,715 $ 676,935
CarMax Balance Sheets
CarMax
Balance Sheets
February 28, 2018
February 28, 2019
February 29, 2020
USD ($) Shares in Thousands
$ in Thousands
Consolidated Balance Sheets - USD ($) $ in Thousands Feb. 29, 2020 Feb. 28, 2019 Feb. 28, 2018
CURRENT ASSETS:
Cash and cash equivalents $ 58,211 $ 46,938 $ 44,525
Restricted cash from collections on auto loans receivable 481,043 440,669 399,442
Accounts receivable, net 191,090 139,850 133,321
Inventory 2,846,416 2,519,455 2,390,694 Average Inventory 2,682,936
Other current assets 86,927 67,101 93,462 Average Receivables 165470
TOTAL CURRENT ASSETS: 3,663,687 3,214,013 3,061,444
Auto loans receivable, net 13,551,711 12,428,487 11,535,704
Property and equipment, net 3,069,102 2,828,058 2,667,061 Average Fixed Assets 2948580 2747559.5 1,333,531
Deferred income taxes 89,842 61,346 63,256
Operating lease assets 449,094 0 0
Other assets 258,746 185,963 158,807 Avergae Total Aseets 30441115.5 27461003 8,743,136
TOTAL ASSETS 21,082,182 18,717,867 17,486,272
CURRENT LIABILITIES:
Accounts payable 737,144 593,171 529,733
Accrued expenses and other current liabilities 331,738 318,204 278,771
Accrued income taxes 1,389 3,784 0
Current portion of operating lease liabilities 30,980 0 0
Short-term debt 40 1,129 127
Current portion of financing and capital lease obligations 0 0 9,994
Current portion...