Using the General Ledger as a Data Warehouse
I was recently at the CFO CPM Conference (CPM) conference, and over cocktails (yes my life is terribly exciting…) and I was talking with a few people about designing the general ledger and how it can be used as a simple “data warehouse.” As this topic has come up before with clients, I think it’s worth a post.
As an overview (for those few brave non-accountants who are still with me…), all accounting systems can be divided into the general ledger and it’s subledgers. Subledgers include things like accounts payable, accounts receivable, inventory, purchase order, payroll, etc. In the past the general ledger consolidates the accounting entries from all the sub-systems, and many companies still operate this way. So, for example, if we enter an invoice in accounts receivable in the A/R sub ledger we would have information about customer, item, invoice number and amount. We’d also have the accounting information ? telling us to credit $40,000 in sales and debit $11,000 accounts receivable for a given division or profit center. Only this relevant accounting information would pass to the general ledger so that financial statements can be prepared.
In general there are a few reasons that people don’t transfer the data to the GL:
- To save space – We can always link back to the subledger if we want the detail, it’s there.
- To make the ledger simpler – When you transfer the detail, you get a lot more stuff to look at in the ledger.
- Time – Transferring all that detail makes things process more slowly.
- The ledger didn’t have place for more data – Years ago, accounting strings were fairly limited – you couldn’t (without a lot of effort) find a place for more information.
All these are or were good reasons for keeping only general data in the general ledger. This is the “thin ledger” approach. Sounds good, right? Generally yes – but not always.
The first problem is tying out. At month end, we close the subledger – we say no more invoices may be processed; however, during our close process, we realize that several invoices were incorrectly posted and we need to correct them through journal entry in the general ledger. Except that now, whenever we run a report based on customer data from the subledger it won’t match the total ?sales? entered in the general ledger, even if we keep our accounting data straight.
Another problem is that many companies don’t have just one system with customer data. Let’s say we had different systems for online sales vs. traditional distribution, or professional services vs. hardware (none of which is uncommon). To get a true picture of sales by customer we have to look at all the subsystems. But if each subsystem can load a customer key to the ledger, the ledger can act as a simple “data warehouse” for customer information. Indeed, we have a client for whom we are doing that very thing. There are five systems which load data to the general ledger. Each one loads customer data so we can use the ledger as a sales tool (in conjunction with some custom files).
Some people would argue that, when you have such complex needs, you should build a real data warehouse – a separate repository for all this data. And yes, if you really have hundreds of records this may not work. But for mid-size companies using the ledger has three basic advantages:
- By using the ledger, everything automatically reconciles to the ledger. Sounds obvious – but the more systems, the more possible different versions of truth.
- All ledgers already have a simple mechanism for making adjustments – the journal entry. Building similar adjustment logic takes time and money.
- Ledgers generally have the best reporting options in any given accounting system. While these built-in report writers don’t give you everything you want, they are often a good starting point.
In future posts, I’ll give more examples of how you can stretch your ledger beyond it’s simple financial statement reporting capabilities.