need help

profileadlons
amzon.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?
Amazon AMZN No > 500 No No yes No yes yes yes Keep and Conitue to Analyze

Data Input

Stock Ticker amazon
Year 2014 2013 2012 2011 2010
Quote
Current Price 651.36
P/E -1252.62
Market Cap 181680 [in million USD]
Shares Outstanding 465 [in million]
Earnings
Earnings Growth Rate 33.46%
Company [Next 5 Years] -3743.62 [in decimal]
Financials
Income Statement
2014 2013 2012 2011 2010
Total Revenue (Sales) 88988 74452 61093 48077 34204
Gross Profit 26236 20271 15122 10789 7643
Operating Income 178 745 676 862 1406
Net Income -241 274 -39 631 1152
Interest Expense, Supplemental 210 141 92 65 39
Diluted Normalized EPS -0.52 0.59 -0.09 1.37 2.53
Balance Sheet
2014 2013 2012 2011 2010
Total Inventory 8299 7411 6031 4992 3202
Total Current Assets 31327 24625 21296 17490 13747
Total Assets 54505 40159 32555 25278 18797
Total Current Liabilities 28089 22980 19002 14896 10372
Total Long Term Debt 15675 7433 5361 2625 1561
Total Liabilities 43764 30413 24363 17521 11933
Retained Earnings (Accumulated Deficit) 1949 2190 1916 1955 1324
Total Equity 10741 9746 8192 7757 6864
2014 2013 2012 2011 2010
Total Common Shares Outstanding 465 459 454 455 451
Cash Flow Statement
2014 2013 2012 2011 2010
Cash from Operating Activities 6842 5475 4180 3903 3495
Capital Expenditures (Ignore the negative sign) 4893 3444 3785 1811 979
Fundamentals
Key Ratio [10 Year Summary]
Avg P/E Book Value/ Share
2014 -1252.62 23.1
2013 1416.98 21.23
2012 -2788.56 18.04
2011 91.73 17.05
2010 73.64 15.22
2009 80.84
2008 35.24
2007 107.15
2006 57.88
2005 41.8

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 (241.0) 274.0 (39.0) 631.0 1,152.0
Interest 210.00 141.00 92.00 65.00 39.00
Long-Term Debt 15,675.00 7,433.00 5,361.00 2,625.00 1,561.00
Total Equity 10,741.0 9,746.0 8,192.0 7,757.0 6,864.0
Invested Capital 26,416.0 17,179.0 13,553.0 10,382.0 8,425.0
ROIC -0.00 0.02 0.00 0.07 0.14
2 Equity Growth Rates:
Year 2014 2013 2012 2011 2010
BVPS 23.1 21.23 18.04 17.05 15.22
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.11 0.11 0.13 0.09
3 EPS Growth Rates:
Year 2014 2013 2012 2011 2010
EPS -0.52 0.59 -0.09 1.37 2.53
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.52 -0.47 -1.40 -0.65
4 Sales Growth Rates
Year 2014 2013 2012 2011 2010
Sales (Revenue) 88,988.00 74,452.00 61,093.00 48,077.00 34,204.00
Sales Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.270 0.228 0.207 0.195
5 Operating Cash Flow (OCF) and Free Cash Flow (FCF) growth rates
Year 2014 2013 2012 2011 2010
OCF 6,842.00 5,475.00 4,180.00 3,903.00 3,495.00
CAPEX 4,893.00 3,444.00 3,785.00 1,811.00 979.00
FCF 1,949.00 2,031.00 395.00 2,092.00 2,516.00
OCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.183 0.206 0.279 0.250
FCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.062 -0.023 1.221 -0.040
6 Gross Margin (>40% is sign of durable competitive advantage; <20% is indicator of highly competitive industry).
Year 2014 2013 2012 2011 2010
Sales 88988 74452 61093 48077 34204
Gross Profit 26236.00 20271.00 15122.00 10789.00 7643.00
Gross Margin 0.29 0.27 0.25 0.22 0.22
7 Operating Margin (>= industry or sector average). (Industry: … ...%; Average of competitors: … …%)
Year 2014 2013 2012 2011 2010
Sales 88988 74452 61093 48077 34204
Operating Profit 178.00 745.00 676.00 862.00 1,406.00
Operating Margin 0.00 0.01 0.01 0.02 0.04
8 Net Margin (> 20% => competitive advantage; <10% sign of competition)
Year 2014 2013 2012 2011 2010
Sales 88988 74452 61093 48077 34204
Net Income -241.00 274.00 -39.00 631.00 1152.00
Net Margin -0.00 0.00 -0.00 0.01 0.03
9 Free Cash Flow Margin (>= 10% indicator of competitive advantage; must not be < 3%).
Year 2014 2013 2012 2011 2010
Sales 88988.00 74452.00 61093.00 48077.00 34204.00
Free Cash Flow 1949.00 2031.00 395.00 2092.00 2516.00
FCF Margin 0.02 0.03 0.01 0.04 0.07
10 Short-Term Financial Health
10.1 Current Ratio ≥ 2
Year 2014 2013 2012 2011 2010
Current Assets 31,327.00 24,625.00 21,296.00 17,490.00 13,747.00
Current Liabilities 28,089.00 22,980.00 19,002.00 14,896.00 10,372.00
Current Ratio 1.12 1.07 1.12 1.17 1.33
10.2 Quick Ratio ≥ 1.5
Year 2014 2013 2012 2011 2010
Current Assets 31,327.0 24,625.0 21,296.0 17,490.0 13,747.0
Inventory 8,299.0 7,411.0 6,031.0 4,992.0 3,202.0
Current Liabilities 28,089.0 22,980.0 19,002.0 14,896.0 10,372.0
Quick Ratio 0.82 0.75 0.80 0.84 1.02
10.3 Interest Coverage Ratio ≥ 5 minimum; but preferably ≥ 10 (except utilities ≥ 2)
Year 2014 2013 2012 2011 2010
EBIT 178.00 745.00 676.00 862.00 1,406.00
Interest Expense 210.00 141.00 92.00 65.00 39.00
Interest Coverage 0.8 5.3 7.3 13.3 36.1
10.4 Operating Cash Flow Ratio ≥ 1
Year 2014 2013 2012 2011 2010
OCF 6,842.00 5,475.00 4,180.00 3,903.00 3,495.00
Current Liabilities 28,089.00 22,980.00 19,002.00 14,896.00 10,372.00
OCF Ratio 0.24 0.24 0.22 0.26 0.34
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 15,675.0 7,433.0 5,361.0 2,625.0 1,561.0
Total Assets 54,505.0 40,159.0 32,555.0 25,278.0 18,797.0
Debt to Assets Ratio 0.29 0.19 0.16 0.10 0.08
11.2 Debt to Equity Ratio ≤ 1
Year 2014 2013 2012 2011 2010
Long-Term Debt 15,675.0 7,433.0 5,361.0 2,625.0 1,561.0
Total Equity 10,741.0 9,746.0 8,192.0 7,757.0 6,864.0
Debt - Equity Ratio 1.46 0.76 0.65 0.34 0.23
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 15,675.0 7,433.0 5,361.0 2,625.0 1,561.0
OCF 6,842.0 5,475.0 4,180.0 3,903.0 3,495.0
How long to payoff Debt 2.3 1.4 1.3 0.7 0.4
12 Growth in Retained Earnings
Year 2014 2013 2012 2011 2010
Retained Earnings 1949 2190 1916 1955 1324
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.10 0.18 0.20 0.48
13 CAPEX Per Share
Year 2014 2013 2012 2011 2010
CAPEX 4893 3444 3785 1811 979
Common Stock Outstanding 465 459 454 455 451
CAPEX per Share 10.5225806452 7.5032679739 8.3370044053 3.9802197802 2.1707317073
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.48 0.51 0.96 0.83

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 -1252.6153846154 -1252.6153846154 1416.98 -2788.5555555556 91.73 73.64 80.84 35.24 107.15 57.88 41.8
Average P/E Ratio -308.05 **(Check the formula for average)
Analysts Estimate of EPS Growth Rate: -3743.62 (see MSN Moneycentral Analysts Estimates)
Latest (full year) EPS = -0.52
k = 0.1173 (may try different numbers based on what you know about the company's moat, financial health and historical ROA).
Current Price = 651.36
Step 8B: Using the Data
EPS at end of last year, EPS0 = -0.52
1. Average EPS growth rate = -0.52 (use the EPS growth rate over the longest period (3-, 4- or 5- year period) you can find)
2. Average Equity growth rate = 0.11 (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 = -3743.6203963401 (see above)
Equity Growth Rate to use for Calculations = -3743.62 (This the lowest of the three numbers above)
Average P/E over 5 years or 10 years = -308.05
k, the required rate of return = 0.1173
Step 9: Estimating what the business is worth
1. Estimate EPS ten years from now, EPS10 = EPS0 (1+g)10 -280389111354166630000000000000000000.00
2. Multiply EPS10 by P/E and call that V10; that is V10 = (EPS10.)x(P/E). 86,373,262,273,367,060,000,000,000,000,000,000,000.00 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)] 28489270929317724000000000000000000000.00
Purchase Decision
Step 10: Current Price = 651.36
Current Price/Intrinsic Value = 0.00
Margin of Safety 1.00
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 = 6,842.0 Capital Expenditure = 4,893.0
a. Latest FCF 1,949.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) 465.00
Number of shares outstanding from last year 465 (for use in calculating F-Score)
Number of shares outstanding two years ago 459
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 1949.0 1828.5 1715.4 1609.3 1509.8 1416.4 1328.8 1246.6 1169.5 1097.2 1029.4 1060.2
PV of FCF 1590.0 1297.1 1058.1 863.2 704.2 574.5 468.7 382.3 311.9 254.4
Intrinsic Value in Year 10 1899.1
Intrinsic Value $9,404
Intrinsic Value per share $20.22
3 Current market price $ 651.36
4 Ratio of current price to intrinsic value 32.21
Margin of Safety = -31.21
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 0
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) 0
8 The gross margin for the last fiscal year (Y1) is greater than the gross margin for the fiscal year two years ago (Y2) 1
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 4
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 31,327
Current Liabilities 28,089
Total Aseets 54,505
Operating Income 178
Market Cap 181,680
Total Liabilities 43,764
Retained Earnings 1,949
Sales 88,988
Ratios Score
X1 = Working Capital / Total Assets 0.0594073938 0.0712888726
X2 =Retained Earnings/Total Asets 0.0357581873 0.0500614623
X3 = Operating Income/Total Assets 0.0032657554 0.0107769929
X4 = Market Capitalization / Liabilities 4.15135728 2.490814368
X5 = Sales / Total Assets 1.6326575544 1.6326575544
Total 4.2555992501
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.