How Consolidation Gets Complex

In our previous post, we outlined the basic concept of consolidation. At the end of the day, consolidation is really about addition – adding in balancing entries. But things can get complicated quickly. Here are some of the complexities we see regularly:

1. Scaling Up to Multiple Systems
On purpose, we’ve used a simplified example. In reality, it’s rare to have such a simple situation. As companies grow, structures get complex, and multiple levels of consolidation must occur. While some people stick with an Excel solution as long as possible, it just isn’t trustworthy. Often, when people “upgrade” from Excel to a real system, they discover that what they thought was working, wasn’t.

2. Setting Up Intercompany Costs
In our example, widgets were sold at the same price to outside wholesalers and the company-owned retailer. But life is rarely so simple. There are all kinds of reasons management may want different intercompany prices. Some we’ve heard include:

  • The effort it takes to sell to a related company is much less then the effort it takes to sell to an outside party. The price should reflect that.
  • We can’t let those guys know our costs. If the sales guys in division X knew the cost, they’d undercut our prices.
  • Everyone here gets paid based on margin. We have to set reasonable margins around the organization to keep compensation in check.

If you’re operating within one country, you have a fair amount of leverage (with some restrictions – talk to your tax guys) on allocating your profit. When your entities are in different countries, things get more complicated. Every government in the world wants to collect more tax. Any costs you set between companies need to be justified. If your manufacturing plant in Mexico charges too little to the U.S., the Mexican government is going to want to know why.

In these situations, you often need to maintain two sets of books – one for tax and one for management.

3. Currency Issues
Currency issues (the subject of an upcoming post in this series) are complex even when you aren’t consolidating. When you are consolidating, they’re even worse. If manufacturing sells to retail, what currency do you use for that transaction? How do you track the value over time? This topic deserves its own post. Stay tuned.

4. How Much Money Do Your Divisions Make?
Companies go through consolidation because outsiders don’t care about all your inter-company back and forth. They only care about the net revenue you earn from customers, not coworkers.
While simple eliminations can create a consolidated view, they don’t help you determine how much money each division really made. To figure that out, it’s not enough to eliminate entries, you also need to allocate costs. Let’s demonstrate with our earlier example:

We assumed a $2.00 cost per widget. Manufacturing charged the retail division $5.00 per widget. Is this fair? Should manufacturing get all the credit? Or should it be split differently? Which divisions should assume which portion of the costs?

5. Partial Ownership and Joint Ventures
So far in our examples, we’ve pretended that all our companies are owned fully by the same entity. This isn’t always the case. How do you consolidate with partial ownership or complex joint ventures?

In our next post, we’ll review different ways of handling these complexities.


Understanding Consolidation

Continuing our series on complex system accounting challenges, in this post we discuss the tricky world of consolidation.

In the context of financial accounting, consolidation is the aggregation of the financial statements of two or more companies under the same ownership into a consolidated financial statement. To really grasp consolidation, you need to understand that in the outside world, no one cares about money that’s traded back and forth between different companies under the same ownership. In the outside world, the only revenue that counts is revenue coming from a real customer. That’s what consolidation is all about – putting together financial statements that eliminate all the internal back and forth and focus only on “real” customer revenue.

Let’s explain how this works with an example of a growing company.

ACME makes and sells widgets to wholesalers around the country. Focusing on the income statement, let’s say (for absolute simplicity) that ACME sells 1000 widgets at a price of $5.00 each and a cost of $2.00 each. Its income statement would look like this:

Wholesale Sales $5000
COGS $-2000
Gross Profit $3000

Everyone’s happy. The company makes money. And any system can handle the accounting.

As ACME grows, it decides to open retail stores. And these retail stores are set up as a different legal entity. This creates more complexity because the manufacturing entity must “sell” widgets to the new retail entity. Let’s assume ACME sells 1000 widgets to its wholesale customers and another 500 widgets through its retail channel. Let’s also assume that the manufacturer charges the retail division the same price it charges outside customers. The retailer then charges its customer $7.00 per widget for a total of $3500. We now we have two income statements, one for manufacturing and one for wholesale:

Manufacturing Retail
Wholesale Sales $5000
Retail Sales $3500
Interco. Sales $2500
COGS $-3000
Interco COGS $-2500
Gross Profit $4500 $1000

Now it’s time to consolidate the income statements. If we add all revenue together, we’d have a total of $11,000. That’s not right. We only sold 1500 widgets and the total price out the door was $8500. So, we have to make journal entries to “eliminate” the intercompany entries while preserving the original statements for the manufacturing and retail group. Elimination simply means backing out all intercompany activity transactions.

So, we set up an additional “elimination statement” either through Excel, by creating a dummy company in the accounting system, or with special consolidation software. Our numbers now look something like this:

Manufacturing Wholesale Elimination Consolidated
Wholesale Sales $5000 $5000
Retail Sales $3500 $3500
Interco. Sales $2500 $-2500 0
COGS $-3000 $3000
Interco COGS $-2500 $2500 0
Gross Profit $4500 $1000 0 $5500

