Sensitivity Analysis--Tabular Output

Program Name: FCIRANGE has been developed for use with Quattro Pro v. 6 and Excel v.4 by the U.S. Army Engineer Waterways Experiment Station, Environmental Laboratory.

Audience:

FCIRANGE is intended to be used by Assessment teams (A-teams) who are developing aggregation equations for calculating a functional capacity index (FCI).

Purpose:

This spreadsheet assesses the influence of a single model variable on the FCI. It allows the user to input an equation (representing the FCI) which contains 1-15 environmental variables. It then determines the change in the FCI by varying the value of the first variable from 0 to 1 while holding all other variables (non-ncremented variables) constant. Then the second variable is varied from 0 to 1 while holding all other variables constant. Then the third, fourth, fifth, and so on until all variables have been examined. The entire process is repeated four times as the value of the non-incremented variables are varied (1, 0.5, 0.1, 0).

Use:

Within the spreadsheet, there are four steps that must be completed by the user. They are as follows:

• Step 1. In cell C4, the user must enter the number of variables present in the model being examined.

• Step 2. In cells C8-C22, the user must list the name of each environmental variable present in the model. Up to 15 variables may be examined in the model. If there are less than 15 variables in the model, remaining cells should be left blank.

• Step 3. In cell C27, the user must enter the model equation (see spreadsheet manual). Care must be taken to input the equation in the correct Quattro Pro or Excel format, taking special note of variations in different versions of Quattro Pro (e.g., the cube root function is available in Quattro Pro v. 7, but not in v. 6).

• Step 4. Finally, the user must press the "Run Macro" button located on cells G29-H31.

Output:

A report will appear below in cells A33-M51. The report will consist of the following:

1. In cells A37-A51, a listing of the variables present in the model.
2. In cells B37-B51, E37-E51, H37-E51, and K37-K51, a listing is given for the range of FCI values as the environmental variable in that row was incremented. The four columns represent the four values for the non-incremented variables (1, 0.5, 0.1, and 0, respectively).
3. In cells C37-C51, F37-F51, I37-I51, and L37-L51, a listing of the lowest FCI value is given for the environmental variable in that row.
4. In cells C37-C51, F37-F51, I37-I51, and L37-L51, a listing of the highest FCI value is given for the environmental variable in that row. (As expected, the Range column (#2) is simply the difference of the Highest (#3) and Lowest (#4) value for that FCI.)

Interpretation of Report:

The user can examine the report to determine how much a particular variable affects the magnitude of an FCI. If the range of the FCI is small for a particular environmental variable, the variable does not exert a great deal of influence on the FCI. The A-Team may wish to modify the function's aggregation equation until each variable has the desired effect on the FCI.

Web Date: October 1997
Updated: April 2008