Answered>Order 27581
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
"Not answered?"
