Financial Modeling with Crystal Ball and Excel

Preface. Acknowledgments. About the Author. Chapter 1: Introduction. Financial Modeling. Risk Analysis. Monte Carlo Simulation. Risk Management. Benefits and Limitations of Using Crystal Ball. Chapter 2: Analyzing Crystal Ball Forecasts. Simulating A 50-50 Portfolio. Varying the Allocations. Presenting the Results. Chapter 3: Building a Crystal Ball Model. Simulation Modeling Process. Defining Crystal Ball Assumptions. Running Crystal Ball. Sources of Error. Controlling Model Error. Chapter 4: Selecting Crystal Ball Assumptions. Crystal Ball's Basic Distributions. Using Historical Data to Choose Distributions. Specifying Correlations. Chapter 5: Using Decision Variables. Defining Decision Variables. Decision Table with One Decision Variable. Decision Table with Two Decision Variables. Using OptQuest. Chapter 6: Selecting Run Preferences. Trials. Sampling. Speed. Options. Statistics. Chapter 7: Net Present Value and Internal Rate of Return. Deterministic NPV and IRR. Simulating NPV and IRR. Capital Budgeting. Customer Net Present Value. Chapter 8: Modeling Financial Statements. Deterministic Model. Tornado Chart and Sensitivity Analysis. Crystal Ball Sensitivity Chart. Conclusion. Chapter 9: Portfolio Models. Single-Period Crystal Ball Model. Single-Period Analytical Solution. Multiperiod Crystal Ball Model. Chapter 10: Value at Risk. VaR. Shortcomings of VaR. CVaR. Chapter 11: Simulating Financial Time Series. White Noise. Random Walk. Autocorrelation. Additive Random Walk with Drift. Multiplicative Random Walk Model. Geometric Brownian Motion Model. Mean-Reverting Model. Chapter 12: Financial Options. Types of Options. Risk-Neutral Pricing and the Black-Scholes Model. Portfolio Insurance. American Option Pricing. Exotic Option Pricing. Bull Spread. Principal-Protected Instrument. Chapter 13: Real Options. Financial Options and Real Options. Applications of ROA. Black-Scholes Real Options Insights. ROV Tool. Summary. Appendix A: Crystal Ball's Probability Distributions. Appendix B: Generating Assumption Values. Appendix C: Variance Reduction Techniques. Appendix D: About the Download. Glossary. References. Index.

[1]  K. Scott Proctor.  Building Financial Models with Microsoft Excel: A Guide for Business Professionals , 2004 .

[2]  Christiane Lemieux,et al.  Variance reduction of Monte Carlo and randomized quasi-Monte Carlo estimators for stochastic volatility models in finance , 1999, WSC '99.

[3]  Jeffrey I. McGill,et al.  Revenue Management: Research Overview and Prospects , 1999, Transp. Sci..

[4]  P. Glasserman,et al.  Pricing American-style securities using simulation , 1997 .

[5]  Ralph B. D'Agostino,et al.  Goodness-of-Fit-Techniques , 2020 .

[6]  Guochang Zhang.  Accounting Information, Capital Investment Decisions, and Equity Valuation: Theory and Empirical Implications , 1999 .

[7]  F. Black,et al.  The Pricing of Options and Corporate Liabilities , 1973, Journal of Political Economy.

[8]  Michael A. Stephens,et al.  Asymptotic Results for Goodness-of-Fit Statistics with Unknown Parameters , 1976 .

[9]  Michael J. McNamara,et al.  Principles of risk management and insurance , 1978 .

[10]  A. W. Kemp,et al.  Univariate Discrete Distributions , 1993 .

[11]  J. Mumpower,et al.  Risk Analysis and Risk Management: An Historical Perspective , 1985 .

[12]  Paul Glasserman,et al.  Fast greeks by simulation in forward LIBOR models , 1999 .

[13]  Alan G. White,et al.  Efficient Procedures for Valuing European and American Path-Dependent Options , 1993 .

[14]  Ruey S. Tsay,et al.  Analysis of Financial Time Series , 2005 .

[15]  Averill M. Law,et al.  Simulation Modeling and Analysis , 1982 .

[16]  Michael C. Fu,et al.  Sensitivity Analysis for Monte Carlo Simulation of Option Pricing , 1995, Probability in the Engineering and Informational Sciences.

[17]  F. Dwyer.  Customer lifetime valuation to support marketing decision making , 1997 .

