Remember that when loading from your ERP system all Balance Sheet Items are loaded as “CLO” for closing balance and all your Income Statement items are loaded as “NONE”. Your rules will calculate the Opening Balance “OPE” records and the Movement “MVT” records prior to you processing the model. In order to balance you then just bring the data into a pivot table (Standard connection to analysis services) and then check that your totals come to zero. But it can get a bit confusing if you do not get your combinations of Flow and TimeDataView correct. So to check you Year to Date figure you select TimeDataView as “YTD” and multi-select “NONE” and “CLO” for the flows. To check the period movement only change the TimeDataView to “Periodic” but then set flow to “NONE” and “MVT”. Voila, it should all balance as per the example below. In this example the 1 and 2 series are Balance Sheet and 3-9 series Income Statement. Also note that if you just add up the numbers you will not get to zero but the Pivot Table understands that some of them are signed and that they should be subtracted. Take a look here and here for a quick overview on this subject.
- Paul Steynberg