{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Measuring variability","Variance, StdDev","Computations","Simulation","Sums (with independence)","Rescaling","I.I.D. Sums (with independence)","Managerial objective"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":1,"defined_names":20,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Measuring variability","index":0,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Measuring Random Variability"]
[]
[null,"Let  X  be a random variable with the distribution: ",null,null,null,null,null,"x","Pr(X=x)"]
[null,null,null,null,null,null,null,1,0.2]
[null,"The expected value of  X  is",null,null,{"$f":"SUMPRODUCT(H4:H7,I4:I7)","v":2.75},null,null,2,0.15]
[null,null,null,null,null,null,null,3,0.35]
[null,"How could we estimate the extent to which the realized value",null,null,null,null,null,4,0.3]
[null,"of  X  might deviate from the expected value?"]
[]
[null,"One \"natural\" approach would be to examine the",null,null,null,null,"x-E[X]","x","Pr(X=x)"]
[null,"possible differences directly:",null,null,null,null,{"$f":"H4-$E$5","v":-1.75},1,0.2]
[null,null,null,null,null,null,{"$f":"H5-$E$5","v":-0.75},2,0.15]
[null,"The expected value of these differences, of",null,null,null,null,{"$f":"H6-$E$5","v":0.25},3,0.35]
[null,"course, is 0. In order to measure \"deviations\" from",null,null,null,null,{"$f":"H7-$E$5","v":1.25},4,0.3]
[null,"the expected value, rather than simple differences,"]
[null,"we need to convert both positive and negative"]
[null,"differences into positive deviations."]
[null,null,null,null,null,null,"|x-E[X]|","x","Pr(X=x)"]
[null,"We might choose to do this by looking at the",null,null,null,null,{"$f":"ABS(G11)","v":1.75},1,0.2]
[null,"absolute values of the differences:",null,null,null,null,{"$f":"ABS(G12)","v":0.75},2,0.15]
[null,null,null,null,null,null,{"$f":"ABS(G13)","v":0.25},3,0.35]
[null,"We could then compute the expected value of",null,null,null,null,{"$f":"ABS(G14)","v":1.25},4,0.3]
[null,"these absolute deviations:",null,null,{"$f":"SUMPRODUCT(G19:G22,I19:I22)","v":0.92500000000000004}]
[null,"and use this as a measure of the inherent"]
[null,"\"variability\" of  X :  X  will differ from its expected"]
[null,"value, on average, by this amount."]
[]
[null,"This all seems very natural. But it's not what we're going to do!"]
[]
[null,"Why not? The best answer I can offer is that we live in a world where \"distance\" isn't"]
[null,"meant to be measured by combining absolute values: Blame God."]
[]
[null,"Instead, we live in a world where we measure distances using the Pythagorean Theorem:"]
[null,"The distance between two points  (x1, y1)  and  (x2, y2)  is"]
[]
[]
[]
[null,"We square the differences in each dimension, add the squares, and then take the"]
[null,"square-root."]
[]
[null,"By analogy, we can convert the possible differences of  X  from its expected value to"]
[null,"positive deviations by squaring the differences. This will give us an initially more"]
[null,"complicated way of measuring variability than simply using absolute values - but"]
[null,"ultimately we'll end up with an easier way of actually dealing with the type of variability"]
[null,"found in the \"real\" world."]

