{"text_format":"1","workbook":"xlsx","n_sheets":11,"sheets":["Expectation","LOOKUP","Simulation","Conditional expectation","A joint distribution","Array formulas","Independence","Plant expansion","Hairy Houdini","Answer","Answer via simulation"],"not_rendered":{"charts":1,"images":0,"macros":1,"external_links":1,"defined_names":32,"comments":2,"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":"Expectation","index":0,"n_rows":26,"n_cols":10,"rows_kept":26,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Exercises on Expectations"]
[]
[null,"Let  X  be a random variable with the distribution: ",null,null,null,null,"x","Pr(X=x)"]
[null,null,null,null,null,null,1,0.2]
[null,"What is  E[X] ?",null,null,{"$f":"SUMPRODUCT(G4:G7,H4:H7)","v":2.75},null,2,0.15,null,{"$f":"showformula(E5)","v":"=SUMPRODUCT(G4:G7,H4:H7)"}]
[null,null,null,null,null,null,3,0.35]
[null,null,null,null,null,null,4,0.3]
[]
[null,"What is  E[X2] ?"]
[]
[null,"The probability distribution of",null,null,null,null,"z","Pr(Z=z)"]
[null,"the random variable  Z = X2  is",null,null,null,null,{"$f":"G4^2","v":1},0.2]
[null,null,null,null,null,null,{"$f":"G5^2","v":4},0.15]
[null,"and therefore  E[X2]  is",null,null,{"$f":"SUMPRODUCT(G12:G15,H12:H15)","v":8.75},null,{"$f":"G6^2","v":9},0.35,null,{"$f":"showformula(E14)","v":"=SUMPRODUCT(G12:G15,H12:H15)"}]
[null,null,null,null,null,null,{"$f":"G7^2","v":16},0.3]
[null,"Excel also allows us to do this"]
[null,"calculation directly from the"]
[null,"original distribution table:",null,null,{"$f":"SUMPRODUCT(G4:G7^2,H4:H7)","v":8.75},null,null,null,null,{"$f":"showformula(E18)","v":"=SUMPRODUCT(G4:G7^2,H4:H7)"}]
[]
[null,"Notice that  E[X2]  is not the same as  ( E[X] )2 .",null,null,null,null,{"$f":"E5^2","v":7.5625},null,null,{"$f":"showformula(G20)","v":"=E5^2"}]
[]
[null,"What's the cumulative probability distribution of  X ?",null,null,null,null,"x","Pr(X≤x)"]
[null,null,null,null,null,null,1,{"$f":"SUM($H$4:H4)","v":0.2}]
[null,null,null,null,null,null,2,{"$f":"SUM($H$4:H5)","v":0.35}]
[null,null,null,null,null,null,3,{"$f":"SUM($H$4:H6)","v":0.7}]
[null,null,null,null,null,null,4,{"$f":"SUM($H$4:H7)","v":1}]

{"sheet":"LOOKUP","index":1,"n_rows":32,"n_cols":5,"rows_kept":32,"cut":false,"hidden":false,"merged":[[10,3,10,4],[10,1,10,2],[0,1,0,5]]}
[null,"The LOOKUP function"]
[]
[null,"The function =LOOKUP(value, lookup-range, result-range) looks for \"value\" in the"]
[null,"\"lookup-range,\" and returns the corresponding entry in the \"result-range.\" It"]
[null,"expects the entries in the lookup-range to be in increasing order, and if the value"]
[null,"isn't found, it selects the last entry in the lookup-range which is less than the"]
[null,"sought-for value."]
[null,null,null,null,"Please recall: Any colored tab"]
[null,"Here's the IRS 2008 Individual tax table:",null,null,"is optional reading."]
[]
[null,"taxable income",null,"taxes"]
[null,"from …","to …","paid on excess","plus"]
[null,0,{"$f":"B14","v":8025},0.1,0]
[null,8025,{"$f":"B15","v":32550},0.15,{"$f":"D13*(C13-B13)+E13","v":802.5}]
[null,32550,{"$f":"B16","v":78850},0.25,{"$f":"D14*(C14-B14)+E14","v":4481.25}]
[null,78850,{"$f":"B17","v":164550},0.28000000000000003,{"$f":"D15*(C15-B15)+E15","v":16056.25}]
[null,164550,{"$f":"B18","v":357700},0.33,{"$f":"D16*(C16-B16)+E16","v":40052.25}]
[null,357700,null,0.35,{"$f":"D17*(C17-B17)+E17","v":103791.75}]
[]
[]
[null,100000," taxable income"]
[]
[null,{"$f":"LOOKUP(B21,B13:B18,E13:E18)","v":16056.25},{"$f":"showformula(B23)","v":"=LOOKUP(B21,B13:B18,E13:E18)"}]
[null,{"$f":"LOOKUP(B21,B13:B18)","v":78850},{"$f":"showformula(B24)","v":"=LOOKUP(B21,B13:B18)"}]
[null,{"$f":"LOOKUP(B21,B13:B18,D13:D18)","v":0.28000000000000003},{"$f":"showformula(B25)","v":"=LOOKUP(B21,B13:B18,D13:D18)"}]
[]
[null,{"$f":"B23+(B21-B24)*B25","v":21978.25}," taxes",{"$f":"showformula(B27)","v":"=B23+(B21-B24)*B25"}]
[]
[null,"The formula in cell B23 looks in the range B13:B18 for the value $100,000. The last"]
[null,"entry less than or equal to this is the fourth, so the fourth entry in E13:E18 is"]
[null,"returned. The formula in cell B24 shows a variant of the LOOKUP function: If the"]
[null,"result-range is not specified, it's assumed to be the same as the lookup-range."]

{"sheet":"Simulation","index":2,"n_rows":38,"n_cols":10,"rows_kept":38,"cut":false,"hidden":false,"merged":[[20,5,21,6],[0,1,0,8],[7,7,7,8],[14,7,14,8]]}
[null,"Simulating a Random Variable, Using its Tabulated Distribution"]
[]
[null,"How could we build a spreadsheet cell which simulates  X  ?"]
[]
[null,"One way is to use Excel's \"lookup\" function. We need a table"]
[null,"containing the cumulative distribution of  X."]
[null,null,null,null,null,null,null,null,null,"cumulative"]
[null,{"$f":"LOOKUP(RAND(),J9:J12,H10:H13)","v":3},"simulated value of  X",null,null,null,null,"distribution",null,"Pr(X≤x)"]
[null,null,{"$f":"showformula(B8)","v":"=LOOKUP(RAND(),J9:J12,H10:H13)"},null,null,null,null,"x","Pr(X=x)",0]
[null,null,null,null,null,null,null,1,0.2,0.2]
[null,null,null,null,null,null,null,2,0.15,0.35]
[null,"Notice the offset in the two ranges: If, for example,",null,null,null,null,null,3,0.35,0.7]
[null,"RAND() takes the value 0.4527, the third value in the",null,null,null,null,null,4,0.3,1]
[null,"range J9:J12 (35%) is the final entry which is less than"]
[null,"or equal to 0.4527, and therefore the third value in the"]
[null,"range H10:H13 (3) is returned. The same value (3) will"]
[null,"be returned whenever RAND() is at least 0.35 and less"]
[null,"than 0.70, i.e., 35% of the time."]
[]
[null,"Let's test this simulation."]
[null,null,null,null,null,"estimated E[X]"]
[null,"is X=1?","is X=2?","is X=3?","is X=4?"]
[null,{"$f":"IF(B8=1,1,0)","v":0},{"$f":"IF(B8=2,1,0)","v":0},{"$f":"IF(B8=3,1,0)","v":1},{"$f":"IF(B8=4,1,0)","v":0}]
[]
[null,{"$f":"ADDRESS(ROW($B$23),COLUMN($B$23))","v":"$B$23"},{"$f":"ADDRESS(ROW($C$23),COLUMN($C$23))","v":"$C$23"},{"$f":"ADDRESS(ROW($D$23),COLUMN($D$23))","v":"$D$23"},{"$f":"ADDRESS(ROW($E$23),COLUMN($E$23))","v":"$E$23"},{"$f":"ADDRESS(ROW($B$8),COLUMN($B$8))","v":"$B$8"},"monitored cell"]
[null,0.20039999999999999,0.14974000000000001,0.35110000000000002,0.29876000000000003,2.7482199999999999,"mean"]
[null,0.40030168924752757,0.35681816880878686,0.47731652842813133,0.4577166781378787,1.0893661913181387,"sample standard deviation"]
[null,0,0,0,0,1,"minimum"]
[null,1,1,1,1,4,"maximum"]
[null,100000,100000,100000,100000,100000,"number of simulation runs"]
[]
[null,{"$f":"1.96*B27/SQRT(B30)","v":2.481095574900837E-3},{"$f":"1.96*C27/SQRT(C30)","v":2.2115819229737836E-3},{"$f":"1.96*D27/SQRT(D30)","v":2.958438493567713E-3},{"$f":"1.96*E27/SQRT(E30)","v":2.8369573628853123E-3},{"$f":"1.96*F27/SQRT(F30)","v":6.7519616062742017E-3},"margin of error (95% confidence)"]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"`"]

{"sheet":"Conditional expectation","index":3,"n_rows":31,"n_cols":9,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Conditional Expectation"]
[]
[null,"What is  E[ X | X is even ]?",null,null,null,null,null,"x","Pr(X=x)"]
[null,null,null,null,null,null,null,1,0.2]
[null,"Given that  X  is even, the conditional probability distribution",null,null,null,null,null,2,0.15]
[null,"of  X  is:",null,null,null,null,null,3,0.35]
[null,null,"x","Pr(X=x)",null,null,null,4,0.3]
[null,null,2,{"$f":"I5/(I5+I7)","v":0.33333333333333337}]
[null,null,4,{"$f":"I7/(I5+I7)","v":0.66666666666666674}]
[]
[null,"Therefore, E[ X | X is even ]  is",null,null,{"$f":"SUMPRODUCT(C8:C9,D8:D9)","v":3.3333333333333339}]
[]
[null,"What is  E[ X | X is odd ]?"]
[]
[null,"Given that  X  is odd, the conditional probability distribution"]
[null,"of  X  is:"]
[null,null,"x","Pr(X=x)"]
[null,null,1,{"$f":"I4/(I4+I6)","v":0.36363636363636365}]
[null,null,3,{"$f":"I6/(I4+I6)","v":0.63636363636363624}]
[]
[null,"Therefore, E[ X | X is odd ]  is",null,null,{"$f":"SUMPRODUCT(C18:C19,D18:D19)","v":2.2727272727272725}]
[]
[null,"What is  E[X] ?"]
[]
[null,"Directly, it's",null,{"$f":"SUMPRODUCT(H4:H7,I4:I7)","v":2.75}]
[]
[null,"But we can also work it out \"case by case.\""]
[]
[null,"E[X] = E[ X | X is even ]∙Pr( X is even ) + E[ X | X is odd ]∙Pr( X is odd )"]
[]
[null,"and this yields",null,{"$f":"E11*(I5+I7)+E21*(I4+I6)","v":2.75}]

{"sheet":"A joint distribution","index":4,"n_rows":82,"n_cols":14,"rows_kept":82,"cut":false,"hidden":false,"merged":[[24,2,26,2],[0,1,0,8],[7,2,9,2],[5,4,5,7],[22,4,22,7]]}
[null,"Joint Probability Distributions"]
[]
[null,"The joint distribution of two (or more) random variables indicates the likelihood of"]
[null,"the variables taking any particular combination of values."]
[]
[null,null,null,null,"X"]
[null,null,null,null,2,3,5,15]
[null,null,"Y",10,0.05,0.12,0.03,0.18]
[null,null,null,20,0.02,7.0000000000000007E-2,0.08,0.09]
[null,null,null,30,0.14000000000000001,0.13,0,0.09]
[null,null,null,null,null,null,null,null,{"$f":"SUM(E8:H10)","v":1},null,"necessarily 100%"]
[null,"The table indicates, for example, that  Pr( X=3, Y=30 ) = 13% ."]
[]
[]
[]
[null,"What is the (marginal) probability distribution of  X ?",null,null,null,null,"x","Pr(X=x)"]
[null,null,null,null,null,null,2,{"$f":"SUM(E8:E10)","v":0.21000000000000002},{"$f":"showformula(H17)","v":"=SUM(E8:E10)"}]
[null,null,null,null,null,null,3,{"$f":"SUM(F8:F10)","v":0.32},{"$f":"showformula(H18)","v":"=SUM(F8:F10)"}]
[null,"The marginal distributions of both X and Y",null,null,null,null,5,{"$f":"SUM(G8:G10)","v":0.11},{"$f":"showformula(H19)","v":"=SUM(G8:G10)"}]
[null,"can, of course, be written at the \"margins\"",null,null,null,null,15,{"$f":"SUM(H8:H10)","v":0.36},{"$f":"showformula(H20)","v":"=SUM(H8:H10)"}]
[null,"of the joint-distribution table:"]
[]
[null,null,null,null,"X"]
[null,null,null,null,2,3,5,15]
[null,null,"Y",10,0.05,0.12,0.03,0.18,{"$f":"SUM(E25:H25)","v":0.38},{"$f":"showformula(I25)","v":"=SUM(E25:H25)"}]
[null,null,null,20,0.02,7.0000000000000007E-2,0.08,0.09,{"$f":"SUM(E26:H26)","v":0.26},{"$f":"showformula(I26)","v":"=SUM(E26:H26)"}]
[null,null,null,30,0.14000000000000001,0.13,0,0.09,{"$f":"SUM(E27:H27)","v":0.36},{"$f":"showformula(I27)","v":"=SUM(E27:H27)"}]
[null,null,null,null,{"$f":"SUM(E25:E27)","v":0.21000000000000002},{"$f":"SUM(F25:F27)","v":0.32},{"$f":"SUM(G25:G27)","v":0.11},{"$f":"SUM(H25:H27)","v":0.36}]
[]
[null,"What is Pr( X = 3 | Y = 10 ) ?"]
[]
[null,"By definition, Pr( X = 3 | Y = 10 ) = Pr( X = 3 and Y = 10 ) / Pr( Y = 10 ) =",null,null,null,null,null,null,{"$f":"F25/I25","v":0.31578947368421051},{"$f":"showformula(I32)","v":"=F25/I25"}]
[]
[null,"What is the probability distribution of  X+Y ?"]
[]
[null,"Z = X+Y is a random variable with the probability distribution:",null,null,null,null,null,"z","Pr(Z=z)"]
[null,null,null,null,null,null,null,12,{"$f":"E25","v":0.05},{"$f":"showformula(I37)","v":"=E25"}]
[null,"What is  E[ X+Y] ?",null,null,null,null,null,13,{"$f":"F25","v":0.12},{"$f":"showformula(I38)","v":"=F25"}]
[null,null,null,null,null,null,null,15,{"$f":"G25","v":0.03},"=G25"]
[null,"From the table to the right:",null,null,{"$f":"SUMPRODUCT(H37:H46,I37:I46)","v":27.13},null,null,22,{"$f":"E26","v":0.02},"=E26"]
[null,"Equivalently, since",null,null,null,null,null,23,{"$f":"F26","v":7.0000000000000007E-2},"=F26"]
[null,"E[ X+Y] = E[X] + E[Y] ,",null,null,{"$f":"SUMPRODUCT(E24:H24,E28:H28)+SUMPRODUCT(D25:D27,I25:I27)","v":27.129999999999995},null,null,25,{"$f":"H25+G26","v":0.26},"=H25+G26"]
[null,null,null,null,null,null,null,32,{"$f":"E27","v":0.14000000000000001},"=E27"]
[null,null,null,null,null,null,null,33,{"$f":"F27","v":0.13},"=F27"]
[null,"For any random variables  X  and  Y , ",null,null,null,null,null,35,{"$f":"H26+G27","v":0.09},"=H26+G27"]
[null,"E[ X+Y ] = E[X] + E[Y] .",null,null,null,null,null,45,{"$f":"H27","v":0.09},"=H27"]
[null,null,null,null,null,null,null,null,{"$f":"SUM(I37:I46)","v":1},null,"necessarily 100%"]
[]
[null,"What is  E[ XY ] ?"]
[]
[null,"We could build a table for the distribution of  W = XY , and then compute the expected"]
[null,"value from that table. An Excel \"array\" formula does precisely the same arithmetic"]
[null,"without requiring the intermediate step:",null,null,null,{"$f":"SUM(E24:H24*D25:D27*E25:H27)","v":133.69999999999999},"{=SUM(E24:H24*D25:D27*E25:H27)}",null,null,null,"As is explained on the next tab, this formula was"]
[null,null,null,null,null,null,null,null,null,null,"entered by typing Ctrl+Shift+Enter."]
[null,"Notice that  E[X]∙E[Y] is",null,null,null,{"$f":"SUMPRODUCT(E24:H24,E28:H28)*SUMPRODUCT(D25:D27,I25:I27)","v":145.13399999999996}]
[null,null,null,null,null,null,null,null,null,null,"Excel automatically expanded E24:H24 to"]
[null,"E[XY]  is not necessarily equal to  E[X]∙E[Y] !"]
[null,null,null,null,null,null,null,null,null,null,2,3,5,15]
[null,null,null,null,null,null,null,null,null,null,2,3,5,15]
[null,null,null,null,null,null,null,null,null,null,2,3,5,15]
[]
[null,null,null,null,null,null,null,null,null,null,"It also expanded D25:D27 to"]
[]
[null,null,null,null,null,null,null,null,null,null,10,10,10,10]
[null,null,null,null,null,null,null,null,null,null,20,20,20,20]
[null,null,null,null,null,null,null,null,null,null,30,30,30,30]
[]
[null,null,null,null,null,null,null,null,null,null,"Finally, it copied"]
[]
[null,null,null,null,null,null,null,null,null,null,0.05,0.12,0.03,0.18]
[null,null,null,null,null,null,null,null,null,null,0.02,7.0000000000000007E-2,0.08,0.09]
[null,null,null,null,null,null,null,null,null,null,0.14000000000000001,0.13,0,0.09]
[]
[null,null,null,null,null,null,null,null,null,null,"It then did the indicated arithmetic on a cell-by-cell basis:"]
[]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K58*K64*K70","v":1},{"$f":"L58*L64*L70","v":3.5999999999999996},{"$f":"M58*M64*M70","v":1.5},{"$f":"N58*N64*N70","v":27}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K59*K65*K71","v":0.8},4.2,8,27]
[null,null,null,null,null,null,null,null,null,null,{"$f":"K60*K66*K72","v":8.4},{"$f":"L60*L66*L72","v":11.700000000000001},{"$f":"M60*M66*M72","v":0},{"$f":"N60*N66*N72","v":40.5}]
[]
[null,null,null,null,null,null,null,null,null,null,"And it applied the SUM function to this entire array:"]
[]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(K76:N78)","v":133.69999999999999}]

