Financial managment

profileMaylat15
RatioAnalysisSpreadsheet.xlsx
Home>Business & Finance homework help>Management homework help>Financial managment

Income Statement

MYSTERY COMPANY INCOME STATEMENT Common Size Income Statement ABOUT THE INCOME STATEMENT
Consolidated Statement of Income - USD ($) shares in Millions, $ in Millions 12 Months Ended Fiscal year ends in December. USD in millions except per share data. Sparklines
2014 2015 2016 2017 2018 2014 2015 2016 2017 2018 Optional The Income Statement sumarizes a company's revenues and expenses,
Revenue (Sales) 88,988 107,006 135,987 177,866 232,887 Based on the equation: Revenues - Expenses = Net Income
Cost of revenue (COGS) 73,518 85,061 105,884 137,183 173,183
Gross profit 15,470 21,945 30,103 40,683 59,704 The Income Statement is also known as a Profit and Loss Statement
Sales, General and administrative 5,884 7,001 9,665 13,743 18,150
Other operating expenses 9,408 12,711 16,252 22,834 29,133 Most income statements cover a 1-year period
Total operating expenses 15,292 19,712 25,917 36,577 47,283
Operating income (EBIT) 178 2,233 4,186 4,106 12,421 All Income Statements have 3 parts: (they may be named differently, but will always give the same information)
Interest Expense 210 459 484 848 1,417 First Part = Gross Profits, also called Statement of Earnings
Other income (expense) (79) (206) 190 548 257 The first part always subtracts the COGS from the total revenue
Income before income taxes (111) 1,568 3,892 3,806 11,261 Second Part = Operating Results, also called Statement of Operations,
Provision for income taxes 167 950 1,425 769 1,197 or EBIT (Earnings before interest and taxes.)
Other income 37 (22) (96) (4) 9 The second part always subtracts the operating expenses from the gross profit
Net income (241) 596 2,371 3,033 10,073 Third Part = Net Profits, also called Statement of Income
Earnings per share which usually includes EPS and the Number of Shares Outstanding
Basic -0.52 1.28 5.01 6.32 20.68 Sparklines are a helpful tool in determining trends. They are very easy to do. The third part always subtracts out interest and taxes
Diluted -0.52 1.25 4.9 6.15 20.14 Click in the cell where you want the sparkline.
Weighted average shares outstanding Click "Insert" at the top of the spreadsheet
Basic 462 467 474 480 487 Find "Sparklines" on the toolbar and click "Line"
Diluted 462 477 484 493 500 A pop-up box will appear The Common-Size Income Statement expresses the Income Statement as a percentage of Total Revenue (Sales).
EBITDA 4845 8308 12492 16132 28019 Click in the Date Range box and then highlight the five years of data on your (Divide everything by Total Sales)
spreadsheet and click enter
Market Price of Mystery Company's Stock 310 676 750 1169 1502 Once you do this for the first line of the Income Statement, you can drag the formula Earnings Available to Shareholders (Text page 55 or under section titled "Income Statement" in Chapter 3.1)
Use this for your Market Ratios down the column to do a sparkline for each item on the Income Statement. Earnings Available to Shareholders = Net Income less Preferred Stock Dividends (if the company has Preferred Stock)
If the company does not have preferred stock: Earnings Available to Shareholders = Net Income
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.
The Common-Size Income Statement computes the Gross, Operating and Net Margins
This firm's revenues show a very strong upward trend - this is very good for the company.

Balance Sheet

