Using Sensitivity Analysis

Use Sensitivity Analysis to manipulate selected accounts and evaluate the effect on key variables. For example, you can see how much you must increase product sales to balance an increase in manufacturing expenses.

You can use Sensitivity Analysis to isolate the value drivers in financial models. Value drivers are key variables that, when manipulated, impact values. Knowing which accounts affect your enterprise helps you make informed decisions.

There are three sensitivity models:

• Full Strategic Modeling model

• Shareholder Value model

• Economic Profit model

Accessing Sensitivity Analysis

To use Sensitivity Analysis:

1. Access the Strategic Modeling ribbon in Microsoft Excel.

2. On the Strategic Modeling ribbon, click Sensitivity Analysis.

3. Set full model options.

   See Setting Global Sensitivity Analysis Options

4. Set shareholder value options.

   See Setting Shareholder Value Options

5. Set economic profit options.

   See Setting Economic Profit Options

6. Test the results in the matrix.

   See Viewing Sensitivity Analysis Results

7. Click OK.

Setting Global Sensitivity Analysis Options

Use the Sensitivity Analysis-Full Model tab to analyze the entire model. Because it uses the full model, it has the longest calculation time.

To set the full model options for sensitivity analysis:

1. Access Sensitivity Analysis.

   See Accessing Sensitivity Analysis.

2. In Sensitivity Analysis, select Full Model tab.

3. In Sensitivity, select an account for analysis.

   Note:

   Accounts using scalar inputs or historical average forecast method are not listed. Accounts using Freeform Formulas that respond to changes in other accounts are included.

4. In period, select a time period.

5. In Display, define how values are presented:

6. In Relative to - Top, enter information for the first account variable that is changed. This information is displayed on the matrix.

   • In Account, select the top variable of the matrix on which the sensitivity is performed.

     The list includes all accounts from the model, plus:

     • Input accounts with no freeform formulas

     • Input accounts with freeform formulas using @input

     • Main accounts calculated as a sum of their subaccounts

     • Subtotal subaccounts

     • For accounts calculated through subaccounting and subtotal subaccounts, the increment percentage applies to the subaccounts output values. Using these accounts, you must select Multiplying by in the Change by field.

     • Calculated accounts such as Net Income are not included.

   • In Change by, select a method for changing the sensitivity variable:

     • Adding: Add the change amount to the input value of the relative variable. The change amount is based on the input type and scale of the relative variable. For example:

       • An increment of 3 for A/R, if it is forecast in Days of Annualized Sales, adds three days to the input value of A/R.

       • An increment of 2 for Sales, if it is forecast as a Growth Rate, adds 2 percent to the input value of Sales.

       • An increment of 5 for Fixed Capital Investment, if it is forecast as Actual Value in Millions, adds 5 million dollars to the input value of Fixed Capital Investment.

     • Multiplying by: Multiplies the input value for the Relative to account by a percentage. For example, an increment of 2 for Sales, if it is forecast as a 10% Growth Rate, multiplies the input value of 10% by 2%, equalling 10.2%.

7. In Relative to - Left, add another sensitivity variable.

   • In Account, select an account to perform a sensitivity using a second variable.

     Note:

     The default account is Period End Exchange Rate unless you have saved another setting.

   • In Change by, select a method for changing the sensitivity variable:

     • Adding: Add the change amount to the input value of the relative variable. The change amount is based on the input type and scale of the relative variable. For example:

       • An increment of 3 for A/R, if it is forecast in Days of Annualized Sales, adds three days to the input value of A/R.

       • An increment of 2 for Sales, if it is forecast as a Growth Rate, adds 2 percent to the input value of Sales.

       • An increment of 5 for Fixed Capital Investment, if it is forecast as Actual Value in Millions, adds 5 million dollars to the input value of Fixed Capital Investment.

     • Multiplying by: Multiplies the input value for the Relative to account by a percentage. For example, an increment of 2 for Sales, if it is forecast as a 10% Growth Rate, multiplies the input value of 10% by 2%, equalling 10.2%.

