Fluent Essays

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 Corporations Form 10-K to complete the case. You will be working with Targets FY 2015 (year ended January 30, 2016) and FY 2014 Reports. Targets Form 10-K is available on the companys website or through the SECs 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 capital40. Current ratio41. Quick ratio42. Inventory turnover43. Average days to sell inventoryMeasures of solvency (15 pts)44. Debt to assets45. Debt to equity46. Number of times interest earned47. Plant assets to long-term liabilitiesMeasures of profitability (15 pts)+548. Net margin (return on sales)49. Asset turnover50. Return on investment51. Return on equitySection 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 changeUse the FY 2014 data and the average percentage change calculated in 2 to prepare a budget for FY 2015Compare actual amounts from FY 2015 to the budgetSection 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=242992014 Report (FY ended in 15) - https://corporate.target.com/annual-reports/pdf-viewer-2014?cover=12672&parts=126782014 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 taxes72,618 $ — 72,618 51,278 14,676 — 2,129 — 4,535 882 3,653 1,20471,279 $ — 71,279 50,039 14,465 — 1,996 (391) 5,170 1,049 4,121 1,42771,960 1,341 73,301 50,568 14,643 467 2,044 (161) 5,740 684 5,056 1,741Net 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.57Diluted (loss)/earnings per share Continuing operations $ Discontinued operations Net (loss)/earnings per share $ Weighted average common shares outstanding Basic Dilutive effect of share-based awards3.83 $ (6.38) (2.56) $ 634.7 5.44.20 $ (1.13) 3.07 $ 635.1 6.75.00 (0.48) 4.52 656.7 6.6Consolidated Statements of Comprehensive Income (millions)201420132012Net (loss)/income$(1,636) $1,971 $2,999Other comprehensive income/(loss), net of taxPension and other benefit liabilities, net of (benefit)/provision for taxesof $(90), $71 and $58(139)11092Currency translation adjustment and cash flow hedges, net of provisionfor taxes of $2, $11 and $8431(425)13Other comprehensive income/(loss)292(315)105Comprehensive (loss)/income$(1,344) $1,656 $3,104Consolidated Statements of Financial Position (millions, except footnotes)January 31,2015February 1,2014AssetsCash and cash equivalents, including short-term investments of $1,520 and $3$2,210 $670Inventory8,7908,278Assets of discontinued operations1,333793Other current assets1,7541,832Total current assets14,08711,573Property and equipmentLand6,1276,143Buildings and improvements26,61425,984Fixtures and equipment5,3465,199Computer hardware and software2,5532,395Construction-in-progress424757Accumulated depreciation(15,106)(14,066)Property and equipment, net25,95826,412Noncurrent assets of discontinued operations4425,461Other noncurrent assets9171,107Total assets$41,404 $44,553Liabilities and shareholders investmentAccounts payable$7,759 $7,335Accrued and other current liabilities3,7833,610Current portion of long-term debt and other borrowings911,143Liabilities of discontinued operations103689Total current liabilities11,73612,777Long-term debt and other borrowings12,70511,429Deferred income taxes1,3211,349Noncurrent liabilities of discontinued operations1931,296Other noncurrent liabilities1,4521,471Total noncurrent liabilities15,67115,545Shareholders investmentCommon stock5353Additional paid-in capital4,8994,470Retained earnings9,64412,599Accumulated other comprehensive lossPension and other benefit liabilities(561)(422)Currency translation adjustment and cash flow hedges(38)(469)Total shareholders investment13,99716,231Total liabilities and shareholders investment$41,404 $44,553Consolidated Statements of Cash Flows(millions)201420132012Operating 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 liabilities2,449 2,129 71 7 — 285 40 — — (512) (115) 7772,694 1,996 106 58 (391) 445 121 157 2,703 (504) (79) 2133,315 2,044 102 67 (161) — 220 (217) — 160 (155) 193Cash provided by operating activities—continuing operations Cash required for operating activities—discontinued operations5,131 (692)7,519 (999)5,568 (243)Cash provided by operations4,4396,5205,325Investing 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 — — 102Cash (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—268Net increase/(decrease) in cash and cash equivalents Cash and cash equivalents at beginning of period (b)1,515 695(89) 784(10) 794Cash and cash equivalents at end of period (c)$ 2,210$695$784Supplemental 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 251Consolidated Statements of Shareholders Investment(millions, except footnotes)Common Stock SharesStock Par ValueAdditional Paid-in CapitalRetained EarningsAccumulated Other ComprehensiveIncome/(Loss)TotalJanuary 28, 2012669.3 $56 $3,487 $12,959 $(681) $ 15,821Net earnings———2,999— 2,999Other comprehensive income————105 105Dividends declared———(903)— (903)Repurchase of stock(32.2)(3)—(1,900)— (1,903)Stock options and awards8.21438—— 439February 2, 2013645.3 $54 $3,925 $13,155 $(576) $ 16,558Net earnings———1,971— 1,971Other comprehensive loss————(315) (315)Dividends declared———(1,051)— (1,051)Repurchase of stock(21.9)(2)—(1,476)— (1,478)Stock options and awards9.51545—— 546February 1, 2014632.9 $53 $4,470 $12,599 $(891) $ 16,231Net loss———(1,636)— (1,636)Other comprehensive income————292 292Dividends declared———(1,273)— (1,273)Repurchase of stock(0.8)——(46)— (46)Stock options and awards8.1—429—— 429January 31, 2015640.2 $53 $4,899 $9,644 $(599) $ 13,9972015 Report Information:Item 6. Selected Financial DataAs of or for the Fiscal Year Ended(millions, except per share data) 2015 2014 2013 2012 (a) 2011 2010Sales (b) $ 73,785 $ 72,618 $ 71,279 $ 73,301 $ 69,865 $ 67,390Net Earnings / (Loss)Continuing operations 3,321 2,449 2,694 3,315 3,049 2,920Discontinued operations 42 (4,085) (723) (316) (120) —Net earnings / (loss) 3,363 (1,636) 1,971 2,999 2,929 2,920Basic Earnings / (Loss) Per ShareContinuing operations 5.29 3.86 4.24 5.05 4.49 4.03Discontinued 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.03Diluted Earnings / (Loss) Per ShareContinuing operations 5.25 3.83 4.20 5.00 4.46 4.00Discontinued 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.00Cash dividends declared per share 2.20 1.99 1.65 1.38 1.15 0.92Total assets (c) 40,262 41,172 44,325 47,878 46,260 43,240Long-term debt, including current portion 12,760 12,725 12,494 16,260 16,127 15,638Consolidated Statements of Operations(millions, except per share data) 2015 2014 2013Sales $ 73,785 $ 72,618 $ 71,279Cost of sales 51,997 51,278 50,039Gross margin 21,788 21,340 21,240Selling, general and administrative expenses 14,665 14,676 14,465Depreciation and amortization 2,213 2,129 1,996Gain on sale (620) — (391)Earnings from continuing operations before interest expense and incometaxes 5,530 4,535 5,170Net interest expense 607 882 1,049Earnings from continuing operations before income taxes 4,923 3,653 4,121Provision for income taxes 1,602 1,204 1,427Net earnings from continuing operations 3,321 2,449 2,694Discontinued operations, net of tax 42 (4,085) (723)Net earnings / (loss) $ 3,363 $ (1,636) $ 1,971Basic earnings / (loss) per shareContinuing operations $ 5.29 $ 3.86 $ 4.24Discontinued operations 0.07 (6.44) (1.14)Net earnings / (loss) per share $ 5.35 $ (2.58) $ 3.10Diluted earnings / (loss) per shareContinuing operations $ 5.25 $ 3.83 $ 4.20Discontinued operations 0.07 (6.38) (1.13)Net earnings / (loss) per share $ 5.31 $ (2.56) $ 3.07Weighted average common shares outstandingBasic 627.7 634.7 635.1Dilutive effect of share-based awards 5.2 5.4 6.7Diluted 632.9 640.1 641.8Antidilutive shares — 3.3 2.3Consolidated Statements of Comprehensive Income(millions) 2015 2014 2013Net income / (loss) $ 3,363 $ (1,636) $ 1,971Other comprehensive income / (loss), net of taxPension and other benefit liabilities, net of (benefit) / provision for taxesof $(18), $(90), and $71 (27) (139) 110Currency translation adjustment and cash flow hedges, net of provisionfor taxes of $2, $2, and $11 (3) 431 (425)Other comprehensive income / (loss) (30) 292 (315)Comprehensive (loss) / income $ 3,333 $ (1,344) $ 1,656Consolidated Statements of Financial Position(millions, except footnotes)January 30,2016January 31,2015AssetsCash and cash equivalents, including short-term investments of $3,008 and $1,520 $ 4,046 $ 2,210Inventory 8,601 8,282Assets of discontinued operations 322 1,058Other current assets 1,161 2,074Total current assets 14,130 13,624Property and equipmentLand 6,125 6,127Buildings and improvements 27,059 26,613Fixtures and equipment 5,347 5,329Computer hardware and software 2,617 2,552Construction-in-progress 315 424Accumulated depreciation (16,246) (15,093)Property and equipment, net 25,217 25,952Noncurrent assets of discontinued operations 75 717Other noncurrent assets 840 879Total assets $ 40,262 $ 41,172Liabilities and shareholders investmentAccounts payable $ 7,418 $ 7,759Accrued and other current liabilities 4,236 3,783Current portion of long-term debt and other borrowings 815 91Liabilities of discontinued operations 153 103Total current liabilities 12,622 11,736Long-term debt and other borrowings 11,945 12,634Deferred income taxes 823 1,160Noncurrent liabilities of discontinued operations 18 193Other noncurrent liabilities 1,897 1,452Total noncurrent liabilities 14,683 15,439Shareholders investmentCommon stock 50 53Additional paid-in capital 5,348 4,899Retained earnings 8,188 9,644Accumulated other comprehensive lossPension and other benefit liabilities (588) (561)Currency translation adjustment and cash flow hedges (41) (38)Total shareholders investment 12,957 13,997Total liabilities and shareholders investment $ 40,262 $ 41,172Consolidated Statements of Cash Flows(millions) 2015 2014 2013Operating activitiesNet earnings / (loss) $ 3,363 $ (1,636) $ 1,971Earnings / (losses) from discontinued operations, net of tax 42 (4,085) (723)Net earnings from continuing operations 3,321 2,449 2,694Adjustments to reconcile net earnings to cash provided by operations:Depreciation and amortization 2,213 2,129 1,996Share-based compensation expense 115 71 106Deferred income taxes (322) 7 58Gain on sale (620) — (391)Loss on debt extinguishment — 285 445Noncash (gains) / losses and other, net (12) 40 87Changes in operating accounts:Accounts receivable originated at Target — — 157Proceeds on sale of accounts receivable originated at Target — — 2,703Inventory (316) (512) (504)Other assets 227 (115) (79)Accounts payable and accrued liabilities 534 777 247Cash provided by operating activities—continuing operations 5,140 5,131 7,519Cash provided by / (required for) operating activities—discontinued operations 704 (692) (999)Cash provided by operations 5,844 4,439 6,520Investing activitiesExpenditures for property and equipment (1,438) (1,786) (1,886)Proceeds from disposal of property and equipment 28 95 70Proceeds from sale of businesses 1,875 — —Change in accounts receivable originated at third parties — — 121Proceeds from sale of accounts receivable originated at third parties — — 3,002Cash paid for acquisitions, net of cash assumed — (20) (157)Other investments 24 106 130Cash provided by / (required for) investing activities—continuing operations 489 (1,605) 1,280Cash provided by / (required for) investing activities—discontinued operations 19 (321) (1,551)Cash provided by / (required for) investing activities 508 (1,926) (271)Financing activitiesChange 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 456Cash required for financing activities (4,516) (998) (6,364)Effect of exchange rate changes on cash and cash equivalents — — 26Net increase / (decrease) in cash and cash equivalents 1,836 1,515 (89)Cash and cash equivalents at beginning of period (a) 2,210 695 784Cash and cash equivalents at end of period (b) $ 4,046 $ 2,210 $ 695Supplemental informationInterest paid, net of capitalized interest $ 604 $ 871 $ 1,043Income taxes (refunded) / paid (127) 1,251 1,386Property and equipment acquired through capital lease obligations 126 88 132Consolidated Statements of Shareholders Investment(millions, except footnotes)CommonStockSharesStockParValueAdditionalPaid-inCapitalRetainedEarningsAccumulated OtherComprehensiveIncome/(Loss) TotalFebruary 2, 2013 645.3 $ 54 $ 3,925 $ 13,155 $ (576) $ 16,558Net earnings — — — 1,971 — 1,971Other 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 — — 546February 1, 2014 632.9 $ 53 $ 4,470 $ 12,599 $ (891) $ 16,231Net loss — — — (1,636) — (1,636)Other comprehensive loss — — — — 292 292Dividends declared — — — (1,273) — (1,273)Repurchase of stock (0.8) — — (46) — (46)Stock options and awards 8.1 — 429 — — 429January 31, 2015 640.2 $ 53 $ 4,899 $ 9,644 $ (599) $ 13,997Net earnings — — — 3,363 — 3,363Other 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 — — 450January 30, 2016 602.2 $ 50 $ 5,348 $ 8,188 $ (629) $ 12,957