{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["ATM","Newsstand sales","Mufflers","Continuity correction","Overtime","Normal miscellany"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":32,"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":"ATM","index":0,"n_rows":46,"n_cols":6,"rows_kept":46,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Supermarket ATM"]
[]
[null,"A chain of supermarkets has learned (from the collection of data over several"]
[null,"months) that the daily weekday draw from the cash machine at its Tel Aviv store"]
[null,"is normally distributed (and varies independently from day to day), with a mean"]
[null,"of $15,000 and a standard deviation of $6,000."]
[]
[null,"What is the probability that the Monday draw exceeds $18,000?"]
[]
[null,"They stock the cash machine on Mondays and Fridays (four days apart). On"]
[null,"Monday, they put $75,000 in the machine. How likely is it that the machine will"]
[null,"run dry before the Friday refill?"]
[]
[]
[null,15000,"mean daily draw"]
[null,6000,"std dev of daily draw"]
[]
[null,18000,"Monday limit"]
[null,{"$f":"1-NORMDIST(B18,B15,B16,TRUE)","v":0.30853753872598688},"Pr( Monday draw over limit )",null,null,{"$f":"showformula(B19)","v":"=1-NORMDIST(B18,B15,B16,TRUE)"}]
[]
[null,4,"k (days before restocking)"]
[null,{"$f":"B21*B15","v":60000},"mean over k days",null,null,{"$f":"showformula(B22)","v":"=B21*B15"}]
[null,{"$f":"SQRT(B21)*B16","v":12000},"standard deviation over k days",null,null,{"$f":"showformula(B23)","v":"=SQRT(B21)*B16"}]
[null,75000,"limit"]
[null,{"$f":"1-NORMDIST(B24,B22,B23,TRUE)","v":0.10564977366685524},"Pr( k-day draw is over limit )",null,null,{"$f":"showformula(B25)","v":"=1-NORMDIST(B24,B22,B23,TRUE)"}]
[]
[]
[null,"Via simulation:"]
[null,null,null,null,null,{"$f":"showformula(B29)","v":""}]
[null,{"$f":"NORMINV(RAND(),$B$15,$B$16)","v":15000.275031611158},"Monday draw",null,null,{"$f":"showformula(B30)","v":"=NORMINV(RAND(),$B$15,$B$16)"}]
[null,{"$f":"NORMINV(RAND(),$B$15,$B$16)","v":19094.77263198121},"Tuesday draw",null,null,{"$f":"showformula(B31)","v":"=NORMINV(RAND(),$B$15,$B$16)"}]
[null,{"$f":"NORMINV(RAND(),$B$15,$B$16)","v":12108.736005868235},"Wednesday draw",null,null,{"$f":"showformula(B32)","v":"=NORMINV(RAND(),$B$15,$B$16)"}]
[null,{"$f":"NORMINV(RAND(),$B$15,$B$16)","v":18814.632844524156},"Thursday draw",null,null,{"$f":"showformula(B33)","v":"=NORMINV(RAND(),$B$15,$B$16)"}]
[]
[null,{"$f":"SUM(B30:B33)","v":65018.416513984761},"four-day draw",null,null,{"$f":"showformula(B35)","v":"=SUM(B30:B33)"}]
[]
[null,{"$f":"IF(B35>B24,1,0)","v":0},"Is the four-day draw over $75,000?"]
[]
[null,{"$f":"ADDRESS(ROW($B$35),COLUMN($B$35))","v":"$B$35"},{"$f":"ADDRESS(ROW($B$37),COLUMN($B$37))","v":"$B$37"},"monitored cell"]
[null,59986.444139635343,0.10557,"mean"]
[null,11967.810754143895,0.30728800718412946,"sample standard deviation"]
[null,6857.9178079397752,0,"minimum"]
[null,112837.96619312468,1,"maximum"]
[null,100000,100000,"number of simulation runs"]
[]
[null,{"$f":"1.96*B41/SQRT(B44)","v":74.177259554346151},{"$f":"1.96*C41/SQRT(C44)","v":1.9045908006978245E-3},"margin of error (95% confidence)"]

{"sheet":"Newsstand sales","index":1,"n_rows":22,"n_cols":6,"rows_kept":22,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Weekly Newspaper Demand"]
[]
[null,"Weekly newsstand sales of a local (weekly, nonsubscription) newspaper are"]
[null,"normally distributed, with a mean of 6,000 and a standard deviation of 700."]
[]
[null,"How likely is it that a press run of 7,000 copies will sell out?"]
[]
[null,"What total monthly sales (4 issues) could the newspaper publisher guarantee to"]
[null,"a regular advertiser, while having a 90% chance of meeting the guarantee?"]
[]
[]
[null,6000,"mean weekly sales"]
[null,700,"standard deviation"]
[]
[null,7000,"press run"]
[null,{"$f":"1-NORMDIST(B15,B12,B13,TRUE)","v":7.6563725509834701E-2},"Pr( press run insufficient )",null,null,{"$f":"showformula(B16)","v":"=1-NORMDIST(B15,B12,B13,TRUE)"}]
[]
[null,4,"issues / month"]
[null,{"$f":"B18*B12","v":24000},"mean monthly sales",null,null,{"$f":"showformula(B19)","v":"=B18*B12"}]
[null,{"$f":"SQRT(B18)*B13","v":1400},"monthly standard deviation",null,null,{"$f":"showformula(B20)","v":"=SQRT(B18)*B13"}]
[null,0.9,"Pr( meet guarantee )"]
[null,{"$f":"NORMINV(1-B21,B19,B20)","v":22205.827808237558},"sales that can be guaranteed",null,null,{"$f":"showformula(B22)","v":"=NORMINV(1-B21,B19,B20)"}]

{"sheet":"Mufflers","index":2,"n_rows":32,"n_cols":10,"rows_kept":32,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Fred's Cut-Rate Muffler Shop"]
[]
[null,"Fred's muffler shop employess 9 mechanics for 8 hours per day. A typical"]
[null,"muffler replacement (this is all that the shop does) takes, on average, 1.25"]
[null,"hours, with one standard-deviation's-worth of uncertainty being 15 minutes."]
[null,"Today, 60 cars have been booked for servicing."]
[]
[null,"How likely is it that Fred will find himself paying some overtime today?"]
[]
[]
[null,"The sum of the 60 service times will be (by the Central Limit Theorem) roughly normally"]
[null,"distributed. Assuming (quite plausibly) that the service times vary independently, the"]
[null,"expected total service time for",null,null,60,"cars is",{"$f":"E13*1.25","v":75},"hours,",null,{"$f":"showformula(G13)","v":"=E13*1.25"}]
[null,"and the total service time has a standard deviation of",null,null,null,null,{"$f":"SQRT(E13)*0.25","v":1.9364916731037085},"hours.",null,{"$f":"showformula(G14)","v":"=SQRT(E13)*0.25"}]
[]
[null,"Consequently, the likelihood that the total time needed exceeds 72 hours is",null,null,null,null,null,null,{"$f":"1-NORMDIST(72,G13,G14,TRUE)","v":0.93933237482075893},{"$f":"showformula(I16)","v":"=1-NORMDIST(72,G13,G14,TRUE)"}]
[]
[]
[null,"How many cars could Fred book, and have no more than a 10% chance of"]
[null,"needing to pay overtime?"]
[]
[]
[null,"A data table makes easy work of this question:"]
[]
[null,null,null,"cars",{"$f":"I16","v":0.93933237482075893},{"$f":"showformula(E25)","v":"=I16"}]
[null,null,null,54,7.152939217714871E-3]
[null,null,null,55,3.9807900730056689E-2]
[null,null,null,56,0.14252470370130632]
[null,null,null,57,0.34555111192242138]
[null,null,null,58,0.6035755086858976]
[null,null,null,59,0.81893723025416887]
[null,null,null,60,0.93933237482075893]

{"sheet":"Continuity correction","index":3,"n_rows":37,"n_cols":11,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Sonshiba Printers"]
[]
[null,"Inkjet printer manufacturers make their profits on ink cartridges. Indeed, to get a"]
[null,"customer committed to using one of their printers (and therefore potentially buying"]
[null,"replacement cartridges for that printer in the future), they will sometimes sell a printer"]
[null,"and a full set of cartridges for less than the price of a set of replacement cartridges"]
[null,"alone."]
[]
[null,"Wise purchasers will at times buy a pair of identical printers, getting two printers and"]
[null,"two sets of cartridges for less than the separate cost of a single printer with cartridges,"]
[null,"and a later set of replacement cartridges."]
[]
[null,"Your online store will  soon run a promotion on Sonshiba printers, selling them at a very"]
[null,"attractive price. The deal will only be available to the first 100 online orderers, each of"]
[null,"whom will be allowed to order either one or two printers. From previous promotions, you"]
[null,"estimate that 65% of the callers will indeed order two printers. (Callers make their"]
[null,"purchasing decisions independently of one another.)"]
[]
[null,"What is the probability that, during the promotion, you will sell at least 160 Sonshiba"]
[null,"printers?"]
[]
[null,null,"x","Pr(X=x)"]
[null,null,1,{"$f":"1-D24","v":0.35}]
[null,null,2,0.65]
[]
[null,null,{"$f":"SUMPRODUCT(C23:C24,D23:D24)","v":1.65},"E[X]",null,{"$f":"showformula(C26)","v":"=SUMPRODUCT(C23:C24,D23:D24)"}]
[null,null,{"$f":"SQRT(SUMPRODUCT(C23:C24^2,D23:D24)-C26^2)","v":0.47696960070847333},"StDev(X)",null,{"$f":"showformula(C27)","v":"=SQRT(SUMPRODUCT(C23:C24^2,D23:D24)-C26^2)"}]
[]
[]
[]
[null,null,100,"callers"]
[null,null,{"$f":"C31*C26","v":165},"E[printers ordered]",null,null,{"$f":"showformula(C32)","v":"=C31*C26"}]
[null,null,{"$f":"SQRT(C31)*C27","v":4.7696960070847334},"StDev(printers ordered)",null,null,{"$f":"showformula(C33)","v":"=SQRT(C31)*C27"}]
[]
[null,null,{"$f":"1-NORMDIST(159,C32,C33,TRUE)","v":0.89579359815792547},null,null,null,{"$f":"showformula(C35)","v":"=1-NORMDIST(159,C32,C33,TRUE)"}]
[null,"best!",{"$f":"1-NORMDIST(159.5,C32,C33,TRUE)","v":0.87556806253103869},"Pr(printers ordered ≥ 160)",null,null,{"$f":"showformula(C36)","v":"=1-NORMDIST(159.5,C32,C33,TRUE)"},null,null,null,"applying the continuity correction"]
[null,null,{"$f":"1-NORMDIST(160,C32,C33,TRUE)","v":0.85274630315994471},null,null,null,{"$f":"showformula(C37)","v":"=1-NORMDIST(160,C32,C33,TRUE)"}]

{"sheet":"Overtime","index":4,"n_rows":39,"n_cols":7,"rows_kept":39,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Fred's Cut-Rate Muffler Shop (continued)"]
[]
[null,"Fred's muffler shop employs 9 mechanics for 8 hours per day. A typical"]
[null,"muffler replacement (this is all that the shop does) takes, on average, 1.25"]
[null,"hours, with one standard-deviation's-worth of uncertainty being 15 minutes."]
[null,"Today, 60 cars have been booked for servicing."]
[]
[null,"Fred's mechanics cost Fred $240/ (guaranteed 8-hour) workday in wages and"]
[null,"benefits. Any worktime over 8 hours in a day for a worker costs Fred $45/hour."]
[]
[null,"What is Fred's expected labor cost for the day?"]
[]
[]
[null,"From our previous analysis, the total worktime required is (approximately) normal:"]
[]
[null,75,"expected time required"]
[null,1.9364916731037085,"standard deviation"]
[]
[null,"We can solve this problem via simulation:"]
[]
[null,{"$f":"NORMINV(RAND(),B16,B17)","v":74.858994144602235},"required worktime",null,{"$f":"showformula(B21)","v":"=NORMINV(RAND(),B16,B17)"}]
[]
[null,{"$f":"9*240","v":2160},"standard workday payroll"]
[null,{"$f":"MAX(0,B21-72)*45","v":128.65473650710058},"overtime",null,{"$f":"showformula(B24)","v":"=MAX(0,B21-72)*45"}]
[null,{"$f":"B23+B24","v":2288.6547365071006},"total"]
[null,{"$f":"IF(B24>0,1,0)","v":1},"overtime required?"]
[]
[null,null,null,{"$f":"ADDRESS(ROW($B$24),COLUMN($B$24))","v":"$B$24"},{"$f":"ADDRESS(ROW($B$25),COLUMN($B$25))","v":"$B$25"},{"$f":"ADDRESS(ROW($B$26),COLUMN($B$26))","v":"$B$26"},"monitored cell"]
[null,null,null,137.10104337199951,2297.1010433720326,0.93952999999999998,"mean"]
[null,null,null,82.14078905529604,82.140789054597803,0.23835676461865393,"sample standard deviation"]
[null,null,null,0,2160,0,"minimum"]
[null,null,null,540.47310000000004,2700.4731000000002,1,"maximum"]
[null,null,null,100000,100000,100000,"number of simulation runs"]
[]
[null,null,null,{"$f":"1.96*D30/SQRT(D33)","v":0.50911388514760525},{"$f":"1.96*E30/SQRT(E33)","v":0.5091138851432776},{"$f":"1.96*F30/SQRT(F33)","v":1.4773505329310208E-3},"margin of error (95% confidence)"]
[]
[null,"We can also solve it analytically, using the formula for conditional normal expectations:"]
[]
[null,{"$f":"B23+45*(1-NORMDIST(72,B16,B17,TRUE))*(B16+B17^2*NORMDIST(72,B16,B17,FALSE)/(1-NORMDIST(72,B16,B17,TRUE))-72)","v":2297.2807904482374},{"$f":"showformula(B39)","v":"=B23+45*(1-NORMDIST(72,B16,B17,TRUE))*(B16+B17^2*NORMDIST(72,B16,B17,FALSE)/(1-NORMDIST(72,B16,B17,TRUE))-72)"}]

{"sheet":"Normal miscellany","index":5,"n_rows":48,"n_cols":4,"rows_kept":48,"cut":false,"hidden":false,"merged":[[0,1,0,8]]}
[null,"Using the normal distribution"]
[]
[null,"Given that Z is a standard normal random variable, find Z for each situation:"]
[]
[null,"The area to the right of Z is 0.01."]
[null,"The area to the right of Z is 0.025"]
[null,"The area to the right of Z is 0.05"]
[null,"The area to the right of Z is 0.10."]
[]
[null,null,"p","z"]
[null,null,0.01,{"$f":"NORMSINV(1-C11)","v":2.3263478740408408}]
[null,null,2.5000000000000001E-2,{"$f":"NORMSINV(1-C12)","v":1.9599639845400536}]
[null,null,0.05,{"$f":"NORMSINV(1-C13)","v":1.6448536269514715}]
[null,null,0.1,{"$f":"NORMSINV(1-C14)","v":1.2815515655446006}]
[]
[]
[]
[null,"The mean cost for employee alcohol rehabilitation programs involving hospitalization is"]
[null,"$10,000 (USA Today, September 12, 1991). Assume that the rehabilitation program"]
[null,"cost has a normal probability distribution with a standard deviation of $2200. Answer"]
[null,"the following questions."]
[]
[null,"What is the probability that a rehabilitation program will cost at least $12,000?"]
[null,"What is the probability that a rehabilitation program will cost at least $6,000?"]
[null,"What is the cost range for the most expensive 10% of the rehabilitation programs?"]
[]
[null,null,10000,"mean rehab cost"]
[null,null,2200,"standard deviation"]
[]
[null,null,{"$f":"1-NORMDIST(12000,C27,C28,TRUE)","v":0.18165107044344897},"Pr( cost > $12,000 )"]
[null,null,{"$f":"1-NORMDIST(6000,C27,C28,TRUE)","v":0.96548182600279242},"Pr( cost > $6,000 )"]
[null,null,{"$f":"NORMINV(0.9,C27,C28)","v":12819.413444198122},"lower end of range for most expensive 10%"]
[]
[]
[null,"Drivers who are members of the Teamsters Union can earn an average of $17.15 per"]
[null,"hour (U.S News & World Report, April 11, 1194). Assume that available data indicate"]
[null,"wages are normally distributed with a standard deviation of $2.25."]
[]
[null,"What is the probability that wages are between $15.00 and $20.00 per hour?"]
[null,"What is the hourly wage of the highest paid 15% of the Teamster drivers?"]
[null,"What is the probability that wages are less than $12.00 per hour?"]
[]
[null,null,17.149999999999999,"mean hourly wage"]
[null,null,2.25,"standard deviation"]
[]
[null,null,{"$f":"NORMDIST(20,C43,C44,TRUE)-NORMDIST(15,C43,C44,TRUE)","v":0.72771433574293776},"Pr( wages between $15 and $20 )"]
[null,null,{"$f":"NORMINV(0.85,C43,C44)","v":19.481975126361025},"lower end of range for highest-paid 15%"]
[null,null,{"$f":"NORMDIST(12,C43,C44,TRUE)","v":1.1042904503150893E-2},"Pr( wages less than $12 )"]