Thus far we’ve dealt only with the income statement, but the same logic applies to balance sheets.

A few additional things to note:

We recommend keeping separate accounts for intercompany and external company transactions. This makes elimination easy. Too often, intercompany and external transactions are mixed together, either because systems are inadequate or not set up appropriately.

As a concept, consolidations aren’t that hard. And in many companies, even mid-size ones, no one pays much attention to them. Often, outside accountants create the consolidated financial statement and only the CFO Controller and/or the bank looks at it. Often, business leaders look only at their individual statements to go about their business.

Yet, in practice, consolidation can get tricky. In our next post, we show how things can quickly get complicated.


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.


Report Writing Tip: Sub-Ledger Accounts

Up to now I’ve mostly been posting on general report writing and management issues, but I’m now going to begin posting at least one report writing tip each week.

For this first quick tip, we’ll start with something that happens far too often in ERP: Accounts that should be controlled by a sub-ledger are instead posted directly to ?fix something? at month?s end. The quick fixes are soon forgotten and then everybody is wondering why the report won’t reconcile.

An example: An invoice gets created in your billing system, it credits revenue (let’s say account 40000) and debits accounts receivable (account 11000). You should NEVER adjust these accounts directly in the General Ledger. If you find a mistake and want to fix it on your financial statement, you should set up separate accounts (40001- revenue GL adjustments) and use those. This way you can track what gets controlled by the sub-ledger and what gets entered in the general ledger. You should also make sure to reverse those postings the next month and make the fix in the subsystem.

Most large systems offer ways that you can lock these accounts down ? so for example, account 40000 above should only be used by the billing system and not directly by the ledger.

Seems obvious and hardly technical, but I can’t tell you over the years how many hours I’ve burned because of this simple lack of control.


No, I don’t know why your spreadsheet doesn’t agree with the system.

Excel is a wonderful tool. It’s flexible, powerful, and relatively easy to learn. You can make it do anything.

Excel is also evil. It’s flexible, powerful, and relatively easy to learn. You can make it do anything.

I’m not joking – I love Excel, but the power to make it do anything has cost me more hours than any other piece of technology. You can make a spreadsheet do anything because you can key in any number you want. Once you?ve done that, there?s really no way to reconstruct what happened. Were you adjusting? Where you fixing a mistake? Did you make a mistake? I’m always happy to help my clients, but troubleshooting problems with spreadsheets is usually not pretty.

Case in point. Just this morning I was asked why a new report–generated from our new dashboard system and tied out to our financial statements–doesn’t agree with a 3-YEAR-OLD SPREADSHEET. As an added bonus I’m getting the question from the client’s European office, so I’m thinking about this at 6am.

Granted, I was probably the person who created that spreadsheet 3 years ago, or at least provided the basic data to someone who then created the spreadsheet. But does anyone remember what the actual request was? Why the modifications were made? What exactly that spreadsheet was trying to show?

I realize that you can’t do everything with a system-generated report. Everyone needs to do ad-hoc analysis and that is what Excel is for. But please, be nice to your systems folks. Try to remember that they don’t know what you did in your spreadsheet. And they never will.


My bonnie makes hundreds of entries

Apologies to those who like Scottish folk songs.

My bonnie makes hundreds of entries
My bonnie runs lots of reports
My bonnie is ready to jump now.
The company announced a reorg.

Restate, restate, restate the earnings, again, again.
Faster, faster, I need those numbers by 10.

This little ditty always amuses finance people. Maybe they need to get out more. In any case, I’m clearly not the only one who suffers from requests for ?just small changes? to reports. Changes which need to be done yesterday if not sooner. Changes which ignore the rigid structures that seemed to make so much sense just yesterday.

I’m not complaining. Writing reports, financial and otherwise, is a large source of revenue for my firm. But it strikes me over and over again that no matter how hard people labor over setting up their new software their design is often out of date within months. Indeed, the more complicated their coding structure, the faster it dies. When they go live, they?re so proud that ?Just by looking at that number, I know the line of business, region, counter party, and manager?s mother?s maiden name.? Years later, new employees are amazed that those same numbers mean anything. As someone said to me, ?I was confused because I was trying to figure out why our entries are so convoluted. Now I know that I should be confused and I feel better.? At one client, 150 initial cost centers were setup. Within two years, only five were being used and the client was using entirely different ways of getting key information to management.

There are two lessons in this. First, KISS: keep it short and simple.* Whenever you design a new way of numbering anything, it needs to be simple. Sure, expensive systems are very flexible. It’s so tempting to think that if you get the setup just right, you?re reporting will be so much easier. And this is true?until the boss wants something different or the first reorganization. Remember, pythons are flexible too and are very good at strangling you.

Next, whenever you buy software, keep serious money available for reporting tools. Too often people spend a fortune to automate order process but then have a ridiculously hard time determining how much money they?re actually making on their products. Given that things change so rapidly in many businesses, you want tools that allow you to make those ?small adjustments? quickly and easily.

Now back to work before my bonnie calls again.

*Those of you who know me might have expected a different version but I’m trying to be a nicer person this year.


Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284

Request a Consult