Minimum Edition: @RISK Industrial. A manufacturer of fuel efficient cars believes that demand for this type of cars might increase in the next few years, so it wants to expand its capacity. To finance this, the company plans to divert profits from car sales to a fund for eventual expansion. The model uses RISKOptimizer to find an optimal plan for doing this.
Minimum Edition: @RISK Industrial. This model uses detailed financial calculations on a number of project worksheets to obtain project NPVs and present values of project costs. The project worksheets include a number of uncertain quantities, and even correlations for a couple of the projects, so that their "bottom lines" are uncertain. These bottom lines feed into a portfolio optimization model. Each project has a chance of failing from a technical standpoint. If it fails, it is not part of the total portfolio, that is, its NPV and costs are not included.
The first example uses a portfolio model to illustrate the "conditional value at risk" concept from finance, in this case the mean return from a portfolio conditional on the fact that the return is below the 5th percentile of all returns.
Minimum Edition: @RISK Industrial. The second spreadsheet builds on the previous example by using RISKOptimizer to find the portfolio weights that maximize the conditional value at risk.
This simulation model follows a sample of 200 customers who each begin a year in a certain credit rating category and with a certain amount of credit exposure. By the end of the year, each customer has either defaulted or not, and in case of default, the percentage that can be recovered is uncertain. The simulation finds the total amount of loss from these customers and this total's percentage of the total amount of exposure. Also, it uses the RiskPercentile function at several confidence levels to find the amounts of reserve required to be confident of covering the losses.
This model illustrates a projection of property damages and human costs from potential natural disasters over a 5-year period that might be assessed by an insurance company. It assumes that each disaster either occurs or doesn't occur in a given year with given probabilities. Then possible property damages and possible human costs are simulated. In each of these, assuming that a disaster occurs, the total damage or cost is composed of the random number of people affected and the damage or cost per person affected.
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.
This model illustrates how uncertainties can be built into an financial statement (income statement, balance sheet, cash flows) to make future projections.
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.
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.
Minimum Edition: @RISK Industrial. The purpose of this model is to schedule starting years of 10 projects. Each project has a random expenditure in its initial year, a random revenue in the following year, and then a stream of annual revenues. The annual revenues for any project remain constant for a given number of years and then decline at a given annual rate. RISKOptimizer is used to determine the starting years of the projects that maximize the NPV of cash flows from all projects.
This model of a company's multiple projects over a 12-month horizon. Each project is planned to start in a given month, and from that month on, it has anticipated costs, some of which are known (or 0) and some of which are uncertain. There are possible random delays (or, for a few projects, possible earlier starting months), which shift the cost schedule to the right (or the left). In addition, each project has a 5% chance of failing in any month after its actual starting month. If it fails in a given month, that month's costs plus any remaining months' costs are not incurred.
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.
This simple model illustrates two ways variable interest rates on a loan might be simulated. In the first model, the yearly interest rates are generated independently of one another. Each is normally distributed with mean 10% and standard deviation 1%. In the second model, a random walk model, the first interest rate is normally distributed with mean 10% and standard deviation 1%, but each succeeding interest rate is normally distributed with mean equal to the actual previous rate and standard deviation 1%.
This model illustrates variance at risk (VAR) in the context of a put on a stock. It follows two portfolios: one where the investor purchases shares of the stock and no puts, and one where the investor purchases shares of the stock and a put on the stock. Simulation shows that clearly how put acts as a hedge on the stock. The VAR with the put is about a 19.8% loss, compared to a 33.9% loss without the put.
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.
This model, really a template, is used to value European stock options: calls and/or puts. The model uses the well-known lognormal model of stock price changes to simulate the future stock price. This formula depends on the time till expiration, the riskfree rate, and the volatility of the stock, as well as a standard normal variate.
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.
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%.
130 East Seneca Street
Suite 505
Ithaca, NY 14850
800 432 RISK (US/Can)
+1 607 277 8000
+1 607 277 8001 fax
sales@palisade.com
+1 607 277 8000 x318
+54-1152528795 Argentina
+56-25813492 Chile
+507-8365675 Panamá
+52 55 5350 2852 México
+511-7086781 Perú
+57-15085187 Colombia
servicioalcliente@palisade.com
ventas@palisade.com
www.palisade-lta.com