ࡱ> ]\  Worksheet&Tools&WindowChart&Tools&WindowVisual Basic Module&Tools&Window\pShohini Banerjee BEPPO&Budget: - Data_Range;<(Exp_Cost: 'Exp_NPV:, Live_Loss;< 'Loss;< ) Mean_Loss: *Penalty;<3Port_by_Scenario;<, Portfolio; 'Req_NPV:F .solver_adj)+; ;< ! solver_cvgMbP? solver_drv solver_eng solver_est solver_ibd solver_itrd+ solver_lhs1: + solver_lhs2:. solver_lhs3;< . solver_lhs4;< . solver_lhs5; . solver_lhs6;  solver_lin solver_mip solver_neg solver_nod solver_num solver_nwt solver_ofx* solver_opt: ! solver_pivư>! solver_preư> solver_pro! solver_redư> solver_rel1 solver_rel2 solver_rel3 solver_rel4 solver_rel5 solver_rel6 solver_reo solver_rep+ solver_rhs1: + solver_rhs2: solver_rhs3. solver_rhs4;< solver_rhs5 solver_rhs6 solver_rlx solver_scl solver_sho solver_timd solver_tmp! solver_tol? solver_typ solver_val solver_ver%WBMIN: 3WBOMITBlitzrange; 2WBOMITLive_Loss;< 0WBOMITLoss_Stats:=Z--% X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1hArial1@Arial1Arial1Arial1Arial1Arial1 Arial1Courier1Courier1 Arial1Arial1Arial1Arial1Arial1Arial1 Arial1hArial1Arial1Arial1QTahoma"$"#,##0_);\("$"#,##0\)"$"#,##0_);[Red]\("$"#,##0\) "$"#,##0.00_);\("$"#,##0.00\)%""$"#,##0.00_);[Red]\("$"#,##0.00\)5*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_),))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)=,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)4+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)74_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"??_);_(@_)0.0% 0.000% 0.0000%;8_("$"* #,##0.0_);_("$"* \(#,##0.0\);_("$"* "-"??_);_(@_)?<_("$"* #,##0.000_);_("$"* \(#,##0.000\);_("$"* "-"??_);_(@_)#,##0;[Red]#,##0"$"#,##0;[Red]"$"#,##0#,##0.00;[Red]#,##0.00"$"#,##0.00;[Red]"$"#,##0.00 #,##0.0# "$"#,##0.0_);[Red]\("$"#,##0.0\) "$"#,##0 "$"#,##0.0 "$"#,##0.00d\-mmm\-yy\ h:mm\ AM/PM0.00.000                + ) , *     a! !  @ !P  a!T  @ !X  a!\  a!T  i!\  i!\" !P"  @"  a!\  !X" !P  a!T  a!T a!T  !X !p 1)\ ,!!P ,!!P  !x !"T  !X  !x !"T !!T !!T !!T "P 1 )\ a!T" !T" "T  !)\ !X  a!\ !X `!" a     a    )\ a!T  !T !"  `"  "p  !x  !  `" @ `"  `" @  `" @@ `" @ `" @@ !"t @!"t !"t @ !"t A@!"t A!"t A@ " a" !"t A @!"t A!"t AA 1 )| A @1 )| A1 )| AA  $ @ $  $ A  $ @ $  $ @  $ @@ $ @ $ @@  !T  !X Normal_WB! Status Normal_WBERR8 ``i̜̙3f3333f3ffff333ff333f33f33BBB EPPOjResultsBall & Savage Associates  wI=zZ,_&b  MbP?_*+%&APage &PMNHP LaserJet 4/4M PostScriptbM_g odXX`T0b`T0`T0/k77@RdCustom page 1BBCustom page 2BBCustom page 3BB"dXX??U} } ` }  }  }  }  }  }  } @ }   wT0@ b@@b;\MrO+b@@b  ,@ Y@ @ ,:@Yta@Yb@mY@+b@0ap0aK E&P Portfolio22+/+++++++++&&&&&&&&&&KOptimizer EPPO22+/+++++++++&&&&&&&&&&/k' Copyright 1998, Ball & Savage Assoc. "11&&*Blitzogram Calculations&&&&&&&1 Portfolio Statistics&  &Max &Min &N &Bins&&&&& 7NPV 7Loss& )&[?!@# B)&p# B~ &@$'20@DD&&&&& Exp.00w@c eB)0*Nd@ #tB &&&.pD''?D.@!' # % A5p  LD(?  LD& J>= +Count)+D@# B+^e(i@  LD+?  LL+3@+^e(i@+ffffff? Req.~ ;@j<==Input &&&.2T@''433333?. @52T@(?& 7Cost7Reserves&&~ ).Lގ.@ ''433333? .@5Lގ.@ (? & Exp.0 0h@c eB0 3pu@ c eB &&' .2B@  ' '?  .@  52B@  (333333?  & + J<=" +&&' .[?!@  ' '?  .@  5[?!@  (333333?  & Req.~ ;@ j<==Input &&' .Lގ.ۿ@ & '? .  5Lގ.ۿ@ ( &4 &&'.&'.5(&  ? Portfolio$ III?I?I& GGGGG<Project Statistics8 Project A8 Project B8 Project C8 Project D8 Project E844444  A B C D E  Exp. ,NPV4@4Ye@4ԋ@4@4Zk@E44444 =Budget>  D D> D D>Gag@ D D>!w@ D D> D D  Exp. ,Cost4? Kw@4ՆG@4Gaw@4!w@~ 4 w@!F /@ %־  Exp.,Reserves~ 6d@6@@6@k@6zm@~ 6gp@   $Data!%Optimization Calculations""""" ! Portfolio by "# - H%/X@#(  ""   Project A Project B Project C Project D Project E!Scenario!Penalty "!""  Scenario 1-G@- -Y-r-ψ'@(9ue eB:u@ D5 :u@D $ B~  @  ""  Scenario 2-+Sa@-G-Y-@o-u9r%e mB:r@ %L :r@ )% L , B~  @  ""  Scenario 3-yqj۝@-G-r-+Ix@~ -u9+I@:+I  : ~  @  ""  Scenario 4-;>1@~ -F-M*#@-r-u9M*ˠ@:M*ˠ  : ~  @  ""  Scenario 5-*~U>f@~ -F-BWzs@-r-u9~a:~a@  :~a@ ~  @  ""  Scenario 6$-@v-F-r-r-r9 |: |@  : |@  @Z ~@  ""  Scenario 7-Pu-F-h@-v~^e@~ -u9h-@:h-  : ~  @  ""  Scenario 8- n@-G-r-S؛@-]g֭@9S@:S  : ~  @  ""  Scenario 9$-x-G-r-r-9 |: |@  : |@  @vK@  ""  Scenario 10-Fy@~ -G-RKQ@-x@-?h@9 @:   : ~  @  ""  Scenario 11-\ͱ}@~ -F-|;@-r-u9|@:|  :  ~  @  "" DlOP K8q`I T0! b" b# \MrO$ % & +' b( b) * + , - . / ta0 bm1 2 b3 04 5 6 7 8 a9 p0: a; < = > ?    Scenario 12 -k\`@ -F-r-@o-u 9y  :y@  :y@!  @FpwFJHj@ "" !! Scenario 13!-@v-F!-h@!-Rfm@!-e(PW@!9BJ@!!:BJ! ! :" ~ ! @ ! "" "" Scenario 14"- n@"-F-r"-Yej̜y@"-;jgif@"9Yej(y:@$-G-r$- e{P@$-JE@$9 e{@$$: e{$ $ :% ~ $ @ $ "" %% Scenario 17%-Pu-@F%-i@%-@o-%9:(y:@~ 6-G6- yUb@6-M*#@~ 6-69,m@666<e mB6:,m6 66<L6 :76 )6< L , B~ 6 @ 6 "" 77 Scenario 357-T$)i@~ 7-G7-j%VA3@7-Rfm@~ 7-Y79;|д@767:;|д7 67 :86 ~ 7 @ 7 "" 88 Scenario 368-y- -r-8-)@89p868:p@8 68 :p@96 ~ 8 @ 8 "" 99 Scenario 379-_;@~ 9-G9- H2@9-Rfm@~ 9-u99$v@969:$v9 69 ::6 ~ 9 @ 9 "" :: Scenario 38:-B(@~ :-F:-i@:-r-u:9:r@ ; "" << Scenario 40<-x-G<-i@<-r-u<9:> ?? D6lPL@ T0A bB bC \MrOD E F +G bH bI J K L M N O taP bmQ R bS 0T U V W X aY p0Z a[ \ ] ^ _ @@ AA BB CC DD EE FF GG HH II JJ KK LL MM NN OO PP Q R S T U V W X Y Z [ \ ] ^ _ Djl ` T0a bb bc \MrOd e f +g bh bi j k l m n o tap bmq r bs 0t u v ` a b c d e f g h i j k l m n o p q r s t u v 2 ]>` A@lhH  &APage &P"Pag`??3323   4 3QQi; Qi; 34E4D FA*F > 3O1D;3 43*4523  43" $%3O&Q4444 p^e(i@2T@Lގ.@2B@[?!@Lގ.ۿ@?ffffff? ? ? 333333? 333333? ]>k d A@ &H  &APage &P"Pagk??3323   4 3QQ;Q;34E4D FAG  3O 3" $%3O&Q43      4444  A B C D EGag@!w@ Risk Measure on this instructive model is mean loss. However it is easy to generalize to any convex risk measure such as Semi-Variance.=Z--% X> "  |kw  MbP?_*+%&APage &P"Pag??ucEPPOU}   T0b,b,,,,BbBB B B B 0B BBB;0BBbB0BBBBBaBp0BaBB Run ResultsLBudget =~ @%Percentages of Projects Taken# L$Investment in Each Project YRun [ENPV\ Mean Loss]Reserves^ Project A_ Project B_ Project C_ Project D` Project E ^ Project A _ Project B _ Project C _ Project D ` Project E Total~ Y?S@T%/X@U>v@M J*?~ NN"~?~ NNƓ\^?N$V^?OW? dxvQ@    L@L e   e.6M\@   e7חe@   fkJ*n@  !DS@ % ~ Y@Sg@TE a@U=w@MsfS?~ NN-P8?N{#L?O7_|? dtb`OS@   e   eRCT@   e!&U@   f; v@  !D@ % ~ Y@S /@TƲ^e@U~(ox@Mz:?~ NNE ?N_We'?~ O? da9"C@   e   eUeNH@   e H4`@   f w@  !D@ % ~ Z@VW@W Ap@XLA8.8y@~ PQ}?QLڛ( +?QȲ?~ R? g   hjF@   hg"D@   h3(3d@   i w@ !D@ % AAAC Project CostsDDDDD AAAA(4? Kw@ Z(4ՆG@ Z(4Gaw@ Z(4!w@ Z(4 w@ Z< +nqZVVG]>d y Q @D',  MMicrosoft Faxy QPo d,,easy to generalize to any conveGPW122<F "d,,??3EPPOo323   4 3Q1:  Project AQ; Q;3      4E4 3Q1:  Project BQ; Q;3   `   4E4 3Q1:  Project CQ; Q;3      4E4 3Q1:  Project DQ; Q;3      4E4 3Q1:  Project EQ; Q;3   `   4E4 3     43     43     43     43     4D FA ;3Ot21 3 "&43*"&4% 3O&Q ENPV'4%XMu3Ob&Q % of Budget in Each Prospect'4523  43" W 3OR%3OQ44 $%3O&Q444%AR( Z3O&Q (%Portfolio Compositions - Budget = 600'44       ]>  Ul A@',  &APage &PMHP LaserJet IIIUl@g,,easy to generalize to any conve@MSUDOHP LaserJet IIId "d,,??3EPPOn323   4 3Q:  Mean LossQ;Q;3      4E4D FAr9 3O' 5 3 43*W@0q@4%.%3O&Q ENPV'4%Yw3O5&Q  Mean Loss'4523  43" $%3O&Q444%Rj3O&Q $!Efficient Frontier - Budget = 600'44  =Z--% X> "    MbP?_*+%&APage &P"Pag?? 6EPPOcU}   ]  @LText 1 Sam L. Savage received his Ph.D. in computer science from Yale in 1973. After a year at General Motors Research Laboratory and 16 years at the University of Chicago Graduate School of Business, he moved to Stanford where he currently teaches courses in Analytical Modeling in Spreadsheets, and directs an Industry/University relations program. In 1985, Dr. Savage led the development of What'sBest!, linking linear programming to spreadsheets. This popular package won PC Magazine's Technical Excellence Award. Dr. Savage's INSIGHT.xla, Business Analysis Software for Microsoft Excel was published in 1998. In his foreword to this work, Harry Markowitz - Nobel Laureate in Economics writes: "Rarely has such sound theory been provided in such an entertaining manner". Dr. Savage consults and lectures extensively to business and government .a L]  @JhText 2 Ben C. Ball, Jr. is an internationally recognized expert and consultant in the Petroleum industry. He received his BS and MS in chemical engineering from MIT, and completed Harvard Graduate Business Schools Advanced Management Program. He is a retired Vice President and Officer of Gulf Oil Corporation, where he spent thirty years in operations and planning. For over twenty years he has held appointments at MIT, including that of Adjunct Pro-fessor of Management and Engineering. Over 75 of his articles have appeared in technical, professional, and management journals and books, including Harvard Busi-ness Review, Petroleum Management, Technology Re-view, and the European Journal of Operational Research. His book, Energy Aftermath, which he co-authored with two MIT colleagues, has been published by Harvard Business School Press. [J].C 6 @ĥText 3 Ben Ball and Sam Savage have been working together since 1990 in the area of portfolio optimization for petroleum exploration prospects. You may contact us as at: benball@mit.edu or savage@stanford.edu=Z--% X> "  Oh+'0@H`x Sam L. SavageESam L. SavageEMicrosoft Excel@T'^@ ՜.+,08@ X` h  Personal Useel EPPOResultsBall & Savage Associates  Worksheets  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKMNOPQRSUVWXYZ[Root EntryT{,{{``FjTD{Book PSummaryInformation(H{ LDocumentSummaryInformation8uD{T