[18]  David W. Sommer,et al.  Risk Management and Insurance , 1997 .

[19]  Ernest Klein,et al.  A comprehensive etymological dictionary of the English language , 1966 .

[20]  Chung-Li Tseng,et al.  Short-Term Generation Asset Valuation: A Real Options Approach , 2002, Oper. Res..

[21]  John M. Charnes,et al.  Using simulation for option pricing , 2000, 2000 Winter Simulation Conference Proceedings (Cat. No.00CH37165).

[22]  Peter M. Kort,et al.  Strategic Technology Adoption Taking into Account Future Technological Improvements: A Real Options Approach , 2000, Eur. J. Oper. Res..

[23]  Jérôme Barraquand,et al.  Numerical Valuation of High Dimensional Multivariate American Securities , 1995, Journal of Financial and Quantitative Analysis.

[24]  P. Embrechts,et al.  Quantitative Risk Management: Concepts, Techniques, and Tools , 2005 .

[25]  Dwight Grant,et al.  Path-dependent options: extending the Monte Carlo simulation approach , 1997 .

[26]  James R. Wilson,et al.  Correlation-induction techniques for estimating quantiles in simulation experiments , 1995, WSC '95.

[27]  A. Falcó,et al.  Regulated Investments and the Valuation of Capital Investment Strategies Through a Real Options' Approach , 2001 .

[28]  Thomas Dangl,et al.  Investment and capacity choice under uncertain demand , 1999, Eur. J. Oper. Res..

[29]  George S. Fishman,et al.  A First Course in Monte Carlo , 2005 .

[30]  Paul Hyden,et al.  Efficiency improvements for pricing American options with a stochastic mesh , 1999, WSC'99. 1999 Winter Simulation Conference Proceedings. 'Simulation - A Bridge to the Future' (Cat. No.99CH37038).

[31]  Nicholas Economides.  Real Options and the Costs of the Local Telecommunications Network , 1999 .

[32]  Detlef Schoder,et al.  Valuing the Real Option of Abandoning Unprofitable Customers when Calculating Customer Lifetime Value , 2006 .

[33]  M. Maguire.  Wall Street Made Me Do It: A Preliminary Analysis of the Major Institutional Investors in U.S. Newspaper Companies , 2003 .

[34]  M. Milevsky,et al.  Asset Allocation Via The Conditional First Exit Time or How To Avoid Outliving Your Money , 1997 .

[35]  Reuven Y. Rubinstein,et al.  Simulation and the Monte Carlo method , 1981, Wiley series in probability and mathematical statistics.

[36]  Mark Rubinstein.  A History of the Theory of Investments: My Annotated Bibliography , 2006 .

[37]  Associates Ibbotson,et al.  Stocks, bonds, bills, and inflation yearbook , 1993 .

[38]  James A. Tilley.  Valuing American Options in a Path Simulation Model , 2002 .

[39]  P. Wilmott.  Derivatives: The Theory and Practice of Financial Engineering , 1998 .

[40]  Fred Glover,et al.  Tabu Search and Adaptive Memory Programming — Advances, Applications and Challenges , 1997 .

[41]  Richard M. Brugger.  Univariate Discrete Distributions (2nd Ed.) , 1994 .

[42]  M. Milevsky,et al.  Asset allocation, life expectancy and shortfall , 1994 .

[43]  N. Metropolis,et al.  The Monte Carlo method. , 1949 .

[44]  P. Boyle.  Options: A Monte Carlo approach , 1977 .

[45]  Jianqing Fan,et al.  Nonlinear Time Series : Nonparametric and Parametric Methods , 2005 .

[46]  Gonzalo Cortazar,et al.  Simulation and Numerical Methods in Real Options Valuation , 2000 .

[47]  M. Stephens.  EDF Statistics for Goodness of Fit and Some Comparisons , 1974 .

[48]  John M. Charnes,et al.  Real-Options Valuation for a Biotechnology Company , 2000 .

[49]  L. Jennergren.  A Tutorial on the McKinsey Model for Valuation of Companies , 2002 .

[50]  M. Stein.  Large sample properties of simulations using latin hypercube sampling , 1987 .

[51]  E. Elton,et al.  Note---On the Maximization of the Geometric Mean with Lognormal Return Distribution , 1974 .

[52]  L. Trigeorgis.  Real Options: Managerial Flexibility and Strategy in Resource Allocation , 1996 .

