FINC 430 Week 2/3 Discussions and Projects
barkersbaseball
quiz_2_excel.xlsxQEP1-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.853y 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.8279954853330643y 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.4933037783000573Linest
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.9749999999999996x,y plot
EY_C2SPY_R 1.4736842105263157 7.2222222222222197 -2.25 -28.999999999999929 -1.4166666666666667 5 11.7 7.6 32.299999999999997 15.6EY_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.7054729876361661EY_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.7941520467836121SPY_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.439999999999998QEP1-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.853y 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.8279954853330643y 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.4933037783000573Linest
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.9749999999999996x,y plot
ROCE_C2SPY_R 1.4736842105263157 7.2222222222222197 -2.25 -28.999999999999929 -1.4166666666666667 5 11.7 7.6 32.299999999999997 15.6ROCE_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.7054729876361661ROCE_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.7941520467836121SPY_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.439999999999998RPPart1-Correlation-Study
| Item Change % | StdDev | Enter IDX Return | StdDev | Industry Return % | StdDev | ||
| Year | Corp_C | Corp_Cs | SPY_R | SPY_Rs | XLY_R wm: SP Sector Idx Return http://www.sectorspdr.com/sectorspdr/ & http://performance.morningstar.com/funds/etf/total-returns.action?t=XLY |
||
| 2010 | 5.0 | 9.6 | 5.0 | 9.6 | 27.46 | 13.2 | |
| 2011 | 11.7 | 11.7 | 5.99 | ||||
| 2012 | 7.6 | 7.6 | 23.6 | ||||
| 2013 | 32.3 | 32.3 | 42.74 | ||||
| 2014 | 15.6 | 15.6 | 9.49 | ||||
| Correlation | SPY_R | XLY_R | SPY_R | ||||
| 2010-2012 | 1.000 | -0.975 | -0.975 | =CORREL(b3:b5,f3:f5) | |||
| 2011-2013 | 1.000 | 0.793 | 0.793 | ||||
| 2012-2014 | 1.000 | 0.725 | 0.725 | ||||
| Reported Value-> | Correl(All) | 1.000 | 0.529 | 0.529 | =CORREL(b3:b7,f3:f7) | ||
| Avg(3) | 1.000 | 0.181 | 0.181 | ||||
| StdDev | 0 | 0.8177736216 | 0.8177736216 | ||||
| Median | 1.000 | 0.725 | 0.725 | ||||
| y, x | Corp_C | Corp_C | XLY_R |
x,y plot
Corp_C2SPY_R 5 11.7 7.6 32.299999999999997 15 .6 5 11.7 7.6 32.299999999999997 15.6x,y plot
Corp_C2XLY_R 5 11.7 7.6 32.299999999999997 15.6 27.46 5.99 23.6 42.74 9.49x,y plot
XLY_R2SPY_R 27.46 5.99 23.6 42.74 9.49 5 11.7 7.6 32.299999999999997 15.6QEP2-DDM-GrowthRate
| Year | Enter Financial Item | Average | StdDev | Geomean | Median | Item Change | StdDev | Enter IDX Return | StdDev | 3yr Rolling Avg | StdDev | 3yr Weighted Roll Avg | StdDev | |||||||||
| Grow | GROW_A | GROW_S | GROW_S+ | GROW_S- | GROW_G | GROW_M | GROW_C | GROW_Cs | SPY_R | SPY_Rs | GROW_3y | GROW_3ys | GROW_3ys+ | GROW_3ys- | GROW_3yw | GROW_3ywM | GROW_3yws* | GROW_3yws+ | GROW_3yws- | Wgt | Periods | |
| 2009 | 56 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | 0.25 | 3 | |||||||||||||
| 2010 | 71 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | 4.7 | 14.8 | 5.0 | 0.1 | 0.25 | ||||||||||
| 2011 | 73 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | 36.5 | 11.7 | 66.7 | 7.6 | 74.3 | 59.1 | 68.3 | 68.3 | 0.6 | 68.9 | 67.6 | 0.5 | |||
| 2012 | 65 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | -8.1 | 7.6 | 69.7 | 3.4 | 73.1 | 66.3 | 68.5 | 68.5 | 0.3 | 68.8 | 68.2 | ||||
| 2013 | 79 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | 5.6 | 32.3 | 72.3 | 5.7 | 78.1 | 66.6 | 74.0 | 74.0 | 0.5 | 74.5 | 73.5 | ||||
| 2014 | 85 | 71.5 | 9.3 | 80.8 | 62.2 | 70.9 | 72.0 | 14.2 | 15.6 | 76.3 | 8.4 | 84.7 | 68.0 | 78.5 | 78.5 | 0.7 | 79.2 | 77.8 | ||||
| 2015 | - | |||||||||||||||||||||
| Correlation | Beta | =Corr(UFCF_C,SPY_R) * (STDEV(UFCF_Cs)/STDEV(SPY_Rs)) | ||||||||||||||||||||
| SPY_R | 0.778 | =CORREL(I4:I6,K4:K6) | 82.29 wm: What happens to beta if we calculate a moving standard deviation for each range? | =D20*(J4/L4) | ||||||||||||||||||
| -0.062 | -6.56 | |||||||||||||||||||||
| 0.442 | 46.74 | |||||||||||||||||||||
| Corr(All) | 0.039 | =CORREL(I4:I8,K4:K8) | Beta (All) | 4.17 | =D23*(J4/L4) | |||||||||||||||||
| Avg(3) | 0.386 | Avg(3) | 40.82 | |||||||||||||||||||
| y, x | GROW_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
Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_A 2009 2010 2011 2012 2013 2014 71.5 71.5 71.5 71.5 71.5 71.5 GROW_S+ 80.840770846134703 80.840770846134703 80.840770846134703 80.8407708 46134703 80.840770846134703 80.840770846134703 GROW_S- 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 62.159229153865297 GROW_M 72 72 72 72 72 723y Roll
Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3y 2009 2010 2011 2012 2013 2014 66.666666666666671 69.666666666666671 72.333333333333329 76.333333333333329 GROW_3ys+ 74.2 53204451160698 73.066013009061862 78.068216844695087 84.713203393317684 GROW_3ys- 59.080128882172644 66.267320324271481 66.59844982197157 67.9534632733489733y Wgt Mov
Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3ywM 2009 2010 2011 2012 2013 2014 68.25 68.5 74 78.5 GROW_3yws+ 68.866568122756931 68.802501532282889 74.477609253551833 79.161231483094838 GROW_3yws- 67.633431877243069 68.197498467717111 73.522390746448167 77.838768516905162Linest
Grow 2009 2010 2011 2012 2013 2014 56 71 73 65 79 85 GROW_3ywM 2009 2010 2011 2012 2013 2014 68.25 68.5 74 78.5x,y plot
UFCF_C2SPY_R 4.7333333333333334 36.5 -8.125 5.6428571428571432 14.166666666666666 5 11.7 7.6 32.299999999999997 15.6RPPart2-Beta-Study
| Item Change % | StdDev | Enter IDX Return | StdDev | Industry Return % | StdDev | ||||||||||
| Year | Corp_C | Corp_Cs | SPY_R | SPY_Rs | XLY_R wm: SP Sector Idx Return http://www.sectorspdr.com/sectorspdr/ & http://performance.morningstar.com/funds/etf/total-returns.action?t=XLY |
||||||||||
| 2010 | 5.0 | 9.6 | 5.0 | 9.6 | 27.46 | 13.2 | |||||||||
| 2011 | 11.7 | 11.7 | 5.99 | ||||||||||||
| 2012 | 7.6 | 7.6 | 23.6 | ||||||||||||
| 2013 | 32.3 | 32.3 | 42.74 | ||||||||||||
| 2014 | 15.6 | 15.6 | 9.49 | ||||||||||||
| Corp_C2SPY_R | Corp_C2XLY_R | XLY_R2SPY_R | |||||||||||||
| Correlation | SPY_R | XLY_R | SPY_R | Beta | Total | Market | Firm | Industry | =Correl(Corp_C,SPY_R) * (STDEV(Corp_Cs)/STDEV(SPY_Rs)) | ||||||
| 2010-2012 | 1.000 | -0.975 | -0.975 | =CORREL(b3:b5,f3:f5) | - | 1.00 wm: What happens to beta if we calculate a moving standard deviation for each range? | -0.71 wm: What happens to beta if we calculate a moving standard deviation for each range? | -1.34 | =G11*(G3/E3) | ||||||
| 2011-2013 | 1.000 | 0.793 | 0.793 | - | 1.00 | 0.58 | 1.09 | ||||||||
| 2012-2014 | 1.000 | 0.725 | 0.725 | - | 1.00 | 0.53 | 1.00 | ||||||||
| Reported Value-> | Correl(All) | 1.000 | 0.529 | 0.529 | =CORREL(b3:b7,f3:f7) | Beta (All) | 1.00 | 1.00 | 0.39 | 0.73 | =G14*(G3/E3) | ||||
| Avg(3) | 1.000 | 0.181 | 0.181 | Avg(3) | - | 1.00 | 0.13 | 0.25 | |||||||
| StdDev | 0 | 0.8177736216 | 0.8177736216 | StdDev | - | 0.00 | 0.60 | 1.12 | |||||||
| Median | 1.000 | 0.725 | 0.725 | Median | - | 1.00 | 0.53 | 1.00 | |||||||
| y, x | Corp_C | Corp_C | XLY_R |
x,y plot
Corp_C2SPY_R 5 11.7 7.6 32.299999999999997 15.6 5 11.7 7.6 32.299999999999997 15.6x,y plot
Corp_C2XLY_R 5 11.7 7.6 32.299999999999997 15.6 27.46 5.99 23.6 42.74 9.49x,y plot
XLY_R2SPY_R 27.46 5.99 23.6 42.74 9.49 5 11.7 7.6 32.299999999999997 15.6
