The Scenario Analysis tool runs a simulation and then sorts and matches all the resulting values of a target forecast with their corresponding assumption values. Then, you can investigate which combination of assumption values gives a particular result. You can run Scenario Analysis on any Crystal Ball model with at least one assumption and forecast that are not frozen. You select a target forecast to analyze and then the forecast’s percentile or value range to examine. The resulting table shows all the values for the target forecast in the designated range, sorted, along with the corresponding assumption values.
CB.ScenarioAnalysisND runs the Scenario Analysis tool and displays results without using dialogs.
Table 159. CB.ScenarioAnalysisND Parameters
Parameter | Required? | VBA Data Type | Table |
|---|---|---|---|
Index | Required | Integer | Table 160, CB.ScenarioAnalysisND Index Parameter — Required, Integer |
Value1 | Optional | Variant | Table 161, CB.ScenarioAnalysisND Value1 Parameter — Optional, Variant |
Value2 | Optional | Variant | Table 162, CB.ScenarioAnalysisND Value2 Parameter — Optional, Variant |
Table 160. CB.ScenarioAnalysisND Index Parameter — Required, Integer
Named Constant Value | VBA Data Type | Index Value | Description |
|---|---|---|---|
cbSceChooseTarget | Integer | 1 | Selects the target forecast to be analyzed. The range can either be specified as a separate line preceding the CB.ScenarioAnalysisND call, such as [A1:C100].Select, or it can be the Value1 parameter, specified as "A1:C100" or similar. If the optional Value1 parameter is not given, the current active range is used. |
cbSceRangeForecastResults | Integer | 2 | Works with other Index constants used as Value1 to set Scenario Analysis to work with a percentile range or value range: cbSceSelectPercentiles [3] cbSceSelectForeValues [4] |
cbSceSelectPercentiles | Integer | 3 | Works with Value1 and Value2 to specify a range of percentiles |
cbSceSelectForeValues | Integer | 4 | Works with Value1 and Value2 to specify a range of forecast values |
cbSceForeOption | Integer | 5 | Works with Value1 to indicate which forecast charts to show while running Scenario Analysis |
cbSceSimTrials | Integer | 6 | Sets the number of trials to run |
cbSceIncludeNonTargetFore | Integer | 7 | When set to True, includes all forecasts in the output table; otherwise, includes only the target forecast |
cbSceRun | Integer | 8 | Runs the Scenario Analysis tool when all settings are complete |
Table 161. CB.ScenarioAnalysisND Value1 Parameter — Optional, Variant
Related Value | Named Constant Value | VBA Data Type | Index Value | Description |
|---|---|---|---|---|
For Index = 1, cbSceChooseTarget | A cell reference with format:Range("A1:A1") or "A1" | Variant | n/a | Specifies the target forecast cell to be analyzed |
For Index = 2, cbSceRangeForecastResults | cbSceSelectPercentiles | Integer | 3 | Indicates that a range of percentiles should be worked on |
For Index = 2, cbSceRangeForecastResults | cbSceSelectForeValues | Integer | 4 | Indicates that a range of forecast values should be worked on |
For Index = 3, cbSceSelectPercentiles | PercentileMin (a numeric value) | Double | n/a | Specifies the minimum value of the percentile range to work on; the maximum is specified as Value2 |
For Index = 4, cbSceSelectForeValues | ForeValueMin (a numeric value) | Double | n/a | Specifies the minimum value of the forecast value range to work on; the maximum is specified as Value2 |
For Index = 5, cbSceForeOption | cbSceShowDefinedFore | Integer | 1 | Sets the forecast option to show charts for all defined forecasts while Scenario Analysis runs |
For Index = 5, cbSceForeOption | cbSceShowTargetFore | Integer | 2 | Sets the forecast option to show only the chart for the target forecast while Scenario Analysis runs |
For Index = 5, cbSceForeOption | cbSceHideFore | Integer | 3 | Sets the forecast option to hide charts for all forecasts while Scenario Analysis runs (the fastest setting) |
For Index = 6, cbSceSimTrials | TrialRuns (a numeric value) | Long integer | n/a | Specifies the number of trials to run for the scenario analysis |
For Index = 7, cbSceIncludeNonTargetFore | True or False | Boolean | n/a | If set to True, includes all forecasts in the output table; if False, includes only the target forecast |
Table 162. CB.ScenarioAnalysisND Value2 Parameter — Optional, Variant
Related Value | Named Constant Value | VBA Data Type | Index Value | Description |
|---|---|---|---|---|
For Index = 3, cbSceSelectPercentiles | PercentileMax (a numeric value) | Double | n/a | Specifies the maximum value of the percentile range to work on; the minimum is specified as Value1 |
For Index = 4, cbSceSelectForeValues | ForeValueMax (a numeric value) | Double | n/a | Specifies the maximum value of the forecast value range to work on; the minimum is specified as Value1 |