{"sheet":"Array formulas","index":5,"n_rows":58,"n_cols":9,"rows_kept":58,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Working with Array Formulas (an optional Excel skill)"]
[]
[null,"The SUMPRODUCT function returns the",null,null,null,"x","Pr( X = x )"]
[null,"sum of the products of matching terms",null,null,null,1,0.2]
[null,"in two (or more) lists. For example, the",null,null,null,2,0.5,null,"Convention: A colored tab at the bottom of"]
[null,"expected value of X, given the probability",null,null,null,5,0.3,null,"a page indicates optional material."]
[null,"distribution at the right, is"]
[]
[null,{"$f":"SUMPRODUCT(F4:F6,G4:G6)","v":2.7},"=SUMPRODUCT(F4:F6,G4:G6)"]
[]
[null,"However, there's a slicker way to do the same calculation:"]
[]
[null,{"$f":"SUM(F4:F6*G4:G6)","v":2.7},"=SUM(F4:F6*G4:G6), entered as an array formula."]
[]
[null,"Array formulas are very powerful ... and potentially somewhat confusing."]
[]
[null,"An \"array\" has some number of rows, and some number of columns. In an"]
[null,"array formula, Excel finds the largest number of rows, R, in any array in the"]
[null,"formula, and the largest number of columns, C. It then expands any array"]
[null,"that is smaller than R-by-C in one of two ways. If R or C is 1, it copies the"]
[null,"single row or column to fill an entire R-by-C array. Otherwise, it adds extra"]
[null,"cells with the value NA (not available)."]
[]
[null,"Then it does all the arithmetic on a cell-by-cell basis. And it"]
[null,"evaluates any formulas that take array inputs using the resulting"]
[null,"array as the input."]
[]
[null,null,1,2,3]
[null,4,{"$f":"C28:E28*B29:B31","v":4},8,12]
[null,5,5,10,15]
[null,6,6,12,18,{"$f":"SUM(C28:E28*B29:B31)","v":90}]
[]
[null,"All nine cells inside the box are part of the same array,"]
[null,"defined by the single formula:",null,null,"{=C28:E28*B29:B31}"]
[]
[null,"The formula was entered as an array formula. First, I"]
[null,"highlighted C29:E31. Then I typed =C28:E28*B29:B31, and"]
[null,"entered the formula by pressing Cntrl+Shift+Enter."]
[]
[null,"What Excel did was determine R = 3 (the number of rows in"]
[null,"B29:B31) and C = 3 (the number of columns in C28:E28)."]
[null,"It expanded B29:B31 into"]
[]
[null,null,4,4,4]
[null,null,5,5,5]
[null,null,6,6,6]
[]
[null,"and C28:E28 into"]
[]
[null,null,1,2,3]
[null,null,1,2,3]
[null,null,1,2,3]
[]
[null,"Then it computed the products of matching cells, and put them"]
[null,"into the space allocated to the array formula."]
[]
[null,"The formula in F31 is =SUM(C28:E28*B29:B31), again entered"]
[null,"by pressing Cntrl+Shift+Enter."]

{"sheet":"Independence","index":6,"n_rows":85,"n_cols":9,"rows_kept":85,"cut":false,"hidden":false,"merged":[[0,1,0,8],[5,4,5,7],[7,2,9,2],[63,4,63,7],[65,2,68,2],[20,4,20,7],[22,2,24,2],[42,4,42,7],[44,2,46,2]]}
[null,"Independent Random Variables"]
[]
[null,"X  and  Y  are independent if knowledge of the value of one doesn't affect your"]
[null,"assessment of the likelihood of the other taking any specific value. In the previous"]
[null,"example, X and Y were not independent."]
[null,null,null,null,"X"]
[null,null,null,null,2,3,5,15]
[null,null,"Y",10,0.05,0.12,0.03,0.18,{"$f":"SUM(E8:H8)","v":0.38}]
[null,null,null,20,0.02,7.0000000000000007E-2,0.08,0.09,{"$f":"SUM(E9:H9)","v":0.26}]
[null,null,null,30,0.14000000000000001,0.13,0,0.09,{"$f":"SUM(E10:H10)","v":0.36}]
[null,null,null,null,{"$f":"SUM(E8:E10)","v":0.21000000000000002},{"$f":"SUM(F8:F10)","v":0.32},{"$f":"SUM(G8:G10)","v":0.11},{"$f":"SUM(H8:H10)","v":0.36}]
[]
[null,"Show that  X  and  Y , defined by the joint distribution above, are not independent."]
[]
[null,"It takes only a single example to show this:",null,null,null,"Pr( X=2 ) =",null,{"$f":"E11","v":0.21000000000000002}]
[null,null,null,null,null,"Pr( X=2 | Y=10) =",null,{"$f":"E8/I8","v":0.13157894736842105}]
[]
[null,"If  X  and  Y  are independent, then  Pr( X=x )∙Pr( Y=y ) = Pr( X=x and Y=y ) for"]
[null,"every pair of values  x  and  y ."]
[]
[null,null,null,null,"U"]
[null,null,null,null,2,3,5,15]
[null,null,"V",10,{"$f":"E$26*$I23","v":7.9799999999999996E-2},{"$f":"F$26*$I23","v":0.1216},4.1800000000000004E-2,0.1368,0.38]
[null,null,null,20,{"$f":"E$26*$I24","v":5.4600000000000003E-2},8.320000000000001E-2,2.86E-2,9.3600000000000003E-2,0.26]
[null,null,null,30,{"$f":"E$26*$I25","v":7.5600000000000001E-2},0.1152,3.9599999999999996E-2,0.12959999999999999,0.36]
[null,null,null,null,0.21,0.32,0.11,0.36]
[]
[null,"U  and  V  have the same marginal distributions as do  X  and  Y , respectively, but  U  and  V"]
[null,"are independent random variables."]
[]
[null,"The marginal distributions of  X  and  Y  separately convey no information"]
[null,"concerning whether or not  X  and  Y  are independent."]
[]
[]
[null,"Compute  E[ UV ]  and  E[U]∙E[V] ."]
[]
[null,"E[ UV ] =",null,{"$f":"SUM(E22:H22*D23:D25*E23:H25)","v":145.13399999999999}]
[null,"E[U]∙E[V] =",null,{"$f":"SUMPRODUCT(E22:H22,E26:H26)*SUMPRODUCT(D23:D25,I23:I25)","v":145.13399999999996}]
[]
[null,"These two quantities are equal whenever  U  and  V  are independent."]
[null,"But, just because these two amounts are equal, we can't conclude that"]
[null,"U  and  V  are independent!"]
[null,null,null,null,"S"]
[null,null,null,null,2,3,5,15]
[null,null,"T",10,0.21,2.2103790169330118E-2,0,0.14789620983066992,0.38]
[null,null,null,20,0,0.13066799896475095,2.6489304835906481E-2,0.10284269619934257,0.26]
[null,null,null,30,0,0.16722821086591899,8.3510695164093526E-2,0.10926109396998747,0.36]
[null,null,null,null,0.21,0.32,0.11,0.36]
[]
[null,"E[ ST ] =",null,{"$f":"SUM(E44:H44*D45:D47*E45:H47)","v":145.13399999999996}]
[null,"E[S]∙E[T] =",null,{"$f":"SUMPRODUCT(E44:H44,E48:H48)*SUMPRODUCT(D45:D47,I45:I47)","v":145.13399999999996}]
[]
[null,"But  S  and  T  are certainly not independent."]
[]
[null,"And, of course, we saw a few tabs ago that"]
[]
[null,"E[ XY ] =",null,133.69999999999999]
[null,"E[X]∙E[Y] =",null,145.13399999999996]
[null,null,null,null,null,null,null,"x","Pr(X=x)"]
[null,"From the very first tab (with  X  having a different distribution):",null,null,null,null,null,1,0.2]
[null,null,null,null,null,null,null,2,0.15]
[null,null,null,null,null,null,null,3,0.35]
[null,"Let  Y = X , so the joint distribution is",null,null,null,null,null,4,0.3]
[null,null,null,null,"X"]
[null,null,null,null,1,2,3,4]
[null,null,"Y",1,0.2,0,0,0,{"$f":"SUM(E66:H66)","v":0.2}]
[null,null,null,2,0,0.15,0,0,{"$f":"SUM(E67:H67)","v":0.15}]
[null,null,null,3,0,0,0.35,0,{"$f":"SUM(E68:H68)","v":0.35}]
[null,null,null,4,0,0,0,0.3,{"$f":"SUM(E69:H69)","v":0.3}]
[null,null,null,null,{"$f":"SUM(E66:E69)","v":0.2},{"$f":"SUM(F66:F69)","v":0.15},{"$f":"SUM(G66:G69)","v":0.35},{"$f":"SUM(H66:H69)","v":0.3}]
[]
[null,"Then (as we've already computed)"]
[]
[null,"E[ XY ] = E[ X2 ] =",null,null,{"$f":"SUM(E65:H65*D66:D69*E66:H69)","v":8.75}]
[null,"E[X]∙E[Y] = ( E[X] )2 =",null,null,{"$f":"SUMPRODUCT(H60:H63*I60:I63)^2","v":7.5625}]
[]
[]
[null,"In summary:"]
[]
[null,"If  X  and  Y  are independent, then  E[ XY ] = E[X]∙E[Y] ."]
[]
[null,"E[ XY ] = E[X]∙E[Y] may be true even when X and Y are not independent."]
[]
[null,"In general, any of  E[ XY ] < E[X]∙E[Y] , E[ XY ] = E[X]∙E[Y] , or E[ XY ] > E[X]∙E[Y]"]
[null,"might be true."]

{"sheet":"Plant expansion","index":7,"n_rows":37,"n_cols":8,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,1,0,8],[10,2,11,2],[10,3,11,3],[10,4,11,5],[10,6,11,7]]}
[null,"Comparing Plant Expansions"]
[]
[null,"The J.R. Ryland Computer company is considering a plant expansion that will enable"]
[null,"the company to begin production of a new computer product. The company’s president"]
[null,"must determine whether to make the expansion a medium- or large-scale project. An"]
[null,"uncertainty is the demand for the new product, which for planning purposes may be low"]
[null,"demand, medium demand, or high demand. The probability estimates for demand are"]
[null,"0.20, 0.50, and 0.30. respectively. Writing the annual profit in $000s, the firm’s planners"]
[null,"have developed the following profit forecast for the medium- and large-scale expansion"]
[null,"projects."]
[null,null,null,null,"medium-scale\nexpansion profits",null,"large-scale\nexpansion profits"]
[]
[null,null,null,null,null,"Prob",null,"Prob"]
[null,null,null,"low",50,0.2,0,0.2]
[null,null,"demand","medium",150,0.5,100,0.5]
[null,null,null,"high",200,0.3,300,0.3]
[]
[]
[null,"Compute the expected value for the profit associated with the two expansion"]
[null,"alternatives. Which decision is preferred if the objective is to maximize"]
[null,"expected profit?"]
[null,null,null,null,null,"expansion profits"]
[null,null,null,"probability",null,"medium","large"]
[null,null,null,0.2,"low",50,0]
[null,null,"demand",0.5,"medium",150,100]
[null,null,null,0.3,"high",200,300]
[]
[null,"Answer:",null,null,"expected profit",{"$f":"SUMPRODUCT($D$24:$D$26,F24:F26)","v":145},{"$f":"SUMPRODUCT($D$24:$D$26,G24:G26)","v":140}]
[]
[null,"Note that the profit numbers themselves are, in reality, expectations - the expected"]
[null,"profit (in the face of current uncertainty about future interest rates, energy prices, and"]
[null,"the like) when demand is low, medium, or high - and our actual calculations above are"]
[null,"actually of the form:"]
[]
[null,"E[profit] = ","   E[ profit | low demand ]∙Pr( low demand )"]
[null,null,"+ E[ profit } medium demand ]∙Pr( medium demand )"]
[null,null,"+ E[ profit | high demand ]∙Pr( high demand ) ."]

