FINC 430 Week 2/3 Discussions and Projects

profilebarkersbaseball
qep1.xlsx
Home>Business & Finance homework help>FINC 430 Week 2/3 Discussions and Projects

QEP1-RelativeValue-EY

Enter Financial Item Average StdDev Geomean Median Item Change StdDev Enter IDX Return StdDev 3yr Rolling Avg StdDev 3yr Weighted Roll Avg StdDev Quartile Exclusive Quartile Wgt Avg Rolling Avg
EY EY_A EY_S EY_S+ EY_S- EY_G EY_M EY_C EY_Cs SPY_R SPY_Rs EY_3y EY_3ys EY_3ys+ EY_3ys- EY_3yw EY_3ywM EY_3yws* EY_3yws+ EY_3yws- EY_Qx EY_Qi EY_Cq SPY_Rq Wgt Periods
2009 3.6 8.4333333333 3.2 11.7 5.2 7.7 8.9 Min 3.60 -29.00 5.00 0.25 3
2010 11.2 8.4333333333 3.2 11.7 5.2 7.7 8.9 1.5 12.5 5.0 0.2 4.73 Q1 6.00 -2.25 7.60 0.25
2011 13 8.4333333333 3.2 11.7 5.2 7.7 8.9 7.2 11.7 9.3 4.1 13.3 5.2 10.2 10.2 0.9 11.1 9.3 8.85 Median 8.9 -1.4 11.7 0.5
2012 9 8.4333333333 3.2 11.7 5.2 7.7 8.9 -2.3 7.6 11.1 1.6 12.7 9.4 10.6 10.6 0.4 10.9 10.2 11.65 Q3 10.65 1.47 15.60
2013 8.7 8.4333333333 3.2 11.7 5.2 7.7 8.9 -29.0 32.3 10.2 2.0 12.2 8.3 9.9 9.9 0.4 10.3 9.4 Max 13.0 7.2 32.3
2014 5.1 8.4333333333 3.2 11.7 5.2 7.7 8.9 -1.4 15.6 7.6 1.8 9.4 5.8 7.0 7.0 0.5 7.5 6.5 Floor 6.00 -2.25 7.60
50.6 2Q Box 2.9 0.8 4.1
3QBox 1.8 2.9 3.9
RangeLo 2.40 26.75 2.60
RangeHi 2.35 5.75 16.70
Mean(g) 7.7 -4.8 14.4
Correlation Beta =Correl(EY_C,SPY_R) * (STDEV(EY_Cs)/STDEV(SPY_Rs))
SPY_R 0.700 =CORREL(K4:K6, M4:M6) 40.66
wm: What happens to beta if we calculate a moving standard deviation for each range?		 =F20*(L4/N4)
-0.917 -53.29
-0.940 -54.60
Correl(All) -0.890 =CORREL(K4:K8,M4:M8) Beta (All) -51.73 =F23*(L4/N4)
Avg(3) -0.386 Avg(3) -22.41
y, x EY_C
*Weighted Moving Standard Deviation http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf Measures of Scale: Standard Deviation http://www.itl.nist.gov/div898/handbook/eda/section3/eda356.htm http://www.morningstar.com/InvGlossary/standard_deviation.aspx

A Avg

EY 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 EY_A 2009 2010 2011 2012 2013 2014 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 EY_S+ 11.683290598009627 11.683290598009627 11.683290598009627 11.683290598009627 11.683290598009627 11.683290598009627 EY_S- 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 EY_M 8.85 8.85 8.85 8.85 8.85 8.85

3y Roll

EY 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 EY_3y 2009 2010 2011 2012 2013 2014 9.2666666666666657 11.066666666666668 10.233333333333333 7.5999999999999988 EY_3ys+ 13.340430964653919 12.702379219518031 12.193492057065217 9.3720045146669335 EY_3ys- 5.1929023686794133 9.4309541138153055 8.2731746096014476 5.827995485333064

3y Wgt Mov

EY 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 EY_3ywM 2009 2010 2011 2012 2013 2014 10.199999999999999 10.55 9.85 6.9749999999999996 EY_3yws+ 11.10143770059697 10.906152887841827 10.252910825804531 7.456696221699942 EY_3yws- 9.298562299403029 10.193847112158174 9.4470891741954688 6.4933037783000573

Linest

EY 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 EY_3ywM 2009 2010 2011 2012 2013 2014 10.199999999999999 10.55 9.85 6.9749999999999996

x,y plot

