How to automate the elimination of Profit on Inventory

Over the next few posts I’ll write about automating common group eliminations in consolidation tools. The first – and perhaps the simplest – in the series is the elimination of Profit on Inventory (POI).

Background

Group companies often sell goods to other companies within the same group, who in turn sell them further to others in the group and eventually to external customers. If the intra-group selling price contains a margin and part of the goods remains in inventory at the end of the accounting period, the selling unit’s profit on the inventory needs to be eliminated.

If the Profit on Inventory is not eliminated, the group will show too high gross margin and too much inventory.

Reporting the data

The group companies don’t usually want to reveal their margins to their inter-company partners. With this solution they don’t need to, since both parties – the seller and the one holding the inventory – report only their own data and the elimination is performed based on this.

The selling entity reports the intra-group revenues and cost of goods sold ideally by the counterparty. On the other hand, the buyer reports their inventory that originates in the group companies. The inventory should also be reported by the counterparty to ensure that the elimination would be most accurate. This can prove to be difficult sometimes, especially for the work in progress and finished goods in inventory.

The more precise the data that is reported, the more precise the elimination. Even if the source systems wouldn’t offer the most precise data, the elimination can still be automated using statistical models, group-wide gross margin percentages, etc.

See an example of POI in Excel. A few words of introduction:

      1. Of the two entities belonging to the group, Unit A sells goods to Unit B who sells them further to a customer. Both add a margin to the costs.
      2. In month end, part of the goods Unit B has bought remain in the inventory so the associated profit needs to be eliminated.
      3. In consolidation, the intra-group elimination of revenue, accounts receivables and payables is recorded in the ICELIM column.
      4. The profit on inventory is eliminated in the POIELIM column.

Elimination

The system calculates the amount for the elimination based on the reported intra-group sales and intra-group inventory information. The data is stored on help accounts or dimension members and consolidation logic is steered to increase cost of goods sold and reduce inventory with the amount.

The Profit on Inventory creates a deferred tax asset. The logic takes care of this as well.

Finally

Since this elimination directly impacts the income statement at gross margin, it’s of particular interest to group management so the solution depends on the requirements of management and the auditors.

In the next post I’ll cover the elimination of profit on fixed assets.

If you need help with consolidation-related issues with HFM, FCCS, OneStream or CCH Tagetik, don’t hesitate to contact me or my inlumi colleagues near you.


About the author

Lauri Järvinen
Principal Consultant at inlumi
lauri.jarvinen@inlumi.com

Lauri is an experienced professional in building consolidation solutions. He has over ten years of experience in developing and maintaining Oracle Hyperion Financial Management solutions. Lauri has a keen eye for the links and dependencies between the technical solution and the process. He is development-driven and always eager to learn.