Need help with experince of finance

profileNeilAli
abbvie.xls
Home>Business & Finance homework help>Need help with experince of finance

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?
abbVie inc ABBV NO No NO Yes Yes NO NO NO NO

Data Input

Stock Ticker ABBV
Year 2014 2013 2012 2011 2010
Quote
Current Price 55.44
P/E 55.08
Market Cap 104141 [in million USD]
Shares Outstanding 1593 [in million]
Earnings
Earnings Growth Rate
Company [Next 5 Years] 9.5 [in decimal]
Financials
Income Statement
2014 2013 2012 2011 2010
Total Revenue (Sales) 19960 18790 18380 17444 15638
Gross Profit 15534 14209 13872 12805 11345
Operating Income 3411 5664 5817 3621 4717
Net Income 1774 4128 5275 3433 4178
Interest Expense, Supplemental 429 299 104 0 0
Diluted Normalized EPS 1.1 2.56 3.35 0 0
Balance Sheet
2014 2013 2012 2011 2010
Total Inventory 1124 1150 1091 872 836
Total Current Assets 16088 17848 15354 7354 8218
Total Assets 27547 29198 27008 19657 21135
Total Current Liabilities 11400 6879 6776 5897 3761
Total Long Term Debt 11555 17257 16869 1537 1670
Total Liabilities 25805 24706 23645 7433 5432
Retained Earnings (Accumulated Deficit) 535 1567 0 0 0
Total Equity 1742 4492 3363 12224 15703
2014 2013 2012 2011 2010
Total Common Shares Outstanding 1593 1588 0 1585 1577
Cash Flow Statement
2014 2013 2012 2011 2010
Cash from Operating Activities 3549 6267 6345 6247 4976
Capital Expenditures (Ignore the negative sign) 612 491 333 356 448
Fundamentals
Key Ratio [10 Year Summary]
Avg P/E Book Value/ Share
2014 55.08 1.09
2013 19.64 2.82
2012 15 -0.03
2011 13.7 7.71
2010 15.5 9.96
2009 18.6
2008 10.9
2007 16.5
2006 16.8
2005 17.3

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 1,774.0 4,128.0 5,275.0 3,433.0 4,178.0
Interest 429.00 299.00 104.00 0.00 0.00
Long-Term Debt 11,555.00 17,257.00 16,869.00 1,537.00 1,670.00
Total Equity 1,742.0 4,492.0 3,363.0 12,224.0 15,703.0
Invested Capital 13,297.0 21,749.0 20,232.0 13,761.0 17,373.0
ROIC 0.17 0.20 0.27 0.25 0.24
2 Equity Growth Rates:
Year 2014 2013 2012 2011 2010
BVPS 1.09 2.82 -0.03 7.71 9.96
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.42 -0.48 4.94 -0.61
3 EPS Growth Rates:
Year 2014 2013 2012 2011 2010
EPS 1.10 2.56 3.35 0.00 0.00
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.00 0.00 -0.43 -0.57
4 Sales Growth Rates
Year 2014 2013 2012 2011 2010
Sales (Revenue) 19,960.00 18,790.00 18,380.00 17,444.00 15,638.00
Sales Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.063 0.046 0.042 0.062
5 Operating Cash Flow (OCF) and Free Cash Flow (FCF) growth rates
Year 2014 2013 2012 2011 2010
OCF 3,549.00 6,267.00 6,345.00 6,247.00 4,976.00
CAPEX 612.00 491.00 333.00 356.00 448.00
FCF 2,937.00 5,776.00 6,012.00 5,891.00 4,528.00
OCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.081 -0.172 -0.252 -0.434
FCF Growth Rates:
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
-0.103 -0.207 -0.301 -0.492
6 Gross Margin (>40% is sign of durable competitive advantage; <20% is indicator of highly competitive industry).
Year 2014 2013 2012 2011 2010
Sales 19960 18790 18380 17444 15638
Gross Profit 15534.00 14209.00 13872.00 12805.00 11345.00
Gross Margin 0.78 0.76 0.75 0.73 0.73
7 Operating Margin (>= industry or sector average). (Industry: … ...%; Average of competitors: … …%)
Year 2014 2013 2012 2011 2010
Sales 19960 18790 18380 17444 15638
Operating Profit 3,411.00 5,664.00 5,817.00 3,621.00 4,717.00
Operating Margin 0.17 0.30 0.32 0.21 0.30
8 Net Margin (> 20% => competitive advantage; <10% sign of competition)
Year 2014 2013 2012 2011 2010
Sales 19960 18790 18380 17444 15638
Net Income 1774.00 4128.00 5275.00 3433.00 4178.00
Net Margin 0.09 0.22 0.29 0.20 0.27
9 Free Cash Flow Margin (>= 10% indicator of competitive advantage; must not be < 3%).
Year 2014 2013 2012 2011 2010
Sales 19960.00 18790.00 18380.00 17444.00 15638.00
Free Cash Flow 2937.00 5776.00 6012.00 5891.00 4528.00
FCF Margin 0.15 0.31 0.33 0.34 0.29
10 Short-Term Financial Health
10.1 Current Ratio ≥ 2
Year 2014 2013 2012 2011 2010
Current Assets 16,088.00 17,848.00 15,354.00 7,354.00 8,218.00
Current Liabilities 11,400.00 6,879.00 6,776.00 5,897.00 3,761.00
Current Ratio 1.41 2.59 2.27 1.25 2.19
10.2 Quick Ratio ≥ 1.5
Year 2014 2013 2012 2011 2010
Current Assets 16,088.0 17,848.0 15,354.0 7,354.0 8,218.0
Inventory 1,124.0 1,150.0 1,091.0 872.0 836.0
Current Liabilities 11,400.0 6,879.0 6,776.0 5,897.0 3,761.0
Quick Ratio 1.31 2.43 2.10 1.10 1.96
10.3 Interest Coverage Ratio ≥ 5 minimum; but preferably ≥ 10 (except utilities ≥ 2)
Year 2014 2013 2012 2011 2010
EBIT 3,411.00 5,664.00 5,817.00 3,621.00 4,717.00
Interest Expense 429.00 299.00 104.00 0.00 0.00
Interest Coverage 8.0 18.9 55.9 0.0 0.0
10.4 Operating Cash Flow Ratio ≥ 1
Year 2014 2013 2012 2011 2010
OCF 3,549.00 6,267.00 6,345.00 6,247.00 4,976.00
Current Liabilities 11,400.00 6,879.00 6,776.00 5,897.00 3,761.00
OCF Ratio 0.31 0.91 0.94 1.06 1.32
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 11,555.0 17,257.0 16,869.0 1,537.0 1,670.0
Total Assets 27,547.0 29,198.0 27,008.0 19,657.0 21,135.0
Debt to Assets Ratio 0.42 0.59 0.62 0.08 0.08
11.2 Debt to Equity Ratio ≤ 1
Year 2014 2013 2012 2011 2010
Long-Term Debt 11,555.0 17,257.0 16,869.0 1,537.0 1,670.0
Total Equity 1,742.0 4,492.0 3,363.0 12,224.0 15,703.0
Debt - Equity Ratio 6.63 3.84 5.02 0.13 0.11
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 11,555.0 17,257.0 16,869.0 1,537.0 1,670.0
OCF 3,549.0 6,267.0 6,345.0 6,247.0 4,976.0
How long to payoff Debt 3.3 2.8 2.7 0.2 0.3
12 Growth in Retained Earnings
Year 2014 2013 2012 2011 2010
Retained Earnings 535 1567 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
13 CAPEX Per Share
Year 2014 2013 2012 2011 2010
CAPEX 612 491 333 356 448
Common Stock Outstanding 1593 1588 0 1585 1577
CAPEX per Share 0.384180791 0.3091939547 0 0.2246056782 0.2840837032
4-year Growth rate 3-year Growth rate 2-year Growth rate 1-year Growth rate
0.08 0.03 0.00 -0.21

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 55.08 55.08 19.64 15 13.7 15.5 18.6 10.9 16.5 16.8 17.3
Average P/E Ratio 23.10 **(Check the formula for average)
Analysts Estimate of EPS Growth Rate: 9.5 (see MSN Moneycentral Analysts Estimates)
Latest (full year) EPS = 1.10
k = 0.15 (may try different numbers based on what you know about the company's moat, financial health and historical ROA).
Current Price = 55.44
Step 8B: Using the Data
EPS at end of last year, EPS0 = 1.10
1. Average EPS growth rate = 0.00 (use the EPS growth rate over the longest period (3-, 4- or 5- year period) you can find)
2. Average Equity growth rate = -0.42 (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 = 9.5 (see above)
Equity Growth Rate to use for Calculations = -0.42 (This the lowest of the three numbers above)
Average P/E over 5 years or 10 years = 23.10
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.00
2. Multiply EPS10 by P/E and call that V10; that is V10 = (EPS10.)x(P/E). 0.10 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.02
Purchase Decision
Step 10: Current Price = 55.44
Current Price/Intrinsic Value = 2227.81
Margin of Safety -2226.81
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 = 3,549.0 Capital Expenditure = 612.0
a. Latest FCF 2,937.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) 1593.00
Number of shares outstanding from last year 1593 (for use in calculating F-Score)
Number of shares outstanding two years ago 1588
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 2937.0 2635.7 2365.4 2122.8 1905.0 1709.6 1534.3 1376.9 1235.7 1108.9 995.2 1025.0
PV of FCF 2292.0 1788.6 1395.8 1089.2 850.0 663.3 517.6 403.9 315.2 246.0
Intrinsic Value in Year 10 1836.0
Intrinsic Value $11,398
Intrinsic Value per share $7.15
3 Current market price $ 55.44
4 Ratio of current price to intrinsic value 7.75
Margin of Safety = -6.75
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) 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) 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) 1
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 16,088
Current Liabilities 11,400
Total Aseets 27,547
Operating Income 3,411
Market Cap 104,141
Total Liabilities 25,805
Retained Earnings 535
Sales 19,960
Ratios Score
X1 = Working Capital / Total Assets 0.170181871 0.2042182452
X2 =Retained Earnings/Total Asets 0.0194213526 0.0271898936
X3 = Operating Income/Total Assets 0.1238247359 0.4086216285
X4 = Market Capitalization / Liabilities 4.0356907576 2.4214144546
X5 = Sales / Total Assets 0.7245798091 0.7245798091
Total 3.7860240309
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.