Time Dimension for forecasting cube

[anoops81]

Hi All,

We are working on a complex forecasting report. We need to create a time dimension for the forcast cube.

I have gone through the previous threads and saw that 1d is better for forecasting and 2d for reporting.


As of now all the data will be at Quarter level and later on (Next Phase) we will have to maintain the data at Month level. We are planning to go with time dimension structure with elements as given below.

TimePeriod (Consolidation)
Q1 2011 (N level)
Q2 2011 ''
Q3 2011 ''
Q4 2011 ''
Q1 2012 ''
Q2 2012 ''
.
.
.




I want to include the month elements also into the picture as it will be used in future month based forecasting. I need your suggestion on how to include months also in the same dim.
As of now calculations are based on the above Quarter elements, but maintainability would be a problem in future if month elements are not included .

Note
We need to move forward and back along the time dimension Quarter elements to implement roll forward calculations which is possible via DIMNM(DIMIX) Combo.


Regards,
Anoop

[MSidat]

Firstly I would probably call your quarters YYYY - Q# as this way you can do alphanumeric manipulation to go through your dimension rather than relying on the dimnm method which relies on you always ensuring your dimension is built correctly and indexed in the right order.

As currerntly your Q2 - 2012 would be seen as being less than Q3 - 2011 which is obviously not the case.

if you are using just the 1 Time dimension put your periods in there like 2011 - P01 (YYYY - PMM) and when forecasting a phase (presuming you would have this in a dimension) just on Quarters ensure the values are put in P3, P6, P9, P12.

Personally there are a few best practices around for this, but in the end it comes down to your finance people and business needs and how they want to view the data and how they wish to slice/dice and adjust numbers.

[anoops81]

Hi MSIdat,

Below is a sample calculation for two Quarters .

Code: Select all

['3Q 2011', 'Accrual Status'] = S: IF(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Foreclosure Da
te
Summarized')@='3Q 2011','Equity',
IF(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '3Q 2011', !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Delinquency Status')@='Non Earning','Cost
 Recovery',
IF(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'TDR Date Summarized')@='3Q 2011','Cash Basis',
IF((DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '3Q 2011' , !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Delinquency Status')@='LPCBM' % DB('Debt
_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Current AC_REC_RSRVS_SPEC')<0),'Cash Basis',
IF((DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Restructure or Extension Closing Date Summarized
')@='3Q 2011' & DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'TDR After Modification')@='no'),'Acc
rual',
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '2Q 2011', !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Accrual Status')
))))) ;


['4Q 2011', 'Accrual Status'] = S: IF(
(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Full Payoff Date Summarized')@='3Q 2011'%
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'DPO Date Summarized')@='3Q 2011'),'',
IF(
(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Foreclosure Date Summarized')@='3Q 2011'%
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Foreclosure Date Summarized')@='4Q 2011'),'Equity',
IF(
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '4Q 2011', !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Delinquency Status')@='Non Earning','Cost Re
covery',
IF(
(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'TDR Date Summarized')@='3Q 2011'%
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'TDR Date Summarized')@='4Q 2011'),'Cash Basis',
IF(
(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '4Q 2011', !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Delinquency Status')@='LPCBM'%
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Current AC_REC_RSRVS_SPEC')<0),'Cash Basis',
IF(
(DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Restructure or Extension Closing Date Summarized')@
='4Q 2011' &
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, !Ess_Timeperiods, !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'TDR After Modification')@='no'),'Accrual',
DB('Debt_Forecasting', !Collateral, !Index, !Ess_Accounts, !Ess_BSLA, '3Q 2011', !Ess_Years, !Ess_Scenario, !Ess_Product, !Ess_Entities, !Ess_DTS, !Ess_CorpCode, !Ess_Currencies, !Currency Type, 'Accrual Status')
))))));

We dont have the calcs for Months as of now. All calc are with respect to Quarter.

Regards,
Anoop

[anoops81]

Hi All ,

If you see the rules

Code: Select all

['3Q 2011', 'Accrual Status']

they are actually meant for each Quarter, So we need to append the rules for the future quarters in the rules editor. As the report is dynamic there is no other way to implement the calculations.

Maintaining the rules in this way is the best approach ?
Is there any way to make the calculations more generic , like writing it for all the time periods ?


Suggestions are most welcome !!

Regards,
Anoop