Create a common-size balance sheet & income statement.
Analysis -- create an analysis over the given topics
MYSTERY COMPANY BALANCE SHEET Common-Size Balance Sheet 2015 2016 2017 heet putine Sparklines Optional 2014 2018 2014 14.557 2.859 17.416 5.612 2015 15.890 3.918 19.808 6.423 31.327136.474 22.730T 30.053 15,76311 18,21511 16.967 21.838 3.319 3.759 Consolidated Balance Sheet - USD ($) $ in Hill Cash and cash equivalents Short-term investments Totalcash Receivables Inventories Total current assets Property, plant and equipment Accumulated Depreciation Property, plant and equipment, net Goodwill Intangible assets Other long-term assets Total non-current assets TOTAL ASSETS Accounts payable Accrued liabilities Deferred revenues Total current liabilities Long-term debt Capital leases Deferred taxes liabilities Other long-term liabilities Total non-current liabilities Total liabilities Common stock Additional paid-in capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity TOTAL LIABILITIES AND SHAREHOL 2.128 23 178 54.505 16.459 9.807 2.611 29.970 65.444 20.397 10.384 3.118 33.899 8.235 5.948 2016 2017 2018 1 9,334 20.522 31750 6.647 10.464 9.500 25.981 30.986 41.250 8 .339 13.164 16.677 16 047 17 174 45.7811 60.197 7 5.101 42.441 68.573 95.770 (13,32711 (19,70711133,973) 29.114 48 866 61.797 3.784 13.350 14.548 3.371 4110 3.8695.5267.092 37.621 71.113 8 7.547 83.402 131.310 162.648 25.309 3 4.616 38.192 13.739 1 8.170 23.663 4.768 5.097 6.536 43.816 5 7.883 68.391 7.694 24.743 23,495 7.519 13.183 16.292 1994 2,386 3.301 5.798 8.535 20.301 4 5.718 50 708 64.117 1 03.601 119.099 5 17.186 21.389 26.791 4.916 8.636 19.625 (1.83711 (1.83711 (1.8371| (484) (1,035) 19.285 27.709 43.549 83,402 131,310 162,648 28.089 8 265 4.224 1.655 15.675 43.764 1.962 18.161 52,060 11.135 1.949 (1.83711 (5111 10.741 54,505 13.394 2.545 (1.83711 (72311 13.384 65.444 Balance Sheet Anal sis List your observations from the Balance Sheet Analysis here: Common Size Income Statement Fiscal year ends in December. USD in millions except per share data. 2014 2015 2016 2017 2018 Sparklines Optional MYSTERY COMPANY INCOME STATEMENT Consolidated Statement of Income - T 12 Months Ended USD ($1 shares in Millions, $ in Millions 2014 2015 2016 2017 Revenue (Sales) 88.988 1 07,006 135.987 177.866 Cost of revenue (COGS) 73,518 85,061 105,884 137,183 Gross profit 15,470 21,945 30,103 40,683 Sales, General and administrative 5,884 7,001 9,665 13,743 Other operating expenses 9.408 12,711 16,252 22,834 Total operating expenses 15,292 19,712 25,917 36,577 Operating income (EBIT) 178 2,233 4.186 4.106 Interest Expense 210 459 484848 Other income (expense) (79) (206) 190 5 48 Income before income taxes (111) 1,568 3,892 3 ,806 Provision for income taxes 157 950 1,425 769 Other income 37 (221 1961 (4) Net income 1241 596 2,371 3,033 Earnings per share Basic -0.52 1.28 5.01 6.32 Diluted -0.52 1.25 4.9 6.15 Weighted average shares outstanding Basic 462 467 474 480 Diluted 477 484 493 EBITDA 4845 8308 12492 16132 2018 232.887 173,183 59,704 18,150 2 9,133 47,283 12,421 1,417 257 11,261 1,197 9 10,073 20.68 20.14 462 487 500 2 8019 Sparklines are a helpful tool in determining trends. They are very easy to do. Click in the cell where you want the sparkline. Click "Insert" at the top of the spreadsheet Find "Sparklines" on the toolbar and click "Line" A pop-up box will appear Click in the Date Range box and then highlight the five years of data on your spreadsheet and click enter Once you do this for the first line of the Income Statement, you can down the column to do a sparkline for each item on the Income Statement 310 676 750 1169 1 502 Market Price of Mystery Company's Stoc) Use this for your Market Ratios Income Statement Analysis Take a look at the Income Statement and the Common-Size Income Statement you have just calculated. List some of the points of interest below and then talk about a few of them in your ratio analysis and summary. You should be able to use some of this information to back up your time series analysis statements. (Company's performance over the five year period). Points of Interest: List your observations from the financial statement here! I listed a couple to get you started. | 777 77277- 227e Stay C2773 Hers 2 927/27/ 27/28 This firm's revenues show a very strong upward trend - this is very good for the company. Part 3 - Ratio Analysis Liquidity: Each Ratio Analysis should include a time series analysis, a comparison to the industry, and an overall assessment for the category. Activity Leverage and Coverage: Profitability: Martet DuPont System Analysis: Part 4 - Sum it All Up MYSTERY COMPANY BALANCE SHEET Common-Size Balance Sheet 2015 2016 2017 heet putine Sparklines Optional 2014 2018 2014 14.557 2.859 17.416 5.612 2015 15.890 3.918 19.808 6.423 31.327136.474 22.730T 30.053 15,76311 18,21511 16.967 21.838 3.319 3.759 Consolidated Balance Sheet - USD ($) $ in Hill Cash and cash equivalents Short-term investments Totalcash Receivables Inventories Total current assets Property, plant and equipment Accumulated Depreciation Property, plant and equipment, net Goodwill Intangible assets Other long-term assets Total non-current assets TOTAL ASSETS Accounts payable Accrued liabilities Deferred revenues Total current liabilities Long-term debt Capital leases Deferred taxes liabilities Other long-term liabilities Total non-current liabilities Total liabilities Common stock Additional paid-in capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity TOTAL LIABILITIES AND SHAREHOL 2.128 23 178 54.505 16.459 9.807 2.611 29.970 65.444 20.397 10.384 3.118 33.899 8.235 5.948 2016 2017 2018 1 9,334 20.522 31750 6.647 10.464 9.500 25.981 30.986 41.250 8 .339 13.164 16.677 16 047 17 174 45.7811 60.197 7 5.101 42.441 68.573 95.770 (13,32711 (19,70711133,973) 29.114 48 866 61.797 3.784 13.350 14.548 3.371 4110 3.8695.5267.092 37.621 71.113 8 7.547 83.402 131.310 162.648 25.309 3 4.616 38.192 13.739 1 8.170 23.663 4.768 5.097 6.536 43.816 5 7.883 68.391 7.694 24.743 23,495 7.519 13.183 16.292 1994 2,386 3.301 5.798 8.535 20.301 4 5.718 50 708 64.117 1 03.601 119.099 5 17.186 21.389 26.791 4.916 8.636 19.625 (1.83711 (1.83711 (1.8371| (484) (1,035) 19.285 27.709 43.549 83,402 131,310 162,648 28.089 8 265 4.224 1.655 15.675 43.764 1.962 18.161 52,060 11.135 1.949 (1.83711 (5111 10.741 54,505 13.394 2.545 (1.83711 (72311 13.384 65.444 Balance Sheet Anal sis List your observations from the Balance Sheet Analysis here: Common Size Income Statement Fiscal year ends in December. USD in millions except per share data. 2014 2015 2016 2017 2018 Sparklines Optional MYSTERY COMPANY INCOME STATEMENT Consolidated Statement of Income - T 12 Months Ended USD ($1 shares in Millions, $ in Millions 2014 2015 2016 2017 Revenue (Sales) 88.988 1 07,006 135.987 177.866 Cost of revenue (COGS) 73,518 85,061 105,884 137,183 Gross profit 15,470 21,945 30,103 40,683 Sales, General and administrative 5,884 7,001 9,665 13,743 Other operating expenses 9.408 12,711 16,252 22,834 Total operating expenses 15,292 19,712 25,917 36,577 Operating income (EBIT) 178 2,233 4.186 4.106 Interest Expense 210 459 484848 Other income (expense) (79) (206) 190 5 48 Income before income taxes (111) 1,568 3,892 3 ,806 Provision for income taxes 157 950 1,425 769 Other income 37 (221 1961 (4) Net income 1241 596 2,371 3,033 Earnings per share Basic -0.52 1.28 5.01 6.32 Diluted -0.52 1.25 4.9 6.15 Weighted average shares outstanding Basic 462 467 474 480 Diluted 477 484 493 EBITDA 4845 8308 12492 16132 2018 232.887 173,183 59,704 18,150 2 9,133 47,283 12,421 1,417 257 11,261 1,197 9 10,073 20.68 20.14 462 487 500 2 8019 Sparklines are a helpful tool in determining trends. They are very easy to do. Click in the cell where you want the sparkline. Click "Insert" at the top of the spreadsheet Find "Sparklines" on the toolbar and click "Line" A pop-up box will appear Click in the Date Range box and then highlight the five years of data on your spreadsheet and click enter Once you do this for the first line of the Income Statement, you can down the column to do a sparkline for each item on the Income Statement 310 676 750 1169 1 502 Market Price of Mystery Company's Stoc) Use this for your Market Ratios Income Statement Analysis Take a look at the Income Statement and the Common-Size Income Statement you have just calculated. List some of the points of interest below and then talk about a few of them in your ratio analysis and summary. You should be able to use some of this information to back up your time series analysis statements. (Company's performance over the five year period). Points of Interest: List your observations from the financial statement here! I listed a couple to get you started. | 777 77277- 227e Stay C2773 Hers 2 927/27/ 27/28 This firm's revenues show a very strong upward trend - this is very good for the company. Part 3 - Ratio Analysis Liquidity: Each Ratio Analysis should include a time series analysis, a comparison to the industry, and an overall assessment for the category. Activity Leverage and Coverage: Profitability: Martet DuPont System Analysis: Part 4 - Sum it All Up
