Excel Project: Part II InstructionsCompetitor #1 Income Statement (Tab 5), Competitor #1 Balance...

Question

Excel Project: Part II InstructionsCompetitor #1 Income Statement (Tab 5), Competitor #1 Balance Sheet (Tab 6), Competitor #1 Cash Flows (Tab 7), Competitor #2 Income Statement (Tab 8), Competitor #2 Balance Sheet (Tab 9), Competitor #2 Cash Flows (Tab 10), Historical and Competitor Ratios (Tab 11)· Continue in the same Excel workbook prepared in Week 2, adding the following tabs:· The 5th tab and 8th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Income Statements.  (Replace “Competitor Company Name” with the name of the competitor.)· Include the latest three years of income statements for your competitor based on SEC filings.· Ensure that the worksheet includes a header with:· Company Name· Financial Statement Name (Income Statements)· Financial Statement Dates· Denomination level ($s in millions or thousands, etc.)· The 6th and 9th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Balance Sheets.  (Replace “Competitor Company Name” with the name of the competitor.)· Include the latest three years of balance sheets for your first competitor based on SEC (10-K) filings.  (Note: Some 10-Ks only provided 2 years of balance sheet data.  You may need to download the second most recent 10-K for your company in order to obtain 3 years of balance sheet data.)· Ensure that the worksheet includes a header with:· Company Name· Financial Statement Name · Financial Statement Dates· Denomination level ($s in millions or thousands, etc.)· The 7th and 10th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Cash Flows.  (Replace “Competitor Company Name” with the name of the competitor.)· Include the latest three years based on SEC (10-K) filings.· Make sure that the Income Statement, Balance Sheet, and Cash Flows Statement tie in as follows:· Net Income from Income statement agrees with Net Income on the Statement of Cash Flows· Ending Cash Balance from Statement of Cash Flows agrees with Cash in the Balance Sheet· The 11th tab in your Financial Statement Analysis Template must be labeled Historical and Competitor Ratios· The list of ratios to be included are as follows.  They should be grouped under the headings, as shown:· Liquidity Ratios· Cuent Ratio· Quick Ratio· Leverage Ratios· Debt to Total Assets Ratio· Debt Equity Ratio· Long-Term Debt to Equity· Times Interest Earned Ratio· Activity Ratios· Inventory Turnove· Fixed Assets Turnove· Total Assets Turnove· Accounts Receivable Turnove· Average Collection Period· Profitability Ratios· Gross Profit Margin· Operating Profit Margin· Net Profit Margin· Return on Total Assets (ROA)· Return on Stockholders’ Equity (ROE)· Earnings Per Share (EPS)· Price Earnings Ratio· Calculate the ratios for your chosen company for the last three years, linking the ratio calculations back to the historical financial statement tabs (Tabs 2-4) using formulas.· Calculate the ratios for each of your company’s competitors (most recent year only), linking the ratio calculations to the competitor financial statements shown in Tabs 5-10 using formulas.· Using the internet, locate the industry averages for the ratios listed above, and include them in the final column of the ratio tab.  Include a footnote on the tab to identify the source of the industry averages.· Other instructions:· The Excel workbook should be professionally formatted.  Professional formatting includes:· All numbers are formatted similarly.  The use of decimals, commas for thousands, and other formatting is consistent.· Dollar signs are included in the first and last numbers of each column.· Subtotals and totals are preceded by a single underline.  Totals are followed by a double underline.· Line descriptions are indentedolded in a manner that makes the spreadsheet easy to read.  · All totals and subtotals in the competitor financial statements as well as the ratio calculations should be calculated using formulas.  This assignment is due by 11:59 p.m. (ET) on Monday of Module/Week 4.ACCT 370Exhibit A: Example Historical and Competitor Ratios (Tab 11)

Md Ishtyaque · Accepted Answer

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		EBITDA	$ 717,195	$ 498,676	$ 461,458
	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
	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				Average Inventory		4,397,358	4265519.5	2,084,246
	Other current assets	155,241	198,408	105,106				Average Recivables		657220.5	615664.5	303178.5
	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
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,

Excel Project: Part II Instructions Competitor #1 Income Statement (Tab 5), Competitor #1 Balance Sheet (Tab 6), Competitor #1 Cash Flows (Tab 7), Competitor #2 Income Statement (Tab 8), Competitor #2...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment