The other day we were reviewing a General Ledger cube for a new client. They had a concern because they wanted to start using Planning Analytics Workspace, rather than just Perspectives or Planning Analytics for Excel.
Problems with the Existing General Ledger Cube
We found five primary problems with their existing Excel based reports and the underlying TM1 General Ledger cube and GL dimensions:
- They used hard coded account description, with a hidden column for account codes,
- They used Excel to total ranges of accounts,
- A minus sign was used in Excel to present the underlying TM1 data the way users wanted,
- The notes column on the old reports was an Excel based note, so it was not stored in TM1 and thus was lost unless a disconnected version of the report was kept.
- In TM1, the sign of data followed traditional accounting, with negative numbers for credits and positive numbers for debits, but they wanted to present all values as positive.
Just to complicate it a little, their primary Profit and Loss and Balance Sheet are not derived straight from the Account dimension, Rather they have a hybrid of account and cost centre that determines whether a value is a direct or indirect cost, and then assigns the expense to a Nature category for presentation directly into the P&L and Balance Sheet. A full Trial Balance is possible two ways. For the “No Nature” element displaying all accounts and within the Nature dimension itself for the total of the Account dimensions.
Solutions to Make the GL Cube work for Planning Analytics Workspace
Our task was to present the General Ledger cube so it could be easily used in Planning Analytics Workspace. This meant not rely on manually added elements in the report, or manually entered descriptions, or manually maintained explanations.
To do this in TM1 we:
- Built into the Nature dimension all the rollups (or consolidations) from the Excel based P&L and Balance Sheet reports,
- Added add an attribute for the names used in the Excel reports, and
- Re-worked the weights for the Account and the Nature dimensions so we could present positive values right through the cube. This was regardless of being a debit or credit at source. We have them add correctly as both a trial balance in both the Account and Nature dimensions, and also add correctly in the in the full Profit and Loss and Balance Sheet structures.
- Added a String measure for notes.
Then in Excel we modified their reports to now:
- use DBRW right through the P&L and Balance Sheet reports, rather than having line items as DBRW and totals as Excel SUM formulae,
- have a DBRA for the descriptions, pulling them directly from the Nature dimension,
- have a check sum on the report. This manually adds each detail line of the financial statements and compares it to the bottom line. This alerts the accountants that there is a problem to investigate,
- replaced the manual text area on the reports to use the new string measure.
Challenges
The biggest challenge was to get the weights correct. Data is loaded against each GL account and “No Nature”. Then using a matrix of the account and cost centre, we are directing values to specific nature elements. We needed to get the weight correct on both dimensions. This was challenging, but resolved with a little bit of trial and error. Make sure you allow time for reconciliation and changes if you try this exercise yourself!
Now we have Excel based GL reports that only use TM1 for the data, descriptions and notes. More importantly, in Planning Analytics Workspace we can open the General Ledger cube and create analysis that uses the same structure and descriptions that have been used in Excel. Further, users don’t need to understand which lines of the report need to add to others, or what the sign of the data is. They just use it. Easy!
