Financial Modeling with Crystal Ball and Excel, + Website

by Charnes, John

• ISBN13: 9781118175446
• ISBN10: 1118175441
• Edition: 2nd
• Format: Paperback
• Copyright: 2012-06-05
• Publisher: Wiley

Summary

Financial Modeling with Crystal Ball and Excel 2E provides tools and techniques you to need know to perform spreadsheet simulation, and answers the essential question of why risk analysis is vital to the decision-making process, whatever the problem you are facing in finance and investment. After reviewing the basics, this book covers how to define and refine probability distributions in financial modeling, and exhaustively reviews the concepts behind the simulation modeling process. It discusses simulation controls and analysis of simulation results. Exercises models help you apply risk analysis to such areas as derivative pricing, cost estimation, portfolio allocation and optimization, design analysis, and cash flow analysis. The tools and techniques reviewed are designed to make anyone expert in financial modeling and simulation. They will help you immediately develop essential skills in the areas of areas of valuation, pricing, hedging, trading, risk management, project evaluation and portfolio management. 

Author Biography

John Charnes, PhD, MBA, is President of the Risk Analytics and Predictive Intelligence Division (RAPID) of Syntelli Solutions Inc. Prior to this, he was finance department chair at the University of Kansas and senior vice president of global portfolio strategies at Bank of America. Charnes created the Crystal Ball Training CD, a multimedia course on the basic elements of stochastic modeling with Crystal Ball, acquired by Oracle. His specialty is the application of computer simulation and statistical methods for identifying and solving business problems, including the use of simulation for option pricing and hedging with derivatives to comply with Financial Accounting Standard (FAS) 133.

Table of Contents

Preface	p. xi
Acknowledgments	p. xvii
About the Author	p. xix
Introduction	p. 1
Financial Modeling	p. 2
Risk Analysis	p. 2
Monte Carlo Simulation	p. 4
Risk Management	p. 8
Benefits and Limitations of Using Crystal Ball	p. 9
Analyzing Crystal Ball Forecasts	p. 11
Simulating a 50-50 Portfolio	p. 11
Varying the Allocations	p. 22
Presenting the Results	p. 27
Building A Crystal Ball Model	p. 29
Simulation Modeling Process	p. 29
Denning Crystal Ball Assumptions and Forecasts	p. 30
Running Crystal Ball	p. 33
Sources of Error	p. 34
Controlling Model Error	p. 36
Selecting Crystal Ball Assumptions	p. 37
Crystal Ball's Basic Distributions	p. 37
Using Historical Data to Choose Distributions	p. 55
Specifying Correlations	p. 64
Using Decision Variables	p. 79
Denning Decision Variables	p. 79
Decision Table with One Decision Variable	p. 81
Decision Table with Two Decision Variables	p. 87
Using OptQuest	p. 98
Selecting Run Preferences	p. 105
Trials	p. 105
Sampling	p. 109
Speed	p. 111
Options	p. 113
Statistics	p. 115
Net Present Value and Internal Rate of Return	p. 117
Deterministic NPV and IRR	p. 117
Simulating NPV and IRR	p. 119
Capital Budgeting	p. 123
Customer Net Present Value	p. 133
Modeling Financial Statements	p. 137
Deterministic Model	p. 137
Tornado Chart and Sensitivity Analysis	p. 138
Crystal Ball Sensitivity Chart	p. 139
Conclusion	p. 143
Portfolio Models	p. 145
Single-period Crystal Ball Model	p. 145
Single-period Analytical Solution	p. 148
Multi-period Crystal Ball Model	p. 149
Value at Risk	p. 155
VaR	p. 155
Shortcomings of VaR	p. 157
Conditional Value at Risk	p. 157
Simulating Financial Time Series	p. 163
White Noise	p. 163
Random Walk	p. 165
Autocorrelation	p. 166
Additive Random Walk with Drift	p. 170
Multiplicative Random Walk Model	p. 173
Geometric Brownian Motion Model	p. 176
Mean-reverting Model	p. 180
Financial Options	p. 187
Types of Options	p. 187
Risk-neutral Pricing and the Black-Scholes Model	p. 188
Portfolio Insurance	p. 192
American Option Pricing	p. 194
Exotic Option Pricing	p. 197
Bull Spread	p. 201
Principal-protected Instrument	p. 201
Real Options	p. 205
Financial Options and Real Options	p. 205
Applications of Real Options Analysis	p. 206
Black-Scholes Real Options Insights	p. 209
Real Options Valuation Tool	p. 211
Credit Risk	p. 221
Expected Loss	p. 221
Credit Risk Simulation Model	p. 223
Conditional Value at Risk	p. 225
Using CVaR to Manage Credit Risk	p. 227
Construction Project Management	p. 229
Project Description	p. 229
Choosing Construction Methods	p. 231
Risk Analysis	p. 231
Stochastic Optimization	p. 234
Oil and Gas Exploration	p. 235
Well Properties	p. 235
Statistical Models	p. 236
Conclusion	p. 239
Crystal Ball's Probability Distributions	p. 241
Bernoulli	p. 241
Beta	p. 243
Beta PERT	p. 244
Binomial	p. 246
Custom	p. 247
Discrete Uniform	p. 251
Exponential	p. 252
Gamma	p. 254
Geometric	p. 255
Hypergeometric	p. 257
Logistic	p. 259
Lognormal	p. 260
Maximum Extreme	p. 262
Minimum Extreme	p. 263
Negative Binomial	p. 264
Normal	p. 266
Pareto	p. 267
Poisson	p. 269
Student's t	p. 270
Triangular	p. 272
Uniform	p. 273
Weibull	p. 275
Yes-No	p. 276
Generating Assumption Values	p. 279
Generating Random Numbers	p. 279
Generating Random Variates	p. 282
Latin Hypercube Sampling	p. 284
Variance Reduction Techniques	p. 287
Using Crystal Ball to Value an Asian Option	p. 288
Antithetic Variates	p. 289
Control Variates	p. 289
Comparison	p. 290
Conclusion	p. 292
About the Download	p. 293
Glossary	p. 297
References	p. 301
Index	p. 311
Table of Contents provided by Ingram. All Rights Reserved.