{"text_format":"1","workbook":"xlsx","n_sheets":15,"sheets":["Introduction","Random numbers","Rolling dice","A tool","Test","Margin of error","The 5-dice problem","Cubs","Tossing coins","Coin-toss setup","Summary","Combinations","Conditional probability","Fords","Adverse Selection"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":3,"defined_names":25,"comments":1,"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":"Introduction","index":0,"n_rows":31,"n_cols":11,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"An Introduction to Simulation"]
[]
[null,"Modern spreadsheet programs give us the ability to keep uncertainty \"alive\" in our"]
[null,"calculations. This, in turn, allows us to deal with problems which are analytically"]
[null,"intractable (as well as problems where we can't personally discover an analytical"]
[null,"approach)."]
[]
[null,"For example, imagine that you're playing a dice game, where you roll five dice. You will"]
[null,"win the game if, on your next roll, the highest number showing on any of the dice is a 5."]
[null,"What are your chances of winning?"]
[]
[null,"Solving this problem directly takes some careful thought: There are many different cases"]
[null,"to consider. You might roll precisely one 5, and four lower numbers. Or two 5s, or three,"]
[null,"or four, or five. If you roll precisely three 5s, they might appear on the first three dice, or"]
[null,"the first, second, and fourth, or some other three of the five. And so on."]
[]
[null,"This problem does yield to a clever analytical approach: The cases in which you will win",null,null,null,null,null,null,null,null,"There are two useful ways to recast a"]
[null,"are precisely those for which your highest die shows a 5 or less, if you subtract off all",null,null,null,null,null,null,null,null,"problem which focuses on a specific"]
[null,"cases in which the highest die shows a 4 or less. Hence, the probability that you'll win",null,null,null,null,null,null,null,null,"quantity (in this case, the highest number"]
[null,"can be computed:",null,null,null,null,null,null,null,null,"showing across five dice), and seeks the"]
[null,null,null,null,null,null,null,null,null,null,"likelihood that the quantity takes a specific"]
[null,{"$f":"5^5","v":3125},"cases where all five dice show 5 or less",null,null,null,null,{"$f":"showformula(B22)","v":"=5^5"},null,null,"value (in this case, 5):"]
[null,{"$f":"4^5","v":1024},"cases where all five dice show 4 or less",null,null,null,null,{"$f":"showformula(B23)","v":"=4^5"}]
[null,{"$f":"B22-B23","v":2101},"cases where you win (the highest die shows a 5)",null,null,null,null,"=B22-B23",null,null,"Pr(value is 5)"]
[null,null,null,null,null,null,null,null,null,null,"= Pr(value is ≤ 5) - Pr(value is ≤ 4)"]
[null,{"$f":"6^5","v":7776},"all possible rolls",null,null,null,null,"=6^5",null,null,"= Pr(value is ≥ 5) - Pr(value is ≥ 6)"]
[]
[null,{"$f":"B24/B26","v":0.27019032921810698},"Pr(you win)",null,null,null,null,"=B24/B26",null,null,"In this problem, it is the first recasting"]
[null,null,null,null,null,null,null,null,null,null,"which helps us."]
[null,"But how might you have solved the problem, had this approach not popped into your"]
[null,"mind?"]

{"sheet":"Random numbers","index":1,"n_rows":21,"n_cols":3,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Generating Random Numbers"]
[]
[null,"Excel has two built-in random-number-generating functions: =RAND() and"]
[null,"=RANDBETWEEN(j,k)."]
[]
[null,"=RAND() takes a random value, equally likely to be anywhere between 0 and 1. Each"]
[null,"time the spreadsheet is recalculated, a new random value is drawn."]
[]
[null,"Pressing the [F9] key in Excel forces a recalculation of your spreadsheet."]
[]
[null,{"$f":"RAND()","v":0.89380138366101158},"=RAND().  Press [F9] a few times, and watch this change."]
[]
[null,"=RANDBETWEEN(j,k) takes a random value, equally likely to be any whole number"]
[null,"between j and k."]
[]
[null,1,"j"]
[null,6,"k"]
[null,{"$f":"RANDBETWEEN(B16,B17)","v":6},"=RANDBETWEEN(j,k)"]
[]
[null,"Press [F9] a few more times to convince yourself that the cell above is perfectly"]
[null,"simulating the roll of a single six-sided die!"]

{"sheet":"Rolling dice","index":2,"n_rows":27,"n_cols":4,"rows_kept":27,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Rolling Five Dice"]
[]
[null,"The values of =RAND() and =RANDBETWEEN(j,k) vary independently from one"]
[null,"recalculation to the next, and also across cells. Therefore, five uses of the"]
[null,"=RANDBETWEEN(1,6) function let us perfectly simulate the roll of five dice:"]
[]
[null,{"$f":"RANDBETWEEN(1,6)","v":2},"die 1",{"$f":"showformula(B7)","v":"=RANDBETWEEN(1,6)"}]
[null,{"$f":"RANDBETWEEN(1,6)","v":2},"die 2",{"$f":"showformula(B8)","v":"=RANDBETWEEN(1,6)"}]
[null,{"$f":"RANDBETWEEN(1,6)","v":5},"die 3",{"$f":"showformula(B9)","v":"=RANDBETWEEN(1,6)"}]
[null,{"$f":"RANDBETWEEN(1,6)","v":6},"die 4",{"$f":"showformula(B10)","v":"=RANDBETWEEN(1,6)"}]
[null,{"$f":"RANDBETWEEN(1,6)","v":3},"die 5",{"$f":"showformula(B11)","v":"=RANDBETWEEN(1,6)"}]
[]
[null,"The =MAX(cell-range) function will show us the highest number appearing on the five"]
[null,"faces:"]
[]
[null,{"$f":"MAX(B7:B11)","v":6},{"$f":"showformula(B16)","v":"=MAX(B7:B11)"}]
[]
[null,"We win in the event that the number above is a 5. The occurrence of an event can be"]
[null,"\"encoded\" numerically, by using the value 0 to represent \"No, it didn't occur,\" and"]
[null,"using the value 1 to represent \"Yes, it did occur.\" The Excel \"=IF\" function can be"]
[null,"used to do the encoding:"]
[]
[null,{"$f":"IF(B16=5,1,0)","v":0},{"$f":"showformula(B23)","v":"=IF(B16=5,1,0)"}]
[]
[null,"In order to estimate the probability that the highest die shows a 5, we could now just hit"]
[null,"the [F9] key many times, while watching the cell above, and keep track of the fraction of"]
[null,"the time a 1 (\"Yes!\") shows up. This, of course, would be very tedious."]

{"sheet":"A tool","index":3,"n_rows":15,"n_cols":2,"rows_kept":15,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"A Simulation Tool"]
[]
[null,"Rather than hitting [F9] repeatedly and monitoring the results, we can let Excel do the"]
[null,"\"hitting [F9] and monitoring\" for us. There are commercially-available Excel add-ins that"]
[null,"will do this - and eventually, any decent spreadsheet program will incorporate this"]
[null,"capability as a basic feature. To bridge the gap, I've developed a simple"]
[null,"simulation-tracking tool that you can use for free."]
[]
[null,"File / Open \"KSim.xlsm\". (If you get a \"warning,\" please tell Excel that it's okay to let"]
[null,"the sheet load and run.) It will open in another Excel window. Use the Window"]
[null,"command at the top of the Excel screen to come back here."]
[]
[null,"If all went well, the only difference you should now see is a new command at the top"]
[null,"of the screen: Simulation. (If all didn't go well, check your Tools / Macro / Security"]
[null,"setting: It must be set to either \"Medium\" or \"Low.\")"]

{"sheet":"Test","index":4,"n_rows":28,"n_cols":8,"rows_kept":28,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Running a Simulation"]
[]
[null,"Let's test the simulation tool by running a very simple simulation: We'll roll a single die"]
[null,"repeateadly, and keep track of the average value showing on the top face."]
[]
[null,{"$f":"RANDBETWEEN(1,6)","v":5},"This is the top face from the current roll."]
[]
[null,"Select Simulation / Run. You'll see a dialog box:"]
[]
[null,"Enter \"10000\" for the \"number of runs,\" select cell"]
[null,"B6 as the \"cell to tabulate,\" and cell B14 as the "]
[null,"\"output range.\" Then hit \"Run.\""]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Your results should look something like the",null,null,null,null,"$B$6","monitored cell"]
[null,"results displayed to the right:",null,null,null,null,3.5082,"mean"]
[null,null,null,null,null,null,1.7194849295188033,"sample standard deviation"]
[null,"The \"mean\" indicates the average of the values",null,null,null,null,1,"minimum"]
[null,"that appeared in cell B6 across 10,000",null,null,null,null,6,"maximum"]
[null,"recalculations of this spreadsheet. It should",null,null,null,null,10000,"number of simulation runs"]
[null,"be close to 3.5, since the value of B6 varies"]
[null,"between 1 and 6 with roughly-equal frequencies."]

{"sheet":"Margin of error","index":5,"n_rows":27,"n_cols":8,"rows_kept":27,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"The \"Margin of Error\" in an Estimate"]
[]
[null,"The simulated mean will vary from one",null,null,null,null,"$B$6","monitored cell"]
[null,"simulation run to the next, depending on",null,null,null,null,3.5082,"mean"]
[null,"how the particular 10,000 rolls of the die",null,null,null,null,1.7194849295188033,"sample standard deviation"]
[null,"turn out. How much trust can we put in this",null,null,null,null,1,"minimum"]
[null,"number as an estimate of the \"true\" mean?",null,null,null,null,6,"maximum"]
[null,null,null,null,null,null,10000,"number of simulation runs"]
[null,"We'll discuss this question in detail during"]
[null,"the \"statistics\" portion of this course. For now, however, we'll borrow an answer at an"]
[null,"intuitive level: I'd be willing, purely on the basis of this simulation, to state with 95%"]
[null,"\"confidence\" that the estimate from the simulation is within"]
[null,{"$f":"1.96*G5/SQRT(G8)","v":3.3701904618568548E-2},"of the true average.",null,null,null,null,{"$f":"showformula(B13)","v":"=1.96*G5/SQRT(G8)"}]
[]
[null,"We'll derive this formula for the \"margin of error\" in an estimate later in our course:"]
[null,"1.96*s/sqrt(n), where s is the \"sample standard deviation,\" and n is the \"sample size\""]
[null,"(the number of simulation runs)."]
[]
[null,"For now, just interpret it in this way: Purely on the basis of the simulation just"]
[null,"conducted, I believe there's a 95% chance that the true average is somewhere"]
[null,"between 3.5082 - 0.0337 = 3.4745 and 3.5082 + 0.0337 = 3.5419."]
[]
[null,"I might have been unlucky, and - just by chance - have gotten a series of simulated rolls"]
[null,"that makes my estimate wrong by more than this margin of error. However, not only is"]
[null,"that unlikely (only 1 chance in 20), but - even when I'm unlucky - I'm typically not off by"]
[null,"much more than this margin of error. (Again, we'll see why this is so later in this"]
[null,"course.)"]

{"sheet":"The 5-dice problem","index":6,"n_rows":27,"n_cols":10,"rows_kept":27,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Solving the Original Problem (through Simulation)"]
[]
[null,"Here's the setup again:",null,null,null,null,null,"five dice are rolled"]
[null,null,null,null,null,null,null,{"$f":"RANDBETWEEN(1,6)","v":6},null,{"$f":"showformula(H4)","v":"=RANDBETWEEN(1,6)"}]
[null,"We'll now simulate 100,000 rolls of the five dice, and",null,null,null,null,null,{"$f":"RANDBETWEEN(1,6)","v":3},null,{"$f":"showformula(H5)","v":"=RANDBETWEEN(1,6)"}]
[null,"track the average of the 0s and 1s (corresponding to",null,null,null,null,null,{"$f":"RANDBETWEEN(1,6)","v":1},null,{"$f":"showformula(H6)","v":"=RANDBETWEEN(1,6)"}]
[null,"whether we lose or win).",null,null,null,null,null,{"$f":"RANDBETWEEN(1,6)","v":4},null,{"$f":"showformula(H7)","v":"=RANDBETWEEN(1,6)"}]
[null,null,null,null,null,null,null,{"$f":"RANDBETWEEN(1,6)","v":3},null,{"$f":"showformula(H8)","v":"=RANDBETWEEN(1,6)"}]
[null,"The average of the 0s and 1s, conveniently, will be the"]
[null,"fraction of the time that we win!",null,null,null,null,null,"the largest of the five rolls"]
[null,null,null,null,null,null,null,{"$f":"MAX(H4:H8)","v":6},null,{"$f":"showformula(H11)","v":"=MAX(H4:H8)"}]
[null,"And here are the results:"]
[null,null,null,null,null,null,null,"is the largest roll a 5?"]
[null,{"$f":"ADDRESS(ROW($H$14),COLUMN($H$14))","v":"$H$14"},"monitored cell",null,null,null,null,{"$f":"IF(H11=5,1,0)","v":0},null,{"$f":"showformula(H14)","v":"=IF(H11=5,1,0)"}]
[null,0.26962999999999998,"mean"]
[null,0.44376979664723032,"sample standard deviation"]
[null,0,"minimum"]
[null,1,"maximum"]
[null,100000,"number of simulation runs"]
[]
[null,"From our simulation, we estimate that the frequency - the PROBABILITY -"]
[null,"of the event that we win is",null,null,{"$f":"B15","v":0.26962999999999998}," And we can be 95%-confident that"]
[null,"our estimate is off by at most",null,null,{"$f":"1.96*B16/SQRT(B19)","v":2.7505136958222015E-3},null,{"$f":"showformula(E23)","v":"=1.96*B16/SQRT(B19)"}]
[]
[null,"Recalling that the actual (computed analytically) probability is",null,null,null,null,null,0.27019032921810698,", we see"]
[null,"that, in fact, we're off by only ",null,null,{"$f":"H25-E22","v":5.6032921810700298E-4},". And, of course, if we needed more"]
[null,"precision, we could simply increase our sample size (i.e., the number of simulation runs)."]

{"sheet":"Cubs","index":7,"n_rows":37,"n_cols":11,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,1,0,8],[12,1,12,8],[22,5,23,7]]}
[null,"Rooting for the Cubs"]
[null,null,null,null,null,null,null,null,null,null,"From a Google search:"]
[null,"The Chicago Cubs have three games left against the Houston Astros, and two against"]
[null,"the St. Louis Cardinals. They need to win at least four of these games to clinch a playoff",null,null,null,null,null,null,null,null,"Can anyone tell me how this term came about? As far as I'm aware it's an American"]
[null,"berth. Given how they've fared against these opponents earlier in the season, you feel",null,null,null,null,null,null,null,null,"term, and we would definitely not say this in Australia, as it would imply that you're"]
[null,"there's a 50% chance of them winning any given game against the Astros, and a 55%",null,null,null,null,null,null,null,null,"having sex with someone for your team. So why the term 'root', when barracking for a"]
[null,"chance of them winning any given game against the Cardinals. How likely is it that the",null,null,null,null,null,null,null,null,"team?"]
[null,"Cubs will make the playoffs?"]
[null,null,null,null,null,null,null,null,null,null,"The American Heritage Dictionary says \"Perhaps from 'root,' to dig with the snout.\""]
[null,"Answer: Again, we could work this problem analytically. From the class notes on"]
[null,"\"Computing Probabilities,\" we find the answer:",null,null,null,null,null,null,null,null,"Merriam Webster says it may come from \"rout.\""]
[null,null,null,null,null,null,null,null,null,null,"Main Entry: root (4)"]
[null,"[3 ·(0.5)2 ·(0.5)] ·(0.55)2 + (0.5)3 ·[2 ·(0.55) ·(0.45)] + (0.5)3 ·(0.55)2 = 21.3125% ",null,null,null,null,null,null,null,null,"Pronunciation: 'rüt also 'rut"]
[null,null,null,null,null,null,null,null,null,null,"Function: intransitive verb"]
[null,"But what if we failed to discover this formula? Simulation comes to our rescue! ",null,null,null,null,null,null,null,null,"Etymology: perhaps alteration of rout (2)"]
[null,"We can combine the =RAND() function with the =IF function to simulate the result of a",null,null,null,null,null,null,null,null,"Date: 1889"]
[null,"game, using the encoding 1 = \"Cubs win\" and 0 = \"Cubs lose.\"",null,null,null,null,null,null,null,null,"1 : to noisily applaud or encourage a contestant or team : CHEER"]
[null,null,null,null,null,null,null,null,null,null,"2 : to wish the success of or lend support to someone or something"]
[null,{"$f":"IF(RAND()<0.5,1,0)","v":0},"=IF(RAND()<0.5,1,0)"]
[null,{"$f":"IF(RAND()<0.5,1,0)","v":1},"=IF(RAND()<0.5,1,0)",null,null,"games against the Astros"]
[null,{"$f":"IF(RAND()<0.5,1,0)","v":0},"=IF(RAND()<0.5,1,0)"]
[]
[null,{"$f":"IF(RAND()<0.55,1,0)","v":1},"=IF(RAND()<0.55,1,0)",null,null,"games against the Cardinals"]
[null,{"$f":"IF(RAND()<0.55,1,0)","v":1},"=IF(RAND()<0.55,1,0)"]
[]
[null,{"$f":"SUM(B19:B24)","v":3},"total number of Cubs victories",null,null,{"$f":"showformula(B26)","v":"=SUM(B19:B24)"}]
[]
[null,{"$f":"IF(B26>=4,1,0)","v":0},"1 (\"Yes\") if the Cubs win at least 4 games, 0 (\"No\") otherise",null,null,null,null,null,{"$f":"showformula(B28)","v":"=IF(B26>=4,1,0)"}]
[]
[null,"And now we use our simulation tool:",null,null,null,{"$f":"ADDRESS(ROW($B$28),COLUMN($B$28))","v":"$B$28"},"monitored cell"]
[null,null,null,null,null,0.21248,"mean"]
[null,"We estimate the probability that",null,null,null,0.4090646928533791,"sample standard deviation"]
[null,"the Cubs reach the playoffs to be",null,null,null,0,"minimum"]
[null,{"$f":"F31","v":0.21248},", with a margin of error of",null,null,1,"maximum"]
[null,{"$f":"1.96*F32/SQRT(F35)","v":2.5354092339568055E-3},"(at the 95%-confidence level).",null,null,100000,"number of simulation runs"]
[]
[null,{"$f":"showformula(B35)","v":"=1.96*F32/SQRT(F35)"}]

{"sheet":"Tossing coins","index":8,"n_rows":20,"n_cols":4,"rows_kept":20,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Coin Tossing"]
[]
[null,"What's the probability of getting at least one head in the course of 5 coin tosses?"]
[null,"What's the probability of getting precisely three heads?"]
[]
[null,"Try this yourself. Use five cells to represent the tosses of the coin - Set up the cells so"]
[null,"that each is 1 if the coin comes up \"heads,\" and 0 if it comes up \"tails.\""]
[]
[null,"Then, use the =SUM formula to count the \"heads,\" and an =IF formula to check whether"]
[null,"the number of heads is at least 1, and another =IF formula to see if the number is"]
[null,"precisely 3."]
[]
[null,"Finally, run the simulation tool, tracking the average values appearing in your two =IF"]
[null,"cells. Pick whatever sample size (number of runs) you like but, when finished, do"]
[null,"compute the margins of error (at the 95%-confidence level) for your two estimates."]
[]
[null,"Hint: The actual probabilities, determined analytically, are"]
[null,{"$f":"1-(0.5)^5","v":0.96875},"and",{"$f":"COMBIN(5,3)*0.5^3*(1-0.5)^2","v":0.3125}]
[]
[null,"One way to set up the simulation appears on the next tab."]

{"sheet":"Coin-toss setup","index":9,"n_rows":13,"n_cols":9,"rows_kept":13,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Coin-Toss Setup"]
[]
[]
[null,null,null,{"$f":"IF(RAND()<0.5,1,0)","v":1},"Toss #1 (0 = \"Tails,\" 1 = \"Heads\")",null,null,null,{"$f":"showformula(D4)","v":"=IF(RAND()<0.5,1,0)"}]
[null,null,null,{"$f":"IF(RAND()<0.5,1,0)","v":1},"Toss #2",null,null,null,{"$f":"showformula(D5)","v":"=IF(RAND()<0.5,1,0)"}]
[null,null,null,{"$f":"IF(RAND()<0.5,1,0)","v":0},"Toss #3",null,null,null,"=IF(RAND()<0.5,1,0)"]
[null,null,null,{"$f":"IF(RAND()<0.5,1,0)","v":1},"Toss #4",null,null,null,"=IF(RAND()<0.5,1,0)"]
[null,null,null,{"$f":"IF(RAND()<0.5,1,0)","v":1},"Toss #5",null,null,null,"=IF(RAND()<0.5,1,0)"]
[]
[null,null,null,{"$f":"SUM(D4:D8)","v":4},"number of \"Heads\"",null,null,null,"=SUM(D4:D8)"]
[]
[null,null,null,{"$f":"IF(D10>=1,1,0)","v":1},"Is the number of \"Heads\" at least 1?",null,null,null,"=IF(D10>=1,1,0)"]
[null,null,null,{"$f":"IF(D10=3,1,0)","v":0},"Is the number of \"Heads\" 3?",null,null,null,"=IF(D10=3,1,0)"]

{"sheet":"Summary","index":10,"n_rows":16,"n_cols":4,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Summary"]
[]
[null,"=IF(RAND()<p,1,0)",null,"simulates the occurrence of an event with probability p."]
[]
[null,"=IF(RAND()<=p,1,0)",null,"does the same, since the probability that RAND() takes"]
[null,null,null,"precisely the value p is 0."]
[]
[null,"If a cell is 1 whenever an event A occurs, and 0 whenever A doesn't occur,"]
[null,"then in the long run, the average of the values appearing in that cell will be"]
[null,"an estimate of Pr(A)."]
[]
[null,"The precision of the estimate is indicated by the margin of error at the"]
[null,"95%-confidence level, 1.96*(sample standard deviation)/SQRT(n)."]
[]
[null,"This precision, when the original cell can only be 0 and 1, is"]
[null,"1.96*SQRT(estimate*(1-estimate))/SQRT(number of observations)."]

{"sheet":"Combinations","index":11,"n_rows":50,"n_cols":18,"rows_kept":50,"cut":false,"hidden":false,"merged":[[0,1,0,8],[8,11,8,13],[8,15,8,17],[0,10,0,17]]}
[null,"Counting Combinations",null,null,null,null,null,null,null,null,"Simulating an Evolving System"]
[]
[null,"Things tend to be counted in two ways:  As sequences (in which order is important),",null,null,null,null,null,null,null,null,"When the underlying \"state\" of a random process changes over time, the performance of"]
[null,"and as sets. Counting sequences is simple.  If there are  m  ways to do one thing,",null,null,null,null,null,null,null,null,"the process over time can be simulated by keeping track of the actual state from one"]
[null,"followed by  n  ways to do another, then there are  mn  ways to carry out the two tasks",null,null,null,null,null,null,null,null,"stage to the next."]
[null,"in sequence.  In how many ways can a 5-card hand be dealt out (as in stud poker,"]
[null,"where the order in which the cards are dealt is important)?  52∙51∙50∙49∙48 =",null,null,null,null,null,null,null,null,"Here, we'll track the number of socks remaining after each selection from the drawer."]
[null,"311,875,200.  (For every one of the 52 ways to start with card 1, there are 51 ways to"]
[null,"continue with card 2, ...)",null,null,null,null,null,null,null,null,null,"contents of drawer",null,null,null,"selection"]
[null,null,null,null,null,null,null,null,null,null,null,"blue","brown","black","choice","blue","brown","black"]
[null,"But how many 5-card hands are there?  Obviously, every hand could be dealt out in",null,null,null,null,null,null,null,null,"stage 1",10,6,8,{"$f":"RANDBETWEEN(1,SUM(L11:N11))","v":5},{"$f":"IF(O11<=L11,1,0)","v":1},{"$f":"IF(AND(O11>L11,O11<=L11+M11),1,0)","v":0},{"$f":"IF(O11>L11+M11,1,0)","v":0}]
[null,"5∙4∙3∙2∙1 = 120 different card-orders.  So there are only 311,875,200 / 120 = 2,598,960",null,null,null,null,null,null,null,null,"stage 2",{"$f":"L11-P11","v":9},{"$f":"M11-Q11","v":6},{"$f":"N11-R11","v":8},{"$f":"RANDBETWEEN(1,SUM(L12:N12))","v":7},{"$f":"IF(O12<=L12,1,0)","v":1},{"$f":"IF(AND(O12>L12,O12<=L12+M12),1,0)","v":0},{"$f":"IF(O12>L12+M12,1,0)","v":0}]
[null,"different hands.",null,null,null,null,null,null,null,null,"total",null,null,null,null,{"$f":"P11+P12","v":2},{"$f":"Q11+Q12","v":0},{"$f":"R11+R12","v":0}]
[]
[null,"Standard notation:  n! = 1∙2∙3∙...∙n ; the symbol \"n!\" is read as \"n factorial\".  This gives",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(MAX(P13:R13)=2,1,0)","v":1},"is it a matching pair?"]
[null,"the total number of ways of arranging  n  distinct items.  The number of ways to select,",null,null,null,null,null,null,null,null,null,{"$f":"ADDRESS(ROW($O$15),COLUMN($O$15))","v":"$O$15"},"monitored cell"]
[null,"in order, k  items from  n, is  n∙(n-1)∙...∙(n-k+1) = n!/(n-k)! .  In our card example, the",null,null,null,null,null,null,null,null,null,0.31768999999999997,"mean"]
[null,"number of ways to deal out a 5-card hand was simply  52! / 47! .  But if we only care",null,null,null,null,null,null,null,null,null,0.46558053175827224,"sample standard deviation"]
[null,"about different hands, and not the order in which the cards arrive, then we must take into",null,null,null,null,null,null,null,null,null,0,"minimum"]
[null,"account that each hand can be dealt out in 5! different ways, so there are really only ",null,null,null,null,null,null,null,null,null,1,"maximum"]
[null,"52! / (47!∙5!)  different hands.",null,null,null,null,null,null,null,null,null,100000,"number of simulation runs"]
[]
[null,"More standard notation:",null,null,null,null,null,null,null,null,null,{"$f":"1.96*L18/SQRT(L21)","v":2.8856980325934578E-3},"margin of error (at 95%-confidence)"]
[]
[]
[null,"is the number of ways of choosing  k  distinct items from a set of  n ; the symbol is read"]
[null,"as \"n choose k\"."]
[]
[null,"Example:  What is the probability of being dealt a \"full house\" (two cards of one rank,"]
[null,"and three of another)? There are "]
[]
[]
[]
[null,"different full houses (13 ways to choose the pair-rank, and 6 ways to choose two cards,"]
[null,"then 12 ways left to choose the triple-rank, and 4 ways to choose three cards).  So the"]
[null,"probability is a bit more than one chance in a thousand, i.e., 3,744 / 2,598,960 ="]
[null,{"$f":"3744/2598960","v":1.4405762304921968E-3},"."]
[]
[null,"Example:  I have 5 pairs of blue socks, 3 pairs of brown socks, and 4 pairs of black"]
[null,"socks lying loose in my drawer.  What's the chance that two randomly-selected socks"]
[null,"(pulled out early in the morning, before my eyes are really open) will match?"]
[]
[null,"There are"]
[]
[]
[null,"ways to pick a matching pair, and",null,null,null,null,null,null,null,{"$f":"88/276","v":0.3188405797101449}]
[]
[]
[null,"ways to pick some pair. Therefore, the probability of a matching pair"]
[null,"is  88 / 276 = 0.3188 = 31.88% .  (I'll match roughly every third day.)"]

{"sheet":"Conditional probability","index":12,"n_rows":34,"n_cols":14,"rows_kept":34,"cut":false,"hidden":false,"merged":[[0,1,0,8],[4,1,4,8]]}
[null,"Identifying the Culprit"]
[]
[null,"Recall the definition: Pr( B | A ) = Pr( A and B ) / Pr( A )."]
[]
[null,"Estimating Conditional Probabilities via Simulation",null,null,null,null,null,null,null,null,"This goes beyond anything required in this class. I just wanted to "]
[null,null,null,null,null,null,null,null,null,null,"illustrate that it can be done."]
[null,"A witness to a hit-and-run accident identifies the fleeing vehicle as a Ford."]
[null,"20% of the vehicles in town are Fords. However, when shown a sample of"]
[null,"photographs, the witness correctly identifies Fords only 90% of the time, and"]
[null,"calls non-Fords Fords 30% of the time. How likely is it that the fleeing vehicle"]
[null,"was actually a Ford?"]
[]
[null,0.2,"cars which are Fords"]
[null,{"$f":"1-B13","v":0.8},"car which aren't Fords"]
[]
[null,0.9,"Pr( calls car a Ford | car is Ford )"]
[null,0.3,"Pr( calls car a Ford | car isn't Ford )"]
[]
[]
[null,{"$f":"IF(RAND()<B13,\"Ford\",\"other\")","v":"Ford"},{"$f":"showformula(B20)","v":"=IF(RAND()<B13,\"Ford\",\"other\")"},null,null,null,null,null,null,null,"make of car seen fleeing",null,null,"(crucial assumption: car in hit-and-run is random car from  town)"]
[null,{"$f":"IF(B20=\"Ford\",IF(RAND()<B16,\"Ford\",\"Other\"),IF(RAND()<B17,\"Ford\",\"other\"))","v":"Ford"},{"$f":"showformula(B21)","v":"=IF(B20=\"Ford\",IF(RAND()<B16,\"Ford\",\"Other\"),IF(RAND()<B17,\"Ford\",\"other\"))"},null,null,null,null,null,null,null,"witness says"]
[]
[null,{"$f":"IF(AND(B20=\"Ford\",B21=\"Ford\"),1,0)","v":1},{"$f":"showformula(B23)","v":"=IF(AND(B20=\"Ford\",B21=\"Ford\"),1,0)"},null,null,null,"A and B",null,null,null,"make seen fleeing is Ford and witness says \"Ford\""]
[null,{"$f":"IF(B21=\"Ford\",1,0)","v":1},{"$f":"showformula(B24)","v":"=IF(B21=\"Ford\",1,0)"},null,null,null,"A",null,null,null,"witness says \"Ford\""]
[]
[null,{"$f":"ADDRESS(ROW($B$23),COLUMN($B$23))","v":"$B$23"},{"$f":"ADDRESS(ROW($B$24),COLUMN($B$24))","v":"$B$24"},"monitored cell"]
[null,0.18081,0.42193999999999998,"mean",null,null,null,{"$f":"B27/C27","v":0.42852064274541407},{"$f":"showformula(H27)","v":"=B27/C27"},null,"estimated conditional probability",null,null,"clearly"]
[null,0.38486260547401968,0.49387151718919253,"sample standard deviation",null,null,null,{"$f":"SQRT(H27*(1-H27))","v":0.49486432634255551},{"$f":"showformula(H28)","v":"=SQRT(H27*(1-H27))"},null,"sample standard deviation",null,null,"trust me for now"]
[null,0,0,"minimum",null,null,null,0,{"$f":"showformula(H29)","v":"0"},null,"minimum"]
[null,1,1,"maximum",null,null,null,1,{"$f":"showformula(H30)","v":"1"},null,"maximum"]
[null,100000,100000,"number of simulation runs",null,null,null,{"$f":"C27*C31","v":42194},{"$f":"showformula(H31)","v":"=C27*C31"},null,"number of simulation runs",null,null,"obvious"]
[]
[null,null,null,null,null,null,null,{"$f":"H27","v":0.42852064274541407},"estimated probability (was Ford | said \"Ford\")"]
[null,null,null,null,null,null,null,{"$f":"1.96*H28/SQRT(H31)","v":4.7219009168180705E-3},"margin of error (at 95% confidence)"]

{"sheet":"Fords","index":13,"n_rows":26,"n_cols":7,"rows_kept":26,"cut":false,"hidden":false,"merged":[[0,1,0,8],[21,6,22,11],[25,6,26,11]]}
[null,"Identifying the Culprit"]
[]
[null,"A witness to a hit-and-run accident identifies the fleeing vehicle as a Ford."]
[null,"20% of the vehicles in town are Fords. However, when shown a sample of"]
[null,"photographs, the witness correctly identifies Fords only 90% of the time, and"]
[null,"calls non-Fords Fords 30% of the time. How likely is it that the fleeing vehicle"]
[null,"was actually a Ford?"]
[]
[null,0.2,"cars which are Fords"]
[null,{"$f":"1-B9","v":0.8},"car which aren't Fords"]
[]
[null,0.9,"Pr( calls car a Ford | car is Ford )"]
[null,0.3,"Pr( calls car a Ford | car isn't Ford )"]
[]
[null,{"$f":"+B9*B12","v":0.18000000000000002},"Pr( car is a Ford and is called a Ford )"]
[null,{"$f":"(1-B9)*B13","v":0.24},"Pr( car isn't a Ford and is called a Ford )"]
[]
[null,{"$f":"B15+B16","v":0.42000000000000004},"Pr(car is called a Ford)"]
[]
[null,{"$f":"B15/B18","v":0.4285714285714286},"Pr( car is a Ford | it's called a Ford )"]
[]
[null,null,null,null,null,null,"is a Ford and is called a Ford"]
[]
[]
[]
[null,null,null,null,null,null,"is called a Ford"]

{"sheet":"Adverse Selection","index":14,"n_rows":29,"n_cols":8,"rows_kept":29,"cut":false,"hidden":true,"merged":[[0,1,0,8]]}
[null,"Adverse Selection"]
[]
[null,"Recall that my car was worth some amount, equally likely to be anywhere between $0"]
[null,"and $500, to me, and was worth 50% more to you. The reality you were facing looked"]
[null,"like this:"]
[]
[null,{"$f":"500*RAND()","v":124.30056611281381},{"$f":"showformula(B7)","v":"=500*RAND()"},null,"the (random) value of the car to me"]
[null,{"$f":"1.5*B7","v":186.4508491692207},{"$f":"showformula(B8)","v":"=1.5*B7"},null,"the value of the car to you"]
[]
[null,"Let's say that you chose to offer me $200 for the car. Then your realized profit from the"]
[null,"offer would have been"]
[]
[null,200,"offer"]
[null,{"$f":"IF(B13>=B7,1,0)","v":1},"acceptance of offer by me (1 = \"Yes,\" 0 = \"No\")",null,null,null,null,{"$f":"showformula(B14)","v":"=IF(B13>=B7,1,0)"}]
[null,{"$f":"B14*(B8-B13)","v":-13.549150830779297},"your profit",null,null,null,null,{"$f":"showformula(B15)","v":"=B14*(B8-B13)"}]
[null,{"$f":"IF(B14=1,B8-B13,0)","v":-13.549150830779297},"I could have also used this formula",null,null,null,null,{"$f":"showformula(B16)","v":"=IF(B14=1,B8-B13,0)"}]
[]
[null,"What, on average, would you expect to profit from your offer? Let's simulate:"]
[]
[null,"Here, I've selected two cells to monitor.",null,null,null,{"$f":"ADDRESS(ROW($B$14),COLUMN($B$14))","v":"$B$14"},{"$f":"ADDRESS(ROW($B$15),COLUMN($B$15))","v":"$B$15"},"monitored cell"]
[null,"This can be done by [Ctrl]-clicking first",null,null,null,0.39976,-19.832459788999959,"mean"]
[null,"one, and then the other.",null,null,null,0.4898513467812653,60.011385906154601,"sample standard deviation"]
[null,null,null,null,null,0,-199.98490000000001,"minimum"]
[null,"We estimate your chance of winning to be",null,null,null,1,99.9983,"maximum"]
[null,{"$f":"F21","v":0.39976},", with a margin of error of",null,null,100000,100000,"number of simulation runs"]
[null,{"$f":"1.96*F22/SQRT(F25)","v":3.0361301026303861E-3},". And your profit, on average, is"]
[null,{"$f":"G21","v":-19.832459788999959},", with a margin of error of"]
[null,{"$f":"1.96*G22/SQRT(G25)","v":0.37195442341328133},". The precise answers, derived"]
[null,"analytically, are 40% and ($20)."]