[53]  S. Howell,et al.  Real Options Analysis , 2001 .

[54]  Bhagwan Chowdhry,et al.  Resources, real options, and corporate strategy , 1998 .

[55]  P. Glasserman,et al.  Estimating security price derivatives using simulation , 1996 .

[56]  H. Johnson,et al.  The American Put Option Valued Analytically , 1984 .

[57]  Gordon B. Pye.  Sustainable Investment Withdrawals , 2000 .

[58]  Reuven Y. Rubinstein,et al.  Simulation and the Monte Carlo Method , 1981 .

[59]  M. W. Green,et al.  2. Handbook of the Logistic Distribution , 1991 .

[60]  William P. Bengen.  Determining Withdrawal Rates Using Historical Data , 2007 .

[61]  Nalin Kulatilaka,et al.  Real Options: Managing Strategic Investment in an Uncertain World , 1998 .

[62]  Hemantha S. B. Herath,et al.  ECONOMIC ANALYSIS OF R&D PROJECTS: AN OPTIONS APPROACH , 1999 .

[63]  A. Kemna,et al.  A pricing method for options based on average asset values , 1990 .

[64]  H. Niederreiter.  Low-discrepancy and low-dispersion sequences , 1988 .

[65]  O. Ore.  Pascal and the invention of probability theory , 1959 .

[66]  Alan G. White,et al.  The Pricing of Options on Assets with Stochastic Volatilities , 1987 .

[67]  D. B. Preston.  Spectral Analysis and Time Series , 1983 .

[68]  P. Samuelson.  Lifetime Portfolio Selection by Dynamic Stochastic Programming , 1969 .

[69]  Francis A. Longstaff,et al.  Valuing American Options by Simulation: A Simple Least-Squares Approach , 2001 .

[70]  L. Haan,et al.  Extreme value theory : an introduction , 2006 .

[71]  Stan Uryasev,et al.  Conditional value-at-risk: optimization algorithms and applications , 2000, Proceedings of the IEEE/IAFE/INFORMS 2000 Conference on Computational Intelligence for Financial Engineering (CIFEr) (Cat. No.00TH8520).

[72]  G. Friedl.  Copeland, Tom/Antikarov, Vladimir, Real Options. A Practitioner’s Guide, Texere LLC, New York 2001, $ 59,95 , 2002 .

[73]  S. Bell.  Approximating the Normal Distribution with the Triangular , 1962 .

[74]  Robert C. Blattberg,et al.  Customer Equity: Building and Man-aging Relationships as Valuable Assets , 2001 .

[75]  Hemantha S. B. Herath,et al.  MULTI-STAGE CAPITAL INVESTMENT OPPORTUNITIES AS COMPOUND REAL Options , 2002 .

[76]  T. Copeland.  Real Options: A Practitioner's Guide , 2001 .

[77]  D. Tasche,et al.  Expected shortfall and beyond , 2002, cond-mat/0203558.

[78]  Glyn A. Holton.  Value at Risk: Theory and Practice , 2003 .

[79]  T. Copeland,et al.  The real-options approach to capital allocation , 2002, IEEE Engineering Management Review.

[80]  William Aspray,et al.  John von Neumann and the origins of modern computing , 1990, History of computing.

[81]  Mark Broadie,et al.  Recent advances in simulation for security pricing , 1995, WSC '95.

[82]  A. Owen,et al.  Valuation of mortgage-backed securities using Brownian bridges to reduce effective dimension , 1997 .

[83]  P. Glasserman,et al.  Monte Carlo methods for security pricing , 1997 .

[84]  Paul Glasserman,et al.  Monte Carlo Methods in Financial Engineering , 2003 .

[85]  Prakash P. Shenoy,et al.  Multistage Monte Carlo Method for Solving Influence Diagrams Using Local Computation , 2004, Manag. Sci..

[86]  Art B. Owen,et al.  Monte Carlo extension of quasi-Monte Carlo , 1998, 1998 Winter Simulation Conference. Proceedings (Cat. No.98CH36274).

[87]  J. Cockcroft.  Investment in Science , 1962, Nature.

[88]  Andrea Gamba,et al.  Real Options Valuation: a Monte Carlo Simulation Approach 1 , 2002 .

[89]  Herbert F. York,et al.  John Von Neumann: The Scientific Genius Who Pioneered the Modern Computer, Game Theory, Nuclear Deterrance, and Much More , 1993 .

[90]  J. Hull.  Options, Futures, and Other Derivatives , 1989 .

