Financial managment

Calculate the Ratios (Part 1)

Using the financial statements for a mystery company provided on the assignment spreadsheet below, calculate all of the ratios for all five years.  When you use excel and link the equations in the first year, you can drag them across and excel will calculate all the other years for you.  This is a great time saver!  See the help video if you don’t know how to do this. .

Ratio Analysis Spreadsheet

Calculate Common-Size Financial Statements and Complete a Financial Statement Analysis (Part 2)

Common size financial statements will help you in your time-series analysis and will give you a better idea of what is going on in the company.  Go to the Income Statement and the Balance Sheet tabs and create a common-size statement out to the side of the original ones.  (I have included a blank format for you). Then complete a financial statement analysis listing a few points of interest.  You should also mention some of your findings in your

Summary (Part 4)

.  (A financial statement analysis is basically just taking a look at the financial statements to get an overall picture of the company.  For instance, by looking at the amount of revenues generated, you can get an idea about the size of a company and by looking at multiple years, you can learn if the revenues are growing or declining.  Both are important bits of information about the company.)

Analyze and Explain the Ratios  (Part 3)

On the same spreadsheet, complete an analysis for each ratio category similar to the one on page 97 of the text book .  A ratio analysis includes a trend analysis, an industry comparison and a sentence summarizing your overall view of the company’s financial performance in that category. Please be thorough, this is an analysis.  

Summary (Part 4)

This is where you summarize all of the information you have attained so far about the company.   Using the information you have gained from the financial statement analysis, the common-sized financial statements, and the ratio analysis; state your impression of the overall financial condition of the company and your recommendations of what needs to be accomplished going forward.  (I am NOT asking you whether or not you would invest in this company, I’m asking if you were the manager of this company, what you would do.) 

Use your critical thinking skills here.  Back up your statements with data (facts). Thoroughly define the problems. Be aware of bias and fallacious reasoning (Don’t just tell me a bunch of things that might or might not be true, I’m looking for facts based on your observations of the firm’s financial statements and ratios).  Consider and evaluate multiple alternatives and their consequences.  Draw a well reasoned conclusion.

Income

< /

td>

Statement

2014

2016 2017 2018

)

88

)

,183

5,884

, also called Statement of Earnings

(expense)

Other income 37

9

and the Number of Shares Outstanding

-0.52

Basic

Diluted 462

484

s

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				201					5					2016					2017					2018	2015		Optional	The Income Statement sumarizes a company’s revenues and expenses,
Revenue (				Sales	88,	9	107,006	135,987	177,866	232,887	Based on the equation: Revenues – Expenses = Net Income
Cost of revenue (		COGS	73,518	85,061	10	5,884	1	37	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	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
	Other income	(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.)
(22)	(96)	(4)	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
	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	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
	462	467	474	480	487	Find “Sparklines” on the toolbar and click “Line”
477	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 Margin
This firm’s revenues show a very strong upward trend – this is very good for the company.

Balance Sheet

Sparklines

2014 2015 2016 2017 2018 2014 2015 2016 2017 2018 Optional

: Current ratio and

5 5 5 5 5

(1,837) (1,837) (1,837) (1,837)

54,505 65,444 83,402 131,310 162,648

MYSTERY COMPANY BALANCE SHEET	Common-Size Balance Sheet	ABOUT THE BALANCE SHEET
Consolidated Balance Sheet – USD ($) $ in Millions	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	Quick Ratio	Solvency Ratios: Debt Ratio, Debt-to-Equity,	Times Interest Earned
Common stock
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)	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	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

Ratios

/

2014 2015 2016 2017 2018

Gross Profits / Sales

/ Sales

/ Sales

Earnings Available to SH /

)

Earnings Available to SH / Total Assets

)

Earnings Available to SH /

Liquidity Ratios

/

Quick Ratio

/ Current Liabilities

/ Total Assets

Total Assets /

Times Interest Earned

/ Interest Expense

/

)

Turnover

COGS / Accounts Payable

365 /

COGS / Inventory

365 / Inventory Turnover

Sales / Total Assets

/ EPS

Market Price per Share /

/

Net Margin

/

2.9%

From above /

2.05

ROA

/

6.1%

Equity Multiplier (FLM) From above /

2.80

ROE

/

16.9%

Part 1 – Ratio Calculations
Ratio Formulas (To save you a little bit of time)	Ratios for Mystery Company	Industry
Numerators	Denominators	Averages
Profitability Ratios
Gross Profit Margin	27.4%
Operating Profit Margin	Operating Profits	6.0%
Net Profit Margin				Earnings Available to SH		2.9%
Earnings per Share (EPS)	# of shares outstanding	3.92
Return on Assets (	ROA		6.1%
Return on Equity (	ROE	Common Stock Equity		16.9%
Current Ratio	Current Assets		Current Liabilities	0.98
Current Assets-	Inventory	0.32
Debt Ratios
Debt to Total Assets	Total Liabilities (Debt)	64.0%
	Equity Multiplier (FLM)	Shareholder Equity		2.80
EBIT (Operating Income)	8.10
Activity Ratios
Average Collection Period	Accounts Receivable	(Sales /		365	4.78
	Accounts Payable	8.07
Days AP	AP Turnover	45.24
	Inventory Turnover	7.07
Days Inventory	51.64
Total Asset Turnover		2.05
Market Ratios
Price/Earnings (P/E)		Market Price per Share	16.48
Market/Book (M/B)	(Book Value/Outstanding Shares)	3.21
Modified DuPont Analysis
		From above	=D32
Total Asset Turnover (TAT)	=D15
Net Profit Margin * TAT	=D38 * D39
=D26
ROA * FLM	=D40*D41
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