1 PAGE Financial Analysis CALCULATIONS SPREADHEET Fiscal 2015-2019 NEEDED
Mattel Incorporated Spreadsheet Assignment
Instructions - MGMT 5355
1. Read “Mattel Incorporated in 2019: Hard Times in the Toy Industry” (Case 7 in Textbook).
2. Complete the Mattel Incorporated Spreadsheet with your calculations of the indicated (green boxes) financial ratios. Calculate your financial ratios for the spreadsheet from the PDF files of the company’s Income Statements and Balance Sheets for 2015-2019 (Mergent Online Financial Statements). NOTE: Use the “Commonly Used Financial Ratios” PDF file formulas for the financial ratio calculations. TIP: Use n=4 in your CAGR calculation. NOTE: Use the Net Income (Loss) per share – diluted as the answer for the EPS (diluted) line on your spreadsheet.
NOTE: Total Debt (Liabilities) can be calculated two ways: (1) Total Current Liabilities line PLUS Total Noncurrent Liabilities line. OR (2) Total Assets line MINUS Total Stockholder’s Equity line.
Submission Instructions: • Upload one file to Blackboard (the completed spreadsheet in .XLS or .XLSX format) on 11/18/2022 by 2pm CST
Sheet1 MATTEL INCORPORATED Financial Analysis Fiscal 2015-2019 Numbers in Thousands, except where indicated 20152016201720182019 INCOME STATEMENT Net Sales ($ actual)$5,702,613,000 Net Sales (in thousands)$5,702,613 Net Sales (in millions)$5,703 Net Sales (in billions)$5.70 Net Sales 1 Year Growth Rate--- Net Sales CAGR 2015-2019 Gross Profit$2,806,358 Gross Margin49.21% Operating Income$540,922 Operating Margin9.49% Net Income (NI)$369,416 NI 1 Year Growth Rate--- Return on Sales (ROS)6.48% Return on Assets (ROA)5.64% Return on Equity (ROE)14.03% EPS - Diluted ($ per share)$1.08 20152016201720182019 BALANCE SHEET Current Assets$3,196,863 Inventories (Net)$587,521 Current Liabilities$1,645,572 Current Ratio1.94 Quick Ratio1.59 Total Debt (Liabilities)$3,919,435 Total Assets$6,552,689 Total Stockholder Equity$2,633,254 Debt to Asset Ratio (D/A)59.81% Debt to Equity Ratio (D/E)148.84% Total Revenues$5,702,613 Total Assets$6,552,689 Fixed Assets (PP&E, Net)$741,147 Total Asset Turnover (TAT)0.87 Fixed Asset Turnover (FAT)7.69 NOTE: Mattel Incorporated's 2019 fiscal year ends 12/31/2019 9/16/2020 Mergent Online - As Reported : Mattel Inc https://www.mergentonline.com/companyfinancials.php?pagetype=asreported&compnumber=5302&period=Annuals&dataarea=PL&range=5¤c… 1/2 Mattel Inc (NMS: MAT) Exchange rate used is that of the Year End reported date As Reported Annual Income Statement Report Date 12/31/2019 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Currency USD USD USD USD USD Audit Status Not Qualified Not Available Not Qualified Not Qualified Not Qualified Consolidated Yes Yes Yes Yes Yes Scale Thousands Thousands Thousands Thousands Thousands Net sales 4,504,571 4,510,852 4,881,951 5,456,650 5,702,613 Cost of sales 2,523,792 2,716,127 3,061,122 2,902,259 2,896,255 Gross profit (loss) 1,980,779 1,794,725 1,820,829 2,554,391 2,806,358 Advertising & promotion expenses 551,517 526,436 642,286 634,947 717,852 Other selling & administrative expenses 1,390,022 1,504,796 1,521,366 1,400,211 1,547,584 Operating income (loss) 39,240 (236,507) (342,823) 519,233 540,922 Interest expense 201,044 181,886 105,214 95,118 85,270 Interest income 6,166 6,463 7,777 9,144 7,230 Other non-operating income (expense), net (2,650) (7,331) (64,727) (23,517) 1,033 Income (loss) before income taxes - U.S. operations (330,139) (342,643) (273,444) 9,179 (3,435) Income (loss) before income taxes - foreign operations 171,851 (76,618) (231,543) 400,563 467,350 Income (loss) before income taxes (158,288) (419,261) (504,987) 409,742 463,915 Current income tax provision (benefit) - federal 5,520 3,935 (3,153) (3,041) (1,405) Current income tax provision (benefit) - state 2,170 1,340 1,885 2,455 1,946 Current income tax provision (benefit) - foreign 68,837 91,930 113,315 91,070 89,825 Total current income tax provision (benefit) 76,527 97,205 112,047 90,484 90,366 Deferred income tax provision (benefit) - federal (5,610) (4,630) 418,341 (4,624) (3,802) Deferred income tax provision (benefit) - state 68 (3,368) 38,450 2,623 (2,200) Deferred income tax provision (benefit) - foreign (15,761) 22,525 (19,989) 3,237 10,135 Powered by Clearbit 9/16/2020 Mergent Online - As Reported : Mattel Inc https://www.mergentonline.com/companyfinancials.php?pagetype=asreported&compnumber=5302&period=Annuals&dataarea=PL&range=5¤c… 2/2 Report Date 12/31/2019 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Currency USD USD USD USD USD Audit Status Not Qualified Not Available Not Qualified Not Qualified Not Qualified Consolidated Yes Yes Yes Yes Yes Scale Thousands Thousands Thousands Thousands Thousands Total deferred income tax provision (benefit) (21,303) 14,527 436,802 1,236 4,133 Provision (benefit) for income taxes 55,224 111,732 548,849 91,720 94,499 Net income (loss) (213,512) (530,993) (1,053,836) 318,022 369,416 Less net income allocable to participating restricted stock units - - - 1,377 3,179 Net income (loss) applicable to common shares - (530,993) (1,053,836) 316,645 366,237 Weighted average shares outstanding - basic 346,127 345,012 343,564 341,480 339,172 Weighted average shares outstanding - diluted 346,127 345,012 343,564 344,233 339,748 Year end shares outstanding 346,800 345,300 343,800 342,400 339,700 Net income (loss) per share - basic (0.62) (1.54) (3.07) 0.93 1.08 Net income (loss) per share - diluted (0.62) (1.54) (3.07) 0.92 1.08 Dividends declared per common share - - 0.91 1.52 1.52 Total number of employees 24,000 27,000 28,000 32,000 31,000 Number of common stockholders 25,000 26,000 27,000 28,000 29,000 Foreign currency translation adjustments 18,919 (106,651) (191,267) (101,539) (213,797) 9/16/2020 Mergent Online - As Reported : Mattel Inc https://www.mergentonline.com/companyfinancials.php?pagetype=asreported&compnumber=5302&period=Annuals&dataarea=BS&range=5¤c… 1/2 Mattel Inc (NMS: MAT) Exchange rate used is that of the Year End reported date As Reported Annual Balance Sheet Report Date 12/31/2019 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Currency USD USD USD USD USD Audit Status Not Qualified Not Available Not Qualified Not Qualified Not Qualified Consolidated Yes Yes Yes Yes Yes Scale Thousands Thousands Thousands Thousands Thousands Cash & equivalents 630,028 594,481 1,079,221 869,531 892,814 Accounts receivable, gross 954,859 992,083 1,154,010 1,136,617 1,169,499 Allowances 18,500 22,000 25,400 21,400 24,400 Accounts receivable, net 936,359 970,083 1,128,610 1,115,217 1,145,099 Raw materials & work in process 103,123 115,966 101,690 112,327 105,917 Finished goods 392,381 426,923 499,014 501,471 481,604 Inventories 495,504 542,889 600,704 613,798 587,521 Prepaid expenses & other current assets 186,083 244,987 303,053 341,518 571,429 Total current assets 2,247,974 2,352,440 3,111,588 2,940,064 3,196,863 Land 25,112 25,023 25,114 25,113 27,049 Buildings 302,956 294,227 303,495 280,226 275,266 Machinery & equipment 812,509 875,308 902,861 828,969 764,657 Software 364,391 400,488 384,568 356,622 331,251 Tools, dies & molds 747,706 831,743 887,442 869,385 840,586 Capital leases - 23,927 24,279 23,970 23,970 Leasehold improvements 183,250 240,636 213,238 261,254 245,082 Property, plant & equipment, gross 2,435,924 2,691,352 2,740,997 2,645,539 2,507,861 Less: accumulated depreciation 1,885,785 2,033,757 1,955,712 1,871,574 1,766,714 Property, plant & equipment, net 550,139 657,595 785,285 773,965 741,147 Right-of-use assets, net 303,187 - - - - Goodwill 1,390,714 1,386,424 1,396,669 1,387,628 1,384,520 Identifiable intangibles, net 553,114 587,528 639,203 201,859 212,161 Deferred income taxes 67,900 49,937 76,750 508,363 317,391 Nonamortizable identifiable intangibles - - - 458,589 488,144 Other noncurrent assets 212,198 209,541 229,008 223,326 212,463 Powered by Clearbit 9/16/2020 Mergent Online - As Reported : Mattel Inc https://www.mergentonline.com/companyfinancials.php?pagetype=asreported&compnumber=5302&period=Annuals&dataarea=BS&range=5¤c… 2/2 Report Date 12/31/2019 12/31/2018 12/31/2017 12/31/2016 12/31/2015 Currency USD USD USD USD USD Audit Status Not Qualified Not Available Not Qualified Not Qualified Not Qualified Consolidated Yes Yes Yes Yes Yes Scale Thousands Thousands Thousands Thousands Thousands Total other noncurrent assets 833,212 847,006 944,961 1,392,137 1,230,159 Total assets 5,325,226 5,243,465 6,238,503 6,493,794 6,552,689 Short-term borrowings - 4,176 - 192,168 16,914 Current portion of long-term debt - - 250,000 - 300,000 Accounts payable 459,357 537,965 572,166 664,857 651,681 Accrued advertising & promotion 93,804 86,935 165,572 85,116 75,991 Lease liability 74,065 - - - - Accrued royalties 94,228 108,109 111,669 107,077 122,153 Accrued taxes other than income taxes - 54,317 74,626 67,555 66,848 Accrued incentive compensation 122,923 87,086 20,218 18,830 52,721 Other accrued liabilities 384,493 363,974 420,054 350,248 340,512 Accrued liabilities 769,513 700,421 792,139 628,826 658,225 Income taxes payable 48,037 10,046 9,498 19,722 18,752 Total current liabilities 1,276,907 1,252,608 1,623,803 1,505,573 1,645,572 Senior notes 2,900,000 2,900,000 3,150,000 2,150,000 2,100,000 Debt issuance costs & debt discount (53,249) (48,277) (26,881) (15,729) - Total debt 2,846,751 2,851,723 3,123,119 2,134,271 - Less: current portion - - 250,000 - 300,000 Long-term debt 2,846,751 2,851,723 2,873,119 2,134,271 1,800,000 Noncurrent lease liabilities 270,853 - - - - Benefit plan liabilities 212,280 166,289 168,539 192,466 195,916 Noncurrent tax liabilities 125,515 150,960 124,330 96,871 108,808 Other noncurrent liabilities 101,206 152,420 191,257 156,831 169,139 Total other noncurrent liabilities 439,001 469,669 484,126 446,168 473,863 Total noncurrent liabilities 3,556,605 3,321,392 3,357,245 2,580,439 2,273,863 Common stock 441,369 441,369 441,369 441,369 441,369 Additional paid-in capital 1,825,569 1,812,682 1,808,391 1,790,832 1,789,870 Treasury stock at cost 2,318,921 2,354,617 2,389,877 2,426,749 2,494,901 Retained earnings (accumulated deficit) 1,413,181 1,629,257 2,179,358 3,545,359 3,745,815 Derivative instruments, net of tax 11,041 11,411 (21,098) 17,469 15,363 Available for-sale securities (8,260) (6,547) (2,799) 3,149 - Defined benefit pension, net of tax (169,857) (142,763) (143,213) (157,704) (159,858) Currency translation adjustments (702,408) (721,327) (614,676) (805,943) (704,404) Accumulated other comprehensive income (loss) (869,484) (859,226) (781,786) (943,029) (848,899) Total stockholders' equity (deficit) 491,714 669,465 1,257,455 2,407,782 2,633,254 US INDUSTRY (NAICS) REPORT 42392 Toy & Craft Supplies Wholesaling in the US Cut and paste: Industry revenue is anticipated to decline as vertical integration persists Claire O'Connor | May 2020 IBISWorld.com +1-800-330-3772 info@IBISWorld.com Contents About This Industry...........................................5 Industry Definition..........................................................5 Major Players................................................................. 5 Main Activities................................................................5 Supply Chain...................................................................6 Similar Industries........................................................... 6 Related International Industries....................................6 Industry at a Glance.......................................... 7 Executive Summary..................................................... 10 Industry Performance..................................... 11 Key External Drivers.....................................................11 Current Performance.................................................