{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Conditional normals","Option pricing"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":3,"defined_names":15,"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":"Conditional normals","index":0,"n_rows":39,"n_cols":13,"rows_kept":39,"cut":false,"hidden":false,"merged":[[0,1,0,4],[3,4,3,6],[5,4,5,5]]}
[null,"Expected Value of a Conditional Normal Variate"]
[]
[null,"z","E[normal random variable\n | value is ³ z]","E[normal random variable\n | value is £ z]"]
[null,{"$f":"mu-5*sigma","v":-5},{"$f":"mu+sigma^2*NORMDIST(B4,mu,sigma,FALSE)/(1-NORMDIST(B4,mu,sigma,TRUE))","v":1.4867199409049058E-6},{"$f":"mu-sigma^2*NORMDIST(B4,mu,sigma,FALSE)/NORMDIST(B4,mu,sigma,TRUE)","v":-5.1865039671258524},"the formulas are used in this row"]
[null,{"$f":"mu-4*sigma","v":-4},1.3383446446857517E-4,-4.22560714448948]
[null,{"$f":"B5+sigma","v":-3},4.4378390421256639E-3,-3.28309865493044,"with these values"]
[null,-2,5.524786267898997E-2,-2.3732155328228428,"m =",0]
[null,-1,0.28759997093917833,-1.525135276160982,"s =",1]
[null,0,0.79788456080286541,-0.79788456080286541]
[null,1,1.5251352761609822,-0.28759997093917833,null,"E( Z | Z ≥ z )","=mu+sigma^2*NORMDIST(z,mu,sigma,FALSE)/(1-NORMDIST(z,mu,sigma,TRUE))"]
[null,2,2.373215532822841,-5.524786267898997E-2,null,"E( Z | Z £ z )","=mu-sigma^2*NORMDIST(z,mu,sigma,FALSE)/NORMDIST(z,mu,sigma,TRUE)"]
[null,3,3.2830986549304151,-4.4378390421256639E-3]
[null,4,4.2256071444894641,-1.3383446446857517E-4]
[null,5,5.1865039663235573,-1.4867199409049058E-6,null,null,"Example:"]
[]
[null,"In most of the examples we've analyzed, we've assumed that any randomness",null,null,null,null,"I have 1200 units of stock on hand, and my next shipment will arrive in a week."]
[null,"is uniform, i.e., that unknown quantities are equally likely to take any value",null,null,null,null,"Weekly demand is normally distributed, with a mean of 1000 and a standard"]
[null,"between specified lower and upper limits. This assumption, while a touch",null,null,null,null,"deviation of 150."]
[null,"unrealistic, has enabled us to work through the examples and extract the key"]
[null,"conceptual points without needing to worry about complex calculations.",null,null,null,null,"If I run out, what is the expected number of units of unsatisfied demand I'll face?"]
[]
[null,"A more realistic assumption would be that the uncertainty we face is normally",null,null,null,null,{"$f":"1-NORMDIST(1200,1000,150,TRUE)","v":9.1211219725867876E-2},"Pr(demand ≥ 1200)",null,null,{"$f":"showformula(G22)","v":"=1-NORMDIST(1200,1000,150,TRUE)"}]
[null,"distributed. If this were the case, we would find ourselves occasionally",null,null,null,null,{"$f":"1000+150^2*NORMDIST(1200,1000,150,FALSE)/(1-NORMDIST(1200,1000,150,TRUE))","v":1269.7202304205341},"E[demand | demand ≥ 1200]",null,null,{"$f":"showformula(G23)","v":"=1000+150^2*NORMDIST(1200,1000,150,FALSE)/(1-NORMDIST(1200,1000,150,TRUE))"}]
[null,"needing to compute conditional expectations in one tail of the normal",null,null,null,null,{"$f":"G23-1200","v":69.720230420534108},"E[shortage | I run out]",null,null,{"$f":"showformula(G24)","v":"=G23-1200"}]
[null,"distribution. (Such computations arise in problems running the gamut from"]
[null,"adverse selection to inventory analysis: given that our offer is accepted, …, or"]
[null,"given that we stock out, … .)",null,null,null,null,"If I don't run out, what's the expected number of items I'll have on hand when the"]
[null,null,null,null,null,null,"next order comes in?"]
[null,"The formulas used to determine the second and third columns of the table given"]
[null,"here aren't pretty - but they do work for values of z between -5 and 5. (A wide",null,null,null,null,{"$f":"NORMDIST(1200,1000,150,TRUE)","v":0.90878878027413212},"Pr(demand £ 1200)",null,null,{"$f":"showformula(G30)","v":"=NORMDIST(1200,1000,150,TRUE)"}]
[null,"enough range for all practical purposes.)",null,null,null,null,{"$f":"1000-150^2*NORMDIST(1200,1000,150,FALSE)/NORMDIST(1200,1000,150,TRUE)","v":972.92934096966064},"E[demand | demand £ 1200]",null,null,{"$f":"showformula(G31)","v":"=1000-150^2*NORMDIST(1200,1000,150,FALSE)/NORMDIST(1200,1000,150,TRUE)"}]
[null,null,null,null,null,null,{"$f":"1200-G31","v":227.07065903033936},"E[surplus | I don't run out]",null,null,{"$f":"showformula(G32)","v":"=1200-G31"}]
[]
[null,null,null,null,null,null,"As expected, 1269.72 * 9.12% + 972.83 * 90.88% = ",null,null,null,null,{"$f":"G22*G23+G30*G31","v":1000},{"$f":"showformula(L34)","v":"=G22*G23+G30*G31"}]
[]
[null,null,null,null,null,null,"That is (using the \"it depends\" formula for expectations),"]
[]
[null,null,null,null,null,null,"E[demand | demand ≥ 1200] * Pr(demand ≥ 1200)"]
[null,null,null,null,null,null,"+ E[demand | demand £ 1200] * Pr(demand £ 1200)  =  E[demand] ."]

