Envisioning a modern accounting reporting system

I am going to start writing this and putting it out there to see if it gauges any interest at all. I am a financial analyst (the controlling/planning/accounting type, not the banking type, from now on I will mean this by “finance”) and a hobbyst programmer (Python).

The finance community is probably one of the communities less understood and most badly served by the software industry. Even though the “math” behind accounting and controlling is rather simple, the complexity of dealing with increasing data, incompatible formats, backups and versioning, missing tools, is real. Of course, a programmer, even a “scripter”, can solve most of these problems, but finance people are not programmers, they have a different job. As a side note, even if one of them were, it is impossible to introduce any new tool because others will not be able to use it.

However, the pain is real. The community is screaming for solutions. There are people whose job is basically to paste data from different systems, ERPs, etc., into spreadsheets. If some of that can be reduced, it will be real value for the company.

But what do finance people do anyway? Let’s do the basics: P&L and Balance Sheet. First rule:

Most of the business will be tracked based on those 2 financial statements. All the KPIs, all you do actually, has little meaning if it is not tracked back to P&L and B/S.

Bonus: remember, if you did not already, that also a figure so vital for a business such as cash flow is strictly dependent on these statements.

I am not going to go into the details of these two statements for now. What I wanted to point out is that at the end, for a business, all the figures track to these. Look at it like 2 parent nodes. Everything, every number, is a child of these nodes. Which brings us to my topic of interest for this post: consolidations.

Let’s say I am buying office supplies for USD 100. In order to register this into my accounting system I will need 2 accounts:

1 cost

2 inventory

My double entry will then be:

cost +100

inventory +100

Which will register a cost in the P&L and an asset (inventory) in the B/S. So, back to consolidations, imagine having hundred of thousands, millions of these transactions. They will all be mapped to P&L and/or B/S accounts.

This is all good and nice as long as you have only few P&L and B/S accounts. However, in reality you do not have a “cost” account, but you rather have a natural account, mapping to a cost center, mapping to a company, mapping to a functional area (an area of the P&L), mapping to a P&L account.

What does this mean? Once you want to study your cost structure, you need to be able to go back and forward from the “high level” of the consolidated P&L to the “low level” of the natural accounts, passing by all the sub groups. As you might have guessed, this is a lot of data (but not big data) and, as of now, there is no ideal solution of doing it properly.

Look at it like a tree of this sort:

→ P&L account

   → Functional Area

      → Company

          → Cost Center

               → Natural Account

At each level you need to summarize groups (yes, group by). The fun starts when you want to compare trees. Say you want to study 2011 against 2012, what would you do? You would take the tree in 2011 minus the tree in 2012, right? Perfect, there is no good way of doing this that I know of.

Another interesting issue is having various people collaborating on this data. Say the company adopts a controlling process: this means doing a plan, forecast, for at least the whole of the current fiscal year. This usually means various people inserting their data. This is because a P&L is “owned” by a multitude of people. If you look back at the tree, you can have: 1. natural account responsibles; 2. cost center responsibles; 3. company responsibles; etc. At each level, depending on the organization, there will be a need to share/review data between multiple users.

Finally, another bulky problem is mapping things. At the end, a P&L (or B/S) is a huge SQL join where everything is mapped. Of course, if you are lucky you have an accounting system were this mapping is done properly and is clear. However, this is not always so. Also, there might be the need of adding “custom” mapping on top of this system for specific analysis. Thus, organizing mapping (for the Excel wizards, vlookups) is very important both to do your analysis properly and to keep track of what you did. Finally, consider that many times data is coming from different systems and has to be mapped together to bring in the picture. This merging, mapping of data is the bread and batter of a finance job and is a real pain as doing it with spreadsheets takes away a considerable amount of time and loses track of things (ie: you might end up using old versions of numbers and make mistakes).

So, summarizing, we identified few things that are a real pain that are only partially served by the existing software solutions:

1. The need to have all data, starting from transactions, in 1 place

2. The need to see this data in trees with various sublevels

3. The need to share/insert data and managing access (edit/view) of a multitude of users

4. The need to map data and, especially, organizing the mapping in a clear fashion

5. The need of all this to be flexible: each company has a different P&L and B/S structure and also different reporting needs

Well, this is for now. It is a long post. I think I only touched the surface and probably many readers will read and still be confused. However, if I see people are interested, I will go in more details. Plus, you are always very welcome to send me an email, I answer to all.