“I’ve had this post in draft for years, and it’s a format I haven’t tried before: a set of notes on how I approach designing General Ledger (GL) and Finance models. This isn’t a ‘how-to guide,’ but rather a collection of my personal considerations. I hope some of you find these insights useful.

When starting a project with the Office of Finance, it’s often best to begin with the objects like the General Ledger (GL), P&L, or Trial Balance. Because these are so closely tied to actuals, you can learn a great deal by examining how the financial module is structured within the ERP system. It’s a really good starting point, because you can get to a production ready result in days, show value by enabling finance office to capture forecast and provide consolidated results, get everybody onboard overall journey and go onto harder / more interesting bits.

My overaching philosophy these days is that I’m trying to build the systems that ’last’, so they won’t be used by the people I’m currently working with and won’t be supported by our team :) This means that things have to be as simple as possible so that they’re easy to understand and have fewer points of failure.

Main GL cube

With this in mind, the main cube in finance module always closely mimics the actual GL object in ERP, typically with the following dimensions:

• Version - surely we want multiple scenarios
• GL Account - more on it below
• Currency - this is one exception to the main ‘keep it very simple’ rule. Even if you don’t need multi-currency now, add it with everything being ‘Local Currency’ as adding it in future is a lot more cumbersome. I’ve personally had to work around the absence of a currency dimension in these cubes multiple times just this year :)
• Cost Centre - or any other dimension(s) that identify the actual posting in your source system (e.g., Company, Profit Centre, or Cost Centre). These can often be multiple dimensions.
• Measure - this is the most interesting dimension, I try to make it meaningfull by having a separate element for every data ‘source’ that we have (e.g. an ‘HR’ element if there’s a HR portion of the model, capex, revenue planning, etc). The goal of having separate elements is to be able to quickly identify source of data and allow reconciliations. Having an additional Adjustment (or multiple such elements) allows overlays on top of the other modules data for the last minute changes or future postings in a month-end reporting process. TM1’s greatest strength is the ability to create a purpose-built model for a part of revenue and expenses components and this dimension is where all these models ‘plug in’.
• Time dimension - single year and month or week dimension with virtual hierarchies to provide different time consolidations for reporting

Planning / forecasting is often done on a more aggregated level of data than actuals. For example chart of accounts has 20 accounts under Office supplies, and nobody has the time to forecast pens and pencils separately (or same story around cost centres). This leads to a few potential solutions:

• Selecting a ‘placeholder’ account to house forecast data (e.g., all forecasts are recorded on the ‘pens’ account, while actuals are assigned correctly).
• Creating dedicated ‘forecasting’ accounts within the chart of accounts specifically for forecast data (e.g., Office Supplies - Forecasting).
• Allocating consolidated input based on an actuals profile (e.g., last month, last year) to distribute data among the more granular accounts under ‘Office Supplies’.

I prefer to:

• have actuals at the granularity that allows easy reconciliation and limits the number of trips to the ERP system for reporting. For example: if we agree that Office supplies is the forecasting level and limit the Account dimension in TM1 to this level and it means that actuals vs forecast reporting will require people to pull detailed by account actual breakdowns from ERP – that’s a design fail in my book.
• capture all user input ‘unmodified’ to allow to trace ‘who input what’, which rules out allocating on input option. I like the ‘forecasting’ accounts approach a lot and advocate for it as it allows to create a ’lowest’ granularity forecast vs actuals reporting and is easy to trace / reason about.

GL breakdown

And another cube that I always put it in is a ‘Line-Item’ breakdown for any GL cube cell, that would normally have a slightly reduced set of dimensions (reducing the number of dimensions is the only reason to separate if from the main cube), for example:

• Version
• Cost centre or anything else identifying the actual posting in your source (can be multiple dimensions, i.e. a company and a Profit centre or Cost centre)
• Line item - 1, 2, 3, you got it
• Time + inputs dimension - a set of picklist inputs (for example Account dropdown) to populate in GL cube, comments, descriptions + all the months / weeks as in the usual time dimension

This cube allows capturing all the extra details that you want for a single account in GL cube, for example, specific trips for travel budget or different projects for the consultancy costs in a very simple way. The data from this cube will populate GL cube (in a special ‘breakdown’ measure element), so you can tie them beack together.

It’s important to note that this cube typically does not contain actuals, as line items generally cannot be directly linked to actual data items. While loading actuals might be feasible if one of the dropdowns acts as a project code or similar identifier, at that point, it would likely be more efficient to create a dedicated dimension and a separate cube for that purpose.

If the main cube we’re building involves balance movements, you can easily integrate debit/credit dropdowns to generate movements based on a single line of input.

This breakdown cube is incredibly powerful. You can literally start with only this cube and then analyze the inputs from your initial forecast to identify which accounts or areas are most frequently used or carry the largest monetary values, these are the prime candidates for more detailed modules.