Home

Advanced Analytical Models

1. sanbiugaa unneziumdry anes Bursn unre nus nogyss s se paziwijdo ag uea aijorund ayy c FEE senhiuu3a uoreziuundgo uaisurs Adde pue zs sse 3 jasse uaea jo Aqpige oa pue suinjai ay Burejnurs Ag japow ay ur uae nurs opeg auo ajeiodio2u rz LE su 610 otjojuod eu SZIWUIW 40 SUNIA J640 cijopiod aZIWIXEW SABANE UES AUO BE BC sugieayiaads peuoippse B pa 2600 OF 199 St 2 3 95001 StyPla4 YORE ONY ped t oliouoa SLU ESUOD Pael 6 593 pambay UNUKE py pae uec sajqene A uoisio uo suomnigsay SC GL3 ga Suba m uonesogg sajqeneg uoisiDag re 812 ONEN sisti OF Winey eziuixepy eAgoefqo Ez ce apo unneziuundo au jo su ieayiaada LE oz 5L o6sr oneyysiy or umay BD 3900000 esr OLPO CL pog opopiog 2 gl l 8 E Ol t080 35 DU SE 9800 5 9608 CL 30 01 DI 558 9 assy L l 9 Ol F reseau 3 CDU SE 00 S 9809 8l WIF 6 558 9 Jas sy L T 6 4 renen 00 SE 00 S 9evtr 8l ssh FL B 5569 assy l 5 BEB I Se DU SE 9900 5 SC vl WEFTS 5589 SSY l E g E 52860 00 SE 98008 5E VI Solo FL 9 556 9 assy l c Y S 2660 S DU SE 9800 5 HEEEL WEL G 8886 9 Jas Sy l 5 8 31990 A00 SE 9800 HETH SPIO p 5528 9 Jass l 5 n 3 D2sz SS DU SE 5800 5 96r s eho LL E 556 9 passy L OL 8 z690 00SE 00 S HETIL WIEL Z 588 9 Jessy L P4 c 8 Fz99 0 00 SE 98008 HEZI WFS OL S8E 2 18557 o T 1H oTiH ipo OTH uopesojy uopne o n Bupuey Bunuey ysiy Bunuey Buyuey oneu Std ununxey unumuy sijbIeM Sd
2. uonejnurs DEEE eee A mm pue unuixeui wnwuu ay Mous sales 3 qeu jsed uauMw yaam Jad pjos SIED Jo Jaquunu oy alas Puondo suonepuns oueuAq aqa a au aquosap pinoo nok ajdwexa 104 slay a qeyiere jn320 0 san eA axi JSOW pue uimuiixeur s UOTINGINS p sui jo tat ane iei umuiumu ay mouy noK asaym uomenjs uondissep notus y Jeuonmquisip Suis SP SAPS Jo au 13 u3 Feudo sfayawieied 3ndur Sty jo Majn Byeurayy euond o 100 uossio4 CF Jeuucurg aAgebew CI siajauie1ed apto VW painbo unuu jequno Y MN s uonnquisip v openan siy ur Busto papajas ay 13 u3 iW uBu Aq jsixa woun sesos MIM Smaa Jualayig zs Apne LY 92x ur q 32 wong Rife ejui or suos arusnsyjawen vogdunssy v o gepeueu NT Aun oyp Wo YTD aiay suigeu uonduinsse Sy 19 u3 11 12 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Data Boundary Typically the average analyst does not use distributional or data boundaries truncation but they exist for truncating the distributional as sumptions For instance if a normal distribution is selected the theoretical boundaries are between negative infinity and positive infinity However in prac tice the simulated variable exists only within some smaller range This range can be entered to truncate the distribution appropriately Correlations Pairwise correlations can be assigned to input assumptions here If assump
3. Sunsoisroqpod nu gg 3uf9id oo 001 x urea Auu Muyu Cumulative Probability GSIEUL 000S pqeqod sagejnunjureiBojsH g apo euroou Ig o ad 2i sol zi cob Aauanbai4 WE L OM ad ooz 00E OOF Aouanbai4 Cumulative GENL 0009 Juiliqeqorg sanejnunayureiBojstH xf apo euroou suondg saauarajalg soaysyeys wueiBojslH B uonnguysig 35923404 103e nuuls x53 Y apo awu 00 001 euio2u 00 001 8auio2u 00 001 893 oo 001 03 O0000c enue eH oo 00c enue eN 8 14300 v TJO0O0lA 41 42 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Notes The two variable t test with unequal variances the population variance of forecast 1 is expected to be different from the population variance of forecast 2 is appropriate when the forecast distributions are from different populations e g data collected from two different geographical locations or two different operating business units etc The two variable t test with equal variances the population variance of forecast 1 is expected to be equal to the population variance of forecast 2 is appropriate when the forecast distributions are from similar populations e g data collected from two different engine designs with similar specifications etc The paired dependent two variable t test is appropriate when the forecast distributions are from exactly the same population and subjects e g data collect
4. du E MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Enter a relevant title R Simulation Properties weg Enter the desired Title First Example Simulation number of simulation trials default is 1 000 Configurations for this simulation apie ch ue 1 000 Select if you want the Select if you want C Pause simulation on error EM Sittin to stop paco igna gt Turn on correlations encountered default is simulation default is Specify random number sequence Macnee checked Select and enter a seed 999 48 amp AA____ value if you want the simulation to follow a specified random number sequence defaut is unchecked FIGURE 1 3 New simulation profile N Start a new simulation profile by performing these three steps Start Excel and create a new or open an existing model You can use the Basic Simulation Model example to follow along Risk Simulator Examples Basic Simulation Model Click on Risk Simulator New Simulation Profile Enter a title for your simulation including all other pertinent information Figure 1 3 The elements in the new simulation profile dialog shown in Figure I 3 include Title Specifying a simulation profile name or title allows you to create multiple simulation profiles in a single Excel model By so doing you can save different simulation scenario profiles within the same model without having to delete existing assumptions and chang
5. Sensitivity Tornado and Sensitivity Charts Linear 496 136 Sensitivity Tornado and Sensitivity Nonlinear 503 137 Simulation Basic Simulation Model 510 138 Simulation Best Surgical Team 517 139 Simulation Correlated Simulation 525 140 Simulation Correlation Effects on Risk 528 141 Simulation Data Fitting 531 142 Simulation Debt Repayment and Amortization 534 143 Simulation Demand Curve and Elasticity Estimation 538 144 Simulation Discounted Cash Flow Return on Investment and Volatility Estimates 542 145 Simulation Infectious Diseases 546 146 Simulation Recruitment Budget Negative Binomial and Multidimensional Simulation 548 147 Simulation Retirement Funding with VBA Macros 556 148 Simulation Roulette Wheel 560 149 Simulation Time Value of Money 562 150 Six Sigma Obtaining Statistical Probabilities Basic Hypothesis Tests Confidence Intervals and Bootstrapping Statistics 571 151 Six Sigma One and Two Sample Hypothesis Tests Using t Tests Z Tests F Tests ANOVA and Nonparametric Tests Friedman Kruskal Wallis Lilliefors and Runs Tests 590 Xii CONTENTS 152 Six Sigma Sample Size Determination and Design of Experiments 623 153 Six Sigma Statistical and Unit Capability Measures Specification Levels and Control Charts 627 154 Valuation Buy versus Lease 631 155 Valuation Banking Classified Loan Borrowing Base 634 156 Valuation Banking Break Even Inventory with
6. resulting in a regres sion equation that is neither efficient nor accurate One quick test of the presence of multicollinearity in a multiple regression equation is that the R squared value is relatively high while the t statistics are relatively low Another quick test is to create a correlation matrix between the independent variables A high cross correlation indicates a potential for autocorrelation The rule of thumb is that a correlation with an absolute value greater than 0 75 is indicative of severe multicollinearity Another test for multicollinearity is the use of the Variance Inflation Factor VIF obtained by regressing each independent variable to all the other independent variables obtaining the R squared value and calculating the VIF A VIF exceeding 2 0 can be considered as severe multicollinearity A VIF exceeding 10 0 indicates destructive multicollinearity Figure 1 43 The Correlation Matrix lists the Pearson s product moment correlations com monly referred to as the Pearson s R between variable pairs The correlation coefficient ranges between 1 0 and 1 0 inclusive The sign indicates the direction of association between the variables while the coefficient indicates the magnitude or strength of association The Pearson s R measures only a linear relationship and is less effective in measuring nonlinear relationships Correlation Matrix CORRELATION X2 x3 x4 x5 x1 0 333 0 959 0 242 0 237 x2 Too 0 349 0 319 0 1
7. 0 51 A Quantity EEE 0 35 C Quantity EE 0 33 4 Price EEE 0 31 6 Price NND 0 22 c Price 0 17 Tax Rate 0 05 Price Erosion 0 03 Sales Growth 0 0 0 1 02 03 0 4 0 5 0 6 FIGURE 1 26 Sensitivity chart without correlations Nonlinear Rank Correlation Net Present Value TT 0 57 6 Quant A 0 52 A Quantity DONE C Quantity EE 0 34 4 Price mm 0 26 e Price CON 0 22 C Price EEE 0 21 Price Erosion 0 18 Tax Rate Ml 0 03 Sales Growth 0 0 0 1 0 2 0 3 04 0 5 0 6 FIGURE 1 27 Sensitivity chart with correlations PROCEDURE Use these three steps to create a sensitivity analysis 1 Open or create a model define assumptions and forecasts and run the simulation the example here uses the Tornado and Sensitivity Charts Linear file 2 Select Risk Simulator Tools Sensitivity Analysis 3 Select the forecast of choice to analyze and click OK Figure 1 28 Modeling Toolkit and Risk Simulator Applications 31 Discounted Cash Flow Model Base Year 2005 Sum PV Net Benefits 1 896 63 Market Risk Adjusted Discount Rete 15 0096 Sum PV Investments 1 800 00 Private Risk Discount Rate 5 00 Net Present Value 96 63 Annualized Sales Growth Rate 2 00 Internal Rate of Retum 18 80 Puce Erosion Rate 5 00 Return on Investment 5 37 Effective Tax Rate 40 00 2005 Sensitivity Analysis H Product A Avg PricesUnit 1000 z Product B Avg Price Unit 12
8. 00 20 00 Discount Rate 13824 57 03 81 24 13 50 16 50 15 0096 Price Erosion 116 80 76 64 40 16 4 5096 5 5096 5 0096 Sales Growth 90 59 102 69 42 40 4 80 2 20 2 00 Depreciation 95 08 98 17 3 08 9 00 11 00 10 00 Interest 97 09 96 16 0 93 1 80 2 20 2 00 Amonization 96 16 97 09 0 93 2 70 3 30 3 00 Capex 96 63 96 63 0 00 0 00 0 00 0 00 Net Capital 96 63 96 63 0 00 0 00 0 00 0 00 FIGURE 1 22 Sensitivity table Although the tornado chart is easier to read the spider chart is important to determine if there are any nonlinearities in the model For instance Figure 1 25 shows another spider chart where nonlinearities are fairly evident the lines on the graph are not straight but curved The example model used is Tornado and Sensitiv ity Charts Nonlinear which applies the Black Scholes option pricing model Such nonlinearities cannot be easily ascertained from a tornado chart and may be impor tant information in the model or may provide decision makers important insight Spider Chart FIGURE 1 28 Spider chart 28 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Tornado Chart Investment Tax Rate A Price B Price A Quantity B Quantity C Price C Quantity Discount Rate Price Erosion Sales Growth 0015 8 0 022 Depreciation s n Interest 22 18 Amortization 27 33 Capex Net Capital 150 100 FIGURE 1 24 Tornado chart into the model s dynamics For instance in this Black Schole
9. 1 52 Are these two values statistically significantly different from one another or are they statistically similar and the slight difference is due entirely to random chance What about 1 53 So how far is far enough to say that the values are statistically different In addition if a models resulting skewness is 0 19 is this forecast distri bution negatively skewed or is it statistically close enough to zero to state that this dis tribution is symmetrical and not skewed Thus if we bootstrapped this forecast 100 times i e run a 1 000 trial simulation for 100 times and collect the 100 skewness co efficients the skewness distribution would indicate how far zero is away from 0 19 If the 9096 confidence on the bootstrapped skewness distribution contains the value zero then we can state that on a 90 confidence level this distribution is symmetrical and not skewed and the value 0 19 is statistically close enough to zero Otherwise if zero falls outside of this 9096 confidence area then this distribution is negatively skewed The same analysis can be applied to excess kurtosis and other statistics Essentially bootstrap simulation is a hypothesis testing tool Classical methods used in the past relied on mathematical formulas to describe the accuracy of sample statistics These methods assume that the distribution of a sample statistic approaches a normal distribution making the calculation of the statistic s standard error or conf
10. 25 eer rt eee poe erre nie R assumptions are perturbed simultaneously to identily the impact Product C Avg Price Unit 15 15 to the results It is used to identify critical success factors of the Product A Sale Quantity 000s Product B Sale Quantity 000s Product C Sale Quantity 000s Total Revenues 1 231 75 Direct Cost of Goods Sold 184 76 Net Present Value Risk Simulator Forecast Distribution 5600 forecast 3500 x 20 00 Please select the forecast s to run sensitivity analysis Histogram Statistics Preferences Options Net Present Value Histogram Cumulative Probability 445 33 Type Two Tail m ng Infinity Infinay Certainty 100 00 a 1 1 A Financial Analysis Present Value of Free Cash Flow 528 24 440 60 367 26 305 91 254 62 Present Value of Investment Outlay 1 800 00 0 00 0 00 0 00 0 00 Net Cash Flows 81 271 75 506 69 485 70 465 25 445 33 FIGURE 1 28 Running sensitivity analysis Note that sensitivity analysis cannot be run unless assumptions and forecasts have been defined and a simulation has been run Results Interpretation The results of the sensitivity analysis comprise a report and two key charts The first is a nonlinear rank correlation chart Figure 1 29 that ranks from highest to lowest the assumption forecast correlation pairs These correlations are nonlinear and nonparametric making them fre
11. Binary Digital Instruments Options Analysis Inverse Floater Bond Options Analysis Options Trading Strategies Options Analysis Options Adjusted Spreads Lattice Options Analysis Options on Debt Options Analysis Five Plain Vanilla Options Probability of Default Bond Yields and Spreads Market Comparable Probability of Default Empirical Model Probability of Default External Options Model Public Company 267 269 271 273 276 283 287 289 291 293 321 329 349 354 356 362 366 372 373 376 380 386 390 394 400 405 407 413 420 422 424 432 434 437 Contents xi 120 Probability of Default Merton Internal Options Model Private Company 441 121 Probability of Default Merton Market Options Model Industry Comparable 442 122 Project Management Cost Estimation Model 443 123 Project Management Critical Path Analysis CPM PERT GANTT 446 124 Project Management Project Timing 453 125 Real Estate Commercial Real Estate ROI 456 126 Risk Analysis Integrated Risk Analysis 460 127 Risk Analysis Interest Rate Risk 472 128 Risk Analysis Portfolio Risk Return Profiles 474 129 Risk Hedging Delta Gamma Hedging 477 130 Risk Hedging Delta Hedging 478 131 Risk Hedging Effects of Fixed versus Floating Rates 479 132 Risk Hedging Foreign Exchange Cash Flow Model 481 133 Risk Hedging Hedging Foreign Exchange Exposure 487 134 Sensitivity Greeks 491 135
12. S IO E DIGU claro F ES 9 50 97060 98550 izg960 Brigo 6Lf20 LzEBID BE 900 0 TX DDSE D 6050 PSELO Bras SrS0 0 SGEB D Tt9PrO D 80860 LiES8T Ve2ZD SEZ O PEELO EX esip D 6LSU 0 EA ecer ao LSS LD 0080 zEDU D 8 900 Lose 0 ccre D 00660 1090 eX Soro Sori OLE O LETS 29210 SnzB6 zoro 5260 Sob 0 gee 0 Sr0z0 9780 IX cl Lb ul 6 8 Z g S F t c L Siqgue a qeueA ya puadapu qoe3 Jo spoyad B27 eAnnqusiq Jo sanje d seq eAnnquisig 022F 9969 6L 82820 azez 0 6920 0 L600 0 Oz SELF T689 6L azzo ror Lean 69800 Bl T88EQ 6SSO0 6 azaz o erez 0 9610 SLOD 8l SEEE LP Lee L azaz 8z8zn 80800 1380 n dl 0s E 0 962081 azaro arero L6ST 0 ccolo 8l 188z 0 SIES ZL 8282 0 8282 0 6802 0 cello SI F99E 0 BELL S 87820 8282 0 ESTO s z Tl e91S0 PEEL EL zazo 8c8c 0 er r eso 0 l oosro 9976 LL 8282 0 8c8 D 810600 89910 cb 9Fz5 0 8T90 0L 87820 zz n 06 00 SED VD M BBE tLGEB 8c8c 0 8282 0 5980 0 06810 0 Ob gi0r0 SLz06 azae 0 a8 0 2900 yoo 0 i GLEO ZLOE G 67820 8202 0 erro o FE80 0 8 B8re0 FESO 8282 0 8282 0 BEBD 0 FL800 L Lrolo 8 698 82820 8282 0 SrEZ T697 g tiBFO DB8FF 87820 ezez 0 8200 0 28000 b z rRFEU 588FF 8282 0 3487 0 cece 0 ere 0 t 10870 85 8VL 82820 8282 0 86100 650 0 E rPLGU PSLBU 87870 zoz LSZ FO ELIZIO 92290 98440 8f8e 0 3182 0 osso 0 08500 D Qnid 1815 40 punogaeddry punog amo Md ZW BE awy uonejeJ10201ny 48 Modeling Toolkit and Risk Simulator Applicatio
13. Stock Options Simple European Call Option 719 Contents xiii 178 Employee Stock Options Suboptimal Exercise 720 179 Employee Stock Options Vesting Blackout Suboptimal Forfeiture 723 180 Exotic Options American and European Lower Barrier Options 725 181 Exotic Options American and European Upper Barrier Options 728 182 Exotic Options American and European Double Barrier Options and Exotic Barriers 731 183 Exotic Options Basic American European and Bermudan Call Options 734 184 Exotic Options Basic American European and Bermudan Put Options 736 185 Real Options American European Bermudan and Customized Abandonment Options 739 186 Real Options American European Bermudan and Customized Contraction Options 749 187 Real Options American European Bermudan and Customized Expansion Options 756 188 Real Options Contraction Expansion and Abandonment Options 763 189 Real Options Dual Variable Rainbow Option Using Pentanomial Lattices 767 190 Real Options Exotic Chooser Options 770 191 Real Options Exotic Complex Floating American and European Chooser 77 192 Real Options Jump Diffusion Option Using Quadranomial Lattices 774 193 Real Options Mean Reverting Calls and Puts Using Trinomial Lattices 777 194 Real Options Multiple Assets Competing Options 779 195 Real Options Path Dependent Path Independent Mutually Exclusive Non Mutually Exclusive and Complex Com
14. Sun uondposaq uonesojy 0 wnay NsrH sumay mumy pennbay pannbay HORII Augen pozuenuuy ssejJ passy TSOOW NOLLVZIALLdO NOILYIOTIV LISSY 66 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS annualized values such that they can be consistently compared across different asset classes Returns are computed using the geometric average of the relative returns while the risks are computed using the logarithmic relative stock returns approach See the chapters on Volatility Models for details on computing the annualized volatil ity and annualized returns on a stock or asset class The Allocation Weights in column E holds the decision variables which are the variables that need to be tweaked and tested such that the total weight is constrained at 100 cell E17 Typically to start the optimization we will set these cells to a uniform value where in this case cells E6 to E15 are set at 1096 each In addition each decision variable may have specific restrictions in its allowed range In this example the lower and upper allocations allowed are 5 and 35 as seen in columns F and G This means that each asset class may have its own allocation boundaries Next column H shows the return to risk ratio which is simply the return percentage divided by the risk percentage where the higher this value the higher the bang for the buck The remaining model shows the individual asset class rankings by returns risk return to risk ratio and a
15. click on Risk Simulator Edit Simulation Profile and make the required changes 2 Defining Input Assumptions The next step is to set input assumptions in your model Note that assumptions can be assigned only to cells without any equations or functions i e typed in numerical values that are inputs in a model whereas output forecasts can be assigned only to cells with equations and functions i e outputs of a model Recall that assumptions 10 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Regular Input Percentile Input V Enable Correlation F Enable Data Bound Triangular Distribution The triangular distribution describes as Minimum infinity situation where you know the minimum maximum and most likely values to occur Maximum rfety T For example you could describe the number of cars sold per week when past sales show the minimum maximum and E Enable Dynamic Simulations FIGURE 1 5 Setting an input assumption and forecasts cannot be set unless a simulation profile already exists Follow these three steps to set new input assumptions in your model 1 Select the cell you wish to set an assumption on e g cell G8 in the Basic Simulation Model example 2 Click on Risk Simulator Set Input Assumption or click the Set Assumption icon in the Risk Simulator icon toolbar 3 Select the relevant distribution you want enter the relevant distribution param e
16. diffusion These processes can be used to forecast a mul titude of variables that seemingly follow random trends but are restricted by prob abilistic laws The process generating equation is known in advance but the actual results generated are unknown Figure I 42 The random walk or Brownian motion process can be used to forecast stock prices prices of commodities and other stochastic time series data given a drift or growth rate and volatility around the drift path The mean reversion process can be uoneurgjso Jayourered ssooodd 3njseu o1g eV 1un931J epeudoidde eq Bu sjepowi ssa oid ISEYI pue sseuuropues ejeorpur senjea d iayy Wosianu02 egeudoidde eiou eq Diu japous tyi ue pue surejqord Aueuogers WOY siayns azueqg pue WOPUE JOU sr esuenbes eu jeu sueaur 1070 COO OFO ioreq INEA wor y 92 ung papag 5000 GEZ ene A Sc angelan 300 GEI HYLA Sc USOT ZEL JEUUON PIEPUGIS oz suna SHOPOU jeuoquanuco VEY Jaye ST repour MSEYIOS e sueau W URU v 969P OP ww jepour qseqaogs Jo Aij qeqoid 68 c ezig duny ZE LEE anjen ue rj Duo see ee AZEN WiO Rey unn SBSEST BEY UNSAY grt REY WIG poud sesse201g pseuoois Bupse22104 uopejnwg 1923310 sseao1d 31 se201s e Oui parajua ag ueg siejeuue1ed say q peyuesaidas jseq si jes eyep Burkuepun ayy jeu jo suonej2edxa jeraueuy pue Juua ucud e pue sa3ueuedxe jsed uo ja1 0 aaey jj nod japow ssasoud 5nseu 0js pyu au Bursoouo uj joarau suoneulquio2 10 japow uoisngip duunf e
17. forecasting with out data or applying expert opinion forecasts The Forecasting Module can be used to generate Automatic time series forecasts with and without seasonality and trend Automatic ARIMA automatically generate the best fitting ARIMA forecasts Basic Econometrics modified multivariate regression forecasts Box Jenkins ARIMA econometric forecasts GARCH Models forecasting and modeling volatility J Curves exponential growth forecasts Markov Chains market share and dynamics forecasts Multivariate regressions modeling linear and nonlinear relationships among variables Nonlinear extrapolations curve fitting S Curves logistic growth forecasts Spline Curves interpolating and extrapolating missing nonlinear values Stochastic processes forecasts random walks mean reversions jump diffusions and mixed processes The Optimization module is used for running Linear and nonlinear optimization Static optimization without simulation dynamic optimization with simula tion and stochastic optimization with simulation and run multiple times Discrete continuous and integer decision variables Analytical Tools Correlated simulations Data diagnostics autocorrelation correlation distributive lags heteroskedas ticity micronumerosity multicollinearity nonlinearity nonstationarity nor mality outliers partial autocorrelation and others Data extraction Data fitting Data import
18. frequency of a particular x value occurring out of the total number of trials while the cumulative frequency smooth line shows the total probabilities of all values at and below x occurring in the forecast Forecast Statistics The forecast statistics shown in Figure I 9 summarize the distribution of the forecast values in terms of the four moments of a distribution 14 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Income 1000 Trials FIGURE 1 8 Forecast chart You can rotate between the histogram and statistics tab by depressing the space bar Preferences The preferences tab in the forecast chart Figure I 10 allows you to change the look and feel of the charts For instance if Always Show Window On Top is selected the forecast charts will always be visible regardless of what other software is running on your computer The Semitransparent When Inactive is a powerful option used to compare or overlay multiple forecast charts at once Median Standard Deviation Variance Coefficient of Variation Maximum Minimum 25 Percentile 75 Percentile Percentage Error Precision at 95 Confidence 2 3154 FIGURE 1 9 Forecast statistics Modeling Toolkit and Risk Simulator Applications 15 Display Always Show Window On Top Semitransparent When Inactive Histogram Resolution Faster Simulation Data Update Interval Faster Update FIGURE 1 10 Forecast chart preferences e g enabl
19. graphically The y axis is the NPV target value while the x axis depicts the percentage change on each of the precedent value The central point is the base case value at 96 63 at 0 change from the base value of each precedent Positively sloped lines indicate a positive relationship or effect negatively sloped lines indicate a negative relationship e g investment is negatively sloped which means that the higher the investment level the lower the NPV The absolute value of the slope indicates the magnitude of the effect computed as the percentage change in the result given a percentage change in the precedent A steep line indicates a higher impact on the NPV y axis given a change in the precedent x axis 4 The tornado chart Figure I 24 illustrates the results in another graphical man ner where the highest impacting precedent is listed first The x axis is the NPV value with the center of the chart being the base case condition Green lighter bars in the chart indicate a positive effect red darker bars indicate a negative effect Therefore for investments the red darker bar on the right side indicate a negative effect of investment on higher NPV in other words capital investment and NPV are negatively correlated The opposite is true for price and quantity of products A to C their green or lighter bars are on the right side of the chart Notes Remember that tornado analysis is a static sensitivity analysis applied on each
20. in cell C18 and represents the objective to be maximized in this optimization exercise To summarize we have the following specifications in this example model Objective Maximize Return to Risk Ratio C18 Decision Variables Allocation Weights E6 E15 Restrictions on Decision Variables Minimum and Maximum Required F6 G15 Constraints Total Allocation Weights Sum to 100 E17 The model has been preset to run the optimization simply click on Risk Simu lator Optimization Run Optimization or alternatively the following shows how to recreate the optimization model Modeling Toolkit and Risk Simulator Applications 67 PROCEDURE Open the example file and start a new profile by clicking on Risk Simulator New Profile and provide it a name The first step in optimization is to set the decision variables Select cell E6 and set the first decision variable Risk Simulator Optimization Set Decision and click on the link icon to select the name cell B6 as well as the lower bound and upper bound values at cells F6 and G6 Then using Risk Simulator copy copy this cell E6 decision variable and paste the decision variable to the remaining cells in E7 to E15 Make sure to use Risk Simulator copy and paste rather than Excel s copy and paste The second step in optimization is to set the constraint There is only one constraint here that is the total allocation in the portfolio must
21. it is recommended that you check the author s web site www realoptionsvaluation com frequently for any analytical updates software upgrades and revised or new models ACKNOWLEDGMENTS A special thank you to the contributors including Mark Benyovszky Morton Glantz Uriel Kusiatin and Victor Wong Dr JOHNATHAN MUN JohnathanMun cs com California USA Software Applications T book covers the following software applications Modeling Toolkit Over 800 functions models and tools and over 300 Excel and SLS templates covering the following applications Business analytics and statistics CDF ICDF PDF data analysis integration Credit and Debt Analysis credit default swap credit spread options credit a m rating debt options and pricing Decision Analysis decision tree Minimax utility functions Exotic Options over 100 types of financial and exotic options Forecasting ARIMA econometrics EWMA GARCH nonlinear extrapolation spline time series Industry Applications banking biotech insurance IT real estate utility 2 Operations Research and Portfolio Optimization continuous discrete integer static dynamic and stochastic Options Analysis BDT interest lattices debt options options trading strategies Portfolio Models investment allocations optimization risk and return profiles a Probability of Default and Banking Credit Risk private public and retail debt credit derivat
22. levels GARCH will first convert the prices into relative returns and then run an internal optimization to fit the historical data to a mean reverting volatility term structure while assuming that the volatility is heteroskedastic in nature changes over time according to some econometric characteristics See Chapter 166 Volatility Volatility Computations Log Returns Log As sets Implied Volatility Management Assumptions EWMA GARCH for details on the GARCH model This approach is used for forecasting the time series of volatility of a marketable security There must be a lot of data available and the data points must all be positive n Markov Chains A Markov chain exists when the probability of a future state depends on a previous state and when linked together forms a chain that reverts to a long run steady state level This approach is typically used to forecast the market share of two competitors The required inputs are the starting probability of a customer Modeling Toolkit and Risk Simulator Applications 77 a in the first store the first state will return to the same store in the next period versus the probability of switching to a competitor s store in the next state See Chapter 86 Forecasting Markov Chains and Market Share for details This method is used to forecast a time series of probabilistic states and the long run steady state condition Maximum Likelihood a Maximum likelihoo
23. optimization Finally an efficient frontier optimization procedure applies the concepts of marginal increments and shadow pricing in optimization That is what would hap pen to the results of the optimization if one of the constraints were relaxed slightly Say for instance the budget constraint is set at 1 million What would happen to the portfolio s outcome and optimal decisions if the constraint were now 1 5 million or 2 million and so forth This is the concept of the Markowitz efficient 64 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS frontiers in investment finance where if the portfolio standard deviation is allowed to increase slightly what additional returns will the portfolio generate This process is similar to the dynamic optimization process with the exception that one of the constraints is allowed to change and with each change the simulation and opti mization process is run This process is best applied manually using Risk Simulator That is run a dynamic or stochastic optimization then rerun another optimization with a constraint and repeat that procedure several times This manual process is important as by changing the constraint the analyst can determine if the results are similar or different and hence whether it is worthy of any additional analysis or can determine how far a marginal increase in the constraint should be to obtain a significant change in the objective and decision variables One item i
24. ovy Parameter 4 New Risk Simulator Icons I paste Parameter 2 X Remove Parameter 21 Close Al Charts Minimize All Charts 16 ME Ed Correlations l ZR Run Simulation 12 New Risk Simulator Menu D es 13 14 Bl Reset senten 15 Example Models D 16 17 Forecasting 18 Optimization Fal Tools 21 E Options 22 Languages z 2 Lense 71 A User Manual 28 QA Hel 28 FIGURE 1 1 Risk Simulator menu and icon toolbar Continued panunuoDd EI IYAD _ _ A deR jenuej Jas 103e nuis ysty ogy e o9 sasu A sabenbue1 suondo 4 4 siooT 4 uoneziundo 4 5unse52104 4 s POW sj duex3 nus 10je nuits Hsi SM uoneinwis dais uonejnuis ung 5mam suoneja1105 4P3 speu ily zin SHEYD Ily 85015 JejeuleJed noy yl 7 SUOD JOFe NWIS YShy impura kdo5 BY a 15e3103j1ndino ws WW uondunssyindupjss 77 pod ay aean 4 M n 1 s u v d o N W H x T 1 H 5 3 3 d 1 oid uonenuis abued gt aijo1g uoneinuis up3 x7 uo 31001 Erfjeuy uoneziundo Bunsea2104 uny uonelnuis Dunip3 515622104 suond alyosd uonenwismaN E uo a suan jurensuop ps 7j UO HdO sisheuy sanas aw uonelodemx3jesuiuoN T ssunjs 2 s uzawouo23 21529 Zp yse22103 uondunssy sijold sljoid J0je nuis JN 310IN uny s y dej uny aaoway ised do ndynops mdups XPI M N ai6uis bug uoispag PS X sassexoldonseupojs T poouipr unuix
25. random chance Right Tailed Hypothesis Test A ngnt tailed hypothesis tests the null hypothesis Ho such that tne population mean is stabstically less than or equal to the hypothesized mean Ihe alternative hypothesis is that the real population mean is stalisbzally greater than the hypothesized mean when tested using the sample dataset Using a t test if the p value is Iess than a specified significance amount typically 0 19 0 05 or 0 07 this means that the population mean is statistically significantly greater than the hypothesized mean at 1096 5 and 1 significance value or 90 95 and 99 statistical confidence Conversely ifthe p value is higher than 0 10 0 05 or 0 01 the population mean is statistically similar or less than the hypothesized mean Left Tailed Hypothesis Test A left talled nypothesis tests the null hypothesis Ho such that the population mean Is statistically greater than or equal to the hypothesized mean The aiternatve nypothesis Is that the real population mean Is statistically less than the hypothesized mean when tested using tne sample dataset Using a t test if the p value s ess than a specified significance amount typically 0 19 0 05 or 0 0 this means that the population mean is stalistically significantly less than the hypothesized mean al 10 5 and 1 significance value or 90 95 and 99 statistical confidence Conversely if the p value is higher tnan 0 10 2 05 or 0 01 tne population mean is stati
26. rank correlation and Kendall s tau are the two nonpara metric alternatives The Spearman correlation is used most commonly and is most appropriate when applied in the context of Monte Carlo simulation there is no dependence on normal distributions or linearity meaning that correlations between different variables with different distribution can be applied In order to compute the Spearman correlation first rank all the x and y variable values and then apply the Pearson s correlation computation Risk Simulator uses the more robust nonparametric Spearman s rank correla tion However to simplify the simulation process and to be consistent with Excel s correlation function the correlation user inputs required are the Pearson s correla tion coefficient Risk Simulator then applies its own algorithms to convert them into Spearman s rank correlation thereby simplifying the process Applying Correlations in Risk Simulator Correlations can be applied in Risk Simulator in several ways When defining assumptions simply enter the correlations into the correlation grid in the set input assumption dialog in Figure I 6 With existing data run the Multi Variable Distribution Fitting Tool to per form distributional fitting and to obtain the correlation matrix between pairwise variables If a simulation profile exists the assumptions fitted automatically will contain the relevant correlation values With the use of a direct input correlati
27. sum to 100 So click on Risk Simulator Optimization Constraints and select ADD to add a new constraint Then select the cell E17 and make it equal to 100 Click OK when done The final step in optimization is to set the objective function and start the opti mization by selecting the objective cell C18 select Risk Simulator Optimization Set Objective and Risk Simulator Optimization Run Optimization and choos ing the optimization of choice Static Optimization Dynamic Optimization or Stochastic Optimization To get started select Static Optimization Check to make sure the objective cell is set for C18 and select Maximize You can now review the decision variables and constraints if required or click OK to run the static optimization Once the optimization is complete you may select Revert to revert back to the original values of the decision variables as well as the objective or select Replace to apply the optimized decision variables Typically Replace is chosen after the optimization is done Figure I 55 shows the screen shots of these procedural steps You can add sim ulation assumptions on the model s returns and risk columns C and D and apply the dynamic optimization and stochastic optimization for additional practice Results Interpretation The optimization s final results are shown in Figure I 56 where the optimal allocation of assets for the portfolio is seen in cells E6 E15 That is given the r
28. test this approximation by using the Distributional Analysis tool Start Programs Real Options Valuation Risk Simulator Distribution Analysis As an example we test a binomial distribution with N 5000 and P 0 50 We then compute the mean of the distribution NP 2500 and the standard deviation of the binomial distribution NP 1 P 5000 0 5 1 0 5 35 3553 We then enter these values in the normal distribution and look at the Cumulative Distribution Function CDF of some random range Sure enough the probabilities we obtain are close although not precisely the same Figure 1 6 The normal distribution does in fact approximate the binomial distribution when NxPislarge compare the results in Figures 1 6 and 1 7 The examples also examine the hypergeometric and Poisson distributions A similar phenomenon occurs When the input parameters are large they revert to oro ed 0OL N SIM Qe tproging rowu DOL N SST O frRcqumy uonnqrunstip erurourq amp jo s3984 UAFA GQ L TnI OO00 T FRO Qiu OOOD Te ure 82 1 Analytics Central Limit Theorem 83 This tool generates the probability density function PDF cumulative distribution function CDF and the Inverse CDF ICDF of all the distributions in Maan aege Risk Simulator including theoretical moments and r SL SURE dA DO probability chart Kurtosis 0 0000 Distribution Mu Sigma 00 Type 2360 51 2421 11 2473
29. the assumption and decision variables Optimization s job is to find the optimal minimum or maximum value of the ob jective by selecting and improving different values for the decision variables When model data are uncertain and can only be described using probability distributions the objective itself will have some probability distribution for any set of decision variables Many algorithms exist to run optimization and many different procedures exist when optimization is coupled with Monte Carlo simulation In Risk Simulator there are three distinct optimization procedures and optimization types as well as different decision variable types For instance Risk Simulator can handle continuous decision variables 1 2535 0 2215 and so forth as well as integers decision variables e g 1 2 3 4 and so forth binary decision variables 1 and 0 for go and no go decisions and mixed decision variables both integers and continuous variables Modeling Toolkit and Risk Simulator Applications 63 On top of that Risk Simulator can handle linear optimization i e when both the objective and constraints are all linear equations and functions as well as nonlinear optimizations i e when the objective and constraints are a mixture of linear as well as nonlinear functions and equations As far as the optimization process is concerned Risk Simulator can be used to run a static optimization that is an optimization that is run on a stati
30. the sample divided by the sample mean proving a unit free measure of dispersion that can be compared across diferent distribubons you can now compare distributions of values denominated in millions of dollars with one in billions of collars cr meters and kilograms etc The First Quartile measures the 25th percentile of the dela points when arranged from s smallest to largesi value The Third Quartile is the value of the 75th percentile data point Sometimes quarliles are used es the upper and lower ranges of a distribution as it t uncates the dala set to ignore outliers The Inter Quartile Range is the difference between the third and first quartiles and is often used to measure the width of the center of a distribution Skewness is the third moment in a dislribut on S ewness characterizes the degree of asymmeby of a dist bution around ils mean Positve skewness indicates a dis ribution wih an asymmetnc tall extending toward more positive values Negatve skewness indicates a distribution with an ssymmetric tall extending toward more negative values Kurtosis charaderizes the relative peakedness or Ralness of a distribution compared lo the normal disbibubon Il is Ihe fourlh moment in a distridulion A positive Kurtosis value indicates a relatively peaked distriDution A negative kurtosis indicales a relatively fat distndution The Kurtosis measured here has been centered to zero certain other kunosis measures are cerlered around 30 While both are equally val
31. value in the current period is related to the value in a previous period and so forth clearly the inflation rate in March is related to February s level which in turn is related to January s level and so forth Ignoring such blatant relationships will yield biased and less accurate forecasts In such events an autocorrelated regression model or an ARIMA model may be better suited Risk Simulator Forecasting ARIMA Finally the autocorrelation functions of a series that is nonstationary tend to decay slowly see Nonstationary report in the model If autocorrelation AC 1 is nonzero it means that the series is first order serially correlated If AC k dies off more or less geometrically with increasing lag it implies that the series follows a low order autoregressive process If AC k drops to zero after a small number of lags it implies that the series follows a low order moving average process Partial correlation PAC k measures the correlation of values that are k periods apart after removing the correlation from the intervening lags If the pattern of autocorrelation can be captured by an autoregression of order less than k then the partial autocorrelation at lag k will be close to zero Ljung Box Q statistics and their p values at lag k have the null hypothesis that there is no autocorrelation up to order k The dotted lines in the plots of the autocorrelations are the approximate two standard error bounds If the autocorrelation is wi
32. which optimization process to run Also the continuous model uses the nonlinear optimization approach this is because the portfolio risk computed is a nonlinear function and the objective is a nonlinear function of portfolio returns divided by portfolio risks while the second example of an integer optimization is an example of a linear optimization model its objective and all of its constraints are linear Therefore these two examples encapsulate all of the procedures aforementioned Example Optimization with Continuous Decision Variables Figure I 54 illustrates the sample continuous optimization model The example here uses the Continuous Optimization file accessed through Risk Simulator Examples In this example there are 10 distinct asset classes e g different types of mutual funds stocks or assets where the idea is to most efficiently and effectively allocate the portfolio holdings such that the best bang for the buck is obtained That is to generate the best portfolio returns possible given the risks inherent in each asset class In order to truly understand the concept of optimization we will have to delve more deeply into this sample model in order to see how the optimization process can best be applied The model shows the 10 asset classes and each asset class has its own set of annualized returns and annualized volatilities These return and risk measures are pour uonezrundo snonunuo Yo l JANI
33. 0 002 004 0 0635 0 0235 D Critical at 1 01150 87 00 0 02 006 00783 0 0183 O Critical at 596 9 1237 96 00 0 02 008 0 0662 0 0062 D Critical at 1096 0 1473 102 00 002 0 10 00916 0 0082 Null Hypothesis The data is normaily distributed 108 00 0 02 012 0 0977 00223 11400 002 014 0 1038 00362 Conclusion The sample data is normally distributed at 127 00 0 02 0 16 0 1160 00420 the 1 4 alpha level 15300 002 018 0 1504 00296 177 00 0 02 020 0 1051 00149 186 00 002 022 0 1994 0 0206 188 00 0 02 024 0 2026 00374 198 00 0 02 026 02193 00407 22200 0 02 028 0 2625 00175 23100 002 0 30 02797 00203 40 00 002 032 0 2975 00225 246 00 0 02 034 0 3096 00304 251 00 0 02 0 36 03199 00401 265 00 002 0 38 0 3494 0 0306 280 00 9 02 040 0 3620 00180 285 00 002 042 0 3931 0 0269 286 00 004 046 0 3953 00547 291 00 0 02 048 0 4065 00735 303 00 0 02 050 0 4336 0 0664 31100 002 052 04519 0 0661 FIGURE 1 48 Sample statistical analysis tool report normality test Simulator As a further example of the tool s use in a continuous distribution and the ICDF functionality Figure I 53 shows the standard normal distribution normal distribution with a mean of zero and standard deviation of one where we apply the ICDF to find the value of x that corresponds to the cumulative probability of 97 50 CDF That is a one tail CDF of 97 50 is equivalent to a two tail 95 confidence interval there is a 2 50 probability in the right tail and 2 50 in the left tail leaving 9
34. 10 015266 Cumulative Probabilty Type Two Tal M NEN Certainty gt 90 003 FIGURE 1 12 Forecast chart two tailed confidence interval Modeling Toolkit and Risk Simulator Applications 17 Income 1000 Trials Frequency PSSssssaBVss 3 Cumulative Probabilty Type Lef Tal gt M NEN Certainty s 95 003 FIGURE 1 18 Forecast chart one tailed confidence interval the certainty level and hit Tab on the keyboard This means that there is a 95 probability that the income will be below 1 3068 i e 95 on the left tail of 1 3068 or a 5 probability that income will be above 1 3068 corresponding perfectly with the results seen in Figure I 12 In addition to evaluating the confidence interval i e given a probability level and finding the relevant income values you can determine the probability of a given income value Figure I 14 For instance what is the probability that income will be Income 1000 Trials 00 90 80 70 2 50 9 40 v 30 20 10 05266 Cumulative Probabilty Type lef Tai M E Certainty gt 64 803 FIGURE 1 14 Forecast chart left tail probability evaluation 18 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS f Income Risk Simulator Forecast Histogram Statistic Preferences Options Income 1000 Trials 00 90 80 70 60 50 40 30 20 Cumulative Probabilty Type Right Tal M EIN Certainty 35 203 FIGURE 1 15 F
35. 10 UOISISAaJ ueaw Hjem ulopuE1 S p JayJay OS ji pue 1552310J sseaoud aijseuao s amp jo ESN ayp JUEMEM o juerouns si uonejndwos jj3o sseupoob e op ejus 4440 yigeqoic ayy jr eumuuajep 0 no 0 dn siy pepmoud ejep ay uan ssedoid 3se4201 s e 10 s1ejaurejed payewsa ay ee Buveojo ayy Arewwins je nsneis Modeling Toolkit and Risk Simulator Applications 51 used to reduce the fluctuations of the random walk process by allowing the path to target a long term value making it useful for forecasting time series variables that have a long term rate such as interest rates and inflation rates these are long term target rates by regulatory authorities or the market The jump diffusion process is useful for forecasting time series data when the variable can occasionally exhibit random jumps such as oil prices or the price of electricity discrete exogenous event shocks can make prices jump up or down These processes can also be mixed and matched as required Multicollinearity exists when there is a linear relationship between the indepen dent variables When this occurs the regression equation cannot be estimated at all In near collinearity situations the estimated regression equation will be biased and provide inaccurate results This situation is especially true when a step wise regression approach is used where the statistically significant independent variables will be thrown out of the regression mix earlier than expected
36. 20 x3 7008 0 196 0 227 xa 1000 0 290 Variance Inflation Factor VIF x2 x3 x4 x5 x1 1 12 12 46 1 06 1 06 x2 BS 114 1 11 1 01 x3 WA 1 04 1 05 x4 WAY 1 09 FIGURE 1 48 Multicollinearity errors 52 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS To test whether the correlations are significant a two tailed hypothesis test is performed and the resulting p values are listed P values less than 0 10 0 05 and 0 01 are highlighted to indicate statistical significance In other words a p value for a correlation pair that is less than a given significance value is statistically significantly different from zero indicating that there is a significant linear relationship between the two variables The Pearson s product moment correlation coefficient R between two variables x and y is related to the covariance cov measure where R y a The benefit of SxS dividing the covariance by the product of the two variables standard deviations s is that the resulting correlation coefficient is bounded between 1 0 and 1 0 inclusive This makes the correlation a good relative measure to compare among different variables particularly with different units and magnitude The Spearman rank based nonparametric correlation is also included in the analysis The Spearman s R is related to the Pearson s R in that the data is first ranked and then correlated The rank correlations provide a better estimate of the relationship betwee
37. 23 24 25 26 27 Decision Analysis Inventory Control 29 30 31 32 Credit Analysis Credit Premium Spread Options Credit Analysis Credit Risk Analysis and Effects on Prices Credit Analysis Internal Credit Risk Rating Model Credit Analysis Profit Cost Analysis of New Credit Debt Analysis Asset Equity Parity Model Debt Analysis Cox Model on Price and Yield of Risky Debt with Mean Reverting Rates Debt Analysis Debt Repayment and Amortization Debt Analysis Merton Price of Risky Debt with Stochastic Asset and Interest Debt Analysis Vasicek Debt Option Valuation Decision Analysis Decision Tree Basics Decision Analysis Decision Tree with EVPI Minimax and Bayes Theorem Decision Analysis Economic Order Quantity and Inventory Reorder Point Decision Analysis Economic Order Quantity and Optimal Manufacturing Decision Analysis Expected Utility Analysis Decision Analysis Queuing Models Exotic Options Accruals on Basket of Assets Exotic Options American Bermudan and European Options with Sensitivities Exotic Options American Call Option on Foreign Exchange Exotic Options American Call Options on Index Futures Exotic Options American Call Option with Dividends Exotic Options Asian Lookback Options Using Arithmetic Averages Exotic Options Asian Lookback Options Using Geometric Averages Exotic Options Asset or Nothing Options Exotic Opt
38. 4 V Interest Pay OCF Mode 01 27 Interest Payments s200 V ProdAQua OCF Mode 01 28 EBT 85874 V Prod BQus DCF Mode 01 29 Taxes 34350 V ProdC Qua DCF Mode 01 30 Net Income DOS Options 31 Depreciation 1200 32 Change in Net Working Capital 0 00 Sse aimee Poe 33 Capital Expenditures X s000 Show Top TS variables 34 Free Cash Flow 528 24 35 Analyze This Worksheet Only Analyze All Worksheets 36 Investments 37 38 FIGURE 1 20 Running a tornado analysis model consists of A B C and where C D E then B D and E are the precedents for A C is not a precedent as it is only an intermediate calculated value Figure L 20 shows the testing range of each precedent variable used to estimate the target result If the precedent variables are simple inputs then the testing range will be a simple perturbation based on the range chosen e g the default is 10 Each precedent variable can be perturbed at different percentages if required A wider range is important as it is better able to test extreme values rather than smaller perturbations around the expected values In certain circumstances extreme values may have a larger smaller or unbalanced impact e g nonlinearities may occur where increasing or decreasing economies of scale and scope creep in for larger or smaller values of a variable and only a wider range will capture this nonlinear impact PROCEDURE Use these three
39. 4 Long Term Value N A Jump Size 21 33 Probability of stochastic model fit 4 63 FIGURE 1 49 Sample statistical analysis tool report stochastic parameter estimation decisions might involve thousands or millions of potential alternatives Considering and evaluating each of them would be impractical or even impossible A model can provide valuable assistance in incorporating relevant variables when analyzing deci sions and finding the best solutions for making decisions Models capture the most important features of a problem and present them in a form that is easy to interpret Models often provide insights that intuition alone cannot An optimization model has three major elements decision variables constraints and an objective In short the optimization methodology finds the best combination or permutation of decision variables e g which products to sell and which projects to execute in every con ceivable way such that the objective is maximized e g revenues and net income or minimized e g risk and costs while still satisfying the constraints e g budget and resources Obtaining optimal values generally requires that you search in an iterative or ad hoc fashion This search involves running one iteration for an initial set of val ues analyzing the results changing one or more values rerunning the model and repeating the process until you find a satisfactory solution This process can be very tedious and time consuming eve
40. 5 in the center or confidence interval area which is equivalent to a 97 50 area for one tail The result is the familiar Z Score of 1 96 Therefore using this distributional analysis tool and the standardized scores for other distributions the exact and cumulative probabilities of other distributions can all be obtained quickly and easily PORTFOLIO OPTIMIZATION In today s competitive global economy companies are faced with many difficult decisions These decisions include allocating financial resources building or expand ing facilities managing inventories and determining product mix strategies Such Modeling Toolkit and Risk Simulator Applications 57 Stochastic Process Parameter Estimations Statistical Summa A stochastic process is a sequence of events or paths generated by probabilistic laws Thal is random events can occur over time bul are governed by specific statistical and probabilistic rules The main stochastic processes include Random Walk or Brownian Motion Mean Reversion and Jump Diffusion These processes can be used to forecast a multitude of variables that seemingly follow random trends but yet are restricted by probabilistic laws The process generating equation is known in advance but the actual results generated is unknown The Random Walk Brownian Motion process can be used to forecast stock prices prices of commodities and other stochastic time series data given a drift or growth rate and a volatilit
41. 5 9149 9156 194 92 06 92 36 92 41 92 45 92 70 92 80 92 84 3321 3326 3348 3373 93 75 93 77 93 82 34 00 94 15 3451 94 57 9464 9469 94 95 95 57 95 62 95 71 35 78 95 83 9537 96 20 96 24 96 40 96 43 96 47 96 81 96 88 97 00 97 07 9721 97 23 3748 37 70 97 77 97 85 98 15 98 17 98 24 38 28 98 32 98 33 38 35 98 65 39 03 9 27 99 46 3947 955 93 73 3 96 100 08 10024 100 36 100 42 100 44 10048 10049 100 83 10117 10128 10134 10145 10146 10155 10173 1174 10181 1223 10255 10258 102 60 102 70 103 17 103 21 103 22 103 32 103 34 103 45 103 65 103 66 103 72 103 81 103 90 103 93 104 46 104 57 104 76 105 20 105 44 105 50 105 52 105 58 105 66 105 87 105 30 105 90 106 29 106 35 106 59 107 01 107 68 107 70 107 93 108 17 108 20 108 34 108 42 108 43 108 49 108 70 109 15 10322 109 35 10952 103 75 110 04 110 16 1025 110 54 1105 mog q44 1 76 130 11 95 112 07 112 13 12 23 11232 2 42 1248 11295 2 92 113 50 113 53 113 63 13 70 14 13 14 14 18 21 14 91 114 95 1540 1558 115 66 16 58 116 38 117 60 118 67 11924 19 52 124 14 12416 124 39 13230 FIGURE 1 38 Distributional fitting report suppose an identical model with identical assumptions and forecasts but without any random seeds is run by 100 different people the results will clearly be slightly different The question is if we collected all the statistics from these 100 people how will the mean be distributed or the median or the skewness or excess kur tosis Suppose one person has a mean value of say 1 50 while another has
42. 50 32913 31513 301 50 Net Income 51524 493 69 472 70 452 25 Noncash Depreciation Amortization 13 00 13 00 13 00 13 00 Noncash Change in Net Working Capital s000 X s000 0 00 0 00 Noncash Capital Expenditures 000 000 000 0 00 Free Cash Flow 528 24 506 69 485 70 465 25 Investment Outlay 1800 00 CT Financial Analysis Present Value of Free Cash Flow 528 24 440 60 367 26 305 91 254 62 Present Value of Investment Outlay 1 800 00 0 00 0 00 0 00 0 00 Net Cash Flows 1 271 76 506 69 485 70 465 25 445 33 FIGURE 1 19 Sample discounted cash flow model cash flow model where the input assumptions in the model are shown The question is What are the critical success drivers that affect the model s output the most That is what really drives the net present value of 96 63 or which input variable impacts this value the most The tornado chart tool can be obtained through Risk Simulator Tools Tornado Analysis To follow along the first example open the Tornado and Sensitivity Charts Linear file in the examples folder Figure I 20 shows this sample model where cell G6 containing the net present value is chosen as the target result to be analyzed The target cell s precedents in the model are used in creating the tornado chart Precedents are all the input variables that affect the outcome of the model For instance if the 24 MODELING TOOLKIT AND RISK SIMULATOR AP
43. 58835 1026854 E Prod C AvgPrice 9508417 98 16816 E Prod BAvg Price 96 16357 97 08976 Prod A Avg Price 97 08876 96 16357 100 6 ij 9862616 96 62616 i 4 pee 96 62616 96 62616 uanizest on wo w0 2x0 25 9X0 X FIGURE 1 21 Tornado analysis report is to identify which of these important impact drivers are uncertain These uncertain impact drivers are the critical success drivers of a project the results of the model depend on these critical success drivers These variables are the ones that should be simulated Do not waste time simulating variables that are neither uncertain nor have little impact on the results Tornado charts assist in identifying these critical success drivers quickly and easily Following this example it might be that price and quantity should be simulated assuming if the required investment and effective tax rate are both known in advance and unchanging Modeling Toolkit and Risk Simulator Applications 27 Base Value 96 6261638553219 input Changes Output Output Effective Input Input Base Case Precedent Cell Downside Upside Value investment 360 00 1 620 00 1 980 00 1 800 00 Tax Rate 26 47 36 00 44 00 40 00 A Price 3 43 189 8 186 40 9 00 11 00 10 00 B Price 1671 176 55 15984 11 03 13 48 12 25 A Quantity 2318 170 07 146 90 45 00 55 00 50 00 B Quantity 30 53 162 72 132 19 31 50 38 50 35 00 C Price 4015 153 44 11296 13 64 16 67 15 15 C Quantity 48 05 145 20 97 16 18 00 22
44. 70 252630 2576889 26314 Formatting X x 2480 00 Single Value 2481 00 Value X 2482 00 2483 00 Range of Values 2464 00 Lower Bound 246i 2485 00 2486 00 Upper Bound A 2497 00 Step Size 2488 00 2499 00 2490 00 2481 00 2492 00 2493 00 2494 00 FIGURE 1 6 Normal approximation of the binomial the normal approximation In fact the normal distribution also can be used to approximate the Poisson and hypergeometric distributions Clearly there will be slight differences in value as the normal is a continuous distribution whereas the binomial Poisson and hypergeometric are discrete distributions Therefore slight variations will obviously exist BETA DISTRIBUTION Finally the Beta worksheet illustrates an interesting distribution the beta distri bution Beta is a highly flexible and malleable distribution and can be made to approximate multiple distributions If the two input parameters alpha and beta are equal the distribution is symmetrical If either parameter is 1 while the other parameter is greater than 1 the distribution is triangular or J shaped If alpha is less than beta the distribution is said to be positively skewed most of the values are near the minimum value If alpha is greater than beta the distribution is negatively skewed most of the values are near the maximum value 84 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS I Distribution Analysis This tool generates the
45. 8 62 Exotic Options Lookback with Fixed Strike 219 63 Exotic Options Lookback with Floating Strike Partial Time 220 64 Exotic Options Lookback with Floating Strike 221 65 Exotic Options Min and Max of Two Assets 222 66 Exotic Options Options on Options 223 67 Exotic Options Option Collar 224 68 Exotic Options Perpetual Options 225 69 Exotic Options Range Accruals Fairway Options 226 70 Exotic Options Simple Chooser 228 71 Exotic Options Spread on Futures 229 72 Exotic Options Supershare Options 230 73 Exotic Options Time Switch Options 231 74 Exotic Options Trading Day Corrections 232 75 Exotic Options Two Asset Barrier Options 233 76 Exotic Options Two Asset Cash or Nothing 234 77 Exotic Options Two Correlated Assets Option 235 78 Exotic Options Uneven Dividend Payments Option 237 79 Exotic Options Writer Extendible Option 238 80 Forecasting Data Diagnostics 239 81 Forecasting Econometric Correlations and Multiple Regression Modeling 248 82 Forecasting Exponential J Growth Curves 254 83 Forecasting Forecasting Manual Computations 257 84 Forecasting Linear Interpolation and Nonlinear Spline Extrapolation 259 85 Forecasting Logistic S Growth Curves 264 CONTENTS 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 Forecast
46. AEAU cdc 0 E SE 6 SEE swajqod ou 3usBpaxsouiDH Sere O Sx sIqeEA JEaul 8bc6 t le aca 96 S62 swuajqod ou J3usepaxsouloH 990E 0 teme Jeauijuou G EU 0 6954 HUS slua qaid ou ansepex soump ARSE O xeldeueA Jeaujuou SEED 0 r4 6 SFF zl swajqod ou J3 sepaxsouloH ZERO cexemdeueA AEAU aste EU ELPA Sb 77ELC sulajqoud ou JYSEPSYSOWOH Ep5Sc LxeldeueA 02128 99 suia qoid ou A ynsa anjea d siano jequalod punog addr punog 1807 insai nsa an ea d ajqeleA Isa sisaujodAH 158 1eauljuon Joaquin EAn eN JEAre uaneuunoddu jsap sisaujod A H 1581 AA Aueauuoy siapno ysoJauinuo123IN Aanse paysoiajay sjnsay 2nsouBeig 45 46 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS If the number of data points is small micronumerosity it may be difficult to detect assumption violations With small sample sizes assumption violations such as non normality or heteroskedasticity of variances are difficult to detect even when they are present With a small number of data points linear regression offers less protection against violation of assumptions With few data points it may be hard to determine how well the fitted line matches the data or whether a nonlinear function would be more appropriate Even if none of the test assumptions are violated a linear regression on a small number of data points may not have sufficient power to detect a significant difference between the slope and zero even if the slope is nonzero The powe
47. Advanced Analytical Motels Over 800 Models and 300 Applications from the Basel II Accord to Wall Street and Beyond JOHNATHAN MUN WILEY John Wiley amp Sons Inc Advanced Analytical Motels Founded in 1807 John Wiley amp Sons is the oldest independent publishing com pany in the United States With offices in North America Europe Australia and Asia Wiley is globally committed to developing and marketing print and electronic products and services for our customers professional and personal knowledge and understanding The Wiley Finance series contains books written specifically for finance and investment professionals as well as sophisticated individual investors and their fi nancial advisors Book topics range from portfolio management to e commerce risk management financial engineering valuation and financial instrument analysis as well as much more For a list of available titles please visit our Web site at www WileyFinance com Advanced Analytical Motels Over 800 Models and 300 Applications from the Basel II Accord to Wall Street and Beyond JOHNATHAN MUN WILEY John Wiley amp Sons Inc Copyright 2008 by Johnathan Mun All rights reserved Published by John Wiley amp Sons Inc Hoboken New Jersey Published simultaneously in Canada No part of this publication may be reproduced stored in a retrieval system or transmitted in any form or by any means electronic mechanical photocopyin
48. For instance a data set such as 1 4 9 16 25 is considered to be nonlinear these data points are from a squared function See Chapter 88 Forecasting Nonlinear Extrapolation and Forecasting for details on nonlinear extrapolation forecasts This methodology is typically applied to forecast time series data Sometimes cross sectional data can be ap plied if there is a nonlinear relationship between data points arranged from small to large values Spline Curves Sometimes there are missing values in a time series data set For instance interest rates for years 1 to 3 may exist followed by years 5 to 8 and then year 10 Spline curves can be used to interpolate the missing years interest rate values based on the data that exist Spline curves can also be used to forecast or extrapolate values of future time periods beyond the time period of available data The data can be linear or nonlinear 78 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS a See Chapter 172 Yield Curve U S Treasury Risk Free Rates and Cubic Spline Curves for details This methodology is used to back fit and forecast time series data only Stochastic Process Forecasting El Sometimes variables are stochastic and cannot be readily predicted using tradi tional means These variables are said to be stochastic Nonetheless most finan cial economic and naturally occurring phenomena e g motion of molecules through the air follow a
49. GURE 1 82 Distributional fitting result Both the results Figure I 32 and the report Figure I 33 show the test statistic p value theoretical statistics based on the selected distribution empirical statistics based on the raw data the original data to maintain a record of the data used and the assumption complete with the relevant distributional parameters i e if you selected the option to automatically generate assumption and if a simulation profile already exists The results also rank all the selected distributions and how well they fit the data BOOTSTRAP SIMULATION Bootstrap simulation is a simple technique that estimates the reliability or accuracy of forecast statistics or other sample raw data Bootstrap simulation can be used to answer a lot of confidence and precision based questions in simulation For instance 36 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Statistical Summary Fitted Assumption 100 61 Theoretical vs Empirical Distribution Fitted Distribution Normal Mean 100 67 Sigma 10 40 Kolmogorov Smirnov Statistic 0 02 P Value for Test Statistic 0 9396 Actual Theoretical Mean 100 61 100 67 Standard Deviation 1031 1040 Skewness 0 01 0 00 Excess Kurtosis 0 0 00 Original Fitted Data 7353 7821 7852 73 50 79 72 79 74 8156 82 08 8268 8275 334 93 64 84 09 94 66 5 00 85 35 85 51 88 04 86 79 86 82 88 91 87 02 87 03 87 45 87 53 87 66 88 05 88 45 851 3 95 90 19 90 54 90 68 90 96 91 2
50. PLICATIONS Al B LT Ye T G E PO mmc 2 Discounted Cash Flow Model 3 4 Base Year 2005 Sum PV Net Benefits 1 896 63 5 Market Risk Adjusted Discount Rate 15 00 Sum PV Investments 1 800 00 6 Private Risk Discount Rate 5 00 Net Present Value 96 63 7 Annualized Sales Growth Rate 2 00 intemal Rate of Retum 18 80 8 Price Erosion Rate 5 00 Retum on Investment 5 37 9 Effective Tax Rate T 40 00 10 x 2005 Fi Tomado Analysis m 12 Prod A Avg Price 13 Prod B Avg Price in 14 Prod C Avg Price 15 Prod A Quantity 16 Prod B Quantity 17 Prod C Quantity Tornado analysis creates static perturbations i e each precedent is perturbed one at a time to identify the impact to the results It is used to identify critical success factors of a model before running simulations Review the precedents below and make any necessary changes L 1225 i 1515 5o0 3500 1 18 Total Revenues sx Select Name Worksheet Base Value Upside Downside Test Points 19 Cost of Goods Sold 18476 V Market Risk DCF Mode 015 01 20 Gross Profit 1 045 99 V Investments DCF Mode 1800 01 21 Operating Expenses 157 50 V Capital Exp DCF Mode 0 01 22 SG amp ACosts 1575 M Changein OCF Mode 01 23 Operating Income EBITDA 873 74 V Depreciatio OCF Mode 01 24 Depreciation 10 00 V Amcr iza o DCF Mode 01 25 Amortization 300 V Efective Ta DCF Mode 01 26 EBIT 860 7
51. Seasonal Lending Trial Balance Analysis 637 157 Valuation Banking Firm in Financial Distress 640 158 Valuation Banking Pricing Loan Fees Model 642 159 Valuation Valuation Model 644 160 Value at Risk Optimized and Simulated Portfolio VaR 647 161 Value at Risk Options Delta Portfolio VaR 651 162 Value at Risk Portfolio Operational and Credit Risk VaR Capital Adequacy 653 163 Value at Risk Right Tail Capital Requirements 657 164 Value at Risk Static Covariance Method 661 165 Volatility Implied Volatility 663 166 Volatility Volatility Computations Log Returns Log Assets Implied Volatility Management Assumptions EWMA GARCH 664 167 Yield Curve CIR Model 673 168 Yield Curve Curve Interpolation BIM Model 674 169 Yield Curve Curve Interpolation NS Model 676 170 Yield Curve Forward Rates from Spot Rates 678 171 Yield Curve Term Structure of Volatility 679 172 Yield Curve U S Treasury Risk Free Rates and Cubic Spline Curves 680 173 Yield Curve Vasicek Model 690 PART 2 Real Options SLS Applications 693 174 Introduction to the SLS Software 695 Single Asset and Single Phased Module 697 Multiple Asset or Multiple Phased SLS Module 704 Multinomial SLS Module 705 SLS Excel Solution Module 709 SLS Excel Functions Module 712 Lattice Maker Module 714 175 Employee Stock Options Simple American Call Option 715 176 Employee Stock Options Simple Bermudan Call Option with Vesting 716 177 Employee
52. Time Series Analysis and enter the relevant inputs The following provides a quick review of each methodology and several quick getting started examples in using the software More detailed descriptions and ex ample models of each of these techniques are found throughout this book Auto ARIMA u Autoregressive integrated moving average ARIMA is an advanced econometric modeling technique ARIMA looks at historical time series data and performs back fitting optimization routines to account for historical autocorrelation the relationship of one value versus another in time the stability of the data to correct for the nonstationary characteristics of the data and this predictive model learns over time by correcting its forecasting errors Advanced knowledge in econometrics is typically required to build good predictive models using this approach The Auto ARIMA module automates some of the traditional ARIMA model ing by automatically testing multiple permutations of model specifications and returns the best fitting model Running the Auto ARIMA is similar to regular ARIMA forecasts the difference being the P D Q inputs are no longer re quired and different combinations of these inputs are automatically run and compared See Chapter 90 Forecasting Time Series ARIMA for more technical details on running and interpreting an ARIMA model This approach can only be used to forecast time series data and can include other independe
53. a at panbay pauinbay HORE Anpe pezyenuuy sD pey JAQON NOLLVZIWI LdO NOLLV2O TIV LISSY 70 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS TABLE 1 8 Optimization Results Portfolio Portfolio Portfolio Returns Objective Returns Risk to Risk Ratio Maximize Returns to Risk Ratio 12 69 4 52 2 8091 Maximize Returns 13 97 6 77 2 0636 Minimize Risk 12 38 4 46 2 7754 portfolio this way the risk is a lot higher as compared to when maximizing the returns to risk ratio although the portfolio returns by themselves are higher In contrast one can minimize the total portfolio risk but the returns will now be less Table L3 illustrates the results from the three different objectives being opti mized From the table the best approach is to maximize the returns to risk ratio that is for the same amount of risk this allocation provides the highest amount of return Conversely for the same amount of return this allocation provides the lowest amount of risk possible This approach of bang for the buck or returns to risk ratio is the cornerstone of the Markowitz efficient frontier in modern portfolio theory That is if we constrained the total portfolio risk levels and successively increased it over time we will obtain several efficient portfolio allocations for different risk characteristics Thus different efficient portfolio allocations can be obtained for different individuals with different risk preference
54. an ad hoc calculation will be fairly intimidating and time consuming On a larger scale suppose there are 100 cities on the salesperson s list the possible itineraries will be as many as 9 3 x 1015 The problem will take many years to calculate manually which is where optimization software steps in automating the search for the optimal itinerary The example illustrated up to now is a deterministic optimization problem that is the airline ticket prices are known ahead of time and are assumed to be constant Now suppose the ticket prices are not constant but are uncertain following some distribution e g a ticket from Chicago to Seattle averages 325 but is never cheaper than 300 and usually doesn t exceed 500 The same uncertainty applies to tickets for the other cities The problem now becomes an optimization under uncertainty Ad hoc and brute force approaches simply do not work Software such as Risk 60 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Distribution Analysis This tool generates the probability density function PDF cumulative distribution function CDF and the Inverse CDF ICDF of all the distributions in Risk Simulator including theoretical moments and ness 0 0000 probability chart Kurtosis 0 1000 Distribution Trials Probability Type Formatting Single Value Value X Range of Values Lower Bound Upper Bound Step Size FIGURE 1 52 Distributional analysis tool binomial di
55. and export Distribution analysis PDF CDF ICDF Distribution designer creating customized distributions and Delphi simula tion Hypothesis tests and bootstrap simulation Sensitivity and dynamic scenario analysis Statistical analysis descriptive statistics distributional fitting hypothesis tests nonlinear extrapolation normality stochastic parameter estimation time series forecasts trending and others Tornado and spider charts The Real Options Super Lattice Solver SLS is another stand alone software that complements Risk Simulator used for solving simple to complex real options problems See Part II of this book for details on this software s applications a 8 m m m mH HN m 8 m m L m m m L 4 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS To install the software insert the accompanying DVD click on the Install Risk Simulator link and follow the onscreen instructions You will need to be online to download the latest version of the software The software requires Windows XP Vista administrative privileges and Microsoft NET Framework 1 1 and 2 0 installed on the computer Most new computers come with Microsoft NET Frame work 1 1 already preinstalled However if an error message pertaining to requiring NET Framework occurs during the installation of Risk Simulator exit the instal lation Then install the relevant NET Framework software also included in
56. and when some of the forecast statistics are required in the optimization For example if the standard deviation of an assumption or forecast is required in the optimization model e g computing the Sharpe ratio in asset allocation and optimization problems where we have mean divided by standard deviation of the portfolio then this approach should be used The stochastic optimization process in contrast is similar to the dynamic opti mization procedure with the exception that the entire dynamic optimization process is repeated T times That is a simulation with N trials is run and then an opti mization is run with M iterations to obtain the optimal results Then the process is replicated T times The results will be a forecast chart of each decision variable with T values In other words a simulation is run and the forecast or assumption statis tics are used in the optimization model to find the optimal allocation of decision variables Then another simulation is run generating different forecast statistics and these new updated values are then optimized and so forth Hence each of the final decision variables will have its own forecast chart indicating the range of the optimal decision variables For instance instead of obtaining single point estimates in the dynamic optimization procedure you can now obtain a distribution of the decision variables hence a range of optimal values for each decision variable also known as a stochastic
57. ar input variable in a model What are the relevant distributional parameters If no historical data exist then the analyst must make assumptions about the variables in question One approach is to use the Delphi method where a group of experts are tasked with estimating the behavior of each variable For instance a group of mechanical engineers can be tasked with evaluating the extreme possibilities of the diameter of a spring coil through rigorous experimentation or guesstimates These values can be used as the variable s input pa rameters e g uniform distribution with extreme values between 0 5 and 1 2 When testing is not possible e g market share and revenue growth rate management still can make estimates of potential outcomes and provide the best case most likely case and worst case scenarios whereupon a triangular or custom distribution can be created However if reliable historical data are available distributional fitting can be accomplished Assuming that historical patterns hold and that history tends to repeat itself historical data can be used to find the best fitting distribution with their relevant parameters to better define the variables to be simulated Clearly adjustments to the forecast value can be made e g structural shifts and adjustments as required to reflect future expectations Figures I 31 through I 33 illustrate a distributional fitting example The next discussion uses the Data Fitting file in the exa
58. are versions or details on installation and licensing The Appendixes provide a more detailed list of all the functions tools and models and the Glossary details the required variable inputs in this software INTRODUCTION TO RISK SIMULATOR This section also provides the novice risk analyst an introduction to the Risk Simu lator software for performing Monte Carlo simulation where a trial version of the software is included in the book s DVD Please refer to About the DVD at the end of this book for details on obtaining this extended trial license This section starts off by illustrating what Risk Simulator does and what steps are taken in a Monte Carlo simulation as well as some of the more basic elements in a simulation analysis It continues with how to interpret the results from a simulation and ends with a discussion of correlating variables in a simulation as well as applying precision and error control Many more advanced techniques such as ARIMA forecasts and opti mization are also discussed Software versions with new enhancements are released continually Please review the software s user manual and the software download site www realoptionsvaluation com for more up to date details on using the latest version of the software See Modeling Risk Applying Monte Carlo Simulation Real Options Analysis Stochastic Forecasting and Portfolio Optimization Hoboken NJ John Wiley amp Sons 2007 also by the author for more technical detai
59. atically perturbs each variable in the model a preset amount captures the fluctuation on the model s forecast or final result and lists the resulting perturbations ranked from the most significant to the least Figures I 19 through 1 24 illustrate the application of a tornado analysis For instance Figure 1 19 is a sample discounted Modeling Toolkit and Risk Simulator Applications 23 Discounted Cash Flow Model Base Year 2005 Sum PV Net Benefits 1 896 63 Market Risk Adjusted Discount Rate 15 0096 Sum PV Investments 1 800 00 Private Risk Discount Rate 5 0096 Net Present Value 96 63 Annualized Sales Growth Rate 2 0096 Internal Rate of Retum 18 8096 Price Erosion Rate 5 0096 Retum on Investment 5 37 Effective Tax Rate 40 00 2005 2006 2007 2008 2009 Product A Avg Price Unit 1000 950 903 857 Product B Avg Price Unit 1225 1164 1106 10 50 Product C Avg PricesUnit 1545 Product A Sale Quantity 000s sooo 5100 5202 53 06 Product B Sale Quantity 000s 3500 3570 3641 3714 Product C Sale Quantity 000s 2000 2040 2081 2122 Total Revenues Direct Cost of Goods Sold Gross Profit Operating Expenses 157 50 16065 16386 167 14 Sales General and Admin Costs Operating Income EBITDA Depreciation 1000 10 00 10 00 10 00 Amortization 300 300 300 300 EBIT Interest Payments 200 200 200 200 EBT Taxes 343
60. ator Example Models 1 Starting Models a New Simulation Profile To start a new simulation you must first create a simulation profile A simulation profile contains a complete set of instructions on how you would like to run a simulation it contains all the assumptions forecasts simulation run preferences and so forth Having profiles facilitates creating multiple scenarios of simulations that is using the same exact model several profiles can be created each with its own specific simulation assumptions forecasts properties and requirements The same analyst can create different test scenarios using different distributional assumptions and inputs or multiple users can test their own assumptions and inputs on the same model Instead of having to make duplicates of the model the same model can be used and different simulations can be run through this model profiling process Multiple Fit Statistical Basic Markov Stochastic Analysis Econometrics Chain Processes ue Obtohution j P Edit Paste esigner H omado Profilo Run ARIMA Multiple Run Data hon Analysis Simulation l GARCH Regression Optimization Inport l A TL ix p BA k qucuig 4 ria ala akiita e eia 9 ele 3 fhem Cubic Maximum x eia Diagnostic x Single Fit Scenarios Fa Set Ly aramas puto Pine Likelihood Forecasting Tool Distribution Sensitivity Profile Forecast ARIMA n Set Objective ae Set Input E Curves Nonlinear Decision Variabl
61. binatorial Nested Options 781 196 Real Options Sequential Compound Options 783 197 Real Options Simultaneous Compound Options 791 198 Real Options Simple Calls and Puts Using Trinomial Lattices 795 PART 3 Real Options Strategic Case Studies Framing the Options 799 199 Real Options Strategic Cases High Tech Manufacturing Build or Buy Decision with Real Options 801 200 Real Options Strategic Cases Oil and Gas Farm Outs Options to Defer and Value of Information 810 xiv CONTENTS 201 Real Options Strategic Cases Pharmaceutical Development Value of Perfect Information and Optimal Trigger Values 814 202 Real Options Strategic Cases Option to Switch Inputs 817 203 Valuation Convertible Warrants with a Vesting Period and Put Protection 821 APPENDIX A List of Models 827 APPENDIX B List of Functions 837 APPENDIX C Understanding and Choosing the Right Probability Distributions 899 APPENDIX D Financial Statement Analysis 919 APPENDIX E Exotic Options Formulae 927 APPENDIX F Measures of Risk 941 APPENDIX G Mathematical Structures of Stochastic Processes 957 Glossary of Input Variables and Parameters in the Modeling Toolkit Software 963 About the DVD 995 About the Author 999 Index 1001 Preface dvanced Analytical Models is a large collection of advanced models with a multi tude of industry and domain applications The book is based on years of academic research and practical consulting experience coupled
62. c model where no simulations are run In other words all the inputs in the model are static and unchanging This optimization type is applicable when the model is assumed to be known and no uncertainties exist Also a static optimization can be first run to determine the optimal portfolio and its corresponding optimal allocation of decision variables before more advanced optimization procedures are applied For instance before running a stochastic optimization problem a static optimization is first run to determine if there exist solutions to the optimization problem before a more protracted analysis is performed Next dynamic optimization is applied when Monte Carlo simulation is used together with optimization Another name for such a procedure is simulation optimization That is a simulation is first run the results of the simulation are then applied in the Excel model and then an optimization is applied to the simulated values In other words a simulation is run for N trials and then an optimization process is run for M iterations until the optimal results are obtained or an infeasible set is found That is using Risk Simulator s optimization module you can choose which forecast and assumption statistics to use and replace in the model after the simulation is run Then these forecast statistics can be applied in the optimization process This approach is useful when you have a large model with many interacting assumptions and forecasts
63. can be run using this existing model Modeling Toolkit and Risk Simulator Applications 71 A B C D E F G H J 1 2 Credit Line ENPV Cost Risk Risk bero Pro R ity Selection 4 Project 1 54 96 8 33 1 26 5 Project 2 1 914 92 1 02 327 6 Project 3 1 551 03 1 03 1 87 7 Project 4 1 012 95 222 2 37 8 Project 5 925 41 0 92 2 85 9 Project 6 560 92 1 35 15 58 10 Project 7 5 633 10 0 51 475 11 Project 8 926 25 1 33 11 74 12 Project 9 2 100 60 0 93 16 56 13 Project 10 1 912 50 1 18 591 14 Project 11 263 52 2 08 13 20 15 Project 12 309 75 1 69 6 00 16 17 Total 17 218 00 8197 44 7 007 40 70 18 Goal MAX lt 5000 19 Sharpe Ratio 2 4573 20 21 ENPV is the expected NPV of each credit line or project while Cost can be the total cost of 22 administration as well as required capital holdings to cover the credit line and Risk is the 23 Coefficient of Variation of the credit line s ENPV FIGURE 1 57 Discrete integer optimization model The example model had been preset and is ready to run Risk Simulator Change Profile and select the optimization profile and then click on Risk Simulator Opti mization Run Optimization or follow the procedure below to recreate the opti mization model form scratch PROCEDURE Open the example file and start a new profile by clicking on Risk Simulator New Profile and provide it a name The first step in optimization is to set up the decision variables Set the first de cision
64. ck on the set forecast icon on the Risk Simulator icon toolbar Enter the relevant information and click OK Figure I 7 illustrates the set forecast properties which include Forecast Name Specify the name of the forecast cell This is important because when you have a large model with multiple forecast cells naming the forecast cells individually allows you to access the right results quickly Do not under estimate the importance of this simple step Good modeling practice is to use short but precise assumption names Forecast Precision Instead of relying on a guesstimate of how many trials to run in your simulation you can set up precision and error controls When an error precision combination has been achieved in the simulation the simulation will pause and inform you of the precision achieved Thus the number of simulation trials is an automated process you do not have to guess the required number Modeling Toolkit and Risk Simulator Applications 18 Specify the name of the forecast ill Forecast Properties Forecast Name Income Click here to link the name of the forecast to Forecast Precision a cell Precision Level Confidence Optional Specify the precision confidence and error levels or Error Level i of Mean value of the Mean Options Specify if you want this V Show Forecast Window forecast to be visible turned on by default Coc emen FIGURE 1 7 Set output forecast of tr
65. d Mean Noles lt gt denotes greater than tor ngnt4ai less tran for ient fail or not equal to for two tail hypothesis tests Hypothesis Testing Summary The one vanable t test is appropriate when the population standard deviation is not known but the sampling dstribution is assumed to be approximately normal the t test is used when the sample size is less than 30 butis also appropriate and in fact promdes more conservative results with larger data sets This t test can be appliedto three types ot hypothesis tests a two tailed test a nght tailed test and a left tailed test All three tests and their respective results are listed below for your re erence Iwo lailed Hypothesis lest A two tai ed hypothesis tests tne nul hypothesis Ho such that the population mean is statisbcally identical to the hypothesized mean The alternative hypothesis is that the real population mean is statistically diferent from the hypothesized mean when tested using the sample dataset Using at test if the computed p value s less than a specified significance amount typically 0 19 0 05 or 0 0 this means that the population mean is statistically significantly different than the hypothesized mean at 10 5 and 1 significance value or at the 90 95 and 99 statistical confidence Conversely ifthe p valuc is higher than 0 10 0 05 or 0 01 the population mean is statistically identical to the hypotnesized mean and any differences are due to
66. d by default is to choose all the tests Click OK when finished Figure I 45 Spend some time going through the reports generated to get a better understand ing of the statistical tests performed sample reports are shown in Figures I 46 to 1 49 Modeling Toolkit and Risk Simulator Applications 53 Data Set fk Statistical Analysis Variable X1 Variable X2 Variable X3 18308 185 1148 600 18068 372 7729 100484 16728 14630 4008 38927 22322 3711 3136 197 Datais from a single variable Data comprises multiple variables in columns FIGURE 1 44 Running the statistical analysis tool Ik Statistical Analyses Select the analyses to run I Descriptive Statistics v Stochastic Process Parameter Estimation IV Distributional Fitting Periodicity Annual x Continuous Discrete v Time series Autocorrelation V Histogram and Charts V Hypothesis Testing IV Time series Forecasting Seasonality Periods Cycle Hypothesized Mean 0 ren sis Forecast Periods I Nonlinear Extrapolation Forecast Periods v Trend Line Projection v Normality Test Forecast Periods tees FIGURE 1 48 Statistical tests 54 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Descriptive Statistics Analysis of Statistics Almost all distnbuhons can be descnbed within 4 moments some dstribubons require one moment while otners require two moments and so forth Descnptve statistics quantitatively capture these momen
67. d estimation MLE is used to forecast the probability of something occurring given some independent variables For instance MLE is used to predict if a credit line or debt will default given the obligor s characteristics 30 years old single salary of 100 000 per year and has a total credit card debt of 10 000 or the probability a patient will have lung cancer if the person is a male between the ages of 50 and 60 smokes five packs of cigarettes per month and so forth See Chapter 118 Probability of Default Empirical Model for details on running this MLE model The data set are typically cross sectional and the dependent variable has to be binary with values of 0 or 1 Multivariate Regression a Multivariate regression is used to model the relationship structure and char acteristics of a certain dependent variable as it depends on other independent exogenous variables Using the modeled relationship we can forecast the future values of the dependent variable The accuracy and goodness of fit for this model can also be determined Linear and nonlinear models can be fitted in regression analysis See Chapter 87 Forecasting Multiple Regression for details on running regression models This methodology can be used to model and forecast time series data cross sectional data or mixed data Nonlinear Extrapolation a The underlying structure of the data to be forecasted is assumed to be nonlinear over time
68. distributions at the limit are shown to approach normality Requirements Modeling Toolkit Risk Simulator This example shows how the Central Limit Theorem works by using Risk Simu lator and without the applications of any mathematical derivations Specifically we look at how the normal distribution sometimes can be used to approximate other distributions and how some distributions can be made to be highly flexible as in the case of the beta distribution The Central Limit Theorem contains a set of weak convergence results in proba bility theory Intuitively they all express the fact that any sum of many independent and identically distributed random variables will tend to be distributed according to a particular attractor distribution The most important and famous result is called the Central Limit Theorem which states that if the sum of the variables has a finite variance then it will be approximately normally distributed As many real pro cesses yield distributions with finite variance this theorem explains the ubiquity of the normal distribution Also the distribution of an average tends to be normal even when the distribution from which the average is computed is decidedly not normal DISCRETE UNIFORM DISTRIBUTION In this model we look at various distributions and see that over a large sample size and various parameters they approach normality We start off with a highly unlikely candidate the discrete uniform dist
69. e Book C View simulation profiles in all workbooks FIGURE 1 4 Change active simulation Specify random number sequence By definition a simulation yields slightly different results every time it is run by virtue of the random number generation routine in Monte Carlo simulation This is a theoretical fact in all random number generators However when making presentations sometimes you may require the same results For example during a live presentation you may like to show the same results that are in some pregenerated printed reports from a previous simulation run when you are sharing models with others you also may want the same results to be obtained every time If that is the case check this preference and enter in an initial seed number The seed number can be any positive integer Using the same initial seed value the same number of trials and the same input assumptions always will yield the same sequence of random numbers guaranteeing the same final set of results Note that once a new simulation profile has been created you can come back later and modify your selections In order to do this make sure that the current active profile is the profile you wish to modify otherwise click on Risk Simulator Change Simulation Profile select the profile you wish to change and click OK Figure I 4 shows an example where there are multiple profiles and how to activate duplicate or delete a selected profile Then
70. e of any distributional requirements i e an assumption with a Weibull distribution can be compared to another with a beta distribution The results from this chart are fairly similar to that of the tornado analysis seen previously of course without the capital investment value which we decided was a known value and hence was not simulated with one special exception Tax rate was relegated to a much lower position in the sensitivity analysis chart Figure I 29 as compared to the tornado chart Figure I 24 This is because by itself tax rate will have a significant impact Once the other variables are interacting in the model however it appears that tax rate has less of a dominant effect This is because 32 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Nonlinear Rank Correlation Net Present Value CHEEEEEEEEEEEENNNN o 0 Quanti O E 0 51 A cuantty EEENNNNNENENO 0 35 C Quantity EEE 0 25 4 Price EEE 0 31 6 Price cu 0 22 C Price 0 17 Tax Rate 0 05 Price Erosion 905 0 03 Sales Growth 0 0 0 1 0 2 0 3 04 0 5 0 6 FIGURE 1 29 Rank correlation chart tax rate has a smaller distribution as historical tax rates tend not to fluctuate too much and also because tax rate is a straight percentage value of the income before taxes whereas other precedent variables have a larger effect on NPV This example proves that it is important to perform sensitivity analysis after a simulation run to ascertain if
71. e sure that the objective cell is either the Sharpe ratio or portfolio returns to risk ratio and select Maximize You can now review the decision variables and constraints if required or click OK to run the static optimization Figure I 58 shows the screen shots of these procedural steps You can add simu lation assumptions on the model s ENPV and risk columns C and D and apply the dynamic optimization and stochastic optimization for additional practice Results Interpretation Figure L 59 shows a sample optimal selection of projects that maximizes the Sharpe ratio In contrast one can always maximize total rev enues but as before this is a trivial process and simply involves choosing the highest returning project and going down the list until you run out of money or exceed the budget constraint Doing so will yield theoretically undesirable projects as the highest yielding projects typically hold higher risks Now if desired you can replicate the optimization using a stochastic or dynamic optimization by adding in assump tions in the ENPV and Risk values For additional hands on examples of optimization in action see the various chapters on optimization models FORECASTING Forecasting is the act of predicting the future whether it is based on historical data or on speculation about the future when no history exists When historical data exist a quantitative or statistical approach is best but if no historical data exist then pot
72. e them each time a new simulation scenario is required Number of trials Enter the number of simulation trials required Running 1 000 trials means that 1 000 different iterations of outcomes based on the input assumptions will be generated You can change this number as desired but the input has to be positive integers The default number of runs is 1 000 trials Pause on simulation error If checked the simulation stops every time an error is encountered in the Excel model that is if your model encounters a compu tational error e g some input values generated in a simulation trial may yield a divide by zero error in a spreadsheet cell the simulation stops This feature is important to help audit your model to make sure there are no computational errors in your Excel model However if you are sure the model works there is no need for you to check this preference Turn on correlations If checked correlations between paired input assumptions will be computed Otherwise correlations will all be set to zero and a simula tion is run assuming no cross correlations between input assumptions Applying correlations will yield more accurate results if correlations do indeed exist and will tend to yield a lower forecast confidence if negative correlations exist Modeling Toolkit and Risk Simulator Applications 9 IR Change Active Simulation Joga Simulation Name Workbook Second Simulation Example Book Third Simulation Exampl
73. e this option on several forecast charts and drag them on top of one another to visually see the similarities or differences Histogram Resolution allows you to change the number of bins of the histogram anywhere from 5 bins to 100 bins Also the Update Data Interval section allows you to control how fast the simulation runs versus how often the forecast chart is updated That is if you wish to see the forecast chart updated at almost every trial this will slow down the simulation as more memory is being allocated to updating Data Filter Q9 Show all data Show only data between limit 4nfnty and Infinity Show only data deviates less than s std deviation Statistic Precision level that used to calculate the error 95 2 Show the following statistic on histogram Mean 1st Quartile Median 4th Quartile FIGURE 1 11 Forecast chart options 16 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS the chart versus running the simulation This is merely a user preference and in no way changes the results of the simulation just the speed of completing the simulation The Copy Chart button allows you to copy the active forecast chart for pasting into other software applications e g PowerPoint or Word and the Close All and Minimize All buttons allow you to control all opened forecast charts at once Options This forecast chart option Figure I 11 allows you to show all the forecast data or to filter in or out
74. ed from the same group of patients before an experimental drug was used and after the drug was applied etc DATA EXTRACTION SAVING SIMULATION RESULTS AND GENERATING REPORTS Raw data of a simulation can be extracted very easily using Risk Simulator s Data Extraction routine Both assumptions and forecasts can be extracted but a simula tion must be run first The extracted data can then be used for a variety of other analyses and the data can be extracted to different formats for use in spreadsheets databases and other software products PROCEDURE 1 Open or create a model define assumptions and forecasts and run the simula tion 2 Select Risk Simulator Tools Data Extraction 3 Select the assumptions and or forecasts you wish to extract the data from and click OK The simulated data can be extracted to an Excel worksheet a flat text file for easy import into other software applications or as risksim files which can be reopened as Risk Simulator forecast charts at a later date Finally you can create a simulation report of all the assumptions and forecasts in the model by going to Risk Simulator Create Report A sample report is shown in Figure L37 REGRESSION AND FORECASTING DIAGNOSTIC TOOL This advanced analytical tool in Risk Simulator is used to determine the econometric properties of your data The diagnostics include checking the data for heteroskedas ticity nonlinearity outliers specification error
75. en the slope and intercept estimates and the fitted values from the linear regression will be biased and the fitted slope and intercept estimates will not be meaningful Over a restricted range of independent or dependent variables nonlinear models may be Modeling Toolkit and Risk Simulator Applications 47 well approximated by linear models this is in fact the basis of linear interpolation but for accurate prediction a model appropriate to the data should be selected A nonlinear transformation should first be applied to the data before running a regression One simple approach is to take the natural logarithm of the independent variable other approaches include taking the square root or raising the independent variable to the second or third power and run a regression or forecast using the nonlinearly transformed data Another typical issue when forecasting time series data is whether the independent variable values are truly independent of each other or are dependent Dependent variable values collected over a time series may be autocorrelated For serially correlated dependent variable values the estimates of the slope and intercept will be unbiased but the estimates of their forecast and variances will not be reliable and hence the validity of certain statistical goodness of fit tests will be flawed For in stance interest rates inflation rates sales revenues and many other time series data are typically autocorrelated where the
76. entially a qualitative or judgmental approach is usually the only recourse Different Types of Forecasting Techniques Generally forecasting can be divided into quantitative and qualitative techniques Qualitative forecasting is used when little to no reliable historical contemporaneous or comparable data exists Several qualitative methods exist such as the Delphi or expert opinion approach a consensus building forecast by field experts marketing experts or internal staff members management assumptions target growth rates set by senior management as well as market research or external data or polling and surveys data obtained through third party sources industry and sector indexes or from active market research These estimates can be either single point estimates an average consensus or a set of forecast values a distribution of forecasts The latter can be entered into Risk Simulator as a custom distribution and the resulting forecasts can be simulated That is a nonparametric simulation can be run using the estimated data points themselves as the distribution Modeling Toolkit and Risk Simulator Applications 78 Decision Variable Properties Decision Name zn EI Decision Type Continuous e g 1 15 2 35 10 55 Binary 0 or 1 Constraints Current Constraints v SD517 lt 5000 v SJ 17 6 R Optimization Summary Optimization is used to allocate resources where the results provide the max re
77. ep T3 HYD zz vyny o3ny EP E ETE poday 23821 lt gt anpefqo 125 C sisAjeuy uoisse16ad uley gt Aoweyy 2 aurds orqn5 7 VIIa E 1 4l Fl x La LU Ba 203eInuis 35r MIA MMY eg sejnuJo3 woke sbeq esur 3uoH 6 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS on the left panel User Accounts Turn User Account Control On or Off and uncheck the option Use User Account Control UAC and restart the computer When restarting the computer you will get a message that UAC is turned off You can turn this message off by going to the Control Panel Security Center Change the Way Security Center Alerts Me Don t Notify Me and Don t Display the Icon The sections that follow provide step by step instructions for using the software As the software is continually updated and improved the examples in this book might be slightly different from the latest version downloaded from the Internet RUNNING A MONTE CARLO SIMULATION Typically to run a simulation in your existing Excel model you must perform these five steps Start a new or open an existing simulation profile Define input assumptions in the relevant cells Define output forecasts in the relevant cells Run the simulation Interpret the results nab wWN If desired and for practice open the example file called Basic Simulation Model and follow along the examples on creating a simulation The example file can be found on the menu at Risk Simul
78. es Simulation Assumption Extrapolation Constraints FIGURE 1 2 Risk Simulator icon toolbar Continued ponunuo JANIH 103enuiis Asra ETITTTUETTT Jojejnuis ysy Ul SjJeqioo uoJ w yp enuen 19sn pue s y uoue day sjopour ojduexa Soor TeoniAeuy S31H d HH ONY S3 1dWVX sapoy 81001 TWOILATVNV 7HVE1001 CHIHL uoor diaH 5 00 Je213jeuv nuew uo jenuew 4S apojw sishieuy sisAjeuy sisijeuy Bus sucieje105 Adun sjbuis saudjseq sisAjeuv 100 ucdupuado uopenxi godes 10je ntuls pen 4850 djaH dwexg opeuio E25He s OUEUAIS A sisayzodAy ypa Gus Sunya uonnquijsig uonnaujsig 29sou amp eig eeg eg 318215 35 n m TT oy UM D M ry y ry s S Um m z ES E T s M x c7 0 1o03e nuis sri aado a ag MIA malay 2q sejnuio 4 3no e 2664 pesuy suoH m PIJ HOSOI 6 F j L Joje nuulS ysty 9 Ui sjeqiooj Sjurensuo2 c U02 3u9Jayip yes pue s jqeea uoisi93p E Buowe jes eAn2efqo Jas uonezuydo sonAjeuy pue sj pow Dunse29J0 4 NOILVZIWILdO ANY 9NILSVO3HO V81001 GNOOAS v s9je103 uny sisApeue uomeziundo E z T M n S u D d o N W 3 f I H 9 d 3 a 2 8 v I F o OEH uex uoneziundo Bunsea103 uny uoneinuis Sumpa I s3se33303 suondumssy ao nuaw uo 3urensuo uoist pez si euy s ss zod siseuy uonejodezpg pooujaxr ureu saung ls suwu vi iv 35522104 uondumssy epjoid ejjod 10jejnuns V N PS S RS ung seuss2ui 2nseupojs uoissajba
79. estrictions of each asset fluctuating between 5 and 35 and where the sum of the allocation must equal 100 the allocation that maximizes the return to risk ratio is seen in Figure 1 56 A few important points have to be noted when reviewing the results and opti mization procedures performed thus far The correct way to run the optimization is to maximize the bang for the buck or returns to risk Sharpe ratio as we have done If instead we maximized the total portfolio returns the optimal allocation result is trivial and does not require optimization to obtain That is simply allocate 5 the minimum allowed to the lowest eight assets 35 the maximum allowed to the highest returning asset and the remaining 25 to the second best returns asset Optimization is not required However when allocating the MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS DecionNane sacs O Decision Type Continuous e q 1 15 235 10 55 Lower Bound Upper Bound Integer e 9 1 2 3 Lower Bound Upper Bound i Binary 0 or 1 Constraint Cell Optimization is used to allocate resources where the results provide the max returns or the min cost risks Uses include managing inventories i portfolio allocation product mix project selection etc Static Optimization Run on static model without simulations Usually run to determine the intial optimal portfolio before more advanced optimiza
80. et and select the data including the variable names cells C5 H55 Click on Risk Simulator Tools Diagnostic Tool Check the data and select the Dependent Variable Y from the drop down menu Click OK when finished Figure 1 38 A common violation in forecasting and regression analysis is heteroskedasticity that is the variance of the errors increases over time see Figure I 39 for test results us ing the diagnostic tool Visually the width of the vertical data fluctuations increases or fans out over time and typically the coefficient of determination R squared coefficient drops significantly when heteroskedasticity exists If the variance of the dependent variable is not constant then the error s variance will not be constant Un less the heteroskedasticity of the dependent variable is pronounced its effect will not be severe the least squares estimates will still be unbiased and the estimates of the slope and intercept will either be normally distributed if the errors are normally dis tributed or at least normally distributed asymptotically as the number of data points becomes large if the errors are not normally distributed The estimate for the variance of the slope and overall variance will be inaccurate but the inaccuracy is not likely to be substantial if the independent variable values are symmetric about their mean yureourpuou pue Arso1oumnuoorui amp 3ronsepoxso1o1eu sjerpno jo sa uro SINSA gg IBN
81. g recording scanning or otherwise except as permitted under Section 107 or 108 of the 1976 United States Copyright Act without either the prior written permission of the Publisher or authorization through payment of the appropriate per copy fee to the Copyright Clearance Center Inc 222 Rosewood Drive Danvers MA 01923 978 750 8400 fax 978 646 8600 or on the Web at www copyright com Requests to the Publisher for permission should be addressed to the Permissions Department John Wiley amp Sons Inc 111 River Street Hoboken NJ 07030 201 748 6011 fax 201 748 6008 or online at http www wiley com go permissions Limit of Liability Disclaimer of Warranty While the publisher and author have used their best efforts in preparing this book they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose No warranty may be created or extended by sales representatives or written sales materials The advice and strategies contained herein may not be suitable for your situation You should consult with a professional where appropriate Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages including but not limited to special incidental consequential or other damages Designations used by companies to distinguish their products a
82. he same signs as well as the same relative strengths Effects of Correlations in Monte Carlo Simulation Although the computations required to correlate variables in a simulation are com plex the resulting effects are fairly clear Figure I 16 shows a simple correlation model Correlation Risk Effects Model in the example folder The calculation for revenue is simply price multiplied by quantity The same model is replicated for no correlations positive correlation 0 9 and negative correlation 0 9 between price and quantity The resulting statistics are shown in Figure I 17 Notice that the standard deviation of the model without correlations is 0 1450 compared to 0 1886 for the positive correlation model and 0 0717 for the negative correlation model That is for simple models with positive relationships e g additions and multiplications negative correlations tend to reduce the average spread of the distribution and create a tighter and more concentrated forecast distribution as compared to positive correlations with larger average spreads However the mean remains relatively stable This implies that correlations do little to change the expected value of projects but can reduce or increase a project s risk Recall in financial theory that Correlation Model Without Positive Negative Correlation Correlation Correlation Price 2 00 2 00 2 00 Quantity 1 00 1 00 1 00 Revenue 2 00 2 00 2 00 FIGURE 1 16 Simple correlation m
83. ials to simulate Review the section on error and precision control for more specific details Show Forecast Window This property allows you to show or not show a par ticular forecast window The default is to always show a forecast chart 4 Run Simulation If everything looks right simply click on Risk Simulator Run Simulation or click on the Run icon on the Risk Simulator toolbar and the simulation will proceed You may also reset a simulation after it has run to rerun it Risk Simulator Reset Simulation or the Reset icon on the toolbar or to pause it during a run Also the step function Risk Simulator Step Simulation or the Step icon on the toolbar allows you to simulate a single trial one at a time which is useful for educating others on simulation i e you can show that at each trial all the values in the assumption cells are being replaced and the entire model is recalculated each time 5 Interpreting the Forecast Results The final step in Monte Carlo simulation is to interpret the resulting forecast charts Figures I 8 to I 15 show the forecast chart and the statistics generated after running the simulation Typically these sections on the forecast window are important in interpreting the results of a simulation Forecast Chart The forecast chart shown in Figure I 8 is a probability histogram that shows the frequency counts of values occurring and the total number of trials simulated The vertical bars show the
84. id centering across zero makes the interpretation simpler A high posilive Kurtosis indicates a peaked distribution around its center and leptokurtic or fat tails This indicates a higher probability of extreme events e g catastrophic events terronst attacks stock market crashes than is predicted in a normal distribution Summary Statistics Statisucs Vorfable X1 Observatons 50 0000 Standard Deviation Sampie 472 9140 Ari hmetic Mean 331 9200 Standard Deviation Fopuiation 171 1761 Geometric Mean 267 3247 Lower Confidence interval for Standard Deviation 148 6096 Trimmed Mean 325 1739 Upper Confidence interval for Standard Deviaton 207 7947 Senderd Error of Arithmenc Meer 244537 Verance Sampie 29893 2266 Lower Confidence interval for Mean 283 0425 Varanca Popuiation 29301 2736 Upper Confidence In ervai for Mean 360 8275 Coefficient of Variability 0 5216 Media 307 0009 First Quartile Q1 204 0000 Mode 47 0000 Third Quart ie Q3 441 6606 Minimum 764 0009 Inter Quartis Range 237 0006 Maximum 717 0000 Skewnoss 0 4838 Range Kurtosis 0 0952 FIGURE 1 46 Sample statistical analysis tool report DISTRIBUTIONAL ANALYSIS TOOL This is a statistical probability tool in Risk Simulator that is rather useful in a variety of settings and can be used to compute the probability density function PDF which is also called the probability mass function PMF for discrete distributions we will use these terms interchangeably where given some d
85. idence interval relatively easy However when a statistic s sampling distribution Modeling Toolkit and Risk Simulator Applications 37 Nonparametric Bootstrap t3 Nonparametric bootstrap simulation is a distibution free A technique used to estimate the reliability or accuracy of gt A MODEL A MODEL B forecast statistics fie to compute the forecast LA a intervals of each of the statistics Bos nep Revenue 200 00 Revenue 200 00 Piese selecta forecastto nn the nonrparametic bootstrap Cost 100 00 Cost 100 00 rs n i m Ens Income imulabon Income 100 00 Income 100 00 mr ModelB s in Model 610 Income Model A Risk Simulator Forecast Distribution 8 Histogram Statistics Preferences Options Income Model A Histogram Cumulative Probability 5000 Trials 500 400 i Statistics to Bootstrap g AD I li 7 Mean Average Deviation V Skewness e 20 Income Model B Risk Simulator Forecast Distribution C Median Maximum 7 Kurtosis ke v Standard Deviation C Minimum 125 Percentile Histogram Statistics Preferences 0j ds E es pone O Variance o Range D 75 Percentile Income Model B Histogram Cumulative Probab Number of Bootstrap Trials 1000 Y Cancel we roa e BI 10372 10872 11372 Type TwoTed v i ininay Infrity Cetany x 10000 FIGURE 1 84 Nonparametric bootstrap simulation is not normally distributed or easily found these cla
86. in put variable in the model that is each variable is perturbed individually and the resulting effects are tabulated This makes tornado analysis a key component to execute before running a simulation Capturing and identifying the most important impact drivers in the model is one of the very first steps in risk analysis The next step 26 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Tornado and Spider Charts Statistical Summa One of the powerful simulation tools is the tornado chart it captures the static impacts of each variable on the outcome of the model Thatis the tool automatically perturbs each precedent variable in the model a user specified preset amount captures the fluctuation on the model s forecast or final result and lists the resulting perturbations ranked from the most significant to the least Precedents are all the input and intermediate variables that affect the outcome of the model For instance if the model consists of Az B C where C D E then B D and E are the precedents for A C is not a precedent as it is only an intermediate calculated value The range and number of values perturbed is user specified and can be set to test extreme values rather than smaller perturbations around the expected values In certain circumstances extreme values may have a larger smaller or unbalanced impact e 9 nonlinearities may occur where increasing or decreasing economies of scale and scope creep occurs for larger o
87. ing Markov Chains and Market Share Forecasting Multiple Regression Forecasting Nonlinear Extrapolation and Forecasting Forecasting Stochastic Processes Brownian Motion Forecast Distribution at Horizon Jump Diffusion and Mean Reversion Forecasting Time Series ARIMA Forecasting Time Series Analysis Industry Applications Biotech Manufacturing Strategy Industry Applications Biotech Inlicensing Drug Deal Structuring Industry Applications Biotech Investment Valuation Industry Application Banking Integrated Risk Management Probability of Default Economic Capital Value at Risk and Optimal Bank Portfolios Industry Application Electric Utility Optimal Power Contract Portfolios Industry Application IT Information Security Intrusion Risk Management Industry Applications Insurance ALM Model Operational Risk Queuing Models at Bank Branches Optimization Continuous Portfolio Allocation Optimization Discrete Project Selection Optimization Inventory Optimization Optimization Investment Portfolio Allocation Optimization Investment Capital Allocation I Basic Model Optimization Investment Capital Allocation II Advanced Model Optimization Military Portfolio and Efficient Frontier Optimization Optimal Pricing with Elasticity Optimization Optimization of a Harvest Model Optimization Optimizing Ordinary Least Squares Optimization Stochastic Portfolio Allocation Options Analysis
88. ions Barrier Options Exotic Options Binary Digital Options Exotic Options Cash or Nothing Options Exotic Options Chooser Option Simple Chooser Exotic Options Chooser Option Complex Chooser Exotic Options Commodity Options Exotic Options Currency Foreign Exchange Options 125 127 129 131 133 135 137 138 141 145 147 149 151 153 158 169 170 172 174 176 178 180 182 184 186 188 189 190 191 193 195 196 197 198 199 Contents ix 45 Exotic Options Double Barrier Options 200 46 Exotic Options European Call Option with Dividends 201 47 Exotic Options Exchange Assets Option 203 48 Exotic Options Extreme Spreads Option 204 49 Exotic Options Foreign Equity Linked Foreign Exchange Options in Domestic Currency 205 50 Exotic Options Foreign Equity Struck in Domestic Currency 207 51 Exotic Options Foreign Equity with Fixed Exchange Rate 208 52 Exotic Options Foreign Takeover Options 209 53 Exotic Options Forward Start Options 210 54 Exotic Options Futures and Forward Options 211 55 Exotic Options Gap Options 212 56 Exotic Options Graduated Barrier Options 213 57 Exotic Options Index Options 214 58 Exotic Options Inverse Gamma Out of the Money Options 215 59 Exotic Options Jump Diffusion Options 216 60 Exotic Options Leptokurtic and Skewed Options 217 61 Exotic Options Lookback with Fixed Strike Partial Time 21
89. iple projects also can be structured as decision variables Constraints describe relationships among decision variables that restrict the val ues of the decision variables For example a constraint might ensure that the total amount of money allocated among various investments cannot exceed a specified TABLE 1 1 Traveling Financial Planner Seattle to Chicago 325 Chicago to Seattle 225 New York to Seattle 350 Seattle to New York 375 Chicago to New York 325 New York to Chicago 325 62 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS TABLE 1 2 Multiple Combinations of the Traveling Financial Planner Problem Seattle Chicago New York 325 325 650 Seattle New York Chicago 375 325 700 Chicago Seattle New York 225 375 600 Chicago New York Seattle 325 350 675 New York Seattle Chicago 350 325 675 New York Chicago Seattle 325 225 550 Three cities means 3 3 x 2 x 1 6 itinerary permutations Five cities means 5 5x 4 x 3 x 2 x 1 120 permutations One hundred cities means 100 100 x 99 x x 1 9 3 x 10 permutations amount or at most one project from a certain group can be selected budget con straints timing restrictions minimum returns or risk tolerance levels Objectives give a mathematical representation of the model s desired outcome such as maximizing profit or minimizing cost in terms of the decision variables In financial analysis for e
90. irst approach is to manually compute the correlation coefficient r of a pair of variables x and y using nas ny Xy 9 y nXx Ex nY X xy Modeling Toolkit and Risk Simulator Applications 19 The second approach is to use Excel s CORREL function For instance if the 10 data points for x and y are listed in cells A1 B10 then the Excel function to use is CORREL A1 A10 B1 B10 The third approach is to run Risk Simulator s Multi Variable Distribu tional Fitting Tool and the resulting correlation matrix will be computed and displayed It is important to note that correlation does not imply causation Two completely unrelated random variables might display some correlation but this does not imply any causation between the two e g sunspot activity and events in the stock market are correlated but there is no causation between the two There are two general types of correlations parametric and nonparametric cor relations Pearson s correlation coefficient is the most common correlation measure and usually is referred to simply as the correlation coefficient However Pearson s correlation is a parametric measure which means that it requires both correlated variables to have an underlying normal distribution and that the relationship be tween the variables is linear When these conditions are violated which is often the case in Monte Carlo simulations the nonparametric counterparts become more important Spearman s
91. istribution Figure 1 4 In fact if you threw 12 dice together and added up their values and repeated the process many times you get an extremely close discrete normal distribution If 12 3 4 5 6 123 4 5 6 7 208046 5 T B 34506067 8 9 45 6 7 8 9 10 5 6 7 8 9 10 11 6 7 8 9 40 44 42 FIGURE 1 3 Summation of two dice 1 Analytics Central Limit Theorem 81 Histogram Frequency uu Rz cn ba 2 3 4 5 B Y 8 9 10 11 12 Mare FIGURE 1 4 Approximation to a normal distribution you add 12 continuous uniform distributions where the results can say take on any continuous value between 1 and 6 you obtain a perfectly normal distribution POISSON BINOMIAL AND HYPERGEOMETRIC DISTRIBUTIONS Continuing with the examples we show that for higher values of the distributional parameters where many trials exist these three distributions also tend to nor mality For instance in the Other Discrete worksheet in the model notice that as the number of trials N in a binomial distribution increases the distribution tends to normal Even with a small probability P value as the number of tri als N increases normality again reigns Figure 1 5 In fact as N x P exceeds about 30 you can use the normal distribution to approximate the binomial distri bution Also this is important as at high N values it is very difficult to compute the exact binomial distribution value and the normal distribution is a lot easier to use We can
92. istribution and its parameters we can determine the probability of occurrence given some outcome x In addition the cumulative distribution function CDF can also be computed which is the sum of the PDF values up to and including this x value Finally the inverse cumulative distribution function ICDF is used to compute the value x given the probability of occurrence This tool is accessible via Risk Simulator Tools Distributional Analysis As an example Figure I 50 shows the computation of a binomial distribution i e a distribution with two outcomes such as the tossing of a coin where the outcome is either heads or tails with some prescribed probability of heads and tails Suppose we toss a coin two times and set the outcome heads as a success we use the binomial distribution with Trials 2 tossing the coin twice and Probability 0 50 the probability of success of getting heads Selecting the PDF and setting the range of Modeling Toolkit and Risk Simulator Applications 59 Hypothesis Test t Test on the Population Mean of One Variable Statistical Summary Statistics from Dataset Calculated Statistics Observations 50 t Statistic 13 5734 Sample Mean 33192 P Value right tail 0 0000 Sample Standard Deviation 172 91 P Value left ta led 1 0000 P Value two tailed 0 0000 User Provided Statisucs Nuill Hypothesis Ho 7 Hypothesized Mean rypothesized Mean 9 00 Alternate Hypothesis Ha u lt gt Hypothesize
93. ives and swaps Real Options Analysis over 100 types abandon barrier contract customized dual asset expand multi asset multi phased pentanomials quadranomials sequential switch and trinomials Risk Hedging delta and delta gamma hedges foreign exchange and interest rate risk 2 Risk Simulation correlated simulation data fitting Monte Carlo simulation a risk simulation Six Sigma capability measures control charts hypothesis tests measurement systems precision sample size Statistical Tools ANOVA Two Way ANOVA nonparametric hypotheses tests parametric tests principal components variance covariance Valuation APT buy versus lease CAPM caps and floors convertibles financial ratios valuation models Value at Risk static covariance and simulation based VaR Volatility EWMA GARCH implied volatility Log Returns Real Options Volatility probability to volatility 2 Yield Curve BIS Cox Merton NS spline Vasicek xvii xviii SOFTWARE APPLICATIONS Risk Simulator Over 25 statistical distributions covering the following applications m Applied Business Statistics descriptive statistics CDF ICDF PDF probabilities stochastic parameter calibration Bootstrap Nonparametric Simulation and Hypothesis Testing testing empirical and theoretical moments Correlated Simulations simulation copulas and Monte Carlo Data Analysis and Regression Diagnostics heteroskedasticity m
94. jesujuoN WNWIXeW AWEN S HOY D aiseg ony viNDiv s y d s uny ow y 23 54 Ado 3ndinojss indupps 3p3 MAN gt gt v M F3 1 2 01 f D ze Se ott X D EP 4l Ea L4 i T n J A man mamay eea semuuo4 jnofejsDed uesup WOH 500j je nAjeuy aly ie SMOUS Joenus ys ur S129100 uonejezsu Sjurp1jsuo jos Uo3I 95suo22r1 pue sa qerJPA uoIst23p Jos qualeyip pup sjoo pasn aAnaalqo Jas uonpziumdo iun Apuonbaiso uny siSAjeue uoneziundo s y 5 001 Je2nApeu uonezinundo uo a asu 1035005 ps By Uoneziundo be Si 0f wepu ps 2 We aibuis Sunya 5 y uoispad 3as AX e poday 23821 g gt anpalqo ps C7 uni uonenuns e Jaye japoui au S 10 dajs jeny ajDurs e und sjeug sydninur UUM UOrPInulis e umy l uny ucneinuis sonAjeuy pue sppoy Burjse22104 unse sis jeuy sau35 Wy uonelodenxa je2uiuoN 5 yasay deis ung 58552201d 215EU2015 gt poouijexr wnwixe em sishjouy uoiss2i62y 77 Vay J c JOJOINWIS 35T4 ndopa MAA epyo1d anpe Burjsixa 9UL po 10 9jjogd 9IqerieA uoisi9p 10 ssh 1829910 uondiunsse 523210J jndjno Bunsmeue s ourg M u e 10 uondunsse sway nuajy ureyy 10 a 8tq do indui Mou e jos JO PIrllS 4STH bunip3 535222104 suojjduinssy 311244 nua j e22104 uopdunssy alljold loid 203E nuils aaoway a Ado ydo ps yndy ps yp3 NaN asta x ya y x Mala ea seinu04 1nofe1 26e uasur awoH
95. ket share and so forth or failure rates cross sectional values that are time independent such as the grade point average of sophomore students across the nation in a particular year given each student s levels of SAT scores IQ and number of alcoholic beverages consumed per week or mixed panel mixture between time series and panel data such as predicting sales over the next 10 years given budgeted marketing expenses and market share projections this means that the sales data is time series but exogenous variables such as marketing expenses and market share exist to help to model the forecast predictions The Risk Simulator software provides the user several forecasting methodologies Auto ARIMA Basic Econometrics Box Jenkins ARIMA Custom Distributions J S Curves GARCH Markov Chains Maximum Likelihood Multivariate Regression Nonlinear Extrapolation Spline Curves Stochastic Process Forecasting Time Series Analysis In general to create forecasts several quick steps are required Start Excel and enter in or open your existing historical data Select the data and click on Risk Simulator and select Forecasting Modeling Toolkit and Risk Simulator Applications 75 Select the relevant forecasting application Auto ARIMA Basic Economet rics Box Jenkins ARIMA J S Curves GARCH Markov Chains Maximum Likelihood Multivariate Regression Nonlinear Extrapolation Spline Curves Stochastic Process Forecasting or
96. known mathematical law or relationship Although the resulting values are uncertain the underlying mathematical structure is known and can be simulated using Monte Carlo risk simulation See Chapter 89 Forecasting Stochastic Processes for details on stochastic process forecasting where we forecast using random walk Brownian motion mean reverting and jump diffusion processes Time Series Analysis a In well behaved time series data typical examples include sales revenues and cost structures of large corporations the values tend to have up to three el ements a base value trend and seasonality Time series analysis uses these historical data and decomposes them into these three elements and recomposes them into future forecasts In other words this forecasting method like some of the others discribed first performs a back fitting backcast of historical data before it provides estimates of future values forecast See Chapter 91 Forecasting Time Series Analysis for details on time series decomposition models This methodology is applicable only to time series data 1 Analytics Central Limit Theorem 79 1 Analytics Central Limit Theorem File Name Analytics Central Limit Theorem Location Modeling Toolkit Analytics Central Limit Theorem Brief Description Illustrating the concept of Central Limit Theorem and Law of Large Numbers using Risk Simulator s set assumptions functionality where many
97. l give you the desires of your beart Psalms 37 4 NIV Preface Contents Software Applications PART 1 Modeling Toolkit and Risk Simulator Applications N No CONN CA A W Introduction to the Modeling Toolkit Software Introduction to Risk Simulator Running a Monte Carlo Simulation Using Forecast Charts and Confidence Intervals Correlations and Precision Control Tornado and Sensitivity Tools in Simulation Sensitivity Analysis Distributional Fitting Single Variable and Multiple Variables Bootstrap Simulation Hypothesis Testing Data Extraction Saving Simulation Results and Generating Reports Regression and Forecasting Diagnostic Tool Statistical Analysis Tool Distributional Analysis Tool Portfolio Optimization Optimization with Discrete Integer Variables Forecasting Analytics Central Limit Theorem Analytics Central Limit Theorem Winning Lottery Numbers Analytics Flaw of Averages Analytics Mathematical Integration Approximation Model Analytics Projectile Motion Analytics Regression Diagnostics Analytics Ships in the Night Analytics Statistical Analysis Analytics Weighting of Ratios XV xvii vii viii CONTENTS 10 Credit Analysis Credit Default Swaps and Credit 12 Credit Analysis External Debt Ratings and Spread 14 15 16 17 18 Debt Analysis Debt Sensitivity Models 20 21 Debt Analysis Vasicek Price and Yield of Risky Debt
98. llocation In other words these rankings show at a glance which asset class has the lowest risk the highest return and so forth The portfolio s total returns in cell C17 is SUMPRODUCT C6 C15 E6 E15 that is the sum of the allocation weights multiplied by the annualized returns for each asset class As an example with a portfolio of four assets we have Rp c ARA wg Rg oc Rc opRp where Rp is the return on the portfolio RA p cp are the individual returns on the projects and c4 p c p are the respective weights or capital allocation across each project In addition the portfolio s diversified risk in cell D17 is computed by taking op 1 n m x 0202 gt Y 20 pj j0 0 Here p are the respective cross correlations be i l i 1 j l tween the asset classes hence if the cross correlations are negative there are risk diversification effects and the portfolio risk decreases However to simplify the computations here we assume zero correlations among the asset classes through this portfolio risk computation but assume the correlations when applying simulation on the returns as will be seen later Therefore instead of applying static correlations among these different asset returns we apply the correlations in the simulation as sumptions themselves creating a more dynamic relationship among the simulated return values Finally the return to risk ratio or Sharpe ratio is computed for the portfolio This value is seen
99. ls on using Risk Simulator Risk Simulator is a Monte Carlo simulation forecasting and optimization soft ware It is written in Microsoft NET C and functions with Excel as an add in This software is compatible and often used with the Real Options SLS software shown in Part II of this book also developed by the author Stand alone software applications in C are also available for implementation into other existing proprietary software or databases The different functions or modules in both software applications are briefly described next The Appendixes provide a more detailed list of all the functions tools and models The Simulation Module allows you to Run simulations in your existing Excel based models Generate and extract simulation forecasts distributions of results Perform distributional fitting automatically finding the best fitting statistical distribution m a Modeling Toolkit and Risk Simulator Applications 3 a m Compute correlations maintaining relationships among multiple simulated random variables Identify sensitivities creating tornado and sensitivity charts Test statistical hypotheses finding statistical differences and similarities be tween pairs of forecasts Run bootstrap simulation testing the robustness of result statistics Run custom and nonparametric simulations simulations using historical data without specifying any distributions or their parameters for
100. lustrates the traveling financial planner problem Suppose the traveling financial planner has to make three sales trips to New York Chicago and Seattle Further suppose that the order of arrival at each city is irrelevant All that is important in this simple example is to find the lowest total cost possible to cover all three cities Table I 1 also lists the flight costs from these different cities The problem here is cost minimization suitable for optimization One basic approach to solving this problem is through an ad hoc or brute force method That is manually list all six possible permutations as seen in Table I 2 Clearly the cheapest itinerary is going from the East Coast to the West Coast going from New York to Modeling Toolkit and Risk Simulator Applications 59 Distribution Analysis This tool generates the probability density function PDF cumulative distribution function CDF and the Inverse CDF ICDF of all the distributions in Mer Eper Risk Simulator including theoretical moments and Skevmess 0 0000 probability chart Kurtosis 0 1000 Distribution Trials Probability FIGURE 1 51 Distributional analysis tool binomial distribution with 20 trials Chicago and finally on to Seattle Here the problem is simple and can be calculated manually as there were three cities and hence six possible itineraries However add two more cities and the total number of possible itineraries jumps to 120 Performing
101. mples folder PROCEDURE Use these four steps to perform a distributional fitting model 1 Open a spreadsheet with existing data for fitting e g use the Data Fitting example file from the Risk Simulator Example Models menu 2 Select the data you wish to fit not including the variable name Data should be in a single column with multiple rows 3 Select Risk Simulator Tools Distributional Fitting Single Variable Decide if you wish to fit to continuous or discrete distributions 34 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Student s T Triangular Uniform 47 56 185 86 53 30 49 71 204 77 53 09 50 24 145 61 52 09 45 81 219 85 Distribution fitting takes existing raw data and statistically finds the best fitting distribution fie by optimizing the parameters of each distribution and performing statistical hypotheses tests Distribution Type Fit to Continuous Distributions Fit to Discrete Distributions Select Distributions to Fit ChiSquare Distribution Exponential Distribution Gamma Distribution Gumbel Maximum Distribution FIGURE 1 81 Single variable distributional fitting 4 Select the specific distributions you wish to fit to or keep the default where all distributions are selected and click OK Figure I 31 5 Review the results of the fit choose the relevant distribution you want and click OK Figure 1 32 Results Interpretation The null hypothesis Ho bei
102. n for small models and often it is not clear how to adjust the values from one iteration to the next 58 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Distribution Analysis This tool generates s probability density function PDF cumulative distribution function CDF and the Inverse CDF CDF of all the distributions in Risk Simulator including theoretical moments and probability chart Distribution Trials Probability Type Formatting Single Value Value X Range of Values Lower Bound Upper Bound FIGURE 1 50 Distributional analysis tool binomial distribution with 2 trials and a 0 5 probability of success A more rigorous method systematically enumerates all possible alternatives This approach guarantees optimal solutions if the model is correctly specified Suppose that an optimization model depends on only two decision variables If each variable has 10 possible values trying each combination requires 100 iterations 10 alter natives If each iteration is very short e g 2 seconds then the entire process could be done in approximately three minutes of computer time However instead of two decision variables consider six then consider that trying all combinations requires 1 000 000 iterations 10 alternatives It is eas ily possible for complete enumeration to take weeks months or even years to carry out The Traveling Financial Planner A very simple example is in order Table I 1 il
103. n two variables when one or both of them is nonlinear It must be stressed that a significant correlation does not imply causation As sociations between variables in no way imply that the change of one variable causes another variable to change When two variables are moving independently of each other but in a related path they may be correlated but their relationship might be spu rious e g a correlation between sunspots and the stock market might be strong but one can surmise that there is no causality and that this relationship is purely spurious STATISTICAL ANALYSIS TOOL Another very powerful tool in Risk Simulator is the statistical analysis tool which determines the statistical properties of the data The diagnostics run include checking the data for various statistical properties from basic descriptive statistics to testing for and calibrating the stochastic properties of the data PROCEDURE Open the example model Risk Simulator Example Models Statistical Analysis and go to the Data worksheet and select the data including the variable names cells C5 E55 m Click on Risk Simulator Tools Statistical Analysis Figure 1 44 Check the data type whether the data selected is from a single variable or multiple variables arranged in columns In our example we assume that the data areas selected are from multiple variables Click OK when finished Choose the statistical tests you wish to perform The suggestion an
104. nd their interactions in the model and correlations among variables are captured in the fluctuations of the results Tornado charts therefore identify which variables drive the results the most and hence are suitable for simulation sensitivity charts identify the impact to the results when multiple interacting variables are simulated together in the model This effect is clearly illustrated in Figure I 26 Notice that the ranking of critical success drivers is similar to the tornado chart in the previous examples However if correlations are added between the assumptions Figure 1 27 shows a very different picture Notice for instance price erosion had little impact on NPV but when some of the input assumptions are correlated the interaction that exists between these correlated variables makes price erosion have more impact Note that tornado analysis cannot capture these correlated dynamic relationships Only after a simulation is run will such relationships become evident in a sensitivity analysis A tornado chart s presimulation critical success factors therefore sometimes will be different from a sensitivity chart s postsimulation critical success factors The postsimulation critical success factors should be the ones that are of interest as these more readily capture the interactions of the model precedents 30 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Nonlinear Rank Correlation Net Present Value o CC NNNM 0 56 6 Quant EE
105. ness world Instead these theoretical models have been coded up into user friendly and pow erful software and this book shows the reader how to start applying advanced modeling techniques almost immediately The trial software applications allow you to access the approximately 300 model templates and 800 functions and tools understand the concepts and use embedded functions and algorithms in their own models In addition you can run risk based Monte Carlo simulations and advanced forecasting methods and perform optimization on a myriad of situa tions as well as structure and solve customized real options and financial options problems Each model template that comes in the Modeling Toolkit software is described in this book Descriptions are provided in as much detail as the applications warrant Some of the more fundamental concepts in risk analysis and real options are covered in the author s other books It is suggested that these books Modeling Toolkit Ap plying Monte Carlo Simulation Real Options Analysis Stochastic Forecasting and Portfolio Optimization 2006 and Real Options Analysis Second Edition 2005 XV Xvi PREFACE both published by John Wiley amp Sons be used as references for some of the mod els in this book Those modeling issues that are in the author s opinion critical whether they are basic issues or more advanced analytical ones are presented in de tail As software applications change continually
106. ng tested is such that the fitted distribution is the same distribution as the population from which the sample data to be fitted comes Thus if the computed p value is lower than a critical alpha level typically 0 10 or 0 05 then the distribution is the wrong distribution Conversely the higher the p value the better the distribution fits the data Roughly you can think of p value as a percentage explained that is if the p value is 1 00 Figure I 32 then setting a normal distribution with a mean of 100 67 and a standard deviation of 10 40 explains close to 100 of the variation in the data indicating an especially good fit The data was from a 1 000 trial simulation in Risk Simulator based on a normal distribution with a mean of 100 and a standard deviation of 10 Because only 1 000 trials were simulated the resulting distribution is fairly close to the specified distributional parameters and in this case about a 100 precision Modeling Toolkit and Risk Simulator Applications 35 T 0 36 0 00 12 Exponential 0 42 0 00 13 F 0 92 0 00 14 Weibull 1 00 0 00 15 Rayleigh 1 00 0 00 16 Beta 1 00 0 00 17 Statistical Summary Normal wn Mean 100 67 Ivs Em oretical vs Empirical Distribution Standard Deviation 1040 Kolmogorov Smirnov Test Statistic Test Statistic 0 02 P Value 1 00 Actual Theoretical Mean 100 61 100 67 Stdev 10 31 10 40 Skewness 0 01 0 00 Kurtosis 0 13 0 00 Automatically Generate Assumption FI
107. ns 49 Test Result Errors Relative Observed Expected O F Regression Enor Average 0 00 Frequency Standard Deviation of Errors 141 83 219 04 0 02 0 02 0 0612 0 0412 D Statistic 0 1036 202 53 0 02 0 04 0 0766 0 0366 D Critical at 1 0 1138 186 04 0 02 0 06 0 0948 0 0348 D Critical at 596 0 1225 174 17 0 02 0 08 0 1097 0 0297 D Critical at 10 0 1458 16213 002 0 10 01265 0 0265 Nui Hypothesis The errors are normally distributed 161 62 0 02 0 12 0 1272 0 0072 160 38 0 02 0 14 0 1291 0 0108 Conclusion The errors are normally distributed at the 145 40 0 02 0 16 0 1526 0 0074 125 alpha fevel 138 92 0 02 0 18 0 1637 0 0163 133 81 0 02 0 20 0 1727 0 0273 120 76 0 02 0 22 0 1973 0 0227 120 12 002 024 0 18855 00415 FIGURE 1 41 Test for normality of errors normally distributed it may indicate that the data might be autocorrelated or suffer from nonlinearities or other more destructive errors Independence of the errors can also be detected in the heteroskedasticity tests Figure L 41 The normality test on the errors performed is a nonparametric test which makes no assumptions about the specific shape of the population from which the sample is drawn allowing for smaller sample data sets to be analyzed This test evaluates the null hypothesis of whether the sample errors were drawn from a normally distributed population versus an alternate hypothesis that the data sample is not normally distributed If the calculated D Stati
108. ns to determine if they are statistically identical or statistically different from one another i e to see if the differences between the means and variances of two different forecasts that occur are based on random chance or if they are in fact statistically significantly different from one another This analysis is related to bootstrap simulation with several differences Classical hypothesis testing uses mathematical models and is based on theoretical distributions This means that the precision and power of the test is higher than bootstrap simula tion s empirically based method of simulating a simulation and letting the data tell the story However the classical hypothesis test is applicable only for testing means and variances of two distributions and by extension standard deviations to see if they are statistically identical or different In contrast nonparametric bootstrap simulation can be used to test for any distributional statistics making it more useful the drawback is its lower testing power Risk Simulator provides both techniques from which to choose PROCEDURE 1 Run a simulation Select Risk Simulator Tools Hypothesis Testing Select the two forecasts to test select the type of hypothesis test you wish to run and click OK Figure I 36 Q2 N Results Interpretation A two tailed hypothesis test is performed on the null hypothesis Ho such that the population means of the two variables are statisticall
109. nt variables in its forecasts Basic Econometrics L Econometrics refers to a branch of business analytics modeling and forecasting techniques for modeling the behavior or forecasting certain business or economic variables Running the Basic Econometrics models are similar to doing regular regression analysis except that the dependent and independent variables are allowed to be modified before a regression is run See Chapter 87 Forecasting Multiple Regression for details on running regression models This approach can be used to model the relationship or forecast time series cross sectional as well as mixed data sets Box Jenkins ARIMA a E A summary of this methodology is provided earlier in the Auto ARIMA section See Chapter 90 Forecasting Time Series ARIMA for details on running an ARIMA model This approach can only be used to forecast time series data and can include other independent variables in its forecasts 76 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Custom Distributions Using Risk Simulator expert opinions can be collected and a customized distri bution can be generated This forecasting technique comes in handy when the data set is small or the goodness of fit is bad when applied to a distributional fitting routine See Chapter 132 Risk Hedging Foreign Exchange Cash Flow Model for details on creating a custom distribution in the chapter a custom distribution is c
110. nversely if the value 0 falls outside of this confidence then the opposite is true The distribution is skewed positively skewed if the forecast statistic is positive and negatively skewed if the forecast statistic is negative Notes The term bootstrap comes from the saying to pull oneself up by one s own boot straps and is applicable because this method uses the distribution of statistics Mean Histogram Cumulative Probability 1000 Trials Frequency 100 15 Type TwoTai vw X 99 9028 100 0707 Certainty E Standard Deviation Histogram Cumulative Probability 1000 Trials 14 Frequency Aypqeaqoag eAgeinun Type Two Tal vw P 35300 36497 Certainty 9 FIGURE 1 35 Bootstrap simulation results Continued Modeling Toolkit and Risk Simulator Applications Skewness Risk Simulator Forecast Distribution Histogram Statistics Preferences Options Skewness Histogram Cumulative Probability 1000 Trials Frequency 0 0189 0 0952 Certainty Type Two Tal v 0 1 0 80 0 70 0 60 0 50 0 40 0 30 0 0 10 0 00 5 Apige aAnemumn 30 Kurtosis Risk Simulator Forecast Distribution Kurtosis Histogram Cumulative Probability 1000 Trials I Frequency 0 0614 0 1634 Certainty FIGURE 1 35 Continued themselves to anal
111. odel amp ouepijuo G5 JE uoisioalg Joug eDejusouag UOHEUEA JO papyon SOUSPYLIOD G5 JE uosbag Jour abewacuey spuscm X Spon LGC SISOUMY SSaUMayS auey wnay WNUKA uongueA jo jusrotgeo Sj nso uone AFI 340314 Spnueoieq GL SNUB SC SISOUMY SS3UM YS auey wn unte seu jo Jaquiny SOnsneis eouepyua 396 1e uarsi alg Jour abeywaciey spnusaiag ZE Spuecued yaz SISOLMY ssauMwaxS auey wn Vnum uongeue jo 3uerouso uongi e piepuejs sjeu jo jequny SONSIETS 21 aromo seovad onsyeg WEBORH o ss onses E ES Em e 22104 1038 nuuis ysry uonejsuo sAnebay anua 2y fl SS t 35822104 1038 nturs sry uoneja110 aAnisog anuarsy fl 22 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Spearman s Nonlinear Rank Correlation on Raw Data Extracted from Simulation Quantity Price Negative Quantity Negative Price Positive Positive Correlation Correlation Correlation Correlation Correlation Correlation 676 145 0 90 102 158 0 89 368 452 461 515 264 880 515 477 235 877 874 833 122 711 769 792 490 641 481 471 336 638 627 446 495 383 82 190 241 568 659 674 651 571 188 286 854 59 458 439 66 950 981 972 707 262 528 569 943 186 865 812 FIGURE 1 18 Correlations recovered negatively correlated variables projects or assets when combined in a portfolio tend to create a diversification effect where the overall risk is reduced The
112. on matrix click on Risk Simulator Edit Correlations after multiple assumptions have been set to view and edit the correlation matrix used in the simulation m Note that the correlation matrix must be positive definite that is the correlation must be mathematically valid For instance suppose you are trying to correlate three variables grades of graduate students in a particular year the number of beers they 20 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS consume a week and the number of hours they study a week You would assume that these correlation relationships exist Grades and Beer The more they drink the lower the grades no show on exams Grades and Study The more they study the higher the grades Beer and Study The more they drink the less they study drunk and partying all the time However if you input a negative correlation between Grades and Study and assuming that the correlation coefficients have high magnitudes the correlation matrix will be nonpositive definite It would defy logic correlation requirements and matrix mathematics However smaller coefficients sometimes still can work even with the bad logic When a nonpositive definite or bad correlation matrix is entered Risk Simulator automatically informs you of the error and offers to adjust these correlations to something that is semipositive definite while still maintaining the overall structure of the correlation relationship t
113. ong Ihe Vee first moment stabslics the median is least susceplisle la outliers A symmetrical distribution nas the Median equal to tne Anthmetic Mean A skewed distribution edsts when the Median is fat away from the Mean The Mode measures the moet frequently occurring data point Minimum is the smallest value in the data sel while Maximurn is Ine largest value Range is the difference between the Maximum and Winimurn values The second moment measures a distribution s spread or width and is trequertiv described using measures such 33 Standard Deviations Variances Quarties and inter Quartile Ranges Standard Deviation indicates the average deviation of all data points fom their mean Itis a popular measure as is associated with risk higher standard deviations mean a wider distribution higher risk or wider dispersion of data points around the mean and ils units are identical to original data set s The Sample Standard Deviation differs ftom the Population Standard Deviation in inat the former uses a degree of freedom correction to account for small sample sizes Also Lower and Upper Confidence Intervals are provided for the Standard Deviation and the true population standard deviation falls within this interval If your data sel covers every element of the population use the Population Standard Deviation instead The wo Variance measures ere simply the squared values of the standard deviations The Coefficient of Variability is the standard deviation of
114. orecast chart right tail probability evaluation less than or equal to 1 To do this select the Left Tail probability type enter 1 into the value input box and hit Tab The corresponding certainty will be computed In this case there is a 64 80 probability income will be at or below 1 For the sake of completeness you can select the Right Tail probability type enter the value 1 in the value input box and hit Tab Figure I 15 The resulting probability indicates the right tail probability past the value 1 that is the probability of income exceeding 1 In this case we see that there is a 35 20 probability of income at or exceeding 1 Note that the forecast window is resizable by clicking on and dragging the bottom right corner of the window Finally it is always advisable that before rerunning a simulation you reset the current simulation by selecting Risk Simulator Reset Simulation CORRELATIONS AND PRECISION CONTROL The correlation coefficient is a measure of the strength and direction of the relation ship between two variables and can take on any values between 1 0 and 1 0 that is the correlation coefficient can be decomposed into its direction or sign positive or negative relationship between two variables and the magnitude or strength of the relationship the higher the absolute value of the correlation coefficient the stronger the relationship The correlation coefficient can be computed in several ways The f
115. oved from the other points can cause the fitted line to pass close to it instead of following the general linear trend of the rest of the data especially if the point is relatively far horizontally from the center of the data However great care should be taken when deciding if the outliers should be removed Although in most cases when outliers are removed the regression results look better a priori justification must first exist For instance if one is regressing the performance of a particular firm s stock returns outliers caused by downturns in the stock market should be included these are not truly outliers as they are inevitabilities in the business cycle Forgoing these outliers and using the regression equation to forecast one s retirement fund based on the firm s stocks will yield incorrect results at best In contrast suppose the outliers are caused by a single nonrecurring business condition e g merger and acquisition and such business structural changes are not forecast to recur then these outliers should be removed and the data cleansed prior to running a regression analysis The analysis here only identifies outliers and it is up to the user to determine if they should remain or be excluded Sometimes a nonlinear relationship between the dependent and independent variables is more appropriate than a linear relationship In such cases running a linear regression will not be optimal If the linear model is not the correct form th
116. probability density function PDF cumulative distribution function CDF and the Inverse CDF ICDF of all the distributions in Mean 7 2500 0000 Risk Simulator including theoretical moments and Shee e n probability chart Kurtosis 2 0 0004 Distribution Binomial v Trials 5000 Binomial Distribution Probability 5 Type iv E 2422 2475 2528 Formatting Single Value Value X Range of Values Lower Bound Upper Bound Step Size FIGURE 1 7 Binomial approximation of the normal 2 Analytics Central Limit Theorem Winning Lottery Numbers File Name Analytics Central Limit Theorem Winning Lottery Numbers Location Modeling Toolkit Analytics Central Limit Theorem Winning Lottery Numbers Brief Description Applying distributional fitting on past winning lottery numbers and to illustrate the Central Limit Theorem and Law of Large Numbers Requirements Modeling Toolkit Risk Simulator This fun model is used to illustrate the behavior of seemingly random events For the best results first review the Central Limit Theorem model in Chapter 1 before
117. r depends on the residual error the observed variation in the independent variable the selected significance alpha level of the test and the number of data points Power decreases as the residual variance increases decreases as the significance level is decreased i e as the test is made more stringent increases as the variation in observed independent variable increases and increases as the number of data points increases Values may not be identically distributed because of the presence of outliers Outliers are anomalous values in the data Outliers may have a strong influence over the fitted slope and intercept giving a poor fit to the bulk of the data points Outliers tend to increase the estimate of residual variance lowering the chance of rejecting the null hypothesis i e creating higher prediction errors They may be due to recording errors which may be correctable or they may be due to the dependent variable values not all being sampled from the same population Apparent outliers may also be due to the dependent variable values being from the same but non normal population However a point may be an unusual value in either an independent or a dependent variable without necessarily being an outlier in the scatter plot In regression analysis the fitted line can be highly sensitive to outliers In other words least squares regression is not resistant to outliers thus neither is the fitted slope estimate A point vertically rem
118. r smaller values of a variable and only a wider range will capture this nonlinear impact A tornado chart lists all the inputs that drive the model starting from the input variable that has the most effect on the results The chart is obtained by perturbing each precedent input at some consistent range e g 1096 from the base case one at a time and comparing their results to the base case A spider chart looks like a spider with a central body and its many legs protruding The positively sloped line indicates a positive relationship while a negatively sloped line indicates a negative relationship Further spider charts can be used to visualize linear and nonlinear relationships The tornado and spider charts help identify the critical success factors of an output cell in order to identify the inputs to simulate The identified critical variables that are uncertain are the ones that should be simulated Do not waste time simulating variables that are neither uncertain nor have little impact on the results Result Base Value 966261638553219 InputChanges qeu Output Output Effective input Input Base Case pide t hgytet Risk Adjusted Discount Rate PrecedentCell Downside Upside Range Downside Upside Value 276 6262 833738 36000 1 62000 1 980 00 2197269 26 4746 3425542 189 8268 16 70663 176 5457 231775 170 0748 30 533 1627193 40 14659 153 1057 48 04737 145205 1382391 57 02984 116 8038 7664095 Prod C Qbantiy 90
119. re often claimed as trademarks In all instances where John Wiley amp Sons Inc is aware of a claim the product names appear in initial capital or all capital letters Readers however should contact the appropriate companies for more complete information regarding trademarks and registration For general information on our other products and services or for technical support please contact our Customer Care Department within the United States at 800 762 2974 outside the United States at 317 572 3993 or fax 317 572 4002 Wiley also publishes its books in a variety of electronic formats Some content that appears in print may not be available in electronic formats For more information about Wiley products visit our Web site at www wiley com Library of Congress Cataloging in Publication Data Mun Johnathan Advanced analytical models over 800 models and 300 applications from the Basel II Accord to Wall Street and beyond Johnathan Mun p cm Wiley finance series Includes index ISBN 978 0 470 17921 5 cloth dvd 1 Finance Mathematical models 2 Risk assessment Mathematical models 3 Mathematical models 4 Computer simulation I Title HG106 M86 2008 003 3 dc22 2007039385 Printed in the United States of America 10 98 765 43 2 1 Dedicated to my wife Penny Without your encouragement advice and support this modeling book would never bave taken off Delight yourself in the Lord and He wil
120. reated to forecast foreign exchange rates This approach can be used to forecast time series cross sectional or mixed data sets J S Curves The J curve or exponential growth curve is where the growth of the next pe riod depends on the current period s level and the increase is exponential This means that over time the values will increase significantly from one period to an other This model is typically used in forecasting biological growth and chemical reactions over time The S curve or logistic growth curve starts off like a J curve with exponential growth rates Over time the environment becomes saturated e g market sat uration competition overcrowding the growth slows and the forecast value eventually ends up at a saturation or maximum level This model is typically used in forecasting market share or sales growth of a new product from market introduction until maturity and decline See Chapter 82 Forecasting Exponential J Growth Curves for details on running the J curve model and Chapter 85 Forecasting Logistic S Growth Curves for running the S curve model These approaches are used to forecast time series data GARCH The generalized autoregressive conditional heteroskedasticity GARCH model is used to model historical and forecast future volatility levels of a marketable security e g stock prices commodity prices oil prices and so forth The data set has to be a time series of raw price
121. refore we see a smaller standard deviation for the negatively correlated model In a positively related model e g A B C or A x B C a negative correlation reduces the risk standard deviation and all other second moments of the distribution of the result C whereas a positive correlation between the inputs A and B will increase the overall risk The opposite is true for a negatively related model e g A B Cor A B C where a positive correlation between the inputs will reduce the risk and a negative correlation increases the risk In more complex models as is often the case in real life situations the effects will be unknown a priori and can be determined only after a simulation is run Figure I 18 illustrates the results after running a simulation extracting the raw data of the assumptions and computing the correlations between the variables The figure shows that the input assumptions are recovered in the simulation that is you enter 0 9 and 0 9 correlations and the resulting simulated values have the same correlations Clearly there will be minor differences from one simulation run to another but when enough trials are run the resulting recovered correlations approach those that were inputted TORNADO AND SENSITIVITY TOOLS IN SIMULATION One of the powerful simulation tools in Risk Simulator is tornado analysis it cap tures the static impacts of each variable on the outcome of the model that is the tool autom
122. ribution The discrete uniform distribution is also known as the equally likely outcomes distribution Where the distribution has a set of N elements and each element has the same probability Figure 1 1 This distribution is related to the uniform distribution but its elements are discrete instead of continuous The input requirement is such that minimum maximum and both values must be integers An example would be tossing a single die with 6 sides The probability of hitting 1 2 3 4 5 or 6 is exactly the same 1 6 So how can a distribution like this be converted into a normal distribution The idea lies in the combination of multiple distributions Suppose you now take a pair of dice and toss them You would have 36 possible outcomes that is the first MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Mean 3 5000 Stdev 1 7078 Skewness 0 0000 Kuggsis 1 2686 FIGURE 1 1 Tossing a single die and the discrete uniform distribution values between 1 and 6 A oC Ro 6 12 3 4 5 6 71 21731 4737 67 7122232425282 732333435353 T424344425404 7525354525255 76 26 36 4656 66 FIGURE 1 2 Tossing two dice 36 possible outcomes single die can be 1 and the second die can be 1 or perhaps 1 2 or 1 3 and so forth until 6 6 with 36 outcomes as in Figure 1 2 Now summing up the two dice you get an interesting set of results Figure 1 3 If you then plotted out these sums you get an approximation of a normal d
123. s OPTIMIZATION WITH DISCRETE INTEGER VARIABLES Sometimes the decision variables are not continuous but discrete integers e g 1 2 3 or binary e g 0 and 1 That is we can use such optimization as on off switches or go no go decisions Figure I 57 illustrates a project selection model where there are 12 projects listed The example here uses the Discrete Optimization file found either on the start menu at Start Real Options Valuation Risk Simulator Examples or accessed directly through Risk Simulator Example Models Each project like before has its own returns ENPV and NPV for expanded net present value and net present value the ENPV is simply the NPV plus any strategic real options values costs of implementation risks and so forth If required this model can be modified to include required full time equivalences FTE and other resources of various functions and additional constraints can be set on these additional resources The inputs into this model are typically linked from other spreadsheet models For instance each project will have its own discounted cash flow or returns on investment model The application here is to maximize the portfolio s Sharpe ratio subject to some budget allocation Many other versions of this model can be created for instance maximizing the portfolio returns minimizing the risks or adding additional constraints where the total number of projects chosen cannot exceed 6 All of these items
124. s micronumerosity stationarity and stochastic properties normality and sphericity of the errors and multicollinearity Each test is described in more detail in their respective reports in the model Modeling Toolkit and Risk Simulator Applications 48 Simulation Example Profile General Number of Trials 1000 Sirp Simulation an Free No Random Seed 123456 Enable Comeletions Yes Assumptions Name we First Assumption Name e Second Assumption Name iple Third Assumption Enebled Yes Enabled Yes Enebled Yes Cell SESS SES9 Cel E 10 Dynamic Simufation No No Dynamic Simulation Wo m n1A2 n3 amp NEI N71 Name nple Second Forecast Enebled yes Coll 3615 Forecast Precision Precision Level Error Levet Name ample Third Forecast Enabled Yes Celi SES A Forecast Precision Precision Level Error Leve Correlation Matrix Sample First Assumption ssumption ssumption Sample First Assumption 1 00 Sample Second Assumption 0 00 1 00 Sample Third Assumption 0 00 0 00 1 00 FIGURE 1 87 Sample simulation report 44 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Multiple Regression Analysis Data Set Diagnostic Tool This tool is used to diagnose forecasting problems in a set of multiple variables Dependent Variable Dependent Variable Y FIGURE 1 38 Running the data diagnostic tool Open the example model Risk Simulator Examples Regression Diagnostics and go to the Time Series Data workshe
125. s model the fact that stock price and strike price are nonlinearly related to the option value is important to know This characteristic implies that option value will not increase or decrease proportionally to the changes in stock or strike price and that there might be some interactions between these two prices as well as other variables As another example an engineering model depicting nonlinearities might indicate that a particular part or component when subjected to a high enough force or tension will break Clearly it is important to understand such nonlinearities Modeling Toolkit and Risk Simulator Applications 29 Spider Chart Stock Price Strike Price Maturity Risk free Rate s Volatility w Dividend Yield 0 0 60 00 40 00 2000 000 2000 4000 60 0096 FIGURE 1 25 Nonlinear spider chart SENSITIVITY ANALYSIS A related feature is sensitivity analysis While tornado analysis tornado charts and spider charts applies static perturbations before a simulation run sensitivity anal ysis applies dynamic perturbations created after the simulation run Tornado and spider charts are the results of static perturbations meaning that each precedent or assumption variable is perturbed a preset amount one at a time and the fluctuations in the results are tabulated In contrast sensitivity charts are the results of dynamic perturbations in the sense that multiple assumptions are perturbed simultaneously a
126. s worthy of consideration There exist other software products that supposedly perform stochastic optimization but in fact they do not For instance after a simulation is run then one iteration of the optimization process is gener ated and then another simulation is run then the second optimization iteration is generated and so forth this is simply a waste of time and resources That is in optimization the model is put through a rigorous set of algorithms where multiple iterations ranging from several to thousands of iterations are required to obtain the optimal results Hence generating one iteration at a time is a waste of time and resources The same portfolio can be solved using Risk Simulator in under a minute as compared to multiple hours using such a backward approach Also such a simulation optimization approach will typically yield bad results and is not a stochastic optimization approach Be extremely careful of such methodologies when applying optimization to your models The following are two example optimization problems One uses continuous decision variables while the other uses discrete integer decision variables In either model you can apply discrete optimization dynamic optimization stochastic opti mization or even the efficient frontiers with shadow pricing Any of these approaches can be used for these two examples Therefore for simplicity only the model setup will be illustrated and it is up to the user to decide
127. ssical methods are difficult to use In contrast bootstrapping analyzes sample statistics empirically by sampling the data repeatedly and creating distributions of the different statistics from each sampling The classical methods of hypothesis testing are available in Risk Simulator and are explained in the next section Classical methods provide higher power in their tests but rely on normality assumptions and can be used only to test the mean and variance of a distribution as compared to bootstrap simulation which provides lower power but is nonparametric and distribution free and can be used to test any distributional statistic PROCEDURE 1 Run a simulation with assumptions and forecasts 2 Select Risk Simulator Tools Nonparametric Bootstrap 3 Select only one forecast to bootstrap select the statistic s to bootstrap and enter the number of bootstrap trials and click OK Figure 1 34 Results Interpretation Figure 1 35 illustrates some sample bootstrap results The example file used was Hypothesis Testing and Bootstrap Simulation For instance the 90 confidence for the skewness statistic is between 0 0189 and 0 0952 such that the value 0 falls 38 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS within this confidence indicating that on a 90 confidence the skewness of this forecast is not statistically significantly different from zero or that this distribution can be considered as symmetrical and not skewed Co
128. steps to create a tornado analysis 1 Select the single output cell i e a cell with a function or equation in an Excel model e g cell G6 is selected in our example 2 Select Risk Simulator Tools Tornado Analysis Modeling Toolkit and Risk Simulator Applications 25 3 Review the precedents and rename them as appropriate renaming the precedents to shorter names allows a more visually pleasing tornado and spider chart and click OK Alternatively click on Use Cell Address to apply cell locations as the variable names Results Interpretation Figure I 21 shows the resulting tornado analysis report which indicates that capital investment has the largest impact on net present value NPV followed by tax rate average sale price and quantity demanded of the product lines and so forth The report contains four distinct elements 1 A statistical summary listing the procedure performed 2 Asensitivity table Figure I 22 shows the starting NPV base value of 96 63 and how each input is changed e g Investment is changed from 1 800 to 1 980 on the upside with a 10 swing and from 1 800 to 1 620 on the downside with a 10 swing The resulting upside and downside values on NPV are 83 37 and 276 63 with a total change of 360 making it the variable with the highest impact on NPV The precedent variables are ranked from the highest impact to the lowest impact 3 The spider chart Figure I 23 illustrates these effects
129. stic is greater than or equal to the D Critical values at various significance values then reject the null hypothesis and accept the alternate hypothesis the errors are not normally distributed Otherwise if the D Statistic is less than the D Critical value do not reject the null hypothesis the errors are normally distributed This test relies on two cumulative frequencies one derived from the sample data set and the second from a theoretical distribution based on the mean and standard deviation of the sample data Sometimes certain types of time series data cannot be modeled using any other methods except for a stochastic process because the underlying events are stochas tic in nature For instance you cannot adequately model and forecast stock prices interest rates the price of oil and other commodity prices using a simple regression model because these variables are highly uncertain and volatile and they do not fol low a predefined static rule of behavior in other words the process is not stationary Stationarity is checked here using the runs test while another visual clue is found in the autocorrelation report the ACF tends to decay slowly A stochastic process is a sequence of events or paths generated by probabilistic laws That is random events can occur over time but are governed by specific statistical and probabilis tic rules The main stochastic processes include random walk or Brownian motion mean reversion and jump
130. stically similar or greater than the hypotnesized mean and any differences are dus ti random chance Because the t test is more conservative and does not require a known population standard deviabon as in the Z test we only use this t test FIGURE 1 47 Sample statistical analysis tool report hypothesis testing of one variable values x as from 0 to 2 with a step size of 1 this means we are requesting the values 0 1 and 2 for x the resulting probabilities are provided in the table and graphically as well as the theoretical four moments of the distribution As the outcomes of the coin toss are heads heads tails tails heads tails and tails heads the probability of getting exactly no heads is 25 one heads is 50 and two heads is 25 Similarly we can obtain the exact probabilities of tossing the coin say 20 times as seen in Figure I 51 The results are presented in both table and graphical formats Figure I 51 shows the same binomial distribution but now the CDF is computed The CDF is simply the sum of the PDF values up to the point x For instance in Figure I 51 we see that the probabilities of 0 1 and 2 are 0 000001 0 000019 and 0 000181 whose sum is 0 000201 which is the value of the CDF at x 2 in Figure I 52 Whereas the PDF computes the probabilities of getting two heads the CDF computes the probability of getting no more than two heads or probabilities of 0 1 and 2 heads Taking the complement i e 1 0 000201 ob
131. stribution s CDF with 20 trials Simulator can take over this optimization problem and automate the entire process seamlessly The next section discusses the terms required in an optimization under uncertainty The Lingo of Optimization Before embarking on solving an optimization problem it is vital to understand the terminology of optimization the terms used to describe certain attributes of the optimization process These words include decision variables constraints and objectives Decision variables are quantities over which you have control for example the amount of a product to make the number of dollars to allocate among different investments or which projects to select from among a limited set As an example portfolio optimization analysis includes a go or no go decision on particular projects Modeling Toolkit and Risk Simulator Applications 61 Distribution Analysis This tool generates the probability density function PDF cumulative distribution function CDF and the Inverse CDF ICDF of all the distributions in Risk Simulator including theoretical moments and probability chart Distribution Mu Sigma Type E 223 0 74 Formatting Single Value Probability Range of Values Lowet Bound Upper Bound Step Size FIGURE 1 58 Distributional Analysis Tool Normal Distribution s ICDF and Z Score In addition the dollar or percentage budget allocation across mult
132. tains 0 999799 or 99 979995 provides the probability of getting three heads or more Using this distributional analysis tool even more advanced distributions can be analyzed such as the gamma beta negative binomial and many others in Risk 56 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Test for Normality The Normality test is a form of nonparametric test which makes no assumptions about the specific shape of the population from which the sample is drawn allowing for smaller sample data sels to be analyzed This test evaluates the null hypothesis of whether Ine data sample was drawn from a normally distnbuted populabon versus an alternate hypothesis that the data sample is not normally distnbuted If the calculated p value is less than or equal to the alpha significance value then reject the null hypothesis and accept the alternate hypothesis Otherwise if the p value is higher than the alpha significance value do not reject the null hypothesis This test relies on two cumulative frequencies one derived from the sample data set the second from a theoretical distribution based on the mean and standard deviation of the sample dala An alternative to this test is the Chi Square test for normality The Chi Square test requires more dala points to run compared to the Normality test used here Test Result Relative Ob j Data Average 33132 tone Frequency Epo OE Standard Deviation 172 91 47 00 0 02 002 0 0497 0 0297 D Statistic 00859 68 0
133. tegorized by application domain and each model is described in more detail in this book Please note that this software uses Excel macros If you receive an error message on macros it is because your system is set to a high security level You need to fix this by starting Excel XP or 2003 and clicking on Tools Macros Security Medium and restarting the software If you are using Excel 2007 you can simply click on Enable Macros when prompted or reset your security settings when in Excel 2007 by clicking on the Office button located at 2 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS the top left of the screen and selecting Excel Options Trust Center Trust Center Settings Macro Settings Enable All Macros Note that the trial version will expire in 30 days To obtain a full corporate li cense please contact the author s firm Real Options Valuation Inc at admin realoptionsvaluation com or visit the company s web site www realoptions valuation com Notice that after the software expiration date some of the mod els that depend on Risk Simulator or Real Options SLS software still will function until their respective expiration dates In addition after the expiration date these worksheets still will be visible but the analytical results and functions will return null values Finally software versions continually change and improve and the best recommendation is to visit the company s web site for any updated or newer softw
134. ters and hit OK to insert the input assumption into your model Figure I 5 Several key areas are worthy of mention in the Assumption Properties Figure I 6 shows the different areas Assumption Name This optional area allows you to enter in unique names for the assumptions to help track what each of the assumptions represents Good modeling practice is to use short but precise assumption names Distribution Gallery This area shows all of the different distributions available in the software To change the views right click anywhere in the gallery and select large icons small icons or list More than two dozen distributions are available Input Parameters Depending on the distribution selected the required relevant parameters are shown You may either enter the parameters directly or link them to specific cells in your worksheet Click on the Link icon to link an input parameter to a worksheet cell Hard coding or typing the parameters is useful when the assumption parameters are assumed not to change Linking to worksheet cells is useful when the input parameters themselves need to be visible on the worksheets or can be changed as in a dynamic simulation where the input parameters themselves are linked to assumptions in the worksheets creating a multidimensional simulation or simulation of simulations suonduirnisse Indu Buoure suornp a1103 Alle 3A0UI31 To ipa ppe 0j Pale Sip asn soniodoud uondumssy g 3ufj9lJ
135. the DVD found in the DOT NET Framework folder Complete the NET installation restart the computer and then reinstall the Risk Simulator software Version 1 1 of the NET Framework is required even if your system has version 2 0 3 0 as they work independently of each other You may also download this software on the Download page of www realoptionsvaluation com See the About the DVD section at the end of this book for details on obtaining an extended trial license Once installation is complete start Microsoft Excel If the installation was suc cessful you should see an additional Risk Simulator item on the menu bar in Excel and a new icon bar as shown in Figure I 1 Figure I 2 shows the icon toolbar in more detail Please note that Risk Simulator supports multiple languages e g English Chi nese Japanese and Spanish and you can switch among languages by going to Risk Simulator Languages You are now ready to start using the software for a trial period You can obtain permanent or academic licenses from www realoptionsvaluation com If you are using Windows Vista make sure to disable User Access Control before installing the software license To do so Click on Start Control Panel Classic View E Microsoft Excel Book1 S iS Be Edt wew inset Format Tool Qua Window tep Adobe POF f g EE Wee ie BO eR A OO Me ere Rane J NS i z Bol U NEXG S 8 EX M Set Output Forecast qg 3 BB lt
136. there are any interactions in the model and if the effects of certain variables still hold The second chart Figure 1 30 illustrates the percent variation explained that is of the fluctuations in the forecast how much of the variation can be explained by each of the assumptions after accounting for all the interactions among variables Notice that the sum of all variations explained is usually close to 100 sometimes other elements impact the model but they cannot be captured Percent Variation Explained Net Present Value o ONE 40 8 Quar EE 25 74 A Quantity 2 56 C Quantity OHNE 11 1526 A Price WUE 9 52 B Price NEM 4 64 C Price ml 3 02 Tax Rate 0 28 Price Erosion 0 11 Sales Growth FIGURE 1 30 Contribution to variance chart Modeling Toolkit and Risk Simulator Applications 33 here directly and if correlations exist the sum may sometimes exceed 100 due to the interaction effects that are cumulative Notes Tornado analysis is performed before a simulation run while sensitivity analysis is performed after a simulation run Spider charts in tornado analysis can consider nonlinearities while rank correlation charts in sensitivity analysis can account for nonlinear and distributional free conditions DISTRIBUTIONAL FITTING SINGLE VARIABLE AND MULTIPLE VARIABLES Another powerful simulation tool is distributional fitting that is which distribution does an analyst or engineer use for a particul
137. thin these bounds it is not significantly different from zero at the 5 significance level Autocorrelation measures the relationship to the past of the dependent Y variable to itself Distributive lags in contrast are time lag relationships between the depen dent Y variable and different independent X variables For instance the movement and direction of mortgage rates tend to follow the federal funds rate but at a time lag typically one to three months Sometimes time lags follow cycles and seasonality e g ice cream sales tend to peak during the summer months and are hence related to the previous summer s sales 12 months in the past The distributive lag analysis Figure I 40 shows how the dependent variable is related to each of the independent variables at various time lags when all lags are considered simultaneously to determine which time lags are statistically significant and should be considered Another requirement in running a regression model is the assumption of nor mality and sphericity of the error term If the assumption of normality is violated or outliers are present then the linear regression goodness of fit test may not be the most powerful or informative test available and this could mean the difference between either detecting a linear fit or not If the errors are not independent and not sy nsa1 Zeg SANG ASIP pue uoge oxioonny Opl 3uUf9iJ velio 58k Ln 99820 Salsi tte 0 06 L60 PRED glg 66s50 EZLVD cEDZ D L amp
138. tions are applied Dynamic Optimization A simulation is first run the results of the simulation are applied in the model and then an optimization is applied to the simulated values Number of Simulation Trials 1000 Stochastic Optimization Similar to dynamic optimization but the process is repeated several times The final decision variables will each have its own forecast chart indicating its optimal range Number of Simulation Trials Number of Optimization Runs FIGURE 1 55 Running Continuous Optimization in Risk Simulator S3 n So uonezrumdo snonurmuo 9G l 3un3IiJ 0087 oney ysy 02 umay 600 00L oars h SOTI TL jero orjojuog g 8 E DL Stogo 9600 8 600 8 HASED HISZ 9e90 01 DI 55619 1858 8 g DL t rasan 9600 3 600 5 967 8 099 BOL FL 6 556 5 lessy 1 y 6 c r606 0 9600 SE 600 5 96068 Pr 9656 Fl B sse lassy G 680 9500 GE OOS WOE ZL PARTA WEGGI 4 88B ASS S E g E S860 9600 8 600 8 FO LL 966E Y 9e lc vl g 58 1888 z zZ t S5 2 660 9600 5E 600 5 3680 CL WIZE 965c El 8 5Sb lassy E 5 g 1980 9600 5E amp DD G Wzc LL 5E Fo Ol p Ss8 lassy 6 6 n g D2920 00 SE SUD S 984 7 95 9ep8 LL E 88 lessy Ol OL 8 Z zego 9600 SE 600 8 96 08 HETI WSE LL Z 888 1888 t d z 6 recen 9600 8 600 8 60 LL HIET SF OL SSE 9 1888 lo iH o 1H uro oid uoge o y uoneoo ny BDumueuy Bupuey ysy Gumuey Burquey open Stn WWE unum subiam uL sump uonduaseQ uopge2opy oO uina Eh suinj
139. tions are required remember to check the Turn on Correlations preference by clicking on Risk Simulator Edit Simulation Profile See the dis cussion on correlations later in this chapter for more details about assigning correlations and the effects correlations will have on a model Short Descriptions Short descriptions exist for each of the distributions in the gallery The short descriptions explain when a certain distribution is used as well as the input parameter requirements See the section in the appendix Under standing Probability Distributions in Modeling Risk Applying Monte Carlo Simulation Real Options Analysis Stochastic Forecasting and Portfolio Op timization Hoboken NJ John Wiley amp Sons 2006 also by the author for details about each distribution type available in the software Note If you are following along with the example continue by setting another assumption on cell G9 This time use the Uniform distribution with a minimum value of 0 9 and a maximum value of 1 1 Then proceed to defining the output forecasts in the next step 3 Defining Output Forecasts The next step is to define output forecasts in the model Forecasts can be defined only on output cells with equations or functions Use these three steps to define the forecasts Select the cell on which you wish to set an assumption e g cell G10 in the Basic Simulation Model example Click on Risk Simulator Set Output Forecast or cli
140. ts The first moment descides the location of a dist bution Le mean median and mode and is interpreted as the expected value expected returns or the average value of occurrences The Arithmetic Mean calculales Ihe average of all occurences by summing up a of the data points and dividing Ihem by the number of points The Geornelric Mean is calculated by taking the power roct of the products of all the dats points and requires them to all be positive The Geometic Mean is more accurate for percentages or rates that fluctuate significantty For example you can use Geometric Mean to calculate average growth rate given compound Interest with variable rates The Trimmed Mean calculates the arithmetic average of the dala se after the extreme ouliers have been trimmed As averages re prone Io significant bias when culliers exist the Trimmed Mean reduces such bias in skewed distribubons The Standard Error of the Mean calculates the error surrounding the sample mean The larger the sample size the smaller Ine error such Ihat for an infinitely large sampile size the error approaches zero indicating thal the population parameter has been estimated Due to sampling errors the 95 Confidence Interval for the Mean is provided Sased on an analysis of the sampile data points the actual populabon mean should fail between these Lower and Upper intervals for the Mean Median is the data poini where 50 of all data points fail above this value and 50 below ls value Am
141. turns or the min cost risks Uses include managing inventories financial a allocation product mix project selection Objective Cell M E Optimization Objective Maximize the value in objective cell Minimize the value in objective cell FIGURE 1 58 Running discrete integer optimization 7A MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS Returnto Profitability Credit Line ENPV Cost Risk Risk Risk Ratio nd Selection Project 1 5496 8 33 1 26 Project 2 1 91492 1 02 327 Project 3 1 551 03 1 03 1 87 Project 4 1 012 95 222 237 Project 5 849 00 92541 0 92 285 Project 6 560 92 1 35 15 58 Project 7 5 633 10 051 475 Proect8 123500 115 00 926 25 1 33 1174 Project 9 2 100 60 0 93 16 56 Project 10 1 912 50 1 18 591 Project 11 26352 208 1320 Project 12 309 75 1 69 6 00 Total 577600 3 694 44 1 539 26 64 Goal MAX lt 5000 lt 6 Sharpe Ratio 3 7543 ENPV is the expected NPV of each credit line or project while Cost can be the total cost of administration as well as required capital holdings to cover the credit line and Risk is the Coefficient of Vanation of the credit lines ENPV FIGURE 1 59 Optimal selection of projects that maximizes the Sharpe ratio On the quantitative side of forecasting the available data or data that need to be forecasted can be divided into time series values that have a time element to them such as revenues of different years inflation rates interest rates mar
142. uded in the book s DVD or can be downloaded directly from the Web at www realoptionsvaluation com Part I of the book deals with models using the Modeling Toolkit and Risk Simulator software applications Part II deals with real options and financial option models using the Real Options SLS software Readers who are currently expert users of the Modeling Toolkit software and Risk Simulator software may skip this section and dive directly into the models INTRODUCTION TO THE MODELING TOOLKIT SOFTWARE The Modeling Toolkit software incorporates about 800 different advanced analytical models functions and tools applicable in a variety of industries and applications Appendix 1 lists the models available in the software as of this book s publication date To install this software for a trial period of 30 days insert the DVD that comes with the book or visit www realoptionsvaluation com and click on Downloads Look for the Modeling Toolkit software This software works on Windows XP or Vista and requires Excel XP 2003 or 2007 to run At the end of the installation process you will be prompted for a license key Please use this trial license Name 30 Day Trial Key 4C55 0BA2 420E CA84 To start the software click on Start Programs Real Options Valuation Modeling Toolkit Modeling Toolkit This action will start Excel Inside Excel you will notice a new menu item called Modeling Toolkit This menu is self explanatory as the models are ca
143. ulticollinearity nonlinearity outliers Forecasting ARIMA Auto ARIMA J S curves GARCH Markov chains mul tivariate regressions stochastic processes Optimization static dynamic stochastic Sensitivity Analysis correlated sensitivity scenario spider tornado m m m gm mg Real Options SLS Customizable Binomial Trinomial Quadranomial and Pentanomial Lattices Lattice Makers lattices with Monte Carlo simulation Super fast super lattice algorithms running thousands of lattice steps in seconds Covering the following applications H Exotic Options Models barriers benchmarked multiple assets portfolio op tions Financial Options Models 3D dual asset exchange single and double barriers Real Options Models abandon barrier contract expand sequential com pound switching Specialized Options mean reverting jump diffusion and dual asset rainbows m m m Employee Stock Options Valuation Toolkit Applied by the U S Financial Accounting Standards Board for FAS 123R 2004 Binomial and closed form models Covers H Blackout Periods Changing Volatility Forfeiture Rates Suboptimal Exercise Multiple Vesting E m m E 1 Modeling Toolkit and Risk Simulator Applications his book covers about 300 different analytical model templates that apply up to 800 modeling functions and tools from a variety of software applications Trial versions of these software applications are incl
144. values that fall within some specified interval or within some standard deviation that you choose Also you can set the precision level here for this specific forecast to show the error levels in the statistics view See the section on precision and error control for more details USING FORECAST CHARTS AND CONFIDENCE INTERVALS In forecast charts you can determine the probability of occurrence called confidence intervals that is given two values what are the chances that the outcome will fall between these two values Figure I 12 illustrates that there is a 90 probability that the final outcome in this case the level of income will be between 0 2781 and 1 3068 The two tailed confidence interval can be obtained by first selecting Two Tail as the type entering the desired certainty value e g 90 and hitting Tab on the keyboard The two computed values corresponding to the certainty value will then be displayed In this example there is a 5 probability that income will be below 0 2781 and another 5 probability that income will be above 1 3068 that is the two tailed confidence interval is a symmetrical interval centered on the median or 50th percentile value Thus both tails will have the same probability Alternatively a one tail probability can be computed Figure I 13 shows a Left Tail selection at 9596 confidence i e choose Left Tail as the type enter 95 as Income 1000 Trials 00 90 80 70 P o g 50 2 40 w 3 20
145. variable by selecting cell J4 and select Risk Simulator Optimization Set Decision click on the link icon to select the name cell B4 and select the Binary variable Then using Risk Simulator copy copy this cell J4 decision variable and paste the decision variable to the remaining cells in J5 to J15 This is the best method if you have only several decision variables and you can name each decision variable with a unique name for identification later Make sure to use the Risk Simulator copy and paste rather than Excel copy and paste functions The second step in optimization is to set the constraints There are two con straints here that is the total budget allocation in the portfolio must be less than 5 000 and the total number of projects must not exceed 6 So click on Risk Simulator Optimization Constraints and select ADD to add a new constraint Then select the cell D17 and make it less than or equal lt to 5000 Repeat by setting cell J17 lt 6 72 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS The final step in optimization is to set the objective function and start the optimization by selecting cell C19 and selecting Risk Simulator Optimization Set Objective then run the optimization using Risk Simulator Optimization Run Optimization and choosing the optimization of choice Static Optimization Dynamic Optimization or Stochastic Optimization To get started select Static Optimization Check to mak
146. with domain expert contribu tions The Modeling Toolkit software that holds all the models Risk Simulator software and Real Options SLS software were all developed by the author with over 1 000 functions tools and model templates in these software applications The trial versions are included in the accompanying DVD The applications covered are vast Included are Basel II banking risk require ments credit risk market risk credit spreads default risk value at risk etc and financial analysis exotic options and valuation risk analysis stochastic forecasting risk based Monte Carlo simulation optimization real options analysis strategic options and decision analysis Six Sigma and quality initiatives management sci ence and statistical applications and everything in between such as applied statistics manufacturing decision analysis operations research optimization forecasting and econometrics This book is targeted at practitioners who require the algorithms examples models and insights in solving more advanced and even esoteric problems This book does not only talk about modeling or illustrate basic concepts and exam ples it comes complete with a DVD filled with sample modeling videos case studies and software applications to help you get started immediately In other words this book dispenses with all the theoretical discussions and mathematical models that are extremely hard to decipher and apply in the real busi
147. xample the objective may be to maximize returns while minimizing risks maximizing the Sharpe ratio or returns to risk ratio The solution to an optimization model provides a set of values for the decision variables that optimizes maximizes or minimizes the associated objective If the real business conditions were simple and if the future were predictable all data in an optimization model would be constant making the model deterministic In many cases however a deterministic optimization model cannot capture all the relevant intricacies of a practical decision making environment When a model s data are uncertain and can only be described probabilistically the objective will have some probability distribution for any chosen set of decision variables You can find this probability distribution by simulating the model using Risk Simulator An optimization model under uncertainty has several additional elements including assumptions and forecasts Assumptions capture the uncertainty of model data using probability distribu tions whereas forecasts are the frequency distributions of possible results for the model Forecast statistics are summary values of a forecast distribution such as the mean standard deviation and variance The optimization process controls the optimization by maximizing or minimizing the objective Each optimization model has one objective a variable that mathematically rep resents the model s objective in terms of
148. y around the drift path The Mean Reversion process can be used to reduce the fluctuations of the Random Walk process by allowing the path to target a long term value making it useful for forecasting time series variables that have a long term rate such as interest rates and inflation rates these are long term target rates by regulatory authorities or the market The Jump Diftusion process is useful for forecasting time series data when the variable can occasionally exhibit random jumps such as oil prices or price of electricty discrete exogenous event shocks can make prices jump up or down Finally these three stochastic processes can be mixed and matched as required Statistical Summary The following are the estimated parameters for a stochastic process given the data provided It is up to you to determine ifthe probability of fit similar to a goodness of fit computation is sufficient to warrant the use of a stochastic process forecast and if so whether itis a random walk mean reversion or a jump diffusion model or combinations thereof In choosing the right stochastic process model you will have to rely on past experiences and a priori economic and financial expectations of what the underlying data set is best represented by These parameters can be entered into a stochastic process forecast Simulation Forecasting Stochastic Processes Annualized Drift Rate 5 66 Reversion Rate N A Jump Rate 16 33 Volatility 7 0
149. y identical to one another The alternative hypothesis H is such that the population means are statistically different from one another If the calculated p values are less than or equal to 0 01 0 05 or 0 10 alpha test levels it means that the null hypothesis is rejected which implies that the forecast means are statistically significantly different at the 1 5 and 10 significance levels If the null hypothesis is not rejected when the p values are high the means of the two forecast distributions are statistically similar to one another The same analysis is performed on variances of two forecasts at a time using the pairwise F test If the p values are small then the variances and standard deviations are statistically different from one another otherwise for large p values the variances are statistically identical to one another The example file used was Hypothesis Testing and Bootstrap Simulation SIR ajdwes japuadag C saouene A enb3 ure sajdwes juspuedapu C seoueue enbaur win sajdue s juspuedepu suondunssy O18 POW uonepuig 8 PPOW auo2u oLa lapoj uongjnuirs V lapo awoau 129 ayeo aWeN 15823104 489 sisaujodd uni 0 31382810 0M 1298s asea 82ueua wopue o anp aie SADUSIBYIP WAY 10 JAYJOUE auo uoi juarejrp fijeansneys aie fay ji a T eauerie pue ueatu aues au aeu suonngusip eao aou 10 Oi Ji autuiayap o pasn st Bunsay sisaqiod F3 Sunse sisaypodAy E
150. yze the accuracy of the statistics Nonparametric simulation is simply randomly picking golf balls from a large basket with replacement where each golf ball is based on a historical data point Suppose there are 365 golf balls in the basket representing 365 historical data points Imagine if you will that the value of each golf ball picked at random is written on a large whiteboard The results of the 365 balls picked with replacement are written in the first column of the board with 365 rows of numbers Relevant statistics e g mean median mode standard deviation etc are calculated on these 365 rows The process is then repeated say 5 000 times The whiteboard will now be filled with 365 rows and 5 000 columns Hence 5 000 sets of statistics i e there will be 5 000 means 5 000 medians 5 000 modes 5 000 standard deviations etc are tabulated and their distributions shown 40 MODELING TOOLKIT AND RISK SIMULATOR APPLICATIONS The relevant statistics of tbe statistics are then tabulated where from these results you can ascertain how confident the simulated statistics are Finally bootstrap re sults are important because according to the Law of Large Numbers and Central Limit Theorem in statistics the mean of the sample means is an unbiased estimator and approaches the true population mean when the sample size increases HYPOTHESIS TESTING A hypothesis test is performed when testing the means and variances of two distri butio

Advanced Analytical Models

Contents

Download Pdf Manuals

Related Search

Related Contents