In the Attachments I posted the excel sheet, I need help completing sections 3-5. I also filled out most of the excel sheet. Please help me complete the last few excel pages.

Annual Report Project for Target Corporation ( FY 2015)

Use the Target Corporation’s Form 10-K to complete the case. You will be working with Target’s FY 2015 (year ended January 30, 2016) and FY 2014 Reports. Target’s Form 10-K is available on the company’s website or through the SEC’s EDGAR database. Appendix A in your text provides instructions for using the EDGAR database.

The following information contains the directions for the problem portion of the Comprehensive Case Exam: (190 points)

Section 2 – (69 points total)

Questions 31 – 51 are presented below. Please be sure to show all support for your work. You will be using the following sections of the annual report: MD&A, consolidated financial statements and notes to the financial statements. Please use your homework and information in the assigned chapters to complete the case.

31. Using the Excel template, compute common-size income statements (vertical analysis) for 2015 and 2014. In common-size income statements, net sales is 100% and every other number is a percentage of sales (you can stop at net earnings). (12 pts)

32. Using the common-size (vertical) income statements, identify the significant trends, and compare to the industry standards included in the template (source: BizStats). (3 pts)

33. Using the Excel template, compute common-size (vertical analysis) balance sheets at the end of fiscal 2015 and 2014. In common-size balance sheets, total assets is 100% and every other number is a percentage of total assets. Be sure to complete the entire balance sheet. (15 pts)

34. Using the common-size (vertical) balance sheet, identify the significant trends, and compare to the industry standards included in the template (source: BizStats). (3 pts)

35. Using Excel, prepare a horizontal analysis of the income statement comparing 2015, 2014 and 2013 (you can stop at net earnings). (15 pts)

36. Using the horizontal analysis of the income statement, identify the significant trends. (3 pts)

37. Using Excel, prepare a horizontal analysis of the balance sheet comparing 2015, 2014 and 2013. (15 pts)

38. Using the horizontal analysis of the balance sheet, identify the significant trends. (3 pts)

Section 3 – (45 pts)

