Sensitivity analysis
A technique used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. It is a technique for systematically changing variables in a model to determine the effects of such changes. This technique is used within specific boundaries that will depend on one or more input variables, such as the effect that changes in interest rates will have on a bond's price. In any budgeting process there are always variables that are uncertain. Future tax rates, interest rates, inflation rates, headcount, operating expenses and other variables may not be known with great precision. Sensitivity analysis answers the question, "if these variables deviate from expectations, what will the effect be (on the business, model, system, or whatever is being analyzed)?"
Sensitivity analysis is very useful when attempting to determine the impact the actual outcome of a particular variable will have if it differs from what was previously assumed. By creating a given set of scenarios, the analyst can determine how changes in one variable(s) will impact the target variable. For example, an analyst might create a financial model that will value a company's equity (the dependent variable) given the amount of earnings per share (an independent variable) the company reports at the end of the year and the company's price-to-earnings multiple (another independent variable) at that time. The analyst can create a table of predicted price-to-earnings multiples and a corresponding value of the company's equity based on different values for each of the independent variables.
Microsoft Excel can generate a sensitivity report in two parts - a changing cell report and a constraints report.
|
1. Cell |
2. Name |
3. Final
Value |
4.
Reduced Cost |
5.
Objective Coefficient |
6.
Allowable Increase |
7.
Allowable Decrease |
- Changing Cells (Adjustable Cells) - For the Changing Cells report, the allowable increase and decrease refer to how much the objective function decision variable coefficient can change without changing the values of any of the decision variables. However, the objective function value will have to change if a coefficient changes and the corresponding decision variable does not change. Note though, that multiplying each term in the objective function by a constant does not change the values of the decision variables. The 100% rule can be used to determine if a change in multiple objective function coefficients will change the values of the decision variables. Under this rule, any combination of changes can occur without a change in the solution as long as the total percentage deviation from the coordinate extremes does not exceed 100%. However, the objective value would change since the objective coefficients are changing. For the purpose of this analysis, the decision variable coefficient is the effective number that is multiplied by the decision variable when the objective function is simplified so that each decision variable appears once. Reduced Cost is how much more attractive the variable's coefficient in the objective function must be before the variable is worth using. Ignore the sign reported by Excel.
- Constraints - The shadow price is the amount that the objective function value would change if the named constraint changed by one unit. The shadow price is valid up to the allowable increase or decrease in the constraint. The shadow price after the constraint is changed by the entire allowable amount is unknown, but is always less favorable than the reported value due to the law of diminishing returns. To determine if a constraint is binding, compare the Final Value with the Constraint R.H. Side. If a constraint is non-binding, its shadow price is zero.
Linearity from Non-Linear Problems
Many problems that initially may be non-linear may be made linear by careful formulation. For example, one can avoid using the inequality ≠. For binary integer variables, X + Y ≠ 1 is the same as saying X = Y.
Binary Variables
When relating continuous decision variables to binary switch variables, the following form often is useful: continuous variable expression < (some large number) (binary variable)
Simulation
Linear programming techniques assume certainty and by themselves do not deal well with significant randomness. The following is one possible procedure for maximizing or minimizing some objective function that contains random variables.
- Express the objective function in terms of the decision variable.
- Define a search range and incremental search value for the decision variable, possibly using problem information to reduce the search range.
- Run a simulation for each incremental value of the decision variable using a Monte Carlo simulator such as Crystal Ball.
- Compare the mean expected values of the objective function and their confidence intervals, possibly using statistical hypothesis testing to identify the best solution.
Confidence Intervals
95% confidence intervals: +/- 1.96 sigma, 90% confidence intervals: +/- 1.645 sigma
Useful Functions
MIN(x, y) or MAX(x, y) = x, y can be any variable (possibly a random variable), expression, or number.
Applications include revenue calculations that might be limited by either demand or production quantity.
~N (μ = x, σ = y) = Normal distribution with mean x, std dev. y; directly to right of random variable
~U[x, y] = Uniform distribution with min x, max y; directly to right of random variable.
0 Comments