Sensitivity Analysis
Sensitivity analysis is a decision-making technique used to evaluate how different values of an independent variable impact a dependent variable under a given set of assumptions. It systematically alters model inputs to determine their effects on outputs, helping decision-makers understand the robustness of their models.
This technique is applied within specific boundaries determined by one or more input variables. For example, analysts may use sensitivity analysis to assess how changes in interest rates affect the price of a bond. In any budgeting or forecasting process, certain variables such as tax rates, interest rates, inflation, headcount, and operating expenses are inherently uncertain. Sensitivity analysis helps answer the key question:
Sensitivity Reports in Excel
- Allowable Increase/Decrease: Indicates how much a decision variable coefficient in the objective function can change without altering the values of the decision variables.
- While the coefficients may change, the optimal decision variables may remain constant, though the objective value itself will shift.
- The 100% Rule: Multiple coefficient changes can occur without affecting the solution, provided the total percentage deviation does not exceed 100%.
- Reduced Cost: Represents how much more attractive a variable’s coefficient must become before the variable is worth including in the solution (Excel’s reported sign can be ignored).
- Shadow Price: The change in the objective function value when a constraint is relaxed or tightened by one unit.
- Valid within the allowable increase or decrease for the constraint. Beyond this range, the shadow price becomes less favorable due to diminishing returns.
- To test if a constraint is binding, compare its Final Value with the Constraint R.H. Side. If it is non-binding, the shadow price equals zero.
Linearity in Non-Linear Problems
- Example: Avoid using ≠. For binary integer variables, the condition X + Y ≠ 1 can be rewritten as X = Y.
- For continuous decision variables linked to binary variables, the constraint often takes the form: continuous expression ≤ (large constant × binary variable).
Simulation in Sensitivity Analysis
Procedure:
- Express the objective function in terms of decision variables.
- Define a search range and incremental values for decision variables.
- Run simulations for each increment using tools such as Crystal Ball.
- Compare expected mean outcomes and confidence intervals, applying hypothesis testing if necessary to determine the best solution.
Confidence Intervals
- 95% confidence interval: ± 1.96σ
- 90% confidence interval: ± 1.645σ
Useful Functions in Sensitivity Analysis
- MIN(x, y) / MAX(x, y): Useful for revenue calculations constrained by either demand or production capacity.
- ~N(μ, σ): Normal distribution with mean μ and standard deviation σ.
- ~U[x, y]: Uniform distribution with minimum x and maximum y.
Applications of Sensitivity Analysis
- Financial modeling (equity valuation, risk analysis)
- Budgeting and forecasting under uncertainty
- Investment decision-making
- Engineering design under varying conditions
- Policy evaluation and economic planning
FAQ's
What is sensitivity analysis in simple terms?
Sensitivity analysis is a technique used to understand how changes in input variables affect the output of a model or system. It helps answer “what-if” questions under uncertainty.
Why is sensitivity analysis important?
It identifies critical variables that influence outcomes, allowing decision-makers to prepare for risks, test assumptions, and make more informed business, financial, or policy decisions.
What is the role of shadow price in sensitivity analysis?
Shadow price shows how much the objective function (e.g., profit or cost) will change if a constraint is relaxed or tightened by one unit. It indicates the value of additional resources.
How is sensitivity analysis applied in finance?
In finance, sensitivity analysis is widely used in equity valuation, risk assessment, budgeting, and investment appraisal to test how changes in factors like interest rates, earnings, or market multiples affect outcomes.
