Applies to:
@RISK for Excel 4.x and Up
@RISK for Project 4.1

How to Create a Regression Equation from Calculated Sensitivities

 

Overview

@RISK sensitivity results can be used to construct a full linear regression equation for an output variable, provided the regression coefficients are expressed in the correct units. This article explains how to obtain those coefficients and how to calculate the constant term when it is not directly available.

Obtain the Correct Regression Coefficients

To create a valid regression equation, you must use actual regression coefficients, expressed in units of output per unit of input.

These coefficients can be obtained directly using the RiskSensitivity( ) worksheet function. If you are using regression values or mapped regression values displayed in a tornado diagram, those values are typically scaled and must be descaled before they can be used in a regression equation.

Regression Equation Form

The regression equation has the form:

Y = b0 + b1X1 + b2X2 + b3X3 + …

In this equation:
• Y is the @RISK output variable
• b0 is the constant (intercept) term
• b1, b2, b3, … are the regression coefficients (descaled if necessary)
• X1, X2, X3, … are the @RISK input variables

Determining the Constant Term (b0)

In @RISK 6.0 and newer, the constant term can be obtained directly from RiskSensitivity( ) by specifying a result type of 4.

In earlier versions of @RISK, the constant term is not reported directly but can be calculated. The regression line is guaranteed to pass through the point where all inputs and the output are at their mean values.

Use the following formula:

b0 = Ȳ − b1X̄1 − b2X̄2 − b3X̄3 − …

Where:
• Ȳ is the mean value of the output
• X̄1, X̄2, X̄3, … are the mean values of the input variables

Mean values can be obtained from the Results Summary window or the Detailed Statistics window.

Once b0 is calculated, the regression equation is complete.

Validity of the Regression Equation

The regression coefficients are global properties of the simulation results. The equation is valid across the full range of input values used in the simulation.

Each regression coefficient represents the best-fit line through all simulation iterations, with all points weighted equally. The regression equation accounts for all iterations of all included variables.

Effect of Skewed Data

If the data are skewed or if different regions of the data cloud behave differently, the regression equation still provides the best overall fit. However, residuals may be larger in some regions and smaller in others, similar to behavior seen in standard two-variable regression.

Important Note

This approach assumes that all meaningful input variables affecting the output are included in the regression. If significant inputs are omitted, the regression equation will be less effective and may not adequately represent the system behavior.

 

Related Articles:

Regression Coefficients in Your Worksheet.

Interpreting Regression Coefficients in Tornado Graphs.