need help

profileadlons
enhanced_template_v2_2_att.xls
Home>Business & Finance homework help>need help

5-Minute QuickScan

5 Minute QuickScan
Date:
Basic Quality Standards Question 2 Question 3 Question 4 Question 5 Question 6 Question 7 Decision
Company Name Ticker .OB or .PK? Mkt Cap < $500M ? Recent IPO? 3 to 5 years Positive EBIT? 3 to 5 years of positive Cash Flow from Operating Activities? 5 years of ROE > 10%? 5 Years of Debt to Equity Ratio < 1? Recent Price to Book Ratio < 2? 3 years of only positive net Tangible Assets? Keep and Conitue to Analyze or Drop Company?
AT&T T .PK no yes yes yes yes yes no yes drop company

Data Input

Stock Ticker T
Year 2014 2013 2012 2011 2010
Quote
Current Price 32.81
P/E 32.72
Market Cap 204 [in million USD]
Shares Outstanding 6.151 [in million]
Earnings
Earnings Growth Rate
Company [Next 5 Years] 3.89 [in decimal]
Financials
Income Statement
2014 2013 2012 2011 2010
Total Revenue (Sales) 132,447 128,752 127,434 126,723 124,280
Gross Profit 71,836 77,288 72,219 69,349 72,017
Operating Income 11,746 30,479 12,997 9,218 19,573
Net Income 6,518 18,553 7,539 4,184 20,179
Interest Expense, Supplemental 3,613 3,940 3,444 3,535 2,994
Diluted Normalized EPS 1.19 3.39 1.25 0.66 3.35
Balance Sheet
2014 2013 2012 2011 2010
Total Inventory 1 1 1 1 1
Total Current Assets 32,028 23,196 22,706 23,027 19,951
Total Assets 292,829 277,787 272,315 270,344 268,488
Total Current Liabilities 37,282 34,995 31,787 30,794 33,951
Total Long Term Debt 76,011 69,290 66,358 61,300 58,971
Total Liabilities 92,612 82,020 81,475 72,453 63,616
Retained Earnings (Accumulated Deficit) 27,736 31,141 22,481 25,453 31,792
Total Equity 86,924 91,482 92,695 105,797 111,950
2014 2013 2012 2011 2010
Total Common Shares Outstanding 6,495 6,495 6,495 6,495 6,495
Cash Flow Statement
2014 2013 2012 2011 2010
Cash from Operating Activities 34,796 39,176 34,743 35,222 34,405
Capital Expenditures (Ignore the negative sign) 20,944 19,465 20,110 19,530 16,554
Fundamentals
Key Ratio [10 Year Summary]
Avg P/E Book Value/ Share
2014 6.02 16.65
2013 6.48 17.41
2012 6.73 16.55
2011 5.84 17.81
2010 5.92 18.89
2009 5.84
2008 5.66
2007 5.56
2006 4.02
2005 3.85

Value Indicators

