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
https://solvay-dwp.onbmc.com/dwp/app/#/itemprofile/622
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)"
VERSION | DATE | MODIFIED BY | DESCRIPTION |
0.01 | 24.10.2023 | Inês Vilares | Initial draft |
| Data Product Type |
|
| Technologies |
|
Data Sources Note: list of all applications and various environment |
|
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).
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.