{"sheet":"Hairy Houdini","index":8,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"It's a Kind of Magic"]
[]
[null,"Hairy Houdini, a hirsute magician, uses his long beard to conceal the machinations"]
[null,"behind his tricks. His final trick is to “magically” create a number of pigeons that he"]
[null,"releases into the air. (Really, they’re hidden in a pouch behind his beard.) Each time he"]
[null,"attempts another release, he knows there’s a 90% he’ll successfully fool the audience."]
[null,"He figures, on the basis of past experience, that his end-of-show tips will add up to"]
[null,"about $10 for each successful release if he fools the audience every time. However, if he"]
[null,"fails to fool the audience on any release, the show will end immediately, and he’ll only"]
[null,"receive about $6 in tips for each successful release. How many pigeons should he try to"]
[null,"release (i.e., after how many consecutive successes should he stop voluntarily –"]
[null,"equivalently, how many pigeons should he put in the pouch before his act)?"]
[]
[null,"Hint: This is not a \"single-period inventory\" problem. However, it yields to either a"]
[null,"marginal-analysis approach, or brute force."]
[]
[null,"The answer is on the next tab."]

{"sheet":"Answer","index":9,"n_rows":51,"n_cols":12,"rows_kept":51,"cut":false,"hidden":false,"merged":[[50,6,53,8],[4,6,4,8],[4,1,4,4],[0,1,0,8],[5,1,7,4]]}
[null,"It's a Kind of Magic (answer)"]
[]
[null,23,"pigeons"]
[]
[null,"Approach 1 (marginal analysis):",null,null,null,null,"Approach 2 (brute force):"]
[null,"given k successful releases, expected marginal profit from trying for one more",null,null,null,null,null,null,null,null,"n","E[profit]"]
[null,null,null,null,null,null,null,"put n pigeons in pouch",null,null,23,{"$f":"SUMPRODUCT(H10:H49,I10:I49)","v":57.36791648546788}]
[null,null,null,null,null,null,null,"Pr( fail first on"]
[null,null,"k",null,null,null,"k","pigeon k )","profit",null,"n",{"$f":"L7","v":57.36791648546788}]
[null,null,1,{"$f":"0.9*10+0.1*((-4)*C10)","v":8.6},null,null,1,{"$f":"(0.9)^(G10-1)*0.1","v":0.1},{"$f":"IF(G10<=$K$7,6*(G10-1),10*$K$7)","v":0},null,1,8.9999999999999982]
[null,null,2,8.1999999999999993,null,null,2,9.0000000000000011E-2,6,null,2,16.739999999999995]
[null,null,3,7.8,null,null,3,8.1000000000000016E-2,12,null,3,23.381999999999991]
[null,null,4,7.4,null,null,4,7.2900000000000006E-2,18,null,4,29.068199999999987]
[null,null,5,7,null,null,5,6.5610000000000016E-2,24,null,5,33.923339999999996]
[null,null,6,6.6,null,null,6,5.9049000000000018E-2,30,null,6,38.056769999999979]
[null,null,7,6.1999999999999993,null,null,7,5.314410000000002E-2,36,null,7,41.564280599999982]
[null,null,8,5.8,null,null,8,4.7829690000000015E-2,42,null,8,44.529721379999977]
[null,null,9,5.4,null,null,9,4.3046721000000017E-2,48,null,9,47.026431198000004]
[null,null,10,5,null,null,10,3.874204890000002E-2,54,null,10,49.118501838599997]
[null,null,11,4.5999999999999996,null,null,11,3.4867844010000017E-2,60,null,11,50.861894039100001]
[null,null,12,4.1999999999999993,null,null,12,3.138105960900002E-2,66,null,12,52.305422781113997]
[null,null,13,3.8,null,null,13,2.8242953648100019E-2,72,null,13,53.491626834334184]
[null,null,14,3.3999999999999995,null,null,14,2.541865828329002E-2,78,null,14,54.457535849099195]
[null,null,15,3,null,null,15,2.2876792454961017E-2,84,null,15,55.235346792567867]
[null,null,16,2.5999999999999996,null,null,16,2.0589113209464913E-2,90,null,16,55.853020188851815]
[null,null,17,2.1999999999999993,null,null,17,1.8530201888518425E-2,96,null,17,56.334805437953278]
[null,null,18,1.7999999999999998,null,null,18,1.6677181699666584E-2,102,null,18,56.701703435345934]
[null,null,19,1.3999999999999995,null,null,19,1.5009463529699923E-2,108,null,19,56.971873778880557]
[null,null,20,1,null,null,20,1.3508517176729934E-2,114,null,20,57.160993019354748]
[null,null,21,0.59999999999999964,null,null,21,1.2157665459056942E-2,120,null,21,57.282569673945311]
[null,null,22,0.19999999999999929,null,null,22,1.0941898913151248E-2,126,null,22,57.34822106742422]
[null,null,23,-0.20000000000000107,null,null,23,9.8477090218361246E-3,132,null,23,57.36791648546788]
[null,null,24,-0.60000000000000142,null,null,24,8.8629381196525109E-3,230,null,24,57.350190609228591]
[null,null,25,-1,null,null,25,7.9766443076872608E-3,230,null,25,57.302330743382456]
[null,null,null,null,null,null,26,7.1789798769185346E-3,230,null,26,57.230540944613267]
[null,null,null,null,null,null,27,6.4610818892266823E-3,230,null,27,57.14008579816408]
[null,null,null,null,null,null,28,5.8149737003040138E-3,230,null,28,57.035416271558603]
[null,null,null,null,null,null,29,5.2334763302736131E-3,230,null,29,56.920279792292575]
[null,null,null,null,null,null,30,4.7101286972462521E-3,230,null,30,56.797816446164177]
[null,null,null,null,null,null,31,4.2391158275216266E-3,230,null,31,56.670642971338523]
[null,null,null,null,null,null,32,3.8152042447694638E-3,230,null,32,56.540926027016361]
[null,null,null,null,null,null,33,3.4336838202925178E-3,230,null,33,56.410446041845233]
[null,null,null,null,null,null,34,3.0903154382632661E-3,230,null,34,56.280652793438172]
[null,null,null,null,null,null,35,2.7812838944369397E-3,230,null,35,56.152713734294082]
[null,null,null,null,null,null,36,2.503155504993246E-3,230,null,36,56.027555959044406]
[null,null,null,null,null,null,37,2.2528399544939214E-3,230,null,37,55.90590260150173]
[null,null,null,null,null,null,38,2.0275559590445295E-3,230,null,38,55.788304355877138]
[null,null,null,null,null,null,39,1.8248003631400765E-3,230,null,39,55.675166733362452]
[null,null,null,null,null,null,"40 or more",{"$f":"1-SUM(H10:H48)","v":1.6423203268260189E-2},230]
[]
[null,null,null,null,null,null,"if we wish to examine cases involving the release of 40 pigeons or more, this table must be extended."]