EY_C2SPY_R 1.4736842105263157 7.2222222222222197 -2.25 -28.999999999999929 -1.4166666666666667 5 11.7 7.6 32.299999999999997 15.6

EY_Qi

Floor 1 2.4 EY_Qi 6 2Q Box EY_Qi 2.8499999999999996 3QBox 2.3500000000000014 1 EY_Qi 1.7999999999999989 Mean(g) EY_Qi 7.7054729876361661

EY_Cq

Floor 1 26.749999999999929 EY_Cq -2.25 2Q Box EY_Cq 0.83333333333333326 3QBox 2.3500000000000014 1 EY_Cq 2.8903508771929824 Mean(g) EY_Qi -4.7941520467836121

SPY_Rq

Floor 1 2.5999999999999996 SPY_Rq 7.6 2Q Box SPY_Rq 4.0999999999999996 3QBox 2.3500000000000014 1 SPY_Rq 3.9000000000000004 Mean(g) EY_Qi 14.439999999999998

QEP1-AbsoluteValue-ROCE

Enter Financial Item Average StdDev Geomean Median Item Change StdDev Enter IDX Return StdDev 3yr Rolling Avg StdDev 3yr Weighted Roll Avg StdDev Quartile Exclusive Quartile Wgt Avg Rolling Avg
ROCE ROCE_A ROCE_S ROCE_S+ ROCE_S- ROCE_G ROCE_M ROCE_C ROCE_Cs SPY_R SPY_Rs ROCE_3y ROCE_3ys ROCE_3ys+ ROCE_3ys- ROCE_3yw ROCE_3ywM ROCE_3yws* ROCE_3yws+ ROCE_3yws- ROCE_Qx ROCE_Qi ROCE_Cq SPY_Rq Wgt Periods
2009 3.6 8.4333333333 3.2 11.7 5.2 7.7 8.9 Min 3.60 -29.00 5.00 0.25 3
2010 11.2 8.4333333333 3.2 11.7 5.2 7.7 8.9 1.5 12.5 5.0 0.2 4.73 Q1 6.00 -2.25 7.60 0.25
2011 13 8.4333333333 3.2 11.7 5.2 7.7 8.9 7.2 11.7 9.3 4.1 13.3 5.2 10.2 10.2 0.9 11.1 9.3 8.85 Median 8.9 -1.4 11.7 0.5
2012 9 8.4333333333 3.2 11.7 5.2 7.7 8.9 -2.3 7.6 11.1 1.6 12.7 9.4 10.6 10.6 0.4 10.9 10.2 11.65 Q3 10.65 1.47 15.60
2013 8.7 8.4333333333 3.2 11.7 5.2 7.7 8.9 -29.0 32.3 10.2 2.0 12.2 8.3 9.9 9.9 0.4 10.3 9.4 Max 13.0 7.2 32.3
2014 5.1 8.4333333333 3.2 11.7 5.2 7.7 8.9 -1.4 15.6 7.6 1.8 9.4 5.8 7.0 7.0 0.5 7.5 6.5 Floor 6.00 -2.25 7.60
50.6 2Q Box 2.9 0.8 4.1
3QBox 1.8 2.9 3.9
RangeLo 2.40 26.75 2.60
RangeHi 2.35 5.75 16.70
Mean(g) 7.7 -4.8 14.4
Correlation Beta =Correl(ROCE_C,SPY_R) * (STDEV(ROCE_Cs)/STDEV(SPY_Rs))
SPY_R 0.700 =CORREL(K4:K6, M4:M6) 40.66
wm: What happens to beta if we calculate a moving standard deviation for each range?		 =F20*(L4/N4)
-0.917 -53.29
-0.940 -54.60
Correl(All) -0.890 =CORREL(K4:K8,M4:M8) Beta (All) -51.73 =F23*(L4/N4)
Avg(3) -0.386 Avg(3) -22.41
y, x ROCE_C
*Weighted Moving Standard Deviation http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf Measures of Scale: Standard Deviation http://www.itl.nist.gov/div898/handbook/eda/section3/eda356.htm http://www.morningstar.com/InvGlossary/standard_deviation.aspx

A Avg

ROCE 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 ROCE_A 2009 2010 2011 2012 2013 2014 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 8.4333333333333336 ROCE_S+ 11.6832905 98009627 11.683290598009627 11.683290598009627 11.683290598009627 11.683290598009627 11.683290598009627 ROCE_S- 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 5.18337606865704 ROCE_M 8.85 8.85 8.85 8.85 8.85 8.85

3y Roll

