Recursiveness in Cross Company Margin – SIT Bussiness Function
Lot of bussiness groups are interested in consolidating their P&L statements. One of the questions most of them have is about what part of the cost of sales in one company is margin of their partners companies indeed.
The bussiness function SIT Stock in Transit is a way to handle this questions, this BF come with ECC EHP5 and there are lot of info about it. If you are not familiar with this function, its a good idea to begin with the SAP Help.
This functionality is not implemented very often, and most of the customers and also the CO consultants dont know about it. The idea of this blog is not to show about the BF, but show how to calculate the values of Cross Company Margin in a Recursive scenario.
I guess some of you have read my blog about Recursivenes and Material Ledger; if you didnt i recomend check it.
I love recursiveness! and I also love material ledger. SIT is one of my favorite functionalities in ML and this is why im shearing this post with all the brave enought to read. I never see a blog nor an example about this topic: Cross company Margin in a recursivenness scenario but its more common than you can imagine. Of Course, the idea is you have a spreadsheet and try to recreate my example. You will see a pretty good aproximation of the result in ckm3n vs the xls (less than 1% error).
Imagine you have 2 company codes: 6000 (with plant 6000) and 6900 (with plant 6900) and both companies sales products to the other. BF SIT is active in order to show Xcompany margins and all the stuff about CCS; but i asume you know the basics of how it works. This blog is not about the Xcompany CCS.
I create the example in my old ECC6 EHP7; but if you have S4Hana 1611 / 1709 /1803 work in the same way.
The product for my product is ZPP_FERT_SIT000002. The example is placed on 01/2019, for simplisity the begining stocks in both plants is 0 and the standard cost in both companies is 1000.
The next chart resume the scenario:
In the scenario, both companies do external produrement of 100 Units, plant 6000 with a cost of 1100/unit and plant 6900 with a unit cost of 1200/unit. The idea of do external procurement in in order to create an example more close to reality; in real world you can have receipts from different sources, revaluations, and more. Lets keep simple!
Company 6000 sales 40 units to company 6900 at a unit sales price 1200
Company 6900 sales 30 units to company 6000 at a unit sales price of 1300.
1: What is the COGS of plant 6000 and 6900 ? (The easy one)
2: What part of the COGS is Cross Company Margin? This is why you are reading this!
Ok, lest begin.
the question #1 is quite simple if you know well the Material Ledger:
In Plant 6000: [(100*1100 + 30*1300) / (100 + 30)] * 40 = 45846
Validate my CKM3N in plant 6000:
In Plant 6900: [ (100*1200 + 40 * 1200 ) / ( 100 + 40 ) ] * 30 = 36000
Validate my CKM3N in plant 6900:
Now, the problem is: how to respond question #2? This is other history.
The 1st concept we must keep in mind is we are working with a periodic monthly price because actual costing is active, and this mean the unit cost is the average of all the receipt and the same happen with the margin part of the COGS.
In our example, both companies have 2 receipts; one from an external procurement and one from a cross company process (grp means Group).
The following diagram try to show graphically the scenario:
In the process of calculate what part of the unit cost (and the COGS) is cross company margin we need to do a recursive calculation; and on the next iteration we update the margin like a carryforward process. Material Ledger BF SIT do basicaly the same stuff in order to track the margin in a cross company process! Lets begin with the iterations:
This screen show the beginins state, the 1st iteration.
In Company 6000, we asume the 100% of the cost is cost (column ‘Margin in the cost = 0), as the initial state of iterations. So in the sales from 6000 to 6900 the total margin is the difference between Sales price and Total cost ( 48000 – 45846 = 2154).
Then, i put this value 2154 as “cost in the margin” in plant 6900 (right side of the screenshot). When plant 6900 sale to 6000, the margin is the sum of the operation margin (difference between Sales Price and Total Cost) and the Margin that come from the cumulative stock (the average of the receipts receipts). In resume
Sales Price = 39000
Total Cost = 36000, from this 36000, 462 is margin (rules of 3rds , proportional part of the cumulative margin (30/140)*2154 = 462
Total Margin = (39000 – 36000) + 462 = 3462
Now, this value ( 3462 ) enter in iteration #2. Now, we calculate a new “Margin In Cost” for the sales from company 6000 to company 6900 in the same way we did in the iteration 1. This new total margin is 3219.
I did 5 iterations doing the same calculation and the values show that they tend to dont change between iterations. See the full xls sheet:
In Sumary, at the end of 5th iteration:
Company 6000 sale 40 units to company 6900. The COGS is 45846, and from this value 1140 is cross company margin. In order to validate the value, you can see the next screenshot of CKM3N.
Also, you in the receipt section, the Cost of Buy is 390000; and 3710 (3706 in the XLS) of this value is Cross Company Margin
Company 6900 sale 30 units to company 6000. The COGS is 36000 and from this value 706 was cross company margin.