{"sheet":"Answer via simulation","index":10,"n_rows":54,"n_cols":17,"rows_kept":54,"cut":false,"hidden":false,"merged":[[0,1,0,8],[2,3,3,3],[2,1,3,1],[2,2,3,2],[2,4,3,4]]}
[null,"Answer via Simulation"]
[]
[null,"release","still going?","success on this try?","first failure on this try",null,{"$f":"MAX(E5:E54)","v":3},"time of first failure (and 0 if he succeeds at least 50 times)"]
[null,"release",null,null,null,null,{"$f":"IF(G3=0,51,G3)","v":3},"time of first failure (set to 51 if the first 50 releases succeed)"]
[null,1,1,{"$f":"IF(RAND()<=0.9,1,0)","v":1},{"$f":"IF(C5=1,IF(D5=0,B5,0),0)","v":0}]
[null,2,{"$f":"IF(C5=1,IF(D5=1,1,0),0)","v":1},{"$f":"IF(RAND()<=0.9,1,0)","v":1},{"$f":"IF(C6=1,IF(D6=0,B6,0),0)","v":0},null,5,10,15,20,25,30,35,40,45,50]
[null,3,1,0,3,null,{"$f":"IF(G6<$G$4,10*G6,6*($G$4-1))","v":12},12,12,12,12,12,12,12,12,12]
[null,4,0,1,0]
[null,5,0,1,0,null,{"$f":"ADDRESS(ROW($G$7),COLUMN($G$7))","v":"$G$7"},{"$f":"ADDRESS(ROW($H$7),COLUMN($H$7))","v":"$H$7"},{"$f":"ADDRESS(ROW($I$7),COLUMN($I$7))","v":"$I$7"},{"$f":"ADDRESS(ROW($J$7),COLUMN($J$7))","v":"$J$7"},{"$f":"ADDRESS(ROW($K$7),COLUMN($K$7))","v":"$K$7"},{"$f":"ADDRESS(ROW($L$7),COLUMN($L$7))","v":"$L$7"},{"$f":"ADDRESS(ROW($M$7),COLUMN($M$7))","v":"$M$7"},{"$f":"ADDRESS(ROW($N$7),COLUMN($N$7))","v":"$N$7"},{"$f":"ADDRESS(ROW($O$7),COLUMN($O$7))","v":"$O$7"},{"$f":"ADDRESS(ROW($P$7),COLUMN($P$7))","v":"$P$7"},"monitored cell"]
[null,6,0,1,0,null,33.890619999999998,48.965339999999998,55.012500000000003,56.800379999999997,56.883719999999997,56.503439999999998,55.871339999999996,55.213079999999998,54.770220000000002,54.462879999999998,"mean"]
[null,7,0,1,0,null,20.063621345724581,39.570233973513062,52.805566070520257,60.292585479594734,63.831297208396393,65.247541567351064,64.946351297743192,63.593282648166387,62.368379963726753,61.279237051015372,"sample standard deviation"]
[null,8,0,1,0,null,0,0,0,0,0,0,0,0,0,0,"minimum"]
[null,9,0,1,0,null,50,100,150,200,250,300,350,400,450,500,"maximum"]
[null,10,0,1,0,null,100000,100000,100000,100000,100000,100000,100000,100000,100000,100000,"number of simulation runs"]
[null,11,0,1,0]
[null,12,0,1,0,null,{"$f":"1.96*G11/SQRT(G14)","v":0.12435561346477805},{"$f":"1.96*H11/SQRT(H14)","v":0.24525885112806933},0.32729228935800903,0.37369731642639303,0.39563047895609305,0.40440845243551027,0.40254165580386975,0.39415524942793717,0.38656322393396508,0.37981264622283312,"margin of error (95% confidence)"]
[null,13,0,1,0]
[null,14,0,1,0]
[null,15,0,1,0]
[null,16,0,1,0]
[null,17,0,1,0]
[null,18,0,1,0]
[null,19,0,1,0]
[null,20,0,1,0]
[null,21,0,1,0]
[null,22,0,1,0]
[null,23,0,1,0]
[null,24,0,1,0]
[null,25,0,1,0]
[null,26,0,1,0]
[null,27,0,1,0]
[null,28,0,1,0]
[null,29,0,1,0]
[null,30,0,1,0]
[null,31,0,1,0]
[null,32,0,1,0]
[null,33,0,1,0]
[null,34,{"$f":"IF(C37=1,IF(D37=1,1,0),0)","v":0},1,0]
[null,35,0,1,0]
[null,36,0,1,0]
[null,37,0,1,0,null,"The expected profit curve appears to top out somewhere between 20 and 25 releases."]
[null,38,0,1,0]
[null,39,0,1,0,null,"We'll see in a while that the expected number of successful releases before the"]
[null,40,0,1,0,null,"first failure is 9.0. Therefore, the expected profit, as the planned number of releases"]
[null,41,0,1,0,null,"grows larger and larger, is heading for $6∙9.0 = ",null,null,null,null,{"$f":"6*(1/0.1-1)","v":54}]
[null,42,0,1,0,null,"(since, if he plans a large number of releases, he'll just about definitely end his"]
[null,43,0,1,0,null,"act with a failure)."]
[null,44,0,1,0]
[null,45,0,1,0]
[null,46,0,1,0]
[null,47,0,1,0]
[null,48,0,1,0]
[null,49,0,1,0]
[null,50,0,1,0]