{"sheet":"Option pricing","index":1,"n_rows":28,"n_cols":11,"rows_kept":28,"cut":false,"hidden":false,"merged":[[10,1,10,7],[0,1,0,7]]}
[null,"Option Pricing"]
[]
[null,"For $50, you purchase 100 $70 call options on a share of common stock",null,null,null,null,null,null,null,null,"The famous Black-Scholes option-pricing formula modifies this"]
[null,"in XYZ Company. The stock is currently trading at $66/share. The options",null,null,null,null,null,null,null,null,"derivation in two ways: It takes the time-value of money into"]
[null,"expire 50 business days from now:",null,null,null,null,null,null,null,null,"account, and it tracks share price changes in percentages"]
[null,null,null,null,null,null,null,null,null,null,"instead of raw currency. Price changes over time are then"]
[null,"They will be worth nothing at expiration if the closing share price is $70 or",null,null,null,null,null,null,null,null,"multiplicative (rather than additive), and so the closing price is"]
[null,"less. Otherwise, you plan on exercising the option on the day of expiration",null,null,null,null,null,null,null,null,"lognormally distributed (its logarithm is normally distributed)."]
[null,"and immediately selling the stock for a net profit of"]
[]
[null,"100*(closing price - $70) -$50"]
[]
[null,"You believe that the stock will rise, on average, by $0.06 per trading  day,"]
[null,"with a daily standard deviation of $0.50. What is your expected profit from"]
[null,"this investment?"]
[]
[null,"A plausible assumption is that day-to-day price changes vary independently,"]
[null,"so (by the Central Limit Theorem) we would expect the total price change"]
[null,"over 50 trading days to be roughly normally distributed"]
[]
[null,"Let S represent the closing price of a share of the stock when the option expires."]
[]
["m",{"$f":"50*0.06+66","v":69},"E[S]",null,{"$f":"showformula(m)","v":"=50*0.06+66"}]
["s",{"$f":"SQRT(50)*0.5","v":3.5355339059327378},"Stdev(S)",null,{"$f":"showformula(s)","v":"=SQRT(50)*0.5"}]
[]
[null,{"$f":"1-NORMDIST(70,m,s,TRUE)","v":0.38864870539476071},"Pr( S≥70 )",null,{"$f":"showformula(B26)","v":"=1-NORMDIST(70,m,s,TRUE)"}]
[null,{"$f":"m+s^2*NORMDIST(70,m,s,FALSE)/(1-NORMDIST(70,m,s,TRUE))","v":72.486872502262941},"E [S | S≥70 ]",null,{"$f":"showformula(B27)","v":"=m+s^2*NORMDIST(70,m,s,FALSE)/(1-NORMDIST(70,m,s,TRUE))"}]
[null,{"$f":"-50+(B27-70)*100*B26+0*(1-B26)","v":46.6519778486321},"E[ net profit ]",null,"=-50+(B27-70)*100*B26+0*(1-B26)"]