Many of us have been through the rigors of data validation at some point during a system implementation – and if you haven’t, lucky you. For the unlucky ones, you’ve likely encountered someone on your team explaining, in not-so-great detail, that “the data that was loaded looks _____” (weird, not right, funny, etc.). Naturally, the first place you might look to resolve any issues are the data sources and transformation rules. But often times, one (or more) of the Scenario settings is the culprit.
I’m going to explain the key properties on the Scenario and the behavior of a select few. The customer’s requirements will drive the property assignments. It's important that they've been set as a first step in your data validation process.
Scenario Type: This property is simply a grouping of Scenarios. This assists when writing business rules or member formulas and the calculations should only run on specific Scenarios. For example, the PYRE formula for actual type Scenarios will likely differ from budget or forecast Scenario types.
Input Frequency: This setting indicates where the data will be stored in the Scenario. For example, if it’s set to Quarterly, data will only be stored on the quarters. Consequently, if a report is run using a monthly time, that cell will display as invalid.
Default View: This property sets the default view for calculations, member formulas and clearing calculated data when it isn’t specified in the formula.
Retain Next Period Data Using Default View: This property affects future period data if prior period data has changed and works with the Scenario’s default view. This affects only flow type accounts.
In the illustration below, the first three months were loaded with data into four Scenarios with different settings:
TEST_YTD_T has the settings: default view as YTD and Retain as True
TEST_YTD_F has the settings: default view as YTD and Retain as False
TEST_PER_T has the settings: default view as Periodic and Retain as True
TEST_PER_F has the settings: default view as Periodic and Retain as False
We change the Feb YTD numbers in each Scenario (red box) and see how it affects March data (last two columns):
In the YTD Scenarios, March YTD doesn’t change, the periodic changes. In the Periodic Scenarios, if Retain is True, March periodic doesn’t change. If Retain is False, March periodic changes.
Now we change the Feb Periodic data in each Scenario (red box) and see how it affects March data (last two columns):
In the Periodic Scenarios, March Periodic doesn’t change, the YTD changes. In the YTD Scenarios, if Retain is True, March YTD doesn’t change. If Retain is False, March YTD changes.
The Retain Next Period Data Using Default View really comes into play in situations where data’s been loaded through a particular time period but then a prior period needs to be reloaded and/or adjusted.
Input View For Adjustments: This property is the standard view for journals for the Scenario. It should generally be the same as the Default View setting.
Use Input View for Adj in Calculations: This property forces calculations to use the Input View For Adjustments view even if the calculation attempts to override the default view.
The next two properties determine how zero or no data is handled within the Scenario. If it’s set to Periodic, a zero is placed in the period Periodic view without data. If it’s set to YTD, a zero is placed in the period YTD view without data (this ‘reverses’ all prior period data when looking at the Periodic view).
No Data Zero View For Adjustments: This property determines how zero or no data is handled within the Scenario for journals. It should generally be the same as the Input View For Adjustments.
No Data Zero View For NonAdjustments: This property determines how zero or no data is handled within the Scenario for data that isn’t entered via journal.
In the illustration below, the Zero No Data settings are set to YTD for the YTD Scenarios and Periodic for the Periodic Scenarios. In April, no data has been entered. In the YTD Scenarios, a zero is placed in YTD which causes the Periodic values to ‘reverse’. In the Periodic Scenarios, a zero is placed in Periodic which causes the YTD values to carry forward.
Considering May, in the YTD Scenarios, a zero is placed in YTD. Since April was also zero YTD, the May Periodic is also zero. In the Periodic Scenarios, the same thing happens as did in April.
Consolidation View: This property determines the standard view of the consolidation. Either Periodic or YTD can be selected yet YTD may enhance consolidation performance. This must be set to Period for Org by Period implementations.
Formula: This member formula runs immediately after the clearing of the Data Unit and prior to all other translations and calculations in the Data Unit Calculation Sequence. In most cases, this shouldn’t hold a member formula as it runs every time the Scenario is consolidated.
Formula for Calculation Drill Down: This allows drill downs to occur on members with formulas.
Clear Calculated Data During Calc: This property clears existing data upon calculation if True is selected. If False, existing data remains but can be cleared manually. We often recommend that this is set to True unless there’s a compelling argument to change it to False (keeping in mind that every member formula and business rule that runs on this Scenario will therefore need to contain a clear statement).
I hope the explanations of the behavior helps you while setting the properties based on how the customer wants to load, modify and view data within each of their Scenarios.
But wait, there’s more! Once you’ve discovered that you need to change one of the Scenario properties, you’ll want to reset the Scenario (clear ALL data from it), change the property and reload the data. I’ve had consultants skip the resetting step and it sometimes led to inconsistent data results. You could skip it at your own risk but it’s best to reset, change and reload.
