The Stock Margin Elimination is more a process then a report, it's a step for the closing process each month all the companies have to eliminate the internal margin for the stock. This is using data from the P&L Integrated margins to have the calculations for this Stock Margin elimination (for WP1) mainly from the SAP CO (Controlling) module . |
For this Application the access is provided based on the BW menu "MS - Stock Margin Elimination (Model S)" and it's done via Service one. Fill the form In SAP system select the "WBP - BW Business Warehouse" In WBP - BW Business Warehouse : Select the Business Role select the "MS - Stock Margin Elimination (Model S)" |
Target Users: As examples: Controllers / Accountants |
VERSION | DATE | MODIFIED BY | DESCRIPTION |
0.01 | 24.10.2023 | Inês Vilares | Initial draft |
|
2.0 Business Process
The process before it was very complex and has a big imapct for the closing process. See below the old logic:
To have a most accurate process for this and more efficient, the idea was based on the following:
For calculation we will use:
→ The quantities that are effectively in stock at the plant level.
We call it LOGISTIC STOCK.
→ The quantities that are being shipped to the plant . These items are not present in the factory but are its property.
We call it STOCK IN TRANSIT.
The goal is to use the IM cost unit to valuate the stock and in the standard cost we have the internal margin between plant and manufacturing plant and since in the IM cost unit we don't have the difference will be our stock margin elimination.
This should be done at D+3 in the morning because it's need it for the closing process. That is why this a closing tool and not a reporting tool.
The idea is to take the quantities in stock and valuate at standard cost vs at IM cost but in term of quantity we need to have two types of quantity: the real stock in plant but also the stock in transit. In BW we had a old logistic stock model and the current stock model wasn't develop and we antecipate and we did this model to provide the financial tool and to be accurate with the logistic quantities. Double check with Ludovic.
3.0 Application Feature Overview
For this application find below all the workbooks available:
| Reports | Definition | Prompts | BW Workbook Query | Query Technical Name |
|---|---|---|---|---|
| Stock Margin elimination report | In this report it's possible to check the different values for the:
| Mandatory:
Optional:
| BW_WBK_MS_0001 | BW_QRY_CPCOMS01_0002 |
4.0 Functional Specification
4.1 General Data/Calculations
For these reports, it’s important to understand some concepts which will allow the user to work with the reports.
| Batch | It's an identification to have a track on the material and can give us a lot of information as where was produced, manufacturing plant, among others. |
|---|
4.2 Process Detail
4.2.1. Report/Process Definition
| Domain | Finance |
|---|---|
| Application | BW reports under MS - Model S |
| Provider |
BW Target Model : 2 sources / 2 ways of calculation
For PF1 the project decided to not do the calculation in BW but it's done in SAP with a program with their own claculation (run once per month).
For WP1 part the calculation is done in BW, we are using:
- the material movement to have the stock model;
- the costing runs for the calculation of the IM cost unit;
- the FI documents because the stock in transit doesn't exist in logistic, in SAP we only have this coming from FI documents and we are using the information from the working capital model.
with these elements we are able to determine the stock margins for WP1 in BW.
After we also have some cross cases stock in WP1 with production in PF1 or the other way and this is also done in BW side.
With this no automatic process from BW to BFC (business needs to perform some analysis/validation).
In summary: Provide a query that give for PF1 and WP1 : - Stock at end of month; For PF1 data the calculation is done in PF1 : those results are aggregate in BW. For WP1 data and Cross-system cases ; the calculations are done in BW. The query will be formatted to be loaded manually in BFC ; but no automatic a loading . Manual adjustments/corrections can be done. |
BW Target Model: the WP1 solution in brief
1- We are extracting the data for the quantities in stock (BW stock model) and in transit (working capital information).
2- We need to identify which is the manufacturing plant of the material because this can be different from the plant. For this we can use the batch info or the material master data.
3- We will valuate the stock at IM Cost unit and to take into to account the transport time it was requested to have this shift of month. Exist a table where the shift of month can be changed by the user (Transaction ZBW_SHIFT_MONTH it will be used via uploading an xls format file in BW the table is /BIC/AAPCOMS032).
4- To valuate the stock at standard cost it's done M+1 to take into account some Solvay pratices regarding the stock revaluation process.
5- The difference will be our stock elimination.
An integrated solution:
For this scope it's important to understand the logic for the integrated margins since the new BW Stock elimination Margin will share the same calculation elements as the P&L in integrated margin - the Integrated Margin (IM) cost unit.
The quantities for:
- Logistic Stock are coming from the BW stock Model;
- Stock In transit are coming From Working Capital Stock Queries.
For the integrated margins logic please check our functional documentation under the scope "Profit and Loss Integrated Costs".
BW Stock elimination Query - Calculation for WP1
i) Calculation in BW Stock Margin Elimination Report - Main process:
Evaluate logistic Inventory at IM cost - Main cases
At D+2/D+3 we extract Total Stock quantities from BW logistic model at Material /plant / Batch Level.
At D+2/D+3 night IM cost unit for WP1 materials are calculated at:
- Month
- Plant
- Material (WP1 code)
IM cost unit are splitted:
- CP
- CNP
- AMO
- Internal duties
- Internal freight
Determine Manufacturing Plant:
1. Manufacturing Plant of Batch if existing
2. If no Manufacturing Plant or no Batch
- Production Plant of material master data (coming from Sp);
- Plant of Stock.
Determine Reference Month:
- We add a Shift of months:
- The shift of month is GBU dependent
- Exception cases maintain for a list of Material /Plant
We evaluate the total Stock quantity using IM Cost unit:
- For the Manufacturing Plant
- The reference Month (closing moth - Shift of months)
- For the material Code
We convert at Company Code currency using:
- Exchange rate M at end of the Closing Month.
If we don’t find IM Cost unit for the manufacturing plant we take the IM cost unit at plant level |
Specific Case : Manufacturing Plant = Plant
If the we determine a Manufacturing plant equal to the Stock (ie: the product is produce in the same plant ) we can have Stock elimination only if raw(s) material(s) is produced from another plant. In all other cases , applying shift of months generate “fake” Stock elimination.
It was first decided to exclude from the perimeter all cases for which the plant = Manufacturing Plant before being able to refine the management rules.
Note: This exclusion can be delete from the query.
Specific Case : Intra/Inter Company
The manufacturing Plant could come from another company code or from the same Company.
The Filter Flag Intra/Inter Com can be used to exclude/include those cases.
Specific Case : Inbound / Outbound cases
The model S can provide stock elimination for plant not linked to the company code . These cases are explained by dropshipping flow especially for IN TRANSIT posting.
We must take into accounts those stock margin elimination ; but we create a flag that permit to regroup these cases.
Case of Internal Freight Cost & Duties:
If we use only IM Cost unit of Manufacturing Plant B for Stock in Plant A , we lose the freight between B an A (Internal Freight/Duties B=> A).
In the query : For Duties and Internal Freight we use:
- the Internal Freight and duties at Plant level if it’s not empty else the Internal Freight and duties at Manufacturing Plant level (case of trading plant).
A complex Case : Plant 7681 Material 16169 for 12/2019
Quantity in stock
In BW
In SAP (MB5B)
2 manufacturing Plants and 3 Batches:
Batch WX09G0302 give the manufacturing plant 8034.
Batch 0924506 give no manufacturing plant.
Controlling SP J3 give the manufacturing plant 7897 (do not forget to control Logistic SP , if Controlling SP is empty).
The reference Month is 11.2019
We use IM cost Unit for 11.2019.
We convert it in EUR (ZFR3 currency) using M end of December 2019.
By selecting November to December 2019, we select November IM cost unit and convert them at end of December. |
Example IM cost unit is existing only for plant 7897 and not for plant 8034.
For manufacturing Plant 7897:
IM CP = 3261 kg (Total stock) * 6204.33 eur / 1000 kg (lot size Manufacturing plant) = 20 232 eur.
For manufacturing Plant 8034 : we take the default rule => IM Cost unit of the plant.
IM CP part =5000 kg (Total stock) * 6258 eur / 1000 kg (lot size plant) = 31 311, 40 EUR.
In BW report we use the standard price of the next month to take into account specific postings are done in SAP that permit by nullify SAP standard stock revaluations (PR) and post it on the next month (posting on Account 1000028227) .
By using M+1 standard price we follow the business rule of this specific process
Detailed rule :
If the material is at standard Price (0PRICE_CTRL = S) we use the price Type P01
Else if the material is at movering average price (0PRICE_CTRL = v); we use P02 if P01 is negative or equal at 0 else P0.
ii) Calculation in BW Stock Margin Elimination Report - Stock in transit
In RCS , the only way to have quantities in transit is to use FI Document . This information is provide by BW Working Capital query.
Remark: we exclude “1000028227” account because we use for stock logitic valuation the standard cost M+1.
Determine Manufacturing Plant :
- Because in FI document there is no Batch the manufacturing plant is:
- Production Plant of material master data (coming from Sp) else .
- Plant of Stock.
Determine Reference Month WC :
- We add a Shift of months:
- The shift of month is GBU dependent.
- Exception cases maintain for a list of Material /Plant.
The shift of Month for WC could be different from Logistic Stock. |
We valuate the total Stock quantity using IM Cost unit :
- For the Manufacturing Plant.
- The reference Month (closing month - Shift of months).
- For the material Code.
We convert at Company Code currency using
- Exchange rate M at end of the Closing Month.
Tips
1) Loading Source axis permit to split data from their source
Add attributes to the Plant or the manufacturing to know the country/ company …. 2) Model S reports works even if you decide to delete some axis from the report; for instance the material. But keep in mind some Keyfigures will not be relevant at an agregated level: Cost unit; Reference Month, Lot Size. 3) Reference Month Logistic / Reference Month in Transit give you the month used for The IM valuation. |
Tips : how to Control/ understand Models S calculations
Logistic part | Stock In transit part | |
|---|---|---|
Total Stock | transaction MB5B in WP1. | Workbook Working Capital Inventory Extract in WBP. |
Manufacturing Plant | Transaction MSC3N to see the batch attributes (WP1). Material Master data(Transaction MM03) for special procurment key: COSTING view or MRP2 view. | Material Master data for special procurement key: COSTING view or MRP2 view. |
Reference Month | By default M -1 no exception currently. | By default M no exception currently. |
IM cost unit for reference month | Launch the P&L query P&L Integrated Margin - IM cost unit from Reference month to closing with exchange rate M and manufacturing plant in Analysis Launch transaction Z_INT_COST for Reference month M-1 and manufacturing plant. | Launch the P&L query P&L Integrated Margin - IM cost unit for Reference month and manufacturing plant in Analysis. Launch transaction Z_INT_COST for Reference month and manufacturing plant. |
| IM valuation (CP/CNP/AMO) | If IM Cost Unit of Manufacturing plant is existing then Qty in stock * IM cost unit of Manufacturing plant for the reference month / Lot Size Manufacturing Plant Else Qty in stock * IM cost unit of Plant for the reference month / Lot Size Plant We convert at end of month using the exchange rate of the prompt -> Currently we concert in currency of the plant (to be change). | Idem |
| IM valuation (Freight & Duties) | If IM Cost Unit for freight and duties of Plant is existing then Qty in stock * IM cost unit of Plant for the reference month / Lot Size Plant Else Qty in stock * IM cost unit of Manufacturing Plant for the reference month / Lot Size Manufacturing Plant We convert at end of month using the exchange rate of the prompt -> Currently we concert in currency of the plant (to be change). | Idem |
| Standard Cost | Use transaction CK13N for the material and the plant and for the Month M+1 in WP1 to obtain the standard unit cost for M+1 By multiplying by Total stock and divide by Lot Size Plant we calculate the standard Cost. |
Stock Margin elimination From PF1
BW Stock Margin elimination provide WP1 and PF1 stock Margin elimination. While the WP1 part is calculated in BW, the PF1 part is entirely calculated in PF1.
For mode details of PF1 calculation see : https://docs.google.com/presentation/d/1GqhfHkYR5mDF1hdGLKi2jkKwXEuT_K4cIYwPLImVTqo/edit?usp=sharing
The results of the PF1 calculation are extracted. The only enriched elements in BW are:
- The attributes of Material /manufacturing Plant.
- The BFC GBU by reading the Material /plant.
The other Characteristics (Manufacturing plant ,Batch , plant company , material …) are determined in PF1.
All the Keyfigures (except Standard Cost) are calculated in PF1 : IM cost ; and Stock Margin Elimination.
The only calculation done in BW is the Standard Cost Which is the summation of LEGAL VARIABLE + LEGAL FIX + LEGAL DEPRECIATION + LCM.
How to identify PF1 datas in BW query
Stock margin elimination PF1 & WP1 are mixed in BW Stock Margin elimination query.
How to Exclude/Select UPIS in the BW query prompt? By selecting the Source system you are able to select (or exclude as you want ) PF1 calculation data from the reporting.
How To identify UPIS data in the query?
If you have decided to select WP1 and PF1 in the report (it’s more longer) , you can identify PF1 data thru Characteristic 'Loading Source'.
Calculation in BW Stock Margin Elimination Report - Cross System cases
In the real life there is Cross system cases:
- Stock in PF1 with production in WP1
- Stock in WP1 with production in PF1
The difficulties are:
- identifying Cross System cases;
- Translate Master Data code PF1<->WP;1
- Valuate at IM of Erp X the stock of the Erp Y.
How to identify Coss System cases
The unique object which is transversal for both PF1 and WP1 is the Batch.
In the Batch we have the information of the plant (so by consequence the Erp Origin).
If a stock in Erp x for a Batch, we have a Manufacturing plant from another ERP , we will consider this entry as Cross System. Else the BW model consider it as MonERP case.
Remark: Only data with Batch information feeded can tested and considered as Cross System .
Remark 2: Batch master data are central to identify cross system cases
How to identify Convert PF1 Material Code <-> WP1 Material code
For the material managed in MDG , we take in priority the link managed in MDG
Else
We use the information managed in the Material Master Data : the field Old material code
How to Valuate PF1 stock at WP1 IM Cost unit at Batch level
- The quantity in Stock and the Standard Cost are the same than the quantities and the standard Cost calculated in in PF1.
- if we can:
- translate PF1 Material Code in WP1 Material Code.
- we find a real WP1 plant in the Batch.
- we find a WP1 IM Cost for the closing month.
- Then :
- IM Cost valuation (CP / CNP / AMO / Freight /Duty ) = Quantity in Stock for PF1 material Code * IM Cost unit for WP1 Material Code / Manufacturing Plant for the Closing Month (no shift of month because in PF1 there is no shift of month).
If we are not able to convert or find any IM cost unit we take the IM valuation (CONSO) done PF1.
How to Valuate WP1stock at PF1 IM Cost unit (conso) at Batch level
- The quantity in Stock and the Standard Cost are the same than the quantities and the standard Cost calculated in WP1.
- It concern only Logistic Part (there is no Batch for In transit).
- if we can:
- translate WP1 Material Code in PF1 Material Code .
- we find a real PF1 plant in the Batch.
- Using PF1 calculation done in Month M-1
- We recalculate IM unit cost at Material/ plant level by dividing Stock quantity by Conso value.
- Then :
- IM Cost valuation (CP / CNP / AMO) = Quantity in Stock for WP1 material Code * IM Cost unit for PF1 Material Code / Manufacturing Plant for the Closing Month M-1.
If we are not able to convert or find any IM cost unit we take the IM valuation (CONSO) done WP1 as in Model S standard model.
Model S : Planning (approximative Hours) and Inputs WP1 part
Model S : Planning (approximative Hours) and Inputs PF1 part