Value Indicators Worksheet
1 ROIC - ROIC > 12% since most businesses lends between 8% and 12%
Year 2014 2013 2012 2011 2010
Net Income 6,518.0 18,553.0 7,539.0 4,184.0 20,179.0
Interest 3,613.00 3,940.00 3,444.00 3,535.00 2,994.00
Long-Term Debt 76,011.00 69,290.00 66,358.00 61,300.00 58,971.00
Total Equity 86,924.0 91,482.0 92,695.0 105,797.0 111,950.0
Invested Capital 162,935.0 160,772.0 159,053.0 167,097.0 170,921.0
ROIC 0.06 0.14 0.07 0.05 0.14
2 Equity Growth Rates:
Year 2014 2013 2012 2011 2010
BVPS 16.65 17.41 16.55 17.81 18.89
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.03 -0.02 0.00 -0.04
3 EPS Growth Rates:
Year 2014 2013 2012 2011 2010
EPS 1.19 3.39 1.25 0.66 3.35
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.23 0.22 -0.02 -0.65
4 Sales Growth Rates
Year 2014 2013 2012 2011 2010
Sales (Revenue) 132,447.00 128,752.00 127,434.00 126,723.00 124,280.00
Sales Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.016 0.015 0.019 0.029
5 Operating Cash Flow (OCF) and Free Cash Flow (FCF) growth rates
Year 2014 2013 2012 2011 2010
OCF 34,796.00 39,176.00 34,743.00 35,222.00 34,405.00
CAPEX 20,944.00 19,465.00 20,110.00 19,530.00 16,554.00
FCF 13,852.00 19,711.00 14,633.00 15,692.00 17,851.00
OCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.003 -0.004 0.001 -0.112
FCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.061 -0.041 -0.027 -0.297
6 Gross Margin (>40% is sign of durable competitive advantage; <20% is indicator of highly competitive industry).
Year 2014 2013 2012 2011 2010
Sales 132447 128752 127434 126723 124280
Gross Profit 71836.00 77288.00 72219.00 69349.00 72017.00
Gross Margin 0.54 0.60 0.57 0.55 0.58
7 Operating Margin (>= industry or sector average). (Industry: … ...%; Average of competitors: … …%)
Year 2014 2013 2012 2011 2010
Sales 132447 128752 127434 126723 124280
Operating Profit 11,746.00 30,479.00 12,997.00 9,218.00 19,573.00
Operating Margin 0.09 0.24 0.10 0.07 0.16
8 Net Margin (> 20% => competitive advantage; <10% sign of competition)
Year 2014 2013 2012 2011 2010
Sales 132447 128752 127434 126723 124280
Net Income 6518.00 18553.00 7539.00 4184.00 20179.00
Net Margin 0.05 0.14 0.06 0.03 0.16
9 Free Cash Flow Margin (>= 10% indicator of competitive advantage; must not be < 3%).
Year 2014 2013 2012 2011 2010
Sales 132447.00 128752.00 127434.00 126723.00 124280.00
Free Cash Flow 13852.00 19711.00 14633.00 15692.00 17851.00
FCF Margin 0.10 0.15 0.11 0.12 0.14
10 Short-Term Financial Health
10.1 Current Ratio ≥ 2
Year 2014 2013 2012 2011 2010
Current Assets 32,028.00 23,196.00 22,706.00 23,027.00 19,951.00
Current Liabilities 37,282.00 34,995.00 31,787.00 30,794.00 33,951.00
Current Ratio 0.86 0.66 0.71 0.75 0.59
10.2 Quick Ratio ≥ 1.5
Year 2014 2013 2012 2011 2010
Current Assets 32,028.0 23,196.0 22,706.0 23,027.0 19,951.0
Inventory 1.0 1.0 1.0 1.0 1.0
Current Liabilities 37,282.0 34,995.0 31,787.0 30,794.0 33,951.0
Quick Ratio 0.86 0.66 0.71 0.75 0.59
10.3 Interest Coverage Ratio ≥ 5 minimum; but preferably ≥ 10 (except utilities ≥ 2)
Year 2014 2013 2012 2011 2010
EBIT 11,746.00 30,479.00 12,997.00 9,218.00 19,573.00
Interest Expense 3,613.00 3,940.00 3,444.00 3,535.00 2,994.00
Interest Coverage 3.3 7.7 3.8 2.6 6.5
10.4 Operating Cash Flow Ratio ≥ 1
Year 2014 2013 2012 2011 2010
OCF 34,796.00 39,176.00 34,743.00 35,222.00 34,405.00
Current Liabilities 37,282.00 34,995.00 31,787.00 30,794.00 33,951.00
OCF Ratio 0.93 1.12 1.09 1.14 1.01
11 Long-Term Financial Health
11.1 Leverage Ratio: Debt to Total Asset Ratio ≤ 0.50 except utilities for which 1.0 is acceptable
Year 2014 2013 2012 2011 2010
Long-Term Debt 76,011.0 69,290.0 66,358.0 61,300.0 58,971.0
Total Assets 292,829.0 277,787.0 272,315.0 270,344.0 268,488.0
Debt to Assets Ratio 0.26 0.25 0.24 0.23 0.22
11.2 Debt to Equity Ratio ≤ 1
Year 2014 2013 2012 2011 2010
Long-Term Debt 76,011.0 69,290.0 66,358.0 61,300.0 58,971.0
Total Equity 86,924.0 91,482.0 92,695.0 105,797.0 111,950.0
Debt - Equity Ratio 0.87 0.76 0.72 0.58 0.53
11.3 How long will it take the company to pay off its long-term debt using Cash Flow from Operations? 3 years or less.
Year 2014 2013 2012 2011 2010
Long-Term Debt 76,011.0 69,290.0 66,358.0 61,300.0 58,971.0
OCF 34,796.0 39,176.0 34,743.0 35,222.0 34,405.0
How long to payoff Debt 2.2 1.8 1.9 1.7 1.7
12 Growth in Retained Earnings
Year 2014 2013 2012 2011 2010
Retained Earnings 27736 31141 22481 25453 31792
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.03 -0.01 -0.16 -0.20
13 CAPEX Per Share
Year 2014 2013 2012 2011 2010
CAPEX 20944 19465 20110 19530 16554
Common Stock Outstanding 6495 6495 6495 6495 6495
CAPEX per Share 3.2246343341 2.9969207082 3.0962278676 3.0069284065 2.5487297921
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.06 0.06 0.10 0.18

