Skip to content

Profitability analysis by product, customer and entity from the consolidated data.

We build the margin matrix on the consolidated figures, with each allocation rule written down and owned by a named person. The price is fixed after we have read the current allocations.

See the servicesStart a conversation

Profitability analysis attributes revenue and cost to products, customers, channels and regions, then shows which of them makes money after shared costs. Constancia, an EPM consultancy, builds it on the consolidation for multi-entity groups pricing a disposal, a contract renewal or a restructure by a date.

Reads
The current margin report, the allocation workbook behind it and the last 3 changes to a rule.
Produces
A margin matrix by product, customer and entity, on allocation rules each written down with a named owner.
Then
The build at a fixed price, with the new matrix run beside the old report until the 2 agree.

Profitability analysis on the consolidation takes in 6 things, with the allocation rules written before the matrix.

  1. 01

    The allocation rules

    Each shared cost, from IT to the head office lease, carries 1 written rule, 1 driver and 1 named owner.

  2. 02

    The revenue by customer

    Revenue attributes to customer, product and channel on dimensions loaded from the billing system, entity by entity.

  3. 03

    The direct costs

    Cost of sales, freight and commissions attach to the product or customer that caused them, from the source system.

  4. 04

    The margin matrix

    Product against customer against entity, with contribution and fully allocated margin on every cell, refreshed each month.

  5. 05

    The intercompany margin

    Margin earned by 1 entity selling to another is stripped at group level, so the product shows the margin the group earns.

  6. 06

    The rerun on a rule change

    A change to a rule reruns the whole matrix, and the owner sees which customers move from profit to loss.

The margin report gets reopened every quarter because the allocation rule lives in a workbook with no owner.

Where your group runs 6 entities selling 400 products to 2,000 customers, the margin report is probably an allocation workbook 1 management accountant built. Head office cost is spread by revenue in that workbook, and the divisional director whose margin looks worst asks for a headcount basis instead.

The rule changes in the second quarter and back in the third, and a customer moves from profit to loss with no sale changing. Constancia has FTSE 100 clients among its 58 implementations over 35 years, and a profitability survey with us starts by reading the allocation workbook.

We write each rule down with a named owner, build the matrix on the consolidated figures and run it beside the old report.

A margin matrix on the consolidation suits a group with 4 features.

  1. 01

    Shared costs above 1 entity

    Head office, IT and group functions sit in 1 entity and serve the others, and somebody has to spread them.

  2. 02

    A margin report that gets reopened

    The rule behind the report changes when the result is unwelcome, and the board has seen 2 versions.

  3. 03

    A disposal, a renewal or a restructure

    A dated decision depends on which product, customer or site makes money after shared costs.

  4. 04

    A consolidation that holds the figures

    The revenue and the costs by entity already tie out in a consolidation, or the group is about to build one.

OneStream (EPM software) holds profitability analysis for a group that wants the allocations on the consolidated result. Abacum (FP&A software) holds it for a finance team analysing margin from the ledgers and the billing system, with the consolidation elsewhere.

An allocation rule holds under challenge according to where it is written and who owns it.

The challenge usually comes from the divisional director whose margin the rule reduces, and the third column is the one that answers it.

How profitability analysis behaves in a spreadsheet, on a platform nobody owns and on a platform with named owners.
A spreadsheetA platform nobody ownsA platform with named owners
The allocation ruleA formula in 1 tab, and the basis changes when the result is unwelcome.A rule set at go-live by a consultant, and nobody in finance can explain it.A written rule per shared cost, with an owner who defends it at the board.
The customer marginRevenue from the billing system pasted against costs spread by revenue share.Customer margin on the platform, with 1 entity's customers still in a workbook.Customer margin across every entity, on the same customer list the consolidation uses.
The intercompany marginLeft in, so the plant's margin and the sales company's margin are both counted.Eliminated at group level, and the product view still shows the transfer price.Eliminated at group level and at product level, so the group margin is the margin the product earns.
The rerun after a rule changeA day of work and a new file name.A rerun that works, and nobody compares it with the previous one.A rerun in minutes, with the customers that moved listed against the rule that moved them.
The board's margin page1 table rebuilt each quarter, and a footnote about the rule change.1 export from the platform, and the CFO explains the rule from memory.1 view from the model, with the rules and their owners printed beneath it.

See more on our

Subscribe on YouTube

  • Latest episode

    How an EPM implementation works, and why the software comes last

Talk to usSubscribe on YouTube

Every Sunday, 6am

The Sunday Close.

Our weekly newsletter on AI in finance.
Yours to enjoy with your Sunday morning coffee.

  • AI in finance observations from Alex (CEO) and Brad (COO)
  • Newest video from our YouTube channel
  • AI in finance trends we have spotted each week

Tell us the date you are working to.

The people who would do the work read every message and reply inside 2 working days.

Start a conversationRead about the office of the CFO