{"sheet":"Variance, StdDev","index":1,"n_rows":43,"n_cols":9,"rows_kept":43,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Squared Deviations"]
[]
[null,"Let  X  be a random variable with the distribution: ",null,null,null,null,null,"x","Pr(X=x)"]
[null,null,null,null,null,null,null,1,0.2]
[null,"The expected value of  X  is",null,null,{"$f":"SUMPRODUCT(H4:H7,I4:I7)","v":2.75},null,null,2,0.15]
[null,null,null,null,null,null,null,3,0.35]
[null,null,null,null,null,null,null,4,0.3]
[]
[null,"The differences between the possible",null,null,null,null,"x-E[X]","x","Pr(X=x)"]
[null,"realized values of  X  and its expected value",null,null,null,null,{"$f":"H4-$E$5","v":-1.75},1,0.2]
[null,"are:",null,null,null,null,{"$f":"H5-$E$5","v":-0.75},2,0.15]
[null,null,null,null,null,null,{"$f":"H6-$E$5","v":0.25},3,0.35]
[null,null,null,null,null,null,{"$f":"H7-$E$5","v":1.25},4,0.3]
[]
[null,"In order to convert the differences between the",null,null,null,null,"(x-E[X])2","x","Pr(X=x)"]
[null,"possible realized values of  X , and its expected",null,null,null,null,{"$f":"G10^2","v":3.0625},1,0.2]
[null,"value, into positive deviations, we will square the",null,null,null,null,{"$f":"G11^2","v":0.5625},2,0.15]
[null,"differences:",null,null,null,null,{"$f":"G12^2","v":6.25E-2},3,0.35]
[null,null,null,null,null,null,{"$f":"G13^2","v":1.5625},4,0.3]
[null,"And finally, we'll summarize what we see by"]
[null,"computing the expected value of this squared"]
[null,"difference:",null,{"$f":"SUMPRODUCT(G16:G19,I16:I19)","v":1.1875}]
[]
[null,"This is the variance of  X : ",null,null,null,"Var(X) = E[ (X-E[X])2 ] ."]
[]
[null,"The variance of  X  is measured in \"square\" units. For example, the variance of the"]
[null,"profit you might make on an investment shows up in square-dollars, a rather"]
[null,"difficult number to interpret directly. In order to restore \"normal\" units of measurement, "]
[null,"we can take the square-root of the variance: ",null,null,null,null,{"$f":"SQRT(D22)","v":1.0897247358851685}]
[]
[null,"This is the standard deviation of  X :",null,null,null,"StdDev(X) ="]
[]
[null,"While not completely accurate, a standard (and acceptable) interpretation of"]
[null,"of the standard deviation of a random variable  X  is that it tells us how far, on"]
[null,"average, the realized value of  X  will differ from the expected value."]
[]
[null,"Why do we \"need\" both the variance and the standard deviation?"]
[]
[null,"Standard deviations are easier to interpret - But it's easier to do computations"]
[null,"using variances. For many practical problems, we start with standard deviations"]
[null,"as \"givens,\" square them to turn them into variances, do our calculations in"]
[null,"terms of the variances, and take a square root at the end to convert a final"]
[null,"resulting variance back into an interpretable standard deviation."]

{"sheet":"Computations","index":2,"n_rows":29,"n_cols":10,"rows_kept":29,"cut":false,"hidden":false,"merged":[[0,1,0,8],[15,7,15,8]]}
[null,"Computations"]
[]
[null,"A bit of algebra yields the following useful relationship:"]
[]
[null,null,"Var(X) = E[ (X-E[X])2 ] = E[X2] - (E[X])2 ."]
[]
[null,"The first expression is easier to interpret - variance is the \"average squared difference"]
[null,"from the expected value\" - but the second provides an easier way of actually computing"]
[null,"the variance of a random variable.",null,null,null,null,null,null,null,"Pr(X£x)"]
[null,null,null,null,null,null,null,"x","Pr(X=x)",0]
[null,"E[X] =",null,{"$f":"SUMPRODUCT(H11:H14,I11:I14)","v":2.75},null,null,null,1,0.2,{"$f":"J10+I11","v":0.2}]
[null,"Var(X) =",null,{"$f":"SUMPRODUCT(H11:H14^2,I11:I14)-(SUMPRODUCT(H11:H14,I11:I14))^2","v":1.1875},"or",{"$f":"SUMPRODUCT(H11:H14^2,I11:I14)-D11^2","v":1.1875},null,2,0.15,{"$f":"J11+I12","v":0.35}]
[null,"StdDev(X) =",null,{"$f":"SQRT(D12)","v":1.0897247358851685},null,null,null,3,0.35,{"$f":"J12+I13","v":0.7}]
[null,null,null,null,null,null,null,4,0.3,{"$f":"J13+I14","v":1}]
[null,"Just as we can use simulation to estimate the"]
[null,"expected value of a random variable, we can also use"]
[null,"it to estimate the standard deviation (and therefore"]
[null,"the variance, as well):"]
[]
[null,null,{"$f":"LOOKUP(RAND(),J10:J13,H11:H14)","v":2},"simulated value of  X",null,null,{"$f":"showformula(C20)","v":"=LOOKUP(RAND(),J10:J13,H11:H14)"}]
[]
[null,null,{"$f":"ADDRESS(ROW($C$20),COLUMN($C$20))","v":"$C$20"},"monitored cell"]
[null,null,2.7502200000000001,"mean"]
[null,null,1.0889360906031602,"sample standard deviation"]
[null,null,1,"minimum"]
[null,null,4,"maximum"]
[null,null,100000,"number of simulation runs"]
[]
[null,null,{"$f":"C24^2","v":1.1857818094180939},"estimated variance"]

{"sheet":"Simulation","index":3,"n_rows":29,"n_cols":10,"rows_kept":29,"cut":false,"hidden":false,"merged":[[0,1,0,8],[15,7,15,8]]}
[null,"Computations"]
[]
[null,"A bit of algebra yields the following useful relationship:"]
[]
[null,null,"Var(X) = E[ (X-E[X])2 ] = E[X2] - (E[X])2 ."]
[]
[null,"The first expression is easier to interpret - variance is the \"average squared difference"]
[null,"from the expected value\" - but the second provides an easier way of actually computing"]
[null,"the variance of a random variable.",null,null,null,null,null,null,null,"Pr(X£x)"]
[null,null,null,null,null,null,null,"x","Pr(X=x)",0]
[null,"E[X] =",null,{"$f":"SUMPRODUCT(H11:H14,I11:I14)","v":2.75},null,null,null,1,0.2,{"$f":"J10+I11","v":0.2}]
[null,"Var(X) =",null,{"$f":"SUMPRODUCT(H11:H14^2,I11:I14)-(SUMPRODUCT(H11:H14,I11:I14))^2","v":1.1875},"or",{"$f":"SUMPRODUCT(H11:H14^2,I11:I14)-D11^2","v":1.1875},null,2,0.15,{"$f":"J11+I12","v":0.35}]
[null,"StdDev(X) =",null,{"$f":"SQRT(D12)","v":1.0897247358851685},null,null,null,3,0.35,{"$f":"J12+I13","v":0.7}]
[null,null,null,null,null,null,null,4,0.3,{"$f":"J13+I14","v":1}]
[null,"Just as we can use simulation to estimate the"]
[null,"expected value of a random variable, we can also use"]
[null,"it to estimate the standard deviation (and therefore"]
[null,"the variance, as well):"]
[]
[null,null,{"$f":"LOOKUP(RAND(),J10:J13,H11:H14)","v":4},"simulated value of  X",null,null,{"$f":"showformula(C20)","v":"=LOOKUP(RAND(),J10:J13,H11:H14)"}]
[]
[null,null,{"$f":"ADDRESS(ROW($C$20),COLUMN($C$20))","v":"$C$20"},"monitored cell"]
[null,null,2.7502200000000001,"mean"]
[null,null,1.0889360906031602,"sample standard deviation"]
[null,null,1,"minimum"]
[null,null,4,"maximum"]
[null,null,100000,"number of simulation runs"]
[]
[null,null,{"$f":"C24^2","v":1.1857818094180939},"estimated variance"]

{"sheet":"Sums (with independence)","index":4,"n_rows":41,"n_cols":11,"rows_kept":41,"cut":false,"hidden":false,"merged":[[38,4,40,4],[0,1,0,8],[7,6,7,9],[9,4,11,4],[36,6,36,9]]}
[null,"Sums (with independence)"]
[]
[null,"If X and Y are independent random variables, it's easy to show that"]
[null,null,null,null,null,null,null,null,null,null,"·"]
[null,null,"Var(X+Y) = Var(X) + Var(Y) + 2 ( E[XY] - E[X]·E[Y] ) = Var(X) + Var(Y) ."]
[]
[]
[null,null,null,null,null,null,"X"]
[null,"Let  X  and  Y have the joint distribution:",null,null,null,null,2,3,5,15]
[null,null,null,null,"Y",10,{"$f":"$K10*G$13","v":0.06},{"$f":"$K10*H$13","v":0.09},7.4999999999999997E-2,7.4999999999999997E-2,0.3]
[null,"E[X] =",{"$f":"SUMPRODUCT(G9:J9,G13:J13)","v":6.3},null,null,20,{"$f":"$K11*G$13","v":8.0000000000000016E-2},0.12,0.1,0.1,0.4]
[null,"Var(X) =",{"$f":"SUMPRODUCT(G9:J9^2,G13:J13)-C11^2","v":26.310000000000002},null,null,30,0.06,0.09,7.4999999999999997E-2,7.4999999999999997E-2,0.3]
[null,"E[Y] =",{"$f":"SUMPRODUCT(F10:F12,K10:K12)","v":20},null,null,null,0.2,0.3,0.25,0.25]
[null,"Var(Y) =",{"$f":"SUMPRODUCT(F10:F12^2,K10:K12)-C13^2","v":60}]
[null,null,null,null,null,null,{"$f":"$F10*G$9","v":20},30,50,150]
[null,"The possible values of  XY are",null,null,null,null,40,60,100,300]
[null,null,null,null,null,null,60,90,150,450]
[]
[null,"We could tediously work out the probability",null,null,null,null,"z","Pr(Z=z)"]
[null,"distribution of  Z = X + Y , and then compute",null,null,null,null,12,{"$f":"G10","v":0.06}]
[null,"the variance of  Z  directly:",null,null,{"$f":"SUMPRODUCT(G20:G29^2,H20:H29)-SUMPRODUCT(G20:G29,H20:H29)^2","v":86.309999999999945},null,13,{"$f":"H10","v":0.09}]
[null,null,null,null,null,null,15,{"$f":"I10","v":7.4999999999999997E-2}]
[null,"Or, simply using the previous calculations, and",null,null,null,null,22,{"$f":"G11","v":8.0000000000000016E-2}]
[null,"avoiding the tedium:",null,null,null,null,23,{"$f":"H11","v":0.12}]
[null,null,null,null,null,null,25,{"$f":"J10+I11","v":0.17499999999999999}]
[null,"Var(X+Y) =",null,{"$f":"C12+C14","v":86.31},null,null,32,{"$f":"G12","v":0.06}]
[null,null,null,null,null,null,33,{"$f":"H12","v":0.09}]
[null,null,null,null,null,null,35,{"$f":"J11+I12","v":0.17499999999999999}]
[null,null,null,null,null,null,45,{"$f":"J12","v":7.4999999999999997E-2}]
[]
[]
[null,"Optional spreadsheet skills:"]
[]
[null,"If you've learned how to enter \"array\" formulas in Excel, the following formulas"]
[null,"provide some alternative, more direct, calculations:"]
[]
[null,"E[X] =",{"$f":"SUM(G38:J38*G39:J41)","v":7.3299999999999992},null,null,null,"X"]
[null,"Var(X) =",{"$f":"SUM(G38:J38^2*G39:J41)-C37^2","v":33.741100000000024},null,null,null,2,3,5,15]
[null,"E[Y] =",{"$f":"SUM(F39:F41*G39:J41)","v":19.8},null,"Y",10,0.05,0.12,0.03,0.18]
[null,"Var(Y) =",{"$f":"SUM(F39:F41^2*G39:J41)-C39^2","v":73.95999999999998},null,null,20,0.02,7.0000000000000007E-2,0.08,0.09]
[null,"Cov(X,Y) =",{"$f":"SUM(F39:F41*G38:J38*G39:J41)-C37*C39","v":-11.433999999999997},null,null,30,0.14000000000000001,0.13,0,0.09]

{"sheet":"Rescaling","index":5,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Rescaling"]
[]
[null,"The following observations can simplify some problems: If  X  is a random variable,"]
[null,"and  a  and  b  are constants,"]
[]
[null,null,"E[aX+b] = a∙E[X] + b"]
[]
[null,null,"Var(aX+b) = a2∙Var(X)"]
[]
[null,null,"StdDev(aX+b) = |a|∙StdDev(X)"]

{"sheet":"I.I.D. Sums (with independence)","index":6,"n_rows":50,"n_cols":9,"rows_kept":50,"cut":false,"hidden":false,"merged":[[18,7,20,11],[32,1,32,3],[0,1,1,8],[30,5,30,6]]}
[null,"Sums (and Averages) of Independent, Identically-Distributed Random Variables"]
[]
[]
[null,"Let  X  represent the end result of a random process which can be repeated. For"]
[null,"example, X  might represent retail sales of an item over the course of a week, or the"]
[null,"change in the price of a share of common stock from one business day to the next, or"]
[null,"the amount of money you win (or lose) after betting $1 on. a particular number on a"]
[null,"roulette wheel."]
[]
[null,"Let  X1, X2,…, Xn  be  n  independent observations of  X . Then"]
[]
[null,"E[ X1 + X2 +…+ Xn ] = E[X1] + E[X2] +…+ E[Xn] = n∙E[X] , and"]
[]
[null,"(since all of the pairwise covariances are zero)"]
[]
[null,"Var( X1 + X2 +…+ Xn ) = Var(X1) + Var(X2) +…+ Var(Xn) = n∙Var(X) ."]
[]
[null,"It follows that"]
[null,null,null,null,null,null,null,"Finance note: This is why the square-root of the time-to-expiration appears in the Black-Scholes option-pricing formula."]
[null,"StdDev( X1 + X2 +…+ Xn ) = √n∙StdDev(X) ."]
[]
[null,"Similarly,"]
[]
[null,"E[ (X1 + X2 +…+ Xn ) / n ] = E[X] ,"]
[]
[null,"Var( (X1 + X2 +…+ Xn ) / n ) = Var(X) / n , and"]
[]
[null,"StdDev( (X1 + X2 +…+ Xn ) / n ) = StdDev(X) / √n ."]
[]
[]
[null,"Let's check this via simulation. ",null,null,null,"distribution of X",null,"Pr(X£x)"]
[null,null,null,null,null,"x","Pr(X=x)",0]
[null,"16 simulated observations of X",null,null,null,1,0.2,{"$f":"H32+G33","v":0.2}]
[null,null,null,null,null,2,0.15,{"$f":"H33+G34","v":0.35}]
[null,null,{"$f":"LOOKUP(RAND(),$H$32:$H$35,$F$33:$F$36)","v":3},null,null,3,0.35,{"$f":"H34+G35","v":0.7}]
[null,null,{"$f":"LOOKUP(RAND(),$H$32:$H$35,$F$33:$F$36)","v":1},null,null,4,0.3,{"$f":"H35+G36","v":1}]
[null,null,4]
[null,null,4,null,null,"first","sum","average"]
[null,null,3,null,null,{"$f":"C35","v":3},{"$f":"SUM(C35:C50)","v":39},{"$f":"AVERAGE(C35:C50)","v":2.4375}]
[null,null,1]
[null,null,4,null,null,{"$f":"ADDRESS(ROW($F$39),COLUMN($F$39))","v":"$F$39"},{"$f":"ADDRESS(ROW($G$39),COLUMN($G$39))","v":"$G$39"},{"$f":"ADDRESS(ROW($H$39),COLUMN($H$39))","v":"$H$39"},"monitored cell"]
[null,null,1,null,null,2.7560600000000002,44.033479999999997,2.7520924999999998,"mean"]
[null,null,4,null,null,1.0883772884672027,4.3479015780122392,0.27174384862576495,"sample standard deviation"]
[null,null,4,null,null,1,24,1.5,"minimum"]
[null,null,2,null,null,4,60,3.75,"maximum"]
[null,null,1,null,null,100000,100000,100000,"number of simulation runs"]
[null,null,4]
[null,null,1,null,null,"4 times as large",null,"1/4 as large"]
[null,null,1]
[null,null,1]

{"sheet":"Managerial objective","index":7,"n_rows":32,"n_cols":2,"rows_kept":32,"cut":false,"hidden":false,"merged":[[0,1,0,8],[5,1,5,8],[17,1,17,8]]}
[null,"A Bit of Theory (and a Practical Conclusion)"]
[]
[null,"\"Chebyshev's Inequality\" is a very general statement concerning how much probability"]
[null,"can reside in the extreme \"tails\" of a probability distribution. For any random variable  X,"]
[]
[null,"Pr( | X - E[X] |  ≥  t∙StdDev(X) )  ≤  1/t2"]
[]
[null,"For example, this tells us that X can take values at least 2 standard deviations away"]
[null,"from its expected value no more than 25% of the time, and can be more than 3 or 4"]
[null,"standard deviations away from its expected value no more than 11.1% or 6.25% of the"]
[null,"time, respectively."]
[]
[null,"If I were to break my capital into n equal amounts, and invest in n stocks with annual"]
[null,"rates of return which vary independently, with the same expected return r and the same"]
[null,"standard deviation σ, then the rate of return on my portfolio, R, will have an expected"]
[null,"value of r, with a standard deviation of σ/√n . Then the inequality can be rewritten as"]
[]
[null,"Pr( | R - r |  ≥  ε )  ≤  1/n ∙ (σ2/ε2) ,"]
[]
[null,"which means that, with enough diversification (i.e., a large enough  n), the actual rate of"]
[null,"return on the portfolio will, just about certainly, be very close to  r, the average of the"]
[null,"expected returns across all the stocks in the portfolio. This means that I can't do any"]
[null,"better than to ask the managers of the firms in which I invest to each maximize the"]
[null,"expected return on that company's capital."]
[]
[null,"Qualifier: Of course, the rates of return are actually unlikely to be independent, since some"]
[null,"economic factors will affect all of the companies. As well, the returns are based on share"]
[null,"prices, which don't perfectly match internal return on investment. You'll discuss these"]
[null,"issues in detail in your finance classes, and you'll find that the same conclusion holds (for"]
[null,"all practical purposes) -"]
[]
[null,"As an investor, you want managers to maximize expected return on investment."]