need help in my homework
NeilAli
activision_blizzard_inc..xls5-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? |
| Activision Blizzard, Inc. | ATVI | no | yes | no | yes | yes | yes | yes | no | yes | conitue to Analyze |
Data Input
| Stock Ticker | ATVI | ||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 |
| Quote | |||||
| Current Price | 34.17 | ||||
| P/E | 26.9 | ||||
| Market Cap | 24.91 | [in million USD] | |||
| Shares Outstanding | 729.02 | [in million] | |||
| Earnings | |||||
| Earnings Growth Rate | |||||
| Company [Next 5 Years] | 7.47 | [in decimal] | |||
| Financials | |||||
| Income Statement | |||||
| 2014 | 2013 | 2012 | 2011 | 2010 | |
| Total Revenue (Sales) | 4,408,000 | 4,583,000 | 4,856,000 | 4,755,000 | 4,447,000 |
| Gross Profit | 0 | 0 | 0 | 0 | 0 |
| Operating Income | 1,183,000 | 1,372,000 | 1,451,000 | 1,328,000 | 469,000 |
| Net Income | 835,000 | 1010000 | 1,149,000 | 1,085,000 | 418,000 |
| Interest Expense, Supplemental | 0 | 0 | 1,000 | 4,000 | 5,000 |
| Diluted Normalized EPS | 1 | 0.95 | 1 | 1 | 0 |
| Balance Sheet | |||||
| 2014 | 2013 | 2012 | 2011 | 2010 | |
| Total Inventory | 0 | 0 | 0 | 0 | 0 |
| Total Current Assets | 6,909,000 | 6,241,000 | 6,274,000 | 5,380,000 | 5,385,000 |
| Total Assets | 14,746,000 | 14012000 | 14,200,000 | 13277000 | 13,406,000 |
| Total Current Liabilities | 2,714,000 | 2405000 | 2,652,000 | 2556000 | 2,907,000 |
| Total Long Term Debt | 4,324,000 | 4668000 | 0 | 0 | 0 |
| Total Liabilities | 7,513,000 | 7390000 | 2,883,000 | 2785000 | 3,203,000 |
| Retained Earnings (Accumulated Deficit) | 3,374,000 | 2686000 | 1,893,000 | 948000 | 57,000 |
| Total Equity | 7,233,000 | 6622000 | 11,317,000 | 10492000 | 10,203,000 |
| 2014 | 2013 | 2012 | 2011 | 2010 | |
| Total Common Shares Outstanding | |||||
| Cash Flow Statement | |||||
| 2014 | 2013 | 2012 | 2011 | 2010 | |
| Cash from Operating Activities | 1,292,000 | 1,264,000 | 1,345,000 | 952,000 | 1,376,000 |
| Capital Expenditures (Ignore the negative sign) | 107,000 | 74,000 | 73,000 | 72,000 | 97,000 |
| Fundamentals | |||||
| Key Ratio [10 Year Summary] | |||||
| Avg P/E | Book Value/ Share | ||||
| 2014 | 1.8 | 10.02 | |||
| 2013 | 1.23 | 9.41 | |||
| 2012 | 1.21 | 10.18 | |||
| 2011 | 0.83 | 9.26 | |||
| 2010 | 1.13 | 8.62 | |||
| 2009 | 0.92 | ||||
| 2008 | 0.23 | ||||
| 2007 | 1.98 | ||||
| 2006 | 0.1 | ||||
| 2005 | 0.31 |
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 | 835,000.0 | 1,010,000.0 | 1,149,000.0 | 1,085,000.0 | 418,000.0 | |
| Interest | 0.00 | 0.00 | 1,000.00 | 4,000.00 | 5,000.00 | |
| Long-Term Debt | 4,324,000.00 | 4,668,000.00 | - 0 | - 0 | - 0 | |
| Total Equity | 7,233,000.0 | 6,622,000.0 | 11,317,000.0 | 10,492,000.0 | 10,203,000.0 | |
| Invested Capital | 11,557,000.0 | 11,290,000.0 | 11,317,000.0 | 10,492,000.0 | 10,203,000.0 | |
| ROIC | 0.07 | 0.09 | 0.10 | 0.10 | 0.04 | |
| 2 | Equity Growth Rates: | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| BVPS | 10.02 | 9.41 | 10.18 | 9.26 | 8.62 | |
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| 0.04 | 0.03 | -0.01 | 0.06 | |||
| 3 | EPS Growth Rates: | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| EPS | 1.13 | 0.95 | 1.01 | 0.92 | 0.33 | |
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| 0.36 | 0.07 | 0.06 | 0.19 | |||
| 4 | Sales Growth Rates | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Sales (Revenue) | 4,408,000.00 | 4,583,000.00 | 4,856,000.00 | 4,755,000.00 | 4,447,000.00 | |
| Sales Growth Rates: | ||||||
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| -0.002 | -0.025 | -0.047 | -0.038 | |||
| 5 | Operating Cash Flow (OCF) and Free Cash Flow (FCF) growth rates | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| OCF | 1,292,000.00 | 1,264,000.00 | 1,345,000.00 | 952,000.00 | 1,376,000.00 | |
| CAPEX | 107,000.00 | 74,000.00 | 73,000.00 | 72,000.00 | 97,000.00 | |
| FCF | 1,185,000.00 | 1,190,000.00 | 1,272,000.00 | 880,000.00 | 1,279,000.00 | |
| OCF Growth Rates: | ||||||
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| -0.016 | 0.107 | -0.020 | 0.022 | |||
| FCF Growth Rates: | ||||||
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| -0.019 | 0.104 | -0.035 | -0.004 | |||
| 6 | Gross Margin (>40% is sign of durable competitive advantage; <20% is indicator of highly competitive industry). | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Sales | 4408000 | 4583000 | 4856000 | 4755000 | 4447000 | |
| Gross Profit | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| Gross Margin | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |
| 7 | Operating Margin (>= industry or sector average). (Industry: … ...%; Average of competitors: … …%) | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Sales | 4408000 | 4583000 | 4856000 | 4755000 | 4447000 | |
| Operating Profit | 1,183,000.00 | 1,372,000.00 | 1,451,000.00 | 1,328,000.00 | 469,000.00 | |
| Operating Margin | 0.27 | 0.30 | 0.30 | 0.28 | 0.11 | |
| 8 | Net Margin (> 20% => competitive advantage; <10% sign of competition) | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Sales | 4408000 | 4583000 | 4856000 | 4755000 | 4447000 | |
| Net Income | 835000.00 | 1010000.00 | 1149000.00 | 1085000.00 | 418000.00 | |
| Net Margin | 0.19 | 0.22 | 0.24 | 0.23 | 0.09 | |
| 9 | Free Cash Flow Margin (>= 10% indicator of competitive advantage; must not be < 3%). | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Sales | 4408000.00 | 4583000.00 | 4856000.00 | 4755000.00 | 4447000.00 | |
| Free Cash Flow | 1185000.00 | 1190000.00 | 1272000.00 | 880000.00 | 1279000.00 | |
| FCF Margin | 0.27 | 0.26 | 0.26 | 0.19 | 0.29 | |
| 10 | Short-Term Financial Health | |||||
| 10.1 | Current Ratio ≥ 2 | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Current Assets | 6,909,000.00 | 6,241,000.00 | 6,274,000.00 | 5,380,000.00 | 5,385,000.00 | |
| Current Liabilities | 2,714,000.00 | 2,405,000.00 | 2,652,000.00 | 2,556,000.00 | 2,907,000.00 | |
| Current Ratio | 2.55 | 2.60 | 2.37 | 2.10 | 1.85 | |
| 10.2 | Quick Ratio ≥ 1.5 | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Current Assets | 6,909,000.0 | 6,241,000.0 | 6,274,000.0 | 5,380,000.0 | 5,385,000.0 | |
| Inventory | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| Current Liabilities | 2,714,000.0 | 2,405,000.0 | 2,652,000.0 | 2,556,000.0 | 2,907,000.0 | |
| Quick Ratio | 2.55 | 2.60 | 2.37 | 2.10 | 1.85 | |
| 10.3 | Interest Coverage Ratio ≥ 5 minimum; but preferably ≥ 10 (except utilities ≥ 2) | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| EBIT | 1,183,000.00 | 1,372,000.00 | 1,451,000.00 | 1,328,000.00 | 469,000.00 | |
| Interest Expense | 0.00 | 0.00 | 1,000.00 | 4,000.00 | 5,000.00 | |
| Interest Coverage | 0.0 | 0.0 | 1451.0 | 332.0 | 93.8 | |
| 10.4 | Operating Cash Flow Ratio ≥ 1 | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| OCF | 1,292,000.00 | 1,264,000.00 | 1,345,000.00 | 952,000.00 | 1,376,000.00 | |
| Current Liabilities | 2,714,000.00 | 2,405,000.00 | 2,652,000.00 | 2,556,000.00 | 2,907,000.00 | |
| OCF Ratio | 0.48 | 0.53 | 0.51 | 0.37 | 0.47 | |
| 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 | 4,324,000.0 | 4,668,000.0 | 0.0 | 0.0 | 0.0 | |
| Total Assets | 14,746,000.0 | 14,012,000.0 | 14,200,000.0 | 13,277,000.0 | 13,406,000.0 | |
| Debt to Assets Ratio | 0.29 | 0.33 | 0.00 | 0.00 | 0.00 | |
| 11.2 | Debt to Equity Ratio ≤ 1 | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Long-Term Debt | 4,324,000.0 | 4,668,000.0 | 0.0 | 0.0 | 0.0 | |
| Total Equity | 7,233,000.0 | 6,622,000.0 | 11,317,000.0 | 10,492,000.0 | 10,203,000.0 | |
| Debt - Equity Ratio | 0.60 | 0.70 | 0.00 | 0.00 | 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 | 4,324,000.0 | 4,668,000.0 | 0.0 | 0.0 | 0.0 | |
| OCF | 1,292,000.0 | 1,264,000.0 | 1,345,000.0 | 952,000.0 | 1,376,000.0 | |
| How long to payoff Debt | 3.3 | 3.7 | 0.0 | 0.0 | 0.0 | |
| 12 | Growth in Retained Earnings | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| Retained Earnings | 3374000 | 2686000 | 1893000 | 948000 | 57000 | |
| 4-year Growth rate | 3-year Growth rate | 2-year Growth rate | 1-year Growth rate | |||
| 1.77 | 2.61 | 4.76 | 15.63 | |||
| 13 | CAPEX Per Share | |||||
| Year | 2014 | 2013 | 2012 | 2011 | 2010 | |
| CAPEX | 107000 | 74000 | 73000 | 72000 | 97000 | |
| Common Stock Outstanding | 0 | 0 | 0 | 0 | 0 | |
| 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 | 26.9 | 1.8 | 1.23 | 1.21 | 0.83 | 1.13 | 0.92 | 0.23 | 1.98 | 0.1 | 0.31 | |
| Average P/E Ratio | 3.33 | **(Check the formula for average) | ||||||||||
| Analysts Estimate of EPS Growth Rate: | 7.47 | (see MSN Moneycentral Analysts Estimates) | ||||||||||
| Latest (full year) EPS = | 1.13 | |||||||||||
| k = | 0.15 | (may try different numbers based on what you know about the company's moat, financial health and historical ROA). | ||||||||||
| Current Price = | 34.17 | |||||||||||
| Step 8B: | Using the Data | |||||||||||
| EPS at end of last year, EPS0 = | 1.13 | |||||||||||
| 1. Average EPS growth rate = | 0.36 | (use the EPS growth rate over the longest period (3-, 4- or 5- year period) you can find) | ||||||||||
| 2. Average Equity growth rate = | 0.04 | (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 = | 7.47 | (see above) | ||||||||||
| Equity Growth Rate to use for Calculations = | 0.04 | (This the lowest of the three numbers above) | ||||||||||
| Average P/E over 5 years or 10 years = | 3.33 | |||||||||||
| 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 | 1.65 | |||||||||||
| 2. Multiply EPS10 by P/E and call that V10; that is V10 = (EPS10.)x(P/E). | 5.48 | 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)] | 1.36 | |||||||||||
| Purchase Decision | ||||||||||||
| Step 10: | Current Price = | 34.17 | ||||||||||
| Current Price/Intrinsic Value = | 25.21 | |||||||||||
| Margin of Safety | -24.21 | |||||||||||
| 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 = | 1,292,000.0 | Capital Expenditure = | 107,000.0 | |||||||||||
| a. Latest FCF | 1,185,000.0 | |||||||||||||
| b. Rate of Growth of FCF over the next 10 years (use lower of 10% or company's 5-year growth rate) | -0.02 | |||||||||||||
| 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) | 729.02 | |||||||||||||
| Number of shares outstanding from last year | 0 | (for use in calculating F-Score) | ||||||||||||
| Number of shares outstanding two years ago | 0 | |||||||||||||
| 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 | 1185000.0 | 1162600.0 | 1140623.3 | 1119062.1 | 1097908.5 | 1077154.8 | 1056793.3 | 1036816.8 | 1017217.8 | 997989.4 | 979124.4 | 1008498.1 | ||
| PV of FCF | 1010956.5 | 862475.1 | 735801.5 | 627732.8 | 535536.3 | 456880.9 | 389777.8 | 332530.3 | 283690.9 | 242024.6 | ||||
| Intrinsic Value in Year 10 | 1806415.3 | |||||||||||||
| Intrinsic Value | $7,283,822 | |||||||||||||
| Intrinsic Value per share | $9,991.25 | |||||||||||||
| 3 | Current market price | $ 34.17 | ||||||||||||
| 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) | 1 |
| 6 | The current ratio for the last fiscal year (Y1) is greater than the current ratio for the fiscal year two years ago (Y2) | 0 |
| 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 | 6,909,000 | |
| Current Liabilities | 2,714,000 | |
| Total Aseets | 14,746,000 | |
| Operating Income | 1,183,000 | |
| Market Cap | 25 | |
| Total Liabilities | 7,513,000 | |
| Retained Earnings | 3,374,000 | |
| Sales | 4,408,000 | |
| Ratios | Score | |
| X1 = Working Capital / Total Assets | 0.2844839278 | 0.3413807134 |
| X2 =Retained Earnings/Total Asets | 0.2288078123 | 0.3203309372 |
| X3 = Operating Income/Total Assets | 0.0802251458 | 0.2647429811 |
| X4 = Market Capitalization / Liabilities | 0.0000033156 | 0.0000019894 |
| X5 = Sales / Total Assets | 0.298928523 | 0.298928523 |
| Total | 1.2253851441 | |
| 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. |