PE Ratio Valuation

Estimating Intrinsic Value (P-E Ratio Approach), comparing with Price and Making Purchase Decision based on Margin of Safety
Step 8
Step 8A: Gathering Data
P/E Ratios
Year Latest Year -1 Year -2 Year -3 Year -4 Year -5 Year -6 Year -7 Year -8 Year -9 Year -10
P/E Ratio 32.72 6.02 6.48 6.73 5.84 5.92 5.84 5.66 5.56 4.02 3.85
Average P/E Ratio 8.06 **(Check the formula for average)
Analysts Estimate of EPS Growth Rate: 3.89 (see MSN Moneycentral Analysts Estimates)
Latest (full year) EPS = 1.19
k = 0.15 (may try different numbers based on what you know about the company's moat, financial health and historical ROA).
Current Price = 32.81
Step 8B: Using the Data
EPS at end of last year, EPS0 = 1.19
1. Average EPS growth rate = -0.23 (use the EPS growth rate over the longest period (3-, 4- or 5- year period) you can find)
2. Average Equity growth rate = -0.03 (use the BVPS growth rate over the longest period (3-, 4- or 5- year period) you can find)
3. Analysts estimate of EPS growth rate = 3.89 (see above)
Equity Growth Rate to use for Calculations = -0.23 (This the lowest of the three numbers above)
Average P/E over 5 years or 10 years = 8.06
k, the required rate of return = 0.15
Step 9: Estimating what the business is worth
1. Estimate EPS ten years from now, EPS10 = EPS0 (1+g)10 0.09
2. Multiply EPS10 by P/E and call that V10; that is V10 = (EPS10.)x(P/E). 0.72 This gives you the intrinsic value ("Sticker Price") ten years from now.
3. Calculate today’s supposed intrinsic value, V0 = V10 x [1/(1+k)10)] 0.18
Purchase Decision
Step 10: Current Price = 32.81
Current Price/Intrinsic Value = 184.05
Margin of Safety -183.05
Decision: Buy if margin of Safety is greater 50%.
Consider buying or put on Watch List if Margin of Safety is between 20% and 50%.
Put on Watch List if Margin of Safety is less than 20%.

FCF Valuation

