
First published in 1998, Financial Models Using Simulation and Optimization quickly became one of the most widely used references for the application of simulation and optimization techniques to financial problems. Now, author Wayne Winston has done it again with a totally new volume! Financial Models II is packed with reallife examples that demonstrate how @RISK, Evolver and Excel can be used to make better financial decisions. Winston's straightforward, stepbystep approach makes his innovative techniques accessible to anyone who uses Microsoft Excel.
Like its predecessor, Financial Models II features years' worth of experience in setting up and solving complicated financial problems using Microsoft Excel and DecisionTools software. You will learn valuable analytical techniques that will help you get the most out of @RISK, Evolver, and Excel. A wide variety of financial examples are presented and solved stepbystep in over 65 chapters. This isn't a book of longwinded theoretical discussions. It's a practical, stepbystep guide that you can use immediately to build the models that you need!
Topics covered include:
• Modeling future stock prices and hedging stock price and interest rate risk. Examples include
Value at Risk (VAR), incorporating analyst forecasts, correlating stock forecasts, and
bootstrapping.• Portfolio optimization, including minimizing a portfolio's risk, finding the Efficiency Frontier,
minimizing the probability of loss, and maximizing the Sharpe Ratio.• Valuing a firm or a stock price, including modeling key drivers of firm value, incorporating
simulation into Proforma models, forecasting income of a corporation, and modeling the
profitability of a new product.• Real options and options pricing analysis, including valuing options by arbitrage
methods, BlackScholes pricing, estimating volatility using the historical and implied
volatility approaches, option pricing using the risk neutral approach, binomial and lognormal
pricing models.• Pricing and marketing models, including optimal product bundling, price response to currency
fluctuations, conjoint analysis, and discrete choice analysis.• Plus: playing craps with @RISK and simulating the NBA finals!
Examples in this book have been developed and used successfully at companies such as GM, Microsoft, and Cisco. All files discussed are included on CDROM. The book is suitable for advanced undergraduates, MBAs, and most of all practicing financial professionals for both selfstudy or education classes.
Take a look inside Financial Models II with the following excerpts:
Generating Correlated Stock Forecasts. We all know that stock prices are correlated. If we own Ford, Microsoft, and Pfizer, we know that if Pfizer goes up this increases the chance that Ford and Microsoft will also go up. How can we incorporate this correlation? We begin by estimating the correlations between the returns on our stocks. Then we use the method of Chapter 7 for incorporating analyst forecasts to generate a lognormal random variable for each stock. @RISK makes it a snap to generate correlated stock price paths.
Generating Stock Returns by Bootstrapping. As an alternative approach, we can use bootstrapping to model future stock prices. We simply take a recent sample of returns on the stocks of Ford, Microsoft, and Pfizer, and assume that for any future day, the day's return is equally likely to equal one of the days from our sample. See the file bootstrap.xls. We will simulate six months of stock prices for Microsoft, Ford, and Pfizer.
Portfolio Optimization. In this chapter we will show how to find desirable portfolios when several investments are available. We will assume that the distributions of future returns will be similar to past returns. For our example (see file hitechport.xls), let's suppose we are given monthly returns on Microsoft and GE for the years 19931997. How could we determine what fraction of our assets we should allocate to each investment during the next year? We begin by bootstrapping off our 19931997 returns to generate many (say 1000) possible distributions of returns for these three stocks. Then we use Evolver to solve for an optimal asset allocation.
Maximizing the Sharpe Ratio. The Sharpe Ratio of a portfolio equals the mean return on a portfolio minus the riskfree rate divided by the portfolio standard deviation. It turns out that whatever your riskreturn preference, you should invest your money in the risky portfolio that maximizes the Sharpe Ratio. Many Wall Street firms evaluate their traders based on their Sharpe Ratios. This requires traders who choose risky trading strategies to attain expected returns commensurate with the high risk they have taken. We can easily use Evolver to find the portfolio that maximizes the Sharpe Ratio.
Minimizing Downside Risk. Although most finance professionals measure the riskiness of a portfolio by its variance or standard deviation, this is a flawed approach to measuring risk. For example, a portfolio with a .5 chance at yielding a 100% return and a .5 chance at yielding a 20% return has a standard deviation of 40% while a portfolio which has a .5 chance at losing 40% and a .5 chance of breaking even has a 20% standard deviation. Clearly, the second portfolio is more risky even though it has a lower standard deviation. The problem is the risk we really care about is the chance of failing to meet a goal The downside risk approach attempts to minimize the average amount by which we fail to meet a target.
Forecasting Income of a Major Corporation. In many large corporations (GM is one example), different parts of the company make forecasts for quarterly net income and an analyst in the CEO's office pulls together the individual forecasts to create a forecast for the entire company's net income. In this section we show an easy way to pool forecasts from different portions of a company and create a probabilistic forecast for the entire company. With such a forecast it is easy to answer questions such as, "What is the probability our quarterly net income will exceed $4 billion?"
Forecasting Structural Costs. A major problem at manufacturing companies is predicting structural or capital costs. Suppose you are assigned to forecast the timing and amount of the structural costs associated with a new vehicle: the Knightrider. Costs in developing this vehicle are associated with three events: 1) Building a prototype. 2) Building a plant. 3) Purchasing machinery. We cannot build a plant until a prototype has been built and we cannot purchase machinery until we build a plant. Even when an event occurs, the time that the expenditures take place is usually several quarters after the event occurs.
@RISK is well suited to forecast the timing of major structural costs. The major sources of uncertainty are the time to complete a project, the time after project when the cost is incurred, and the size of the cost. The following example shows how @RISK can be used to forecast structural costs. See file Costs.xls.
Types of Real Options. The key observation that led to the field of real options was the observation that many actual investment opportunities (not just those involving stocks) may be viewed as combinations of puts and calls. Therefore, if we know how to value puts and calls we can value many actual investment opportunities. Here are some examples of real options.
Valuing Options by Arbitrage Methods. The famous BlackScholes option pricing formula is based on arbitrage pricing methods. Arbitrage pricing (for our purposes) implies that if an investment has no risk it should yield the riskfree rate of return. If this is not the case then we can create a moneymaking machine or arbitrage opportunity. By an arbitrage opportunity we mean a simulation in which we can spend $0 today and ensure we have no chance of losing money and a positive chance of making money. We can use this simple insight to price very complex financial derivatives.
Valuing a "Pioneer Option:" Web TV. In April 1997 Microsoft purchased Web TV for $425 million. Let's assume that Microsoft felt the true value of Web TV at this point in time was $300 million. Could the purchase of Web TV still be worthwhile? If the purchase of Web TV gives Microsoft the "option" to enter another business in the future, then the value of this option might outweigh the $125 million NPV of the Web TV deal.
Conjoint Analysis. It is important for marketers to understand which attributes of a product are most important to customers. Knowledge of the relative importance of product attributes can be used to design a product or predict the market share for a product. Conjoint analysis provides a way of assessing attribute importance and is particularly valuable when the set of key attributes is relatively limited. Conjoint analysis has been used to design many products such as soap, gasoline, auto insurance, airline service, and more. The wellknown hotel chain Courtyard by Marriott was designed after an extensive conjoint study. Evolver is wellsuited to finding the ideal combination of attributes in a conjoint analysis.
About Wayne Winston
Wayne Winston (Ph.D., Yale University) is a Professor of Decision Sciences
at the Kelley School of Business at Indiana University in Bloomington.
He has constantly been recognized for excellence in business education,
including being named best MBA teacher or four occasions. Dr. Winston
has written several books, including Operations
Research, Simulation Modeling Using @RISK, and Decision
Making Under Uncertainty with RISKOptimizer. He is also an instructor
for Palisade's DecisionTools Software Seminars.