For numbers 39-51, identify the appropriate formula and calculate the following ratios for 2015 and 2014 **AND analyze the results. (Market performance ratios (earnings per share, book value per share, price-earnings ratio, and dividend yield are already computed and included in the template.)

NOTE: You will need to use the 2014 reports in order to calculate averages where applicable.

Measures of liquidity (15 pts)

39. Working capital

40. Current ratio

41. Quick ratio

42. Inventory turnover

43. Average days to sell inventory

Measures of solvency (15 pts)

44. Debt to assets

45. Debt to equity

46. Number of times interest earned

47. Plant assets to long-term liabilities

Measures of profitability (15 pts)+5

48. Net margin (return on sales)

49. Asset turnover

50. Return on investment

51. Return on equity

Section 4 – Budgeting (25 points)

Calculate the percentage change for each of the following categories of revenues and expenses for FY 2010 through FY 2014.

Calculate the average percentage change

Use the FY 2014 data and the average percentage change calculated in 2 to prepare a budget for FY 2015

Compare actual amounts from FY 2015 to the budget

Section 5 – Analysis – (50 points)

Prepare an analytic report on your findings in Sections 2 – 4.

2015 Report (FY ended in ’16) – https://corporate.target.com/annual-reports/pdf-viewer-2015?cover=24293&parts=24299

2014 Report (FY ended in ’15) – https://corporate.target.com/annual-reports/pdf-viewer-2014?cover=12672&parts=12678

2014 Report Information:

Consolidated Statements of Operations

(millions, except per share data) 2014 2013 2012 Sales $ Credit card revenues Total revenues Cost of sales Selling, general and administrative expenses Credit card expenses Depreciation and amortization Gain on receivables transaction Earnings from continuing operations before interest expense and income taxes Net interest expense Earnings from continuing operations before income taxes Provision for income taxes

72,618 $ — 72,618 51,278 14,676 — 2,129 — 4,535 882 3,653 1,204

71,279 $ — 71,279 50,039 14,465 — 1,996 (391) 5,170 1,049 4,121 1,427

71,960 1,341 73,301 50,568 14,643 467 2,044 (161) 5,740 684 5,056 1,741

Net earnings from continuing operations Discontinued operations, net of tax Net (loss)/earnings $ Basic (loss)/earnings per share Continuing operations $ Discontinued operations Net (loss)/earnings per share $

2,449 (4,085) (1,636) $ 3.86 $ (6.44) (2.58) $

2,694 (723) 1,971 $ 4.24 $ (1.14) 3.10 $

3,315 (316) 2,999 5.05 (0.48) 4.57

Diluted (loss)/earnings per share Continuing operations $ Discontinued operations Net (loss)/earnings per share $ Weighted average common shares outstanding Basic Dilutive effect of share-based awards

3.83 $ (6.38) (2.56) $ 634.7 5.4

4.20 $ (1.13) 3.07 $ 635.1 6.7

5.00 (0.48) 4.52 656.7 6.6

Consolidated Statements of Comprehensive Income (millions)

2014

2013

2012

Net (loss)/income

$

(1,636) $

1,971 $

2,999

Other comprehensive income/(loss), net of tax

Pension and other benefit liabilities, net of (benefit)/provision for taxesof $(90), $71 and $58

(139)

110

92

Currency translation adjustment and cash flow hedges, net of provisionfor taxes of $2, $11 and $8

431

(425)

13

Other comprehensive income/(loss)

292

(315)

105

Comprehensive (loss)/income

$

(1,344) $

1,656 $

3,104

Consolidated Statements of Financial Position (millions, except footnotes)

January 31,2015

February 1,2014

Assets

Cash and cash equivalents, including short-term investments of $1,520 and $3

$

2,210 $

670

Inventory

8,790

8,278

Assets of discontinued operations

1,333

793

Other current assets

1,754

1,832

Total current assets

14,087

11,573

Property and equipment

Land

6,127

6,143

Buildings and improvements

26,614

25,984

Fixtures and equipment

5,346

5,199

Computer hardware and software

2,553

2,395

Construction-in-progress

424

757

Accumulated depreciation

(15,106)

(14,066)

Property and equipment, net

25,958

26,412

Noncurrent assets of discontinued operations

442

5,461

Other noncurrent assets

917

1,107

Total assets

$

41,404 $

44,553

Liabilities and shareholders’ investment

Accounts payable

$

7,759 $

7,335

Accrued and other current liabilities

3,783

3,610

Current portion of long-term debt and other borrowings

91

1,143

Liabilities of discontinued operations

103

689

Total current liabilities

11,736

12,777

Long-term debt and other borrowings

12,705

11,429

Deferred income taxes

1,321

1,349

Noncurrent liabilities of discontinued operations

193

1,296

Other noncurrent liabilities

1,452

1,471

Total noncurrent liabilities

15,671

15,545

Shareholders’ investment

Common stock

53

53

Additional paid-in capital

4,899

4,470

Retained earnings

9,644

12,599

Accumulated other comprehensive loss

Pension and other benefit liabilities

(561)

(422)

Currency translation adjustment and cash flow hedges

(38)

(469)

Total shareholders’ investment

13,997

16,231

Total liabilities and shareholders’ investment

$

41,404 $

44,553

Consolidated Statements of Cash Flows

(millions)

2014

2013

2012

Operating activitiesNet (loss)/earnings Losses from discontinued operations, net of tax

$ (1,636) $ (4,085)

1,971 (723)

$

2,999 (316)

Net earnings from continuing operations Adjustments to reconcile net earnings to cash provided by operations: Depreciation and amortization Share-based compensation expense Deferred income taxes Gain on receivables transaction Loss on debt extinguishment Noncash losses/(gains) and other, net (a)Changes in operating accounts: Accounts receivable originated at Target Proceeds on sale of accounts receivable originated at Target Inventory Other assets Accounts payable and accrued liabilities

2,449 2,129 71 7 — 285 40 — — (512) (115) 777

2,694 1,996 106 58 (391) 445 121 157 2,703 (504) (79) 213

3,315 2,044 102 67 (161) — 220 (217) — 160 (155) 193

Cash provided by operating activities—continuing operations Cash required for operating activities—discontinued operations

5,131 (692)

7,519 (999)

5,568 (243)

Cash provided by operations

4,439

6,520

5,325

Investing activitiesExpenditures for property and equipment Proceeds from disposal of property and equipment Change in accounts receivable originated at third parties Proceeds from sale of accounts receivable originated at third parties Cash paid for acquisitions, net of cash assumed Other investments

(1,786) 95 — — (20) 106

(1,886) 70 121 3,002 (157) 130

(2,346) 66 254 — — 102

Cash (required for)/ provided by investing activities—continuing operations Cash required for investing activities—discontinued operations

(1,605) (321)

1,280 (1,551)

(1,924) (931)

Cash required for investing activities

(1,926)

(271)

(2,855)

Financing activitiesChange in commercial paper, net Reductions of short-term debt Additions to long-term debt Reductions of long-term debt Dividends paid Repurchase of stock Stock option exercises and related tax benefit Other

(80) — 1,993 (2,079) (1,205) — 373 —

(890) — — (3,463) (1,006) (1,461) 456 —

970 (1,500) 1,971 (1,529) (869) (1,875) 360 (16)

Cash required for financing activities

(998)

(6,364)

(2,488)

Effect of exchange rate changes on cash and cash equivalents

—

26

8

Net increase/(decrease) in cash and cash equivalents Cash and cash equivalents at beginning of period (b)

1,515 695

(89) 784

(10) 794

Cash and cash equivalents at end of period (c)

$ 2,210

$

695

$

784

Supplemental information Interest paid, net of capitalized interest Income taxes paid Property and equipment acquired through capital lease obligations

$

871 1,251 88

$

1,043 1,386 132

$

697 1,603 251

Consolidated Statements of Shareholders’ Investment(millions, except footnotes)

Common Stock Shares

Stock Par Value

Additional Paid-in Capital

Retained Earnings

Accumulated Other ComprehensiveIncome/(Loss)

Total

January 28, 2012

669.3 $

56 $

3,487 $

12,959 $

(681) $ 15,821

Net earnings

—

—

—

2,999

— 2,999

Other comprehensive income

—

—

—

—

105 105

Dividends declared

—

—

—

(903)

— (903)

Repurchase of stock

(32.2)

(3)

—

(1,900)

— (1,903)

Stock options and awards

8.2

1

438

—

— 439

February 2, 2013

645.3 $

54 $

3,925 $

13,155 $

(576) $ 16,558

Net earnings

—

—

—

1,971

— 1,971

Other comprehensive loss

—

—

—

—

(315) (315)

Dividends declared

—

—

—

(1,051)

— (1,051)

Repurchase of stock

(21.9)

(2)

—

(1,476)

— (1,478)

Stock options and awards

9.5

1

545

—

— 546

February 1, 2014

632.9 $

53 $

4,470 $

12,599 $

(891) $ 16,231

Net loss

—

—

—

(1,636)

— (1,636)

Other comprehensive income

—

—

—

—

292 292

Dividends declared

—

—

—

(1,273)

— (1,273)

Repurchase of stock

(0.8)

—

—

(46)

— (46)

Stock options and awards

8.1

—

429

—

— 429

January 31, 2015

640.2 $

53 $

4,899 $

9,644 $

(599) $ 13,997

2015 Report Information:

Item 6. Selected Financial Data

As of or for the Fiscal Year Ended

(millions, except per share data) 2015 2014 2013 2012 (a) 2011 2010

Sales (b) $ 73,785 $ 72,618 $ 71,279 $ 73,301 $ 69,865 $ 67,390

Net Earnings / (Loss)

Continuing operations 3,321 2,449 2,694 3,315 3,049 2,920

Discontinued operations 42 (4,085) (723) (316) (120) —

Net earnings / (loss) 3,363 (1,636) 1,971 2,999 2,929 2,920

Basic Earnings / (Loss) Per Share

Continuing operations 5.29 3.86 4.24 5.05 4.49 4.03

Discontinued operations 0.07 (6.44) (1.14) (0.48) (0.18) —

Basic earnings / (loss) per share 5.35 (2.58) 3.10 4.57 4.31 4.03

Diluted Earnings / (Loss) Per Share

Continuing operations 5.25 3.83 4.20 5.00 4.46 4.00

Discontinued operations 0.07 (6.38) (1.13) (0.48) (0.18) —

Diluted earnings / (loss) per share 5.31 (2.56) 3.07 4.52 4.28 4.00

Cash dividends declared per share 2.20 1.99 1.65 1.38 1.15 0.92

Total assets (c) 40,262 41,172 44,325 47,878 46,260 43,240

Long-term debt, including current portion 12,760 12,725 12,494 16,260 16,127 15,638

Consolidated Statements of Operations

(millions, except per share data) 2015 2014 2013

Sales $ 73,785 $ 72,618 $ 71,279

Cost of sales 51,997 51,278 50,039

Gross margin 21,788 21,340 21,240

Selling, general and administrative expenses 14,665 14,676 14,465

Depreciation and amortization 2,213 2,129 1,996

Gain on sale (620) — (391)

Earnings from continuing operations before interest expense and income

taxes 5,530 4,535 5,170

Net interest expense 607 882 1,049

Earnings from continuing operations before income taxes 4,923 3,653 4,121

Provision for income taxes 1,602 1,204 1,427

Net earnings from continuing operations 3,321 2,449 2,694

Discontinued operations, net of tax 42 (4,085) (723)

Net earnings / (loss) $ 3,363 $ (1,636) $ 1,971

Basic earnings / (loss) per share

Continuing operations $ 5.29 $ 3.86 $ 4.24

Discontinued operations 0.07 (6.44) (1.14)

Net earnings / (loss) per share $ 5.35 $ (2.58) $ 3.10

Diluted earnings / (loss) per share

Continuing operations $ 5.25 $ 3.83 $ 4.20

Discontinued operations 0.07 (6.38) (1.13)

Net earnings / (loss) per share $ 5.31 $ (2.56) $ 3.07

Weighted average common shares outstanding

Basic 627.7 634.7 635.1

Dilutive effect of share-based awards 5.2 5.4 6.7

Diluted 632.9 640.1 641.8

Antidilutive shares — 3.3 2.3

Consolidated Statements of Comprehensive Income

(millions) 2015 2014 2013

Net income / (loss) $ 3,363 $ (1,636) $ 1,971

Other comprehensive income / (loss), net of tax

Pension and other benefit liabilities, net of (benefit) / provision for taxes

of $(18), $(90), and $71 (27) (139) 110

Currency translation adjustment and cash flow hedges, net of provision

for taxes of $2, $2, and $11 (3) 431 (425)

Other comprehensive income / (loss) (30) 292 (315)

Comprehensive (loss) / income $ 3,333 $ (1,344) $ 1,656

Consolidated Statements of Financial Position

(millions, except footnotes)

January 30,

2016

January 31,

2015

Assets

Cash and cash equivalents, including short-term investments of $3,008 and $1,520 $ 4,046 $ 2,210

Inventory 8,601 8,282

Assets of discontinued operations 322 1,058

Other current assets 1,161 2,074

Total current assets 14,130 13,624

Property and equipment

Land 6,125 6,127

Buildings and improvements 27,059 26,613

Fixtures and equipment 5,347 5,329

Computer hardware and software 2,617 2,552

Construction-in-progress 315 424

Accumulated depreciation (16,246) (15,093)

Property and equipment, net 25,217 25,952

Noncurrent assets of discontinued operations 75 717

Other noncurrent assets 840 879

Total assets $ 40,262 $ 41,172

Liabilities and shareholders’ investment

Accounts payable $ 7,418 $ 7,759

Accrued and other current liabilities 4,236 3,783

Current portion of long-term debt and other borrowings 815 91

Liabilities of discontinued operations 153 103

Total current liabilities 12,622 11,736

Long-term debt and other borrowings 11,945 12,634

Deferred income taxes 823 1,160

Noncurrent liabilities of discontinued operations 18 193

Other noncurrent liabilities 1,897 1,452

Total noncurrent liabilities 14,683 15,439

Shareholders’ investment

Common stock 50 53

Additional paid-in capital 5,348 4,899

Retained earnings 8,188 9,644

Accumulated other comprehensive loss

Pension and other benefit liabilities (588) (561)

Currency translation adjustment and cash flow hedges (41) (38)

Total shareholders’ investment 12,957 13,997

Total liabilities and shareholders’ investment $ 40,262 $ 41,172

Consolidated Statements of Cash Flows

(millions) 2015 2014 2013

Operating activities

Net earnings / (loss) $ 3,363 $ (1,636) $ 1,971

Earnings / (losses) from discontinued operations, net of tax 42 (4,085) (723)

Net earnings from continuing operations 3,321 2,449 2,694

Adjustments to reconcile net earnings to cash provided by operations:

Depreciation and amortization 2,213 2,129 1,996

Share-based compensation expense 115 71 106

Deferred income taxes (322) 7 58

Gain on sale (620) — (391)

Loss on debt extinguishment — 285 445

Noncash (gains) / losses and other, net (12) 40 87

Changes in operating accounts:

Accounts receivable originated at Target — — 157

Proceeds on sale of accounts receivable originated at Target — — 2,703

Inventory (316) (512) (504)

Other assets 227 (115) (79)

Accounts payable and accrued liabilities 534 777 247

Cash provided by operating activities—continuing operations 5,140 5,131 7,519

Cash provided by / (required for) operating activities—discontinued operations 704 (692) (999)

Cash provided by operations 5,844 4,439 6,520

Investing activities

Expenditures for property and equipment (1,438) (1,786) (1,886)

Proceeds from disposal of property and equipment 28 95 70

Proceeds from sale of businesses 1,875 — —

Change in accounts receivable originated at third parties — — 121

Proceeds from sale of accounts receivable originated at third parties — — 3,002

Cash paid for acquisitions, net of cash assumed — (20) (157)

Other investments 24 106 130

Cash provided by / (required for) investing activities—continuing operations 489 (1,605) 1,280

Cash provided by / (required for) investing activities—discontinued operations 19 (321) (1,551)

Cash provided by / (required for) investing activities 508 (1,926) (271)

Financing activities

Change in commercial paper, net — (80) (890)

Additions to long-term debt — 1,993 —

Reductions of long-term debt (85) (2,079) (3,463)

Dividends paid (1,362) (1,205) (1,006)

Repurchase of stock (3,438) — (1,461)

Stock option exercises and related tax benefit 369 373 456

Cash required for financing activities (4,516) (998) (6,364)

Effect of exchange rate changes on cash and cash equivalents — — 26

Net increase / (decrease) in cash and cash equivalents 1,836 1,515 (89)

Cash and cash equivalents at beginning of period (a) 2,210 695 784

Cash and cash equivalents at end of period (b) $ 4,046 $ 2,210 $ 695

Supplemental information

Interest paid, net of capitalized interest $ 604 $ 871 $ 1,043

Income taxes (refunded) / paid (127) 1,251 1,386

Property and equipment acquired through capital lease obligations 126 88 132

Consolidated Statements of Shareholders’ Investment

(millions, except footnotes)

Common

Stock

Shares

Stock

Par

Value

Additional

Paid-in

Capital

Retained

Earnings

Accumulated Other

Comprehensive

Income/(Loss) Total

February 2, 2013 645.3 $ 54 $ 3,925 $ 13,155 $ (576) $ 16,558

Net earnings — — — 1,971 — 1,971

Other comprehensive income — — — — (315) (315)

Dividends declared — — — (1,051) — (1,051)

Repurchase of stock (21.9) (2) — (1,476) — (1,478)

Stock options and awards 9.5 1 545 — — 546

February 1, 2014 632.9 $ 53 $ 4,470 $ 12,599 $ (891) $ 16,231

Net loss — — — (1,636) — (1,636)

Other comprehensive loss — — — — 292 292

Dividends declared — — — (1,273) — (1,273)

Repurchase of stock (0.8) — — (46) — (46)

Stock options and awards 8.1 — 429 — — 429

January 31, 2015 640.2 $ 53 $ 4,899 $ 9,644 $ (599) $ 13,997

Net earnings — — — 3,363 — 3,363

Other comprehensive income — — — — (30) (30)

Dividends declared — — — (1,378) — (1,378)

Repurchase of stock (44.7) (4) — (3,441) — (3,445)

Stock options and awards 6.7 1 449 — — 450

January 30, 2016 602.2 $ 50 $ 5,348 $ 8,188 $ (629) $ 12,957