MYSTERY COMPANY BALANCE SHEET Common-Size Balance Sheet Sparklines ABOUT THE BALANCE SHEET
Consolidated Balance Sheet - USD ($) $ in Millions 2014 2015 2016 2017 2018 2014 2015 2016 2017 2018 Optional The balance sheet reports a firm's total assets, and how these assets are financed, through a mix of debt and equity.
Cash and cash equivalents 14,557 15,890 19,334 20,522 31,750 Remember the Accounting Equation: Assets = Liabilities + Shareholder's Equity
Short-term investments 2,859 3,918 6,647 10,464 9,500
Total cash 17,416 19,808 25,981 30,986 41,250 That mix of debt and equity used to finance a firm's assets is called the firm's CAPITAL STRUCTURE - You will see this term later!
Receivables 5,612 6,423 8,339 13,164 16,677 A standard balance shee has three parts: assets, liabilities, and equity. The assets and liabilities are divided into short-term and long-term.
Inventories 8,299 10,243 11,461 16,047 17,174 The balance sheet is the only financial statement which applies to a single point in time.
Total current assets 31,327 36,474 45,781 60,197 75,101
Property, plant and equipment 22,730 30,053 42,441 68,573 95,770 There is a very important distinction between Short-Term and Long-Term
Accumulated Depreciation (5,763) (8,215) (13,327) (19,707) (33,973) Short-term assets and liabilities refer to a firm's operations
Property, plant and equipment, net 16,967 21,838 29,114 48,866 61,797 Short-term assets and liabilities are expected to be converted to cash or paid off within 1 year
Goodwill 3,319 3,759 3,784 13,350 14,548 Short-term assets should more than offset short-term liabilities, which represents a firm's liquidity
Intangible assets 764 762 854 3,371 4,110
Other long-term assets 2,128 2,611 3,869 5,526 7,092 Long-term assets and liabilities last longer than a year and are considered fixed
Total non-current assets 23,178 28,970 37,621 71,113 87,547 Stockholder's equity is assumed to have an infinite life - very long-term
TOTAL ASSETS 54,505 65,444 83,402 131,310 162,648 A firm's short and long-term assets combined should more than offset a firm's long-term liabilities,
Accounts payable 16,459 20,397 25,309 34,616 38,192 which represents the solvency of a firm
Accrued liabilities 9,807 10,384 13,739 18,170 23,663
Deferred revenues 1,823 3,118 4,768 5,097 6,536 Contrary to what your textbook states, the liquidity of a firm does NOT refer to the overall financial position (solvency) of a firm!
Total current liabilities 28,089 33,899 43,816 57,883 68,391 While a lower liquidity could possibly be a precursor or early sign of financial distress, it does not mean the company is headed
Long-term debt 8,265 8,235 7,694 24,743 23,495 for banckruptcy. You would need more information to determine that.
Capital leases 4,224 5,948 7,519 13,183 16,292 You would have to look at the strength of the firm's long-term assets to determine the solvency of the firm.
Deferred taxes liabilities 1,531 2,016 1,787 1,994 2,386 Many firm's go through periods of low liquidity and are more than able to rebound from this.
Other long-term liabilities 1,655 1,962 3,301 5,798 8,535 Remember: Liquidity is only about short-term obligations, and Solvency is about the long-term obligations
Total non-current liabilities 15,675 18,161 20,301 45,718 50,708
Total liabilities 43,764 52,060 64,117 103,601 119,099 Liquidity Ratios: Current ratio and Quick Ratio Solvency Ratios: Debt Ratio, Debt-to-Equity, Times Interest Earned
Common stock 5 5 5 5 5
Additional paid-in capital 11,135 13,394 17,186 21,389 26,791 Healthy firm's are normally both solvent and possess adequate liquidity. However, it is very possible for a healthy company
Retained earnings 1,949 2,545 4,916 8,636 19,625 to have low liquidity. It is also possible for a firm with a very healthy liquidity to go bankrupt because it could not cover its
Treasury stock (1,837) (1,837) (1,837) (1,837) (1,837) long-term debt.
Accumulated other comprehensive income (511) (723) (985) (484) (1,035)
Total stockholders' equity 10,741 13,384 19,285 27,709 43,549 Long-term assets represent the value of a firm's property, equipment and other capital assets and investments, minus depreciation
TOTAL LIABILITIES AND SHAREHOLDERS EQUITY 54,505 65,444 83,402 131,310 162,648 what the firm owns - used for the production of goods or services
Short-term assets are a firm's cash, short-term investments, receivables, and inventories,
a firm's revenue from operations - used to facilitate day-to-day operational expenses and short-term investments.
Balance Sheet Analysis
List your observations from the Balance Sheet Analysis here: The textbook's definitions of Income Statement and Balance Sheet are a little confusing.
Income Statement = Financial summary of a firms operating results
Balance Sheet = Summary of firm's financial position
Just remember that the Income Statement summarizes the firm's income
and the Balance Sheet balances the firm's Assets with its Debt and Equity
The Common-Size Balance Sheet expresses the Balance Sheet as a percentage of Total Assets
Specifically, the asset part of the Balance Sheet is expressed in terms of Total Assets
and the Liabilities and Equity portion is expressed in terms of Total Liabilities and Shareholder's Equity
But, since Total Assets = Total Liabilities and Shareholder's Equity, it's easier to just say it is a percentage of Total Assets.
(Divide everything by Total Assets or Total Liabilities and Shareholder's Equity)

Ratio Analysis

Part 1 - Ratio Calculations
Ratios Ratio Formulas (To save you a little bit of time) Ratios for Mystery Company Industry
Numerators / Denominators 2014 2015 2016 2017 2018 Averages
Profitability Ratios
Gross Profit Margin Gross Profits / Sales 27.4%
Operating Profit Margin Operating Profits / Sales 6.0%
Net Profit Margin Earnings Available to SH / Sales 2.9%
Earnings per Share (EPS) Earnings Available to SH / # of shares outstanding 3.92
Return on Assets (ROA) Earnings Available to SH / Total Assets 6.1%
Return on Equity (ROE) Earnings Available to SH / Common Stock Equity 16.9%
Liquidity Ratios
Current Ratio Current Assets / Current Liabilities 0.98
Quick Ratio Current Assets-Inventory / Current Liabilities 0.32
Debt Ratios
Debt to Total Assets Total Liabilities (Debt) / Total Assets 64.0%
Equity Multiplier (FLM) Total Assets / Shareholder Equity 2.80
Times Interest Earned EBIT (Operating Income) / Interest Expense 8.10
Activity Ratios
Average Collection Period Accounts Receivable / (Sales / 365) 4.78
Accounts Payable Turnover COGS / Accounts Payable 8.07
Days AP 365 / AP Turnover 45.24
Inventory Turnover COGS / Inventory 7.07
Days Inventory 365 / Inventory Turnover 51.64
Total Asset Turnover Sales / Total Assets 2.05
Market Ratios
Price/Earnings (P/E) Market Price per Share / EPS 16.48
Market/Book (M/B) Market Price per Share / (Book Value/Outstanding Shares) 3.21
Modified DuPont Analysis /
Net Margin From above / =D32 2.9%
Total Asset Turnover (TAT) From above / =D15 2.05
ROA Net Profit Margin * TAT / =D38 * D39 6.1%
Equity Multiplier (FLM) From above / =D26 2.80
ROE ROA * FLM / =D40*D41 16.9%
Part 2 - Common-Size Statements Go to "Income Statement" tab and "Balance Sheet" tab to complete these.
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:
Market:
DuPont System Analysis:
Part 4 - Sum it All Up