[91]  Jonathan T. Guyton.  Decision Rules and Portfolio Management for Retirees: Is the 'Safe' Initial Withdrawal Rate Too Safe? , 2005 .

[92]  R. Caflisch.  Monte Carlo and quasi-Monte Carlo methods , 1998, Acta Numerica.

[93]  M. D. McKay,et al.  A comparison of three methods for selecting values of input variables in the analysis of output from a computer code , 2000 .

[94]  C. Read,et al.  Handbook of the Normal Distribution, 2nd Edition. , 1998 .

[95]  F. Glover.  HEURISTICS FOR INTEGER PROGRAMMING USING SURROGATE CONSTRAINTS , 1977 .

[96]  Diane M. Lander,et al.  Challenges to the practical implementation of modeling and valuing real options , 1998 .

[97]  Arjun K. Gupta,et al.  Handbook of beta distribution and its applications , 2004 .

[98]  Michael C. Fu.  Pricing of financial derivatives via simulation , 1995, WSC '95.

[99]  Eduardo S. Schwartz,et al.  Investment Under Uncertainty. , 1994 .

[100]  Samuel Kotz,et al.  The Triangular Distribution , 2004 .

[101]  Peter M. Kort,et al.  Strategic capital budgeting: asset replacement under market uncertainty , 2002, OR Spectr..

[102]  Christiane Lemieux,et al.  Efficiency improvement by lattice rules for pricing Asian options , 1998, 1998 Winter Simulation Conference. Proceedings (Cat. No.98CH36274).

[103]  Felisa J. Vázquez-Abad,et al.  Accelerated simulation for pricing Asian options , 1998, 1998 Winter Simulation Conference. Proceedings (Cat. No.98CH36274).

[104]  William J. Morokoff.  Generating Quasi-Random Paths for Stochastic Processes , 1998, SIAM Rev..

[105]  J. Patel,et al.  Handbook of the normal distribution , 1983 .

[106]  Richard Isaac.  The Pleasures of Probability , 1996 .

[107]  Alfred Taudes,et al.  Options Analysis of Software Platform Decisions: A Case Study , 2000, MIS Q..

[108]  Mark J. Warshawsky,et al.  Making Retirement Income Last a Lifetime , 2001 .

[109]  John Campbell,et al.  Real options analysis of the timing of IS investment decisions , 2002, Inf. Manag..

[110]  James E. Smith,et al.  Options in the Real World: Lessons Learned in Evaluating Oil and Gas Investments , 1999, Oper. Res..

[111]  Paul Bratley,et al.  A guide to simulation (2nd ed.) , 1986 .

[112]  K. S. Tan,et al.  Quasi-Monte Carlo Methods in Numerical Finance , 1996 .

[113]  N. L. Johnson,et al.  Continuous Univariate Distributions. , 1995 .

[114]  D. Leonard-Barton,et al.  Wellsprings of Knowledge: Building and Sustaining the Sources of Innovation , 1995 .

[115]  Prakash P. Shenoy,et al.  A Forward Monte Carlo Method For Solving Influence Diagrams Using Local Computation , 2000 .

[116]  Alexander J. Triantis,et al.  Capital Budgeting for Interrelated Projects: A Real Options Approach , 1998, Journal of Financial and Quantitative Analysis.

[117]  Edward H. Bowman,et al.  Real Options Analysis and Strategic Decision Making , 2001 .

[118]  Christoph H. Loch,et al.  Special Issue on Design and Development: Project Management Under Risk: Using the Real Options Approach to Evaluate Flexibility in R...D , 2001, Manag. Sci..

[119]  M. Fu,et al.  Pricing American Options: A Comparison of Monte Carlo Simulation Approaches ⁄ , 2001 .

[120]  Eric R. Ziegel,et al.  Analysis of Financial Time Series , 2002, Technometrics.

[121]  Eugene Seneta,et al.  Bortkiewicz's Data and the Law of Small , 1987 .

[122]  Donald E. Knuth,et al.  The art of computer programming. Vol.2: Seminumerical algorithms , 1981 .

[123]  Donald Ervin Knuth,et al.  The Art of Computer Programming , 1968 .

[124]  Frederick F. Reichheld,et al.  The Loyalty Effect: The Hidden Force Behind Growth, Profits, and Lasting Value (Эффект лояльности: скрытая движущая сила роста, прибыли и постоянной ценности) , 1996 .