8. Click Update to view the percentage calculation in the grid.

9. To copy the data in the grid, click Copy. By doing this, you can copy and paste the data from the grid.

10. Click OK.

Setting Shareholder Value Options

Use the Shareholder Value tab to perform a sensitivity analysis using a limited number of value driver variables from the Shareholder Value model. Because this group is a subset, calculations are faster, but may give different results than the Full Model.

To set the shareholder value options for sensitivity analysis:

1. Access Sensitivity Analysis.

   See Accessing Sensitivity Analysis.

2. In Sensitivity Analysis, select Shareholder Value tab.

3. In Sensitivity, select an account for analysis.

4. In Display, select a display option.

5. In Relative to - Top, perform these steps:

   • In Value Driver, select the top variable of the matrix on which the sensitivity is performed in addition to the increment type and amount. The default is Sales Growth Rate.

   • In Change %, enter the percent multiplied by or added to the Relative to account.

     You cannot enter negative amounts. Examples:

     If you enter 2% for Sales Growth Rate here and select Multiplying in the Change % field, Strategic Modeling multiplies Sales Growth Rate by 98% to obtain the -2% result and multiplies Sales Growth Rate by 102% to obtain the +2%. So, 10% becomes 9.8% and 10.2%.

     If you enter 2% for Sales Growth Rate here and select Adding in the Change % field, Strategic Modeling adds or subtracts 2% to obtain the result. So 10% becomes 8% and 12% growth rate.

6. In Relative to - Left, perform these steps:

   • In Value Driver, select the left variable of the matrix on which the sensitivity is performed. The variables are the same as in the Relative (top) text box. Default: Profit Margin.

   • In Change % , enter the percent increment for the left variable. The rules in the Change % for the top variable field apply.

7. In Change by, select either Adding or Multiplying the variable by a percentage.

8. Click Update to view the percentage calculation in the grid.

9. To copy the data in the grid, click Copy. By doing this, you can copy and paste the data from the grid.

10. Click OK.

Setting Economic Profit Options

Use the Sensitivity Analysis—Economic Profit tab to analyze sensitivity using a subset of value driver variables from the Full Model. Because this group is a subset, calculations are faster, but may give different results than the Full Model.

To set the shareholder value options:

1. Access Sensitivity Analysis.

   See Accessing Sensitivity Analysis.

2. In Sensitivity Analysis, select Economic Profit tab.

3. In Sensitivity, select an account variable on which to perform sensitivity analysis.

4. In Display, select an option for displaying results.

5. In Relative to - Top, perform these steps:

   • In Value Driver, select the top variable of the matrix on which the sensitivity is performed in addition to the increment type and amount. The default is Sales Growth Rate.

   • In Change %, enter the percent multiplied by or added to the Relative to account. You cannot enter negative amounts.

6. In Relative to - Left, perform these steps:

   • In Value Driver, select the left variable of the matrix on which the sensitivity is performed. The variables listed are the same as the Relative (top) field. The default is Profit Margin.

   • In Change %, enter the percent increment for the left variable. The Change % rules for the top variable field apply.

7. In Change by, select either Adding or Multiplying the variable by a percentage.

8. Click Update to view the percentage calculation in the grid.

9. To copy the data in the grid, click Copy. By doing this, you can copy and paste the data from the grid.

10. Click OK.

Viewing Sensitivity Analysis Results

To view Sensitivity Analysis results:

1. Access Sensitivity Analysis.

   See Accessing Sensitivity Analysis.

2. Select the Full Model, Shareholder Value, or Economic Profit tabs.

3. Click Update.

4. View results in the matrix:

   When you enter values and click Update, Strategic Modeling updates the matrix on the Sensitivity Analysis, showing how the two variables affect the sensitivity of the selected account. You can print or copy and paste from the matrix.

Using One Variable for Sensitivity Analysis

To analyze sensitivity for one variable, enter the top variable information, and enter 0 (zero) for left variable's percent change. Displays only one row (the middle, horizontal row of numbers) of calculated results.