need help

profileadlons
alphabet_inc.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?
Alphabet Inc GOOG L No NO no yes yes yes yes yes yes safe company

Data Input

Stock Ticker GOOG L
Year 2014 2013 2012 2011 2010
Quote
Current Price 695.32
P/E 32.9068
Market Cap 201,563,533 [in million USD]
Shares Outstanding 289.89
Earnings
Earnings Growth Rate
Company [Next 5 Years] 16.74 [in decimal]
Financials
Income Statement
2014 2013 2012 2011 2010
Total Revenue (Sales) 66,001 59,825 50,175 37,905 29,321
Gross Profit 40,310 33,967 29,541 24,717 18,904
Operating Income 16,496 13,966 12,760 11,742 10,381
Net Income 14,444 12,920 10,737 9,737 8,505
Interest Expense, Supplemental 101 83 84 58 0
Diluted Normalized EPS 21.02 19.07 16.16 14.88 13.16
Balance Sheet
2014 2013 2012 2011 2010
Total Inventory 0 426 505 0 0
Total Current Assets 80,685 72,886 60,454 52,758 41,562
Total Assets 131,133 110,920 93,798 72,574 57,851
Total Current Liabilities 16,805 15,908 14,337 8,913 9,996
Total Long Term Debt 3,228 2,236 2,988 2,986 0
Total Liabilities 26,633 23,611 22,083 14,429 11,610
Retained Earnings (Accumulated Deficit) 75,706 61,262 48,342 37,605 27,868
Total Equity 104,500 87,309 71,715 58,145 46,241
2014 2013 2012 2011 2010
Total Common Shares Outstanding 680.172 671.665 659.958 649.79 642.603
Cash Flow Statement
2014 2013 2012 2011 2010
Cash from Operating Activities 22,376 18,659 16,619 14,565 11,081
Capital Expenditures (Ignore the negative sign) 0 0 0 0 0
Fundamentals
Key Ratio [10 Year Summary]
Avg P/E Book Value/ Share
2014 33.1 153.64
2013 30.77 129.99
2012 28.12 108.67
2011 25.26 89.48
2010 19.62 71.96
2009 16.7
2008 14.18
2007 9.29
2006 5.94
2005 4.46

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 14,444.0 12,920.0 10,737.0 9,737.0 8,505.0
Interest 101.00 83.00 84.00 58.00 0.00
Long-Term Debt 3,228.00 2,236.00 2,988.00 2,986.00 - 0
Total Equity 104,500.0 87,309.0 71,715.0 58,145.0 46,241.0
Invested Capital 107,728.0 89,545.0 74,703.0 61,131.0 46,241.0
ROIC 0.14 0.15 0.14 0.16 0.18
2 Equity Growth Rates:
Year 2014 2013 2012 2011 2010
BVPS 153.64 129.99 108.67 89.48 71.96
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.21 0.20 0.19 0.18
3 EPS Growth Rates:
Year 2014 2013 2012 2011 2010
EPS 21.02 19.07 16.16 14.88 13.16
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.12 0.12 0.14 0.10
4 Sales Growth Rates
Year 2014 2013 2012 2011 2010
Sales (Revenue) 66,001.00 59,825.00 50,175.00 37,905.00 29,321.00
Sales Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.225 0.203 0.147 0.103
5 Operating Cash Flow (OCF) and Free Cash Flow (FCF) growth rates
Year 2014 2013 2012 2011 2010
OCF 22,376.00 18,659.00 16,619.00 14,565.00 11,081.00
CAPEX 0.00 0.00 0.00 0.00 0.00
FCF 22,376.00 18,659.00 16,619.00 14,565.00 11,081.00
OCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.192 0.154 0.160 0.199
FCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.192 0.154 0.160 0.199
6 Gross Margin (>40% is sign of durable competitive advantage; <20% is indicator of highly competitive industry).
Year 2014 2013 2012 2011 2010
Sales 66001 59825 50175 37905 29321
Gross Profit 40310.00 33967.00 29541.00 24717.00 18904.00
Gross Margin 0.61 0.57 0.59 0.65 0.64
7 Operating Margin (>= industry or sector average). (Industry: … ...%; Average of competitors: … …%)
Year 2014 2013 2012 2011 2010
Sales 66001 59825 50175 37905 29321
Operating Profit 16,496.00 13,966.00 12,760.00 11,742.00 10,381.00
Operating Margin 0.25 0.23 0.25 0.31 0.35
8 Net Margin (> 20% => competitive advantage; <10% sign of competition)
Year 2014 2013 2012 2011 2010
Sales 66001 59825 50175 37905 29321
Net Income 14444.00 12920.00 10737.00 9737.00 8505.00
Net Margin 0.22 0.22 0.21 0.26 0.29
9 Free Cash Flow Margin (>= 10% indicator of competitive advantage; must not be < 3%).
Year 2014 2013 2012 2011 2010
Sales 66001.00 59825.00 50175.00 37905.00 29321.00
Free Cash Flow 22376.00 18659.00 16619.00 14565.00 11081.00
FCF Margin 0.34 0.31 0.33 0.38 0.38
10 Short-Term Financial Health
10.1 Current Ratio ≥ 2
Year 2014 2013 2012 2011 2010
Current Assets 80,685.00 72,886.00 60,454.00 52,758.00 41,562.00
Current Liabilities 16,805.00 15,908.00 14,337.00 8,913.00 9,996.00
Current Ratio 4.80 4.58 4.22 5.92 4.16
10.2 Quick Ratio ≥ 1.5
Year 2014 2013 2012 2011 2010
Current Assets 80,685.0 72,886.0 60,454.0 52,758.0 41,562.0
Inventory 0.0 426.0 505.0 0.0 0.0
Current Liabilities 16,805.0 15,908.0 14,337.0 8,913.0 9,996.0
Quick Ratio 4.80 4.55 4.18 5.92 4.16
10.3 Interest Coverage Ratio ≥ 5 minimum; but preferably ≥ 10 (except utilities ≥ 2)
Year 2014 2013 2012 2011 2010
EBIT 16,496.00 13,966.00 12,760.00 11,742.00 10,381.00
Interest Expense 101.00 83.00 84.00 58.00 0.00
Interest Coverage 163.3 168.3 151.9 202.4 0.0
10.4 Operating Cash Flow Ratio ≥ 1
Year 2014 2013 2012 2011 2010
OCF 22,376.00 18,659.00 16,619.00 14,565.00 11,081.00
Current Liabilities 16,805.00 15,908.00 14,337.00 8,913.00 9,996.00
OCF Ratio 1.33 1.17 1.16 1.63 1.11
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 3,228.0 2,236.0 2,988.0 2,986.0 0.0
Total Assets 131,133.0 110,920.0 93,798.0 72,574.0 57,851.0
Debt to Assets Ratio 0.02 0.02 0.03 0.04 0.00
11.2 Debt to Equity Ratio ≤ 1
Year 2014 2013 2012 2011 2010
Long-Term Debt 3,228.0 2,236.0 2,988.0 2,986.0 0.0
Total Equity 104,500.0 87,309.0 71,715.0 58,145.0 46,241.0
Debt - Equity Ratio 0.03 0.03 0.04 0.05 0.00
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 3,228.0 2,236.0 2,988.0 2,986.0 0.0
OCF 22,376.0 18,659.0 16,619.0 14,565.0 11,081.0
How long to payoff Debt 0.1 0.1 0.2 0.2 0.0
12 Growth in Retained Earnings
Year 2014 2013 2012 2011 2010
Retained Earnings 75706 61262 48342 37605 27868
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.28 0.30 0.32 0.35
13 CAPEX Per Share
Year 2014 2013 2012 2011 2010
CAPEX 0 0 0 0 0
Common Stock Outstanding 680.172 671.665 659.958 649.79 642.603
CAPEX per Share 0 0 0 0 0
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.00 0.00 0.00 0.00

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.9068 33.1 30.77 28.12 25.26 19.62 16.7 14.18 9.29 5.94 4.46
Average P/E Ratio 20.03 **(Check the formula for average)
Analysts Estimate of EPS Growth Rate: 16.74 (see MSN Moneycentral Analysts Estimates)
Latest (full year) EPS = 21.02
k = 0.15 (may try different numbers based on what you know about the company's moat, financial health and historical ROA).
Current Price = 695.32
Step 8B: Using the Data
EPS at end of last year, EPS0 = 21.02
1. Average EPS growth rate = 0.12 (use the EPS growth rate over the longest period (3-, 4- or 5- year period) you can find)
2. Average Equity growth rate = 0.21 (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 = 16.74 (see above)
Equity Growth Rate to use for Calculations = 0.12 (This the lowest of the three numbers above)
Average P/E over 5 years or 10 years = 20.03
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 67.78
2. Multiply EPS10 by P/E and call that V10; that is V10 = (EPS10.)x(P/E). 1,357.65 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)] 335.59
Purchase Decision
Step 10: Current Price = 695.32
Current Price/Intrinsic Value = 2.07
Margin of Safety -1.07
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 = 22,376.0 Capital Expenditure = 0.0
a. Latest FCF 22,376.0
b. Rate of Growth of FCF over the next 10 years (use lower of 10% or company's 5-year growth rate) 0.10
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) 289.89
Number of shares outstanding from last year 680.172 (for use in calculating F-Score)
Number of shares outstanding two years ago 671.665
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 22376.0 24613.6 27075.0 29782.5 32760.7 36036.8 39640.4 43604.5 47964.9 52761.4 58037.6 59778.7
PV of FCF 21403.1 20472.6 19582.4 18731.0 17916.6 17137.7 16392.5 15679.8 14998.1 14346.0
Intrinsic Value in Year 10 107075.2
Intrinsic Value $283,735
Intrinsic Value per share $978.77
3 Current market price $ 695.32
4 Ratio of current price to intrinsic value 0.71
Margin of Safety = 0.29
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) 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 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 80,685
Current Liabilities 16,805
Total Aseets 131,133
Operating Income 16,496
Market Cap 201,563,533
Total Liabilities 26,633
Retained Earnings 75,706
Sales 66,001
Ratios Score
X1 = Working Capital / Total Assets 0.4871390115 0.5845668138
X2 =Retained Earnings/Total Asets 0.5773222606 0.8082511648
X3 = Operating Income/Total Assets 0.1257959476 0.4151266272
X4 = Market Capitalization / Liabilities 7568.1873239965 4540.9123943979
X5 = Sales / Total Assets 0.5033134299 0.5033134299
Total 4543.2236524337
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.