Behavioral and Simulation analysis is often used in financial modeling and analysis to understand the tradeoffs between quantifiable risks and returns, using a systematic methodology that adjusts for a variety of financial and business variables, to arrive at a range of outcomes that are then used to make business decisions.
The widespread availability and use of sophisticated financial modeling software applications that extend the functionality of the basic spreadsheet has greatly enhanced the use of behavioral analysis in financial modeling and analysis.
In this article, we will touch on a number of different behavioral & simulation analysis and techniques, namely that of sensitivity and scenario analysis, decision trees and financial simulations.
Sensitivity and Scenario Analysis in Financial Modeling
In a typical financial model, variability in cash inflows and net present value (NPV) are often modeled using sensitivity and scenario analysis. Sensitivity analysis uses several possible values for a given variable, such as cash inflows, to assess that variable’s impact on the company’s return, measured here by the NPV. This technique is often used by financial analysts to get a feel for the variability of return in response to changes in a key variable.
One of the most common sensitivity approaches used in financial modeling is to estimate the NPV in relation to a number of different estimates of cash inflow, which vary from the optimistic case (best) estimates for cash flow, to the base case (expected) estimate of cash inflow, to the pessimistic case (worst) estimate of cash inflow.
The NPV range can then be determined by subtracting the pessimistic outcome NPV from the optimistic outcome NPV. Often, by putting forward an NPV range, a good financial analyst is able to present a balanced view of a business case by highlighting both the potential downside risks as well as the potential upside of the investment, allowing business executives to make calculated decisions based on their risk appetite.
Scenario analysis in financial modeling is similar to sensitivity analysis, but broader in scope. Scenario analysis evaluates the impact of simultaneous changes in a number of variables, such as cash inflows, cash outflows, the cost of capital, or even leading revenue / cost growth rates. The combined effect of changes in these variables are then applied to evaluate the impact on the company’s return.
For instance, a company could evaluate the impact of a high or low risk-free interest rate environment on a company’s NPV. Each scenario will affect the company’s cash inflow, cash outflows, and cost of capital, thereby resulting in different levels of NPV. A financial analyst can then use these NPV estimates to assess the risk involved with respect to the interest rate environment.
Decision Trees in Financial Modeling
Decision trees use diagrams to map the various investment decision alternatives and payoffs, along with their probabilities of occurrence.
Decision trees are named as such due to their resemblance to the branches of a tree, and rely on estimates of the probabilities associated with the outcomes (payoffs) of competing courses of action. The payoffs of each course of action are weighted by the associated probability; the weighted payoffs are summed; and the expected value of each course of action is then determined.
Clearly, the alternative on the decision tree that provides the highest expected value will be chosen as the preferred course of action in a financial modeling and analysis exercise.
Using Financial Simulations in Financial Modeling
Financial simulation techniques, such as Monte Carlo Simulations, are statistical based behavioral approaches that apply predetermined probability distributions and random numbers to estimate risky outcomes.
By tying the various cash flow components in a financial model together in a mathematical model and repeating the process several times, the financial analyst can develop a probability distribution of project returns.
Assume that we are attempting to generate an NPV simulation model for a financial modeling exercise. The financial analyst will undertake a process of generating random numbers and use the predetermined probability distributions for cash inflows and cash outflows to determine values for each of these variables.
Consequently, by substituting these values into the mathematical model, and repeating the process perhaps a thousand times, a probability distribution of NPV values can be created.
Whilst this example only uses gross cash inflows and cash outflows as the simulated outcome, more sophisticated financial simulations can obviously be applied to individual cash inflow and outflow components, such as sales volume, sales price, raw material cost, labor cost, maintenance cost, and so on. From the distribution of returns, a financial analyst can determine not only the expected value of the return but also the probability of achieving or surpassing a given return.
Financial simulations (including Monte Carlo simulations) are in general considered far superior as compared to other behavioral analysis techniques used in financial modeling and analysis, as it enables financial analysts to view a continuum of risk-return tradeoffs rather than a single point estimate.
There is an excellent Monte Carlo Simulation Excel Add-In available in the Finance 3.0 forums, which takes a random number input (using either a manual data input or Excel’s RAND function) and recalculates the Excel worksheet for as many repetitions as you request. It outputs the results to a new Excel worksheet with summary statistics and a histogram. The Excel add-in allows you to run Monte Carlo simulations in more than one cell and also includes several other options.
This Excel add-in require you to be signed in to Finance 3.0 for free access and download.
Not a member? Sign up to Finance 3.0 today, free and quick registration process.
Visit Finance 3.0 - don't miss out on this high quality financial learning opportunity