Fabricante do software líder mundial em análise de risco e de decisão
ENGLISH I ESPAÑOL I PORTUGUÉS I FRANÇAIS I DEUTSCH I 日本語 I 中文 I РУССКИЙ
Live Chat
Modelos Setoriais
@RISK em Finanças

@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

» Ver todos os estudos de caso finanças.

Random Walk para Precificação de Ativos e
Avaliação de Opções

Os modelos de preços de ativos (ações, propriedades, commodities) em geral assumem um passeio aleatório (random walk) ao longo do tempo, no qual as mudanças periódicas de preço são aleatórias, e nos modelos mais simples, independentes entre si. O nível de preço futuro de um ativo pode resultar de algum contrato ou pagamento possuir valor, como no caso de opções no mercado financeiro. Nestes casos, o valro do contrato (pagamento contingente ou opção) é calculada como a média do valor presente líquido descontado do pagamento futuro. No caso especial de opções Européiais de um ativo negociável subjacente, o valor calculado da simulação pode ser comparado com a fórmula matemática que fornece a solução analítica, como a equação de Black-Scholes. Em casos muito mais complexos, a fórmula analítica pertinente pode ser desconhecida ou muito complexa de se derivar e pode ser desejável o uso de técnicas de simulação. Este modelo em particular compara a média de pagamentos simulados para opções Européias (Put e Call) com a avaliação por Black-Scholes.

Tais exemplos podem ser discutidos mais amplamente durante cursos de treinamento da Palisade, e podem incluir generalizações tais como o uso de diferentes fórmulas de pagamento (incluindo aquelas dependentes do caminho), random walks de reversão à média, correlacionados para diversos ativos, bem como a avaliação de opções de troca ou switch (onde o usuário pode altenar entre para a mais barata entre duas formas de energia). No caso de random walks correlacionados de múltiplos ativos com um coeficiente de correlação constante, os mesmos podem ser configurados usando o ícone Séries Temporais Correlacionadas na janela de Correlação.

Baixar do modelo-exemplo: PrecosAtivosOpcoes.BSMulti.xls

Fluxo de Caixa Descontado (FCD)
Cálculos de Fluxo de Caixa Descontado (FCD) são um exemplo frequente do uso do @RISK. No modelo abaixo, as fontes de risco são a taxa de crescimento das receitas e os custos variáveis como um percentual das vendas. Após levar em conta o investimento e aplicar um fator de desconto, o FCD é determinado. Seguindo-se à simulação, a média do FCD é conhecida como valor presente líquido (VPL). Neste exemplo, os resultados mostram que o FCD médio é positivo (cerca de 40), e a probabilidade de um VPL negativo é de cerca de 15%. A decisão de proceder ou não com este projeto dependerá da perspectiva de risco (tolerância) do tomador de decisão.

Este exemplo também foi extendido para calcular a distribuição de pagamentos de bônus na suposição de que um bônus é pago sempre que o FCD líquido for maior que uma quantidade fixa (como 50), utilizando também algumas funções Estatísticas do @RISK (RiskMean, RiskTarget, RiskTargetD) para calcular o FCD líquido médio, a probabilidade de FCD líquido negativo e a probabilidade de pagamento de bônus.

Baixar do modelo-exemplo: FluxoDeCaixa.xls


Financial Forecasting
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


Financial Statement Forecasting
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


New Product Profitability
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


New Product Profitability: Advanced Sensitivity Analysis
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


Projecting Interest Rates and Other Trends:
Random Trend and Random Walk

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


Impact of Price or Other Variables on Profit:
Sensitivity Simulation

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


Valor em Risco (VAR - Value at Risk)

Qualquer pessoa que possua um portfolio de investimento sabe que há uma grande grau de incerteza sobre o valor futuro do portfolio. Recentemente o conceito de value at risk (VAR) tem sido usado para ajudar a descrever a incerteza do portfollio. De forma simples, o value at risk de um portfolio usualmente é considerado o percentil 5% de perda do valor de um portfolio neste ponto do tempo. Em outras palavras, é considera-se que há apenas uma chance em 20 que a perda do portfolio exceda o VAR. Para ilustrar a idéia, suponha que o portfolio hoje vale $100. Simulamos o valor do portfolio em um ano e encontramos uma chance de 5% que o valor do portfolio seja $80 ou menos. Asism o VAR do portfolio é $20 ou 20%. O exemplo seguinte mostra como o @RISK pode ser usado para medir o VAR. O exemplo também demonstra como comprar puts pode reduzir bastante o risco de uma ação. Os dois outputs, C24 e C25, representa a faixa de ganho percentual se não compramos a put contra o ganho percentual se compramos a put. Os resultados ilustram que hã uma chance maior de uma perda grande se não compramos a put, embora o retorno médio seja ligeiramente superior se não compramos a put.

Este exemplo foi extraído do Capítulo 45 do livro Financial Models using Simulation and Optimization de Wayne Winston, publicado pela Palisade Corporation, onde uma explicação detalhada passo-a-passo pode ser encontrada. O exemplo também é explicado em mais detalhe no Manual do @RISK.

Baixar do modelo-exemplo: Var.xls


Value at Risk (VAR) using @RISK Goal Seek
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 and @RISK


Product Launch
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 and Evolver


Auto Loans
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



Palisade Brasil
Praia de Botafogo,
nº 501 Sala 101, Botafogo
22250-000 Rio de Janeiro-RJ
+55 (21) 3958 1443
+1 607 277 8000 x318 tel
800 432 7475 x318 E.U.A.
vendas@palisade.com
Palisade
800 432 RISK (US/Can)
+1 607 277 8000
sales@palisade.com
www.palisade.com
Palisade EMEA & India
+44 1895 425050
salesEMEA@palisade.com
www.palisade.com
Palisade Asia-Pacific
+61 2 9252 5922
salesAP@palisade.com
www.palisade.com
Palisade アジア・
パシフィック
東京事務所
+81 3 5456 5287 tel
sales.jp@palisade.com
www.palisade.com/jp/
Palisade Latinoamérica
+1 607 277 8000 x318
800 432 RISK (EEUU/Canadá)
+1 607 277 8001 fax
+54 (11) 5252-8795 Argentina
+56 2581-3492        Chile
+507 836-5675        Panamá
+52 55 5350 2852   México
+51 1 708-6781       Perú
+57 1 508-5187       Colombia
servicioalcliente@palisade.com
ventas@palisade.com
www.palisade-lta.com