pT 2- Excel

 

Please be sure to read through the directions carefully.  You will be providing financial statements for 2 competitors and conducting ratio analysis as well as researching market trends.  It is essential you follow all the instructions. 

Use Spreadsheet attached 

– Select two Competitors: WalMart, Target, Costco, Amazon, Sears and Staples

 Please be sure to read through the directions carefully.  You will be providing financial statements for 2 competitors and conducting ratio analysis as well as researching market trends.  It is essential you follow all the instructions.  

Use Spreadsheet attached 

INCOME STATEMENT

and shares in millions, except per share amounts

Feb-20

9

$

$

$

1 

1 

4

3

from continuing operations

– – 1 

Net earnings $ $ 1,541 $ $ 1,464 $

																																																																																																																																																																																																																																																																																																																																																																																																																																																														Best Buy Co., Inc.
Consolidated Statements of Earnings
																				$
		BBY
		Retail industry
		Products: Consumer electronics and appliances
		Competitors: WalMart, Target, Costco, Amazon, Sears and Staples
	Fiscal Years Ended	1															–			2-Feb			-1		3-Feb-18
Revenue	43,6	38	42,879	42,15				1
Cost of sales	33,590	32,918	32,275
Gross profit	$ 10,048.0	$ 9,961	$ 9,876.00
Selling, general and administrative expenses	7,998	8,015	8,023
	Restructuring charges		41		46			10
Operating income	$ 2,009	$ 1,900	1843
Other income (expense):
Gain on sale of investments	12
Investment income and other		47	49		48
Interest expense		-6		-7	-75
Earnings from continuing operations before income tax expense	$ 1,993	$ 1,888	$ 1,817
Income tax expense	452	424	818
		Net earnings		$ 1,541		$ 1,464	$ 999
Gain from discontinued operations, net of $0 tax expense
$ 1,000

BALANCE SHEET

Best Buy Co., Inc. 

, except per share and share amounts

BBY
Retail industry
Products: Consumer electronics and appliances
Competitors: WalMart, Target, Costco, Amazon, Sears and Staples

2-Feb-19

$

$

, net

–

–

–

$

$

$

$

–

–

– –

– –

$ 15591 $ 12901

Consolidated Balance Sheets
	$ in millions
	1-Feb-20
Assets
Current assets
Cash and cash equivalents	2,229	1,980
	Receivables	1,149	1,015
	Merchandise inventories	5,174	5,409
Other current assets	305	466
Total current assets	$ 8,857	$ 8,870
Property and equipment
Land and buildings	650	637
Leasehold improvements	2,203	2,119
Fixtures and equipment	6,286	5,865
Property under capital and financing leases	579
Property under finance leases	89
Gross property and equipment	$ 9,228	$ 9,200
Less accumulated depreciation	6,900	6,690
Net property and equipment	$ 2,328	$ 2,510
Operating lease assets	2,709
Goodwill	984	915
	Other assets	713	606
Total assets		15591		12901
Liabilities and equity
Current liabilities
	Accounts payable	5,288	5,257
Unredeemed gift card liabilities	281		290
Deferred revenue	501	446
Accrued compensation and related expenses	410	482
Accrued liabilities	906	982
Current portion of operating lease liabilities	660
Current portion of long-term debt	14	56
Total current liabilities	8060	7513
Long-term operating lease liabilities	2,138
Long-term liabilities	657	750
Long-term debt	1,257	1,332
Contingencies and commitments (Note 13)
Equity
Best Buy Co., Inc. Shareholders’ Equity
Preferred stock, $1.00 par value: Authorized 400,000 shares; Issued and outstanding none
Common stock, $0.10 par value: Authorized 1.0 billion shares; Issued and outstanding 256,494,000 and 265,703,000 shares, respectively	26	27
Additional paid-in capital
Retained earnings	3,158	2,985
Accumulated other comprehensive income	295	294
Total equity	3479	3306
Total liabilities and equity

CASH FLOW

Best Buy Co., Inc. 

Consolidated Statements of Cash Flows $ in millions

BBY
Retail industry
Products: Consumer electronics and appliances
Competitors: WalMart, Target, Costco, Amazon, Sears and Staples

Fiscal Years Ended 1-Feb-20 2-Feb-19 3-Feb-18

Operating activities Net earnings $

1,541 

$

1,464 

$

1,000  Adjustments to reconcile net earnings to total cash provided by operating activities: Depreciation and amortization 812  7

70  683 

Restructuring charges 41  46  10 

Stock-based compensation 143  123  129  Deferred income taxes

70  10 

162  Other, net 21  -25 -13 Changes in operating assets and liabilities, net of acquired assets and liabilities: Receivables

-131 28  315  Merchandise inventories

237  -194 -335 Other assets

16  -34 -21 Accounts payable 47 

432  -196 Income taxes -132 22 

290 
Other liabilities -100 -234 117  Total cash provided by operating activities 2565 2408 2,141  Investing activities Additions to property and equipment, net of $10, $53 and $123, respectively, of non-cash capital expenditures -743 -819 -688 Purchases of investments -330

–

-4,325 Sales of investments 322  2,098  4,018  Acquisitions, net of cash acquired -14

5 -787

–
Other, net 1  16  -7
Total cash provided by (used in) investing activities -895 508 -1,002 Financing activities Repurchase of common stock -1,003 -1,505 -2,004 Issuance of common stock

48  38 

163  Dividends paid -527 -497 -409 Borrowings of debt

–

498 

–
Repayments of debt -15 -546 -46 Other, net -1 -6 -1
Total cash used in financing activities -1,498 -2,018 -2,297 Effect of exchange rate changes on cash

-1 -14

25  Increase (decrease) in cash, cash equivalents and restricted cash 171  884  -1,133 Cash, cash equivalents and restricted cash at beginning of period 2,184  1,300  2,433  Cash, cash equivalents and restricted cash at end of period

$

2355

$

2184

$

1300 References

Best Buy Co., Inc. (2020). Best Buy Co., Inc. (BBY). Yahoo Finance – Stock Market Live, Quotes, Business & Finance News. 

https://finance.yahoo.com/quote/BBY/cash-flow?p=BBY

UNITED STATES SECURITIES AND EXCHANGE COMMISSION. (2020, February 1). Inline XBRL viewer. SEC.gov. 

https://www.sec.gov/ix?doc=/Archives/edgar/data/764478/000076447820000017/bby-20200201x10k.htm

https://finance.yahoo.com/quote/BBY/cash-flow?p=BBY
https://www.sec.gov/ix?doc=/Archives/edgar/data/764478/000076447820000017/bby-20200201x10k.htm

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 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

· Current 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 Turnover

· Fixed Assets Turnover

· Total Assets Turnover

· Accounts Receivable Turnover

· 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 indented/bolded 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 370

Exhibit A: Example Historical and Competitor Ratios (Tab 11)