@RISK is used for a wide variety of applications in financial forecasting, investments, and banking. Following are just a few examples with downloadable sample spreadsheets. Download and install a free trial version of @RISK to view the models in full.
For more examples of @RISK in finance, see the following books published by Palisade:
» Financial Models using Simulation and Optimization
» Financial Models using Simulation and Optimization II: Investment Valuation, Options Pricing, Real Options, and Product Pricing Models
» Decision Making under Uncertainty with RISKOptimizer
» Read real-world case studies using @RISK in finance.
Models of the prices of assets (stocks, property, commodities) very often assume a random walk over time, in which the periodic price changes are random, and in the simplest models are independent of each other. The future price level of the asset may result in some contract or payoff becoming valuable, such as in the case of financial market options. In these cases, the value of the contract (contingent payment or option) is calculated as the average discounted value of the future payoff. In the special case of European options on a traded underlying asset, the value calculated from the simulation may be compared with mathematical formulas that analytically provide the valuation, such as the Black-Scholes equation. In many more complex cases, the pertinent analytic formulas may be unknown or very complex to derive, and one may wish to rely on simulation techniques. This particular model compares the average simulated payoff for European Call and Put options with the Black-Scholes valuation.
Such examples can be discussed further during Palisade training courses, and may include generalizations, such as the use of different payoff formulas (including path-dependent ones), of mean-reverting random walks, of correlated random walks for several assets, as well as the valuation of switching options (e.g. where the user may switch to the cheaper of two sources of energy). For the case of the correlated random walks of multiple assets with a constant correlation coefficient, these can be set up using the Correlated Time Series feature of @RISK.
Example model: AssetPrices.Options.BS.Multi.xls
Discounted cash flow (DCF) calculations are a frequent example of the use of @RISK. In the example model, the sources of risk are the revenue growth rate and the variable costs as a percentage of sales. After taking into account the assumed investment, and applying a discount factor, the DCF is derived. Following the simulation, the average (mean) of the DCF is known as the net present value (NPV). In this example, the results show that the average DCF is positive (about 40), whereas the probability of a negative DCF is about 15%. The decision as to whether to proceed or not with this project will therefore depend on the risk perspective or tolerance of the decision-maker. This example has also been extended to calculate the distribution of bonus payments on the assumption that a bonus is paid whenever the net DCF is larger than a fixed amount (such as 50). It also uses some of the @RISK Statistics functions RiskMean, RiskTarget, and RiskTargetD to work out the average net DCF, the probability that the net DCF is negative and the probability that a bonus is paid.
Example model: CashFlow.xls
This model demonstrates the analysis of uncertainty in a financial forecast. Imagine you are deciding whether to launch a new product line. A simplified analysis of the cash flow activity of this venture might look as shown below. Since most of the elements of the model involve the prediction of future events, they all involve uncertainty. The values in cells in green have all been replaced with @RISK distribution formulas. The cells in red have been marked as @RISK outputs so a detailed analysis can be performed on their simulated results. By adding @RISK distributions to your financial models, you can go beyond the simplistic "best-case/worst-case" analysis that can lead to bad business decisions.
Example model: Finance.xls
This is a basic example of financial statement modeling. Such models are built for general forecasting purposes, including financing needs and credit analysis. In this example, a company has a fairly healthy forecasted cash flow for 2009, but also aims to reduce its long-term debt in 2009 to $70,000 from $97,000 in 2008. The company is forecasting that in the base case its financial position will sufficient to do this. However, it wishes to analyze the probability that a short-term financing facility will be needed. The short-term debt (which is zero in the base case) is therefore set as an @RISK output, and the probability that it is non-zero can be seen both from the Results Summary window, and also from the RiskTargetD function built directly into the model sheet.
Example model: FinStatements@RISK.xls
When a company develops a new product, the profitability of the product is highly uncertain. Simulation is an excellent tool to estimate the average profitability and riskiness of new products. Imagine Pigco is thinking of marketing a new drug used to make hippos healthier. The model below sets up the variables involved in marketing the new product, such as market size, use of the drug, whether competitors enter the market, etc. @RISK distributions (shown in green) are used to illustrate the uncertainty. We will make the NPV of our 5 year profits our output cell. Analyzing the results of this output will help Pigco decide whether introducing the hippo drug would be profitable or not.
This example was taken from Chapter 28 of Financial Models using Simulation and Optimization by Wayne Winston, published by Palisade Corporation, where a detailed, step-by-step explanation can be found. It is also explained further in the @RISK User's Guide.
Example model: Hippo.xls
When a company develops a new product, the profitability of the product is highly uncertain. Simulation is an excellent tool to estimate the average profitability and riskiness of new products. Imagine Pigco is thinking of marketing a new drug used to make hippos healthier. The model below sets up the variables involved in marketing the new product, such as market size, use of the drug, whether competitors enter the market, etc. @RISK distributions (shown in green) are used to illustrate the uncertainty. We will make the NPV of our 5 year profits our output cell. Analyzing the results of this output will help Pigco decide whether introducing the hippo drug would be profitable or not.
If any of the inputs such as price, variable costs, interest rate, competition, or market share can be determined with greater precision, Advanced Sensitivity Analysis can help decide which ones to focus on. For example, one can set up an analysis stepping through seven values for each of the inputs in the range of +/-10% from the current cell value. If mean is selected as the "tracking statistic," the Price and Year 1 Market Size stand out as the most significant in the resulting Tornado diagram. On the other hand, if the tracking statistic is standard deviation, the Tornado diagram shows Year 1 best share as the most significant input, followed closely by Price and Year 1 Market Size. On this basis a decision can be made to research these three inputs further.
This example was taken from Chapter 28 of Financial Models using Simulation and Optimization by Wayne Winston, published by Palisade Corporation, where a detailed, step-by-step explanation can be found. It is also explained further in the @RISK User's Guide.
Example model: HippoSensitivity.xls
In this example, distribution functions are used to model uncertain trends. Both a simple random trend and a random walk are illustrated. For each iteration, a new value is sampled for each period in a trend. This allows your results to include the effects of all possible trend values as opposed to a single set of best estimates. In the random trend there is no correlation between periods. In the random walk, the value in each period is influenced by the value of the previous period.
Example model: Rate.xls
In this example, Sensitivity Simulation capabilities are being used to analyze how price impacts the simulation results for sales of a new product. The price is not uncertain, but has to be chosen by the decision-maker. Sensitivity Simulation lets you enter all prices being considered using @RISK’s Simtable function. In this example, the function RiskSimtable({25,50,75,100}) lists 4 prices: $25, $50, $75, $100. In the Simulation Settings box, make sure the # Simulations is set to 4, the number of values in the Simtable function. Examine the Summary Graphs for the Sales Revenue output range from each of the simulations to compare how different price levels affect the results of the model.
Example model: SenSim.xls
Anybody who owns a portfolio of investments knows there is a great deal of uncertainty about the future worth of the portfolio. The concept of value at risk (VAR) has been used to help describe a portfolio's uncertainty. Simply stated, the value at risk of a portfolio at a future point in time is usually considered to be the fifth percentile of the loss in the portfolio's value at that point in time. In other words, there is considered to be only one chance in 20 that the portfolio's loss will exceed the VAR. To illustrate the idea, suppose a portfolio today is worth $100. We simulate the portfolio's value one year from now and find there is a 5% chance that the portfolio's value will be $80 or less. Then the portfolio's VAR is $20 or 20%. The following example shows how @RISK can be used to measure VAR. The example also demonstrates how buying puts can greatly reduce the risk in a stock. The two outputs represent the range of the percentage gain if we do not buy a put vs. the percentage gain if we do buy a put. The results illustrate there is a greater chance of a big loss if we do not buy the put, although the average return is slightly higher if we do not buy the put.
This example was taken from Chapter 45 of Financial Models using Simulation and Optimization by Wayne Winston, published by Palisade Corporation, where a detailed, step-by-step explanation can be found. It is also explained further in the @RISK User's Guide.
Example model: Var.xls
Anybody who owns a portfolio of investments knows there is a great deal of uncertainty about the future worth of the portfolio. The concept of value at risk (VAR) has been used to help describe a portfolio's uncertainty. Simply stated, value at risk of a portfolio at a future point in time is usually considered to be the fifth percentile of the loss in the portfolio's value at that point in time. In other words, there is considered to be only one chance in 20 that the portfolio's loss will exceed the VAR. To illustrate the idea, suppose a portfolio today is worth $100. We simulate the portfolio's value one year from now and find there is a 5% chance that the portfolio's value will be $80 or less. Then the portfolio's VAR is $20 or 20%. The following example shows how @RISK can be used to measure VAR. The example also demonstrates how buying puts can greatly reduce the risk in a stock. The two outputs represent the range of the percentage gain if we do not buy a put vs. the percentage gain if we do buy a put. The results illustrate there is a greater chance of a big loss if we do not buy the put, although the average return is slightly higher if we do not buy the put.
If one put is purchased per every share in the portfolio (psr=1), VAR is reduced from 34% to 19%, but the mean profit is also reduced from 25% to 21%. Suppose an investor is not satisfied with the lower mean profit, but is unwilling to accept the VAR of 34% by buying no puts. If the minimum mean profit that is satisfactory to the investor is 23%, Goal Seek can be used to determine the reduced put to share ratio that will yield this mean profit. To perform the analysis, the selected statistic will be the mean, which is to be found "In Cell" "%age Gain with put", and which we want to bring up "To Value" 0.23, "By Changing" the put to share ratio. The analysis shows that a put to share ratio of around 0.55 yields the desired mean profit. With the option to Generate Complete Simulation Results for Solution on, we can check the @RISK Results Summary window to see that the VAR of a portfolio with 0.55 puts per every share is 26%. The investor needs to decide if this level of risk is acceptable.
This example was taken from Chapter 62 of Financial Models using Simulation and Optimization by Wayne Winston, published by Palisade Corporation, where a detailed, step-by-step explanation can be found. It is also explained further in the @RISK User's Guide. This model differs from Winston's example in that it allows different ratios of the amount of puts to the amount of shares in the portfolio.
Example model: VarGoalSeek.xls
TopRank recognizes @RISK distribution functions and incorporates them in What-If analyses. This ability provides more flexibility and accuracy in modeling the possible input values in your What-If analysis.
In this example, Jupiter Corporation is building a new model of 4-door sedan. Assuming that the car will generate sales for the next 5 years, management has identified 5 factors that can influence the total revenue during that period. Several of these factors have probability distributions associated with them. During a What-If analysis, TopRank sees the probability distributions associated with these items and performs a smart sensitivity analysis using them, stepping through the range of the distribution while spacing the steps such that each interval encompasses equal amounts of probability.
Download example model:
ProductLaunchTopRankRISK.xls
NeuralTools can be used to predict unknown values of a category dependent variable from known values of numeric and category independent variables. In this example, the neural net has learned to predict whether an auto loan applicant will be making timely payments, late payments, or default on the loan. Evolver can be used to find the loan amount that will raise the probability that this applicant falls in the "timely payments" category to a target value of 90%.
Download example model: AutoLoansWithEvolver.xls