Estimating Intrinsic Value Using Free Cash Flow Approach
1 Data
OCF = 34,796.0 Capital Expenditure = 20,944.0
a. Latest FCF 13,852.0
b. Rate of Growth of FCF over the next 10 years (use lower of 10% or company's 5-year growth rate) -0.06
c. Terminal Growth Rate (Rate of growth of FCF from year 11 in perpetuity - use average GDP growth rate) 0.03
d. Discount rate 0.15
e. Number of shares outstanding (undiluted) 6.15
Number of shares outstanding from last year 6495 (for use in calculating F-Score)
Number of shares outstanding two years ago 6495
2 Estimates
a. Estimates of FCF from year 1 to year 10
b. Estimate of intrinsic value in year 10
c. Estimate of intrinsic value today
Year: 0 1 2 3 4 5 6 7 8 9 10 11
FCF0 FCF1 FCF2 FCF3 FCF4 FCF5 FCF6 FCF7 FCF8 FCF9 FCF10 FCF11
FCF 13852.0 13000.9 12202.2 11452.5 10748.9 10088.5 9468.6 8886.9 8340.9 7828.4 7347.5 7567.9
PV of FCF 11305.2 9226.6 7530.2 6145.7 5015.7 4093.6 3340.9 2726.7 2225.3 1816.2
Intrinsic Value in Year 10 13555.5
Intrinsic Value $66,982
Intrinsic Value per share $10,889.54
3 Current market price $ 32.81
4 Ratio of current price to intrinsic value 0.00
Margin of Safety = 1.00
5 Purchase or sale decision
Buy if Margin of Safety is 50% or more.
Consider buying or putting on Watch List if Margin of Safety is between 20% and 50%.
Put on Watch List if Margin of Safety is less than 20%.

Piotroski F Score

Piotroski Screening (F-Score) Criteria
Criterion Score
1 The return on assets for the last fiscal year (Y1) is positive 1
2 Cash from operations for the last fiscal year (Y1) is positive 1
3 The return on assets ratio for the last fiscal year (Y1) is greater than the return on assets ratio for the fiscal year two years ago (Y2) 0
4 Cash from operations for the last fiscal year (Y1) is greater than income after taxes for the last fiscal year (Y1) 1
5 The long-term debt to assets ratio for the last fiscal year (Y1) is less than the long-term debt to assets ratio for the fiscal year two years ago (Y2) 0
6 The current ratio for the last fiscal year (Y1) is greater than the current ratio for the fiscal year two years ago (Y2) 1
7 The average shares outstanding for the last fiscal year (Y1) is less than or equal to the average number of shares outstanding for the fiscal year two years ago (Y2) 1
8 The gross margin for the last fiscal year (Y1) is greater than the gross margin for the fiscal year two years ago (Y2) 0
9 The asset turnover for the last fiscal year (Y1) is greater than the asset turnover for the fiscal year two years ago (Y2) 0
TOTAL 5
F-Score of 8 or 9 => strong company; low probability of bankruptcy.
F-Score between 0 and 2 => weak company; high probability of failing.

Altman Z Score

Altman Z Score:
Current Assets 32,028
Current Liabilities 37,282
Total Aseets 292,829
Operating Income 11,746
Market Cap 204
Total Liabilities 92,612
Retained Earnings 27,736
Sales 132,447
Ratios Score
X1 = Working Capital / Total Assets -0.017942212 -0.0215306544
X2 =Retained Earnings/Total Asets 0.0947173948 0.1326043527
X3 = Operating Income/Total Assets 0.0401121474 0.1323700863
X4 = Market Capitalization / Liabilities 0.0022010539 0.0013206323
X5 = Sales / Total Assets 0.4523015139 0.4523015139
Total 0.6970659308
Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + 1.0X5
Z < 1.8 => High probability of bankruptcy
1.8 < Z < 2.7 => caution; moderate probability of bankruptcy
2.7 < Z < 2.99=> proceed with caution
Z > 3 => safe; bankruptcy unlikely.