ROCE 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 ROCE_3y 2009 2010 2011 2012 2013 2014 9.2666666666666657 11.066666666666668 10.233333333333333 7.5999999999999988 ROCE_3ys+ 13.340430964653919 12.702379219518031 12.193492057065217 9.3720045146669335 ROCE_3ys- 5.1929023686794133 9.4309541138153055 8.2731746096014476 5.827995485333064

3y Wgt Mov

ROCE 2009 2010 2011 2012 20 13 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 ROCE_3ywM 2009 2010 2011 2012 2013 2014 10.199999999999999 10.55 9.85 6.9749999999999996 ROCE_3yws+ 11.10143770059697 10.906152887841827 10.252910825804531 7.456696221699942 ROCE_3yws- 9.298562299403029 10.193847112158174 9.4470891741954688 6.4933037783000573

Linest

ROCE 2009 2010 2011 2012 2013 2014 3.6 11.2 13 9 8.6999999999999993 5.0999999999999996 ROCE_3ywM 2009 2010 2011 2012 2013 2014 10.199999999999999 10.55 9.85 6.9749999999999996

x,y plot

ROCE_C2SPY_R 1.4736842105263157 7.2222222222222197 -2.25 -28.999999999999929 -1.4166666666666667 5 11.7 7.6 32.299999999999997 15.6

ROCE_Qi

Floor 1 2.4 ROCE_Qi 6 2Q Box ROCE_Qi 2.8499999999999996 3QBox 2.3500000000000014 1 ROCE_Qi 1.7999999999999989 Mean(g) ROCE_Qi 7.7054729876361661

ROCE_Cq

Floor 1 26.749999999999929 ROCE_Cq -2.25 2Q Box ROCE_Cq 0.83333333333333326 3QBox 2.3500000000000014 1 ROCE_Cq 2.8903508771929824 Mean(g) ROCE_Qi -4.7941520467836121

SPY_Rq

Floor 1 2.5999999999999996 SPY_Rq 7.6 2Q Box SPY_Rq 4.0999999999999996 3QBox 2.3500000000000014 1 SPY_Rq 3.9000000000000004 Mean(g) ROCE_Qi 14.439999999999998

ReadMe

Finance 430 - Financial Management - Fall 2015-Revision Professor William T. Marcoux, Jr. University of Maryland University College e: [email protected] Financial Management Instrumentation Model-DRAFT Financial Ratios, Rolling Average, Dispersion, Quartile, Correlation, Beta and Ranking NOTE: This is not a template; modify and use as necessary to meet your own requirements for applied financial management analysis. Outline of Model 1. Financial Ratios and Items from Company Financial Reports 2. 2-4 Measures of Absolute Value 3. 2-4 Measures of Relative Value 4. Descriptive Statistics 5. Serialize financial items from 3-5 years of historical reports 6. Plot item change and benchmark return standard deviation for All Items 7. Copy sheet for new items 8. Derive and Rank items against comparable companies 9. Probability Distribution overlay of annual observations 10. Quartile box, Mean, Median dispersion chart 11. Beta Study - Total-Market-Industry-Firm References: Fundamentals of Financial Management E. Brigham & J. Houston, 7th edition Valuation A. Damodaran, University Edition Excel Box Chart for Quartile-Dispersion Analysis http://peltiertech.com/excel-box-and-whisker-diagrams-box-plots/ *Weighted Moving Standard Deviation http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf Measures of Scale: Standard Deviation http://www.itl.nist.gov/div898/handbook/eda/section3/eda356.htm http://www.morningstar.com/InvGlossary/standard_deviation.aspx Beta http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/totalbeta.html http://pages.stern.nyu.edu/~adamodar/New_Home_Page/valquestions/totalbeta.htm SP Sector Index Return http://www.sectorspdr.com/sectorspdr/ & http://performance.morningstar.com/funds/etf/total-returns.action?t=XLY
EV_EBIT Ratio by Sector
e.g., [Industrial Services | Engineering & Construction]
R-T: Damodaran, Value Multiples by Sector [ev/ebit(DA) ratio] list: http://people.stern.nyu.edu/ADAMODAR/New_Home_Page/datafile/vebitda.html
Earnings Yield [EBIT/EV]
ROCE [EBIT/CE]
FCF Yield [FCF / MV]
Market Value-MV [EBIT * EV_EBIT Ratio]
Interest-bearing debt [ST+LT]
Excess Cash [Cash-(Revenue * 5%)]
Enterprise Value-EV [MV + Debt - Excess Cash]
Capital Employed-CE [Cash + AR + INV - AP + NFA]
Return on Sales-RS [EBIT/Revenue]