The Profit & Loss Integrated Costs or Integrated Margins application it’s part of the Finance domain using data from SAP systems and non-ERP system information based on data already built for the Profit and Loss report using COPA data (Controlling - Profitability Analysis) a component from SAP CO module.
The goal of the Integrated Costs is to have the full profitability of a product at customer level.
- In standard reports the margin is given by legal entity,
- With the integrated margin report, the aim is to give the margin at Group or GBU level and get rid of legal entities constraints.
For this Application the access is the same as the Profit and Loss so it's based on the BW menu "PL - Profit and Loss" 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 "PL - Profit and Loss"
VERSION | DATE | MODIFIED BY | DESCRIPTION |
0.01 | 01.08.2023 | Inês Vilares | Initial draft |
| Data Product Type |
|
| Technologies |
|
Data Sources Note: list of all applications and various environment |
|
2.0 Business Process
From the business process please see below the logic comparing the contribuiton margin with the integrated margin process for selling a product to a final customer where in this scenario, the product undergoes multiple stages involving several companies from Solvay, in its production. Throughout this process, there are internal margins between the companies which making it challenging to determine the real value of the external margin when the product is sold to the final customer, as these internal margins are already included into the overall flow.
To obtain a clearer view of the external margins, we have this process to not take in consideration the internal margins and only have the influence of the freight and duties. By doing so, we can present more transparent and easily comprehensible values of the contribution margins for each company involved.
Contribution Margin Concept:
Integrated Margin Concept:
3.0 Application Feature Overview
| Reports | Definition | Prompts | BW Workbook Query | Query Technical Name |
|---|---|---|---|---|
P&L Integrated Margin - Freight & Duties Costs Tables | This workbook is to be used to display freight costs used in the integrated margin calculation. For this we have 2 sheets:
In the Process Detail section we can see in the more detail the information and how to use it in the reports. | Optional:
| BW_WBK_PL_0006 | Tiers info: BW_QRY_MVCOPA04_0001 Manual Table info: RCS_QRY_ODS_PCP5_001 |
| P&L Integrated Margin - IM Cost Unit Query | This workbook is to be used to display the estimation of the IM cost unit after the calculation of Z_M_INT program (a monthly program executed to calculated the integrated margin). | Mandatory:
Optional:
| BW_WBK_PL_0013 | BW_QRY_MVCOPA06_0001 |
| P&L Integrated Margin - Contribution Margin Query | Old workbook. We are evaluating if we can remove this from the scope. | Mandatory:
Optional:
| BW_WBK_PL_0005 | BW_QRY_MVCOPA01_0005 |
| P&L Integrated Margin - CM/GM Query | This report represent the values for the P&L financial statement until the Gross Margin KPI. Can be analyzed by one or several companies, GBU's, among other fields to see the KPI's information. It's possible to select one period or more. The conversion will be based on the last period in the selected prompts. In this statement we have in consideration the integrated margin values which are represented in the measures using the "C" in the Heading. | Mandatory:
Optional:
| BW_WBK_PL_0004 | BW_QRY_MVCOPA01_0004 |
| P&L Integrated Margin - Monthly CM/GM Query | This report represent the same data from "P&L Integrated Margin - CM/GM Query" but this shows the data for all months of the year selected in the prompts. | Mandatory:
Optional:
| BW_WBK_PL_0007 | BW_QRY_MVCOPA01_0037 |
| P&L Integrated Margin - Monthly CM/GM Query (Month Selected) | This report represent the same data from "P&L Integrated Margin - Monthly CM/GM Query" but this only shows the data only for the month selected in the prompts. | Mandatory:
Optional:
| BW_WBK_PL_0010 | BW_QRY_MVCOPA01_0009 |
4.0 Functional Specification
4.1 General Data/Calculations
For these reports, it’s important to understand some general concepts which are transversal to all the reports which allows the users to work with the reports in the same way as the SAP system and BFC tool.
| Consolidation View | The query takes the integration rate of the company and the partner from the company code master data.
The formula to retrieve the consolidated sales is in line with BFC: Consolidated Sales = Amount * Max [ 0, Integration Rate of CY (tingsoc) - Integration Rate of Partner (tingpart) ] Internal Sales = Amount * Min [ Integration Rate of CY (tingsoc), Integration Rate of Partner (tingpart) ] The integration rate of the partner is managed on the TRADING PARTNER. For specific cases some external customer must be considered as internal for certain company code (joint venture for example) . For cases the Partner integration rate is taken at the crossing Sold To/Company code. In this way, the user has 4 options:
|
|---|---|
| GBU Assignment | The concept for GBU doesn't exist in SAP but it’s a requirement to have in BW since in BFC we have this definition. This GBU assignment is different for WP1 and PF1 and it will be explain in detail in the Process Definition section. |
| Exchange Rate | The exchange rates are explained in the functional documentation "Profit and Loss Report". See below some links usefull for the exchange rates:
|
| PRS Concept | The PRS is one dedicated SAP environment (based on PF1) whose aim is to unify some structural information as Master Data information for customers, vendors, company codes, etc. For this, depending on each master data we are referring to, we have specific tables created to maintain the data and have the configuration from source systems to BFC definition and alignment. |
| Value Fields concept | CO-PA is a hypercube (like a pivot table in excel) where figures are posted to value fields. Value fields are mapped to BFC accounts in the interface tables. This interface between SAP and BFC has several tables which allows to have the data aligned. |
| Annual Closing | Each end of year, the main BW master data (about the structure) which are not time-dependent need to be frozen = no more loading from ECC or flat file on these main master data. To avoid the impact of the new evolution scheduled for next year on the yearly closing a freeze is applied to block the loading to allow the yearly closing period with the same scope done for the year and not with inputs for next year, for some master data, the loading are freeze in the middle of December until end of January. So when there is a new organization, historical data is restated in BW (not in SAP and BFC). |
4.2 Process Detail
4.2.1. Report/Process Definition
| Domain | Finance |
|---|---|
| Application | BW reports under P&L folder |
| Provider | MVCOPA01, MVCOPA04 and MVCOPA06 |
SAP BW High Level View
To see the data from the source systems see below how the information is organized in the BW perspective:
Part of MVCOPA01 provider:
PQ1 - old data not used anymore.
MVCOPA04 & MVCOPA06 providers:
Integrated Margin for PF1
The logic for PF1 is using a specific program ZZCONACT where it's manage in PF1 system and we only load the data into BW.
Integrated Margin for WP1
In order to include the necessary information in the P&L statement, please refer to the following two steps to acquire this information:
| 1º Step: Costing Run | In WP1, the costing run is marked on D-2 or D-1 and released on D1. It is used as a reference for the calculation of the integrated costing. |
|---|
"Costing run" refers to a process in which costs are calculated and assigned to various objects within a business or production process. Typically involves the calculation of costs associated with materials, products, or services.
During a costing run, the system calculates costs based on various factors such as direct materials, labor, overhead, and other expenses.
When there is a code in each view, the one used in the integrated costing calculation is the one in the Costing 1 view.
| 2º Step: Freight and Duties | The average freight & duties can be determined: With a table manually maintained and uploaded in BW and using a calculation done in BW with data coming from the application TIERS (Transport Integrated and Enhanced Reporting Solution). |
|---|
For this part, we have a workbook BW_WBK_PL_0006 P&L Integrated Margin – Freight & Duties Costs Tab where we have two tabs:
- Default Freight Cost - Is an estimation of freight cost between 2 plants => these amount are managed manually by controller and loaded by flat file via ZBW_LOAD_ODS_PCP5;
- Freight (TIERS) & Duties - Calculation done by BW based on TIERS (only when activated). The calculation is based on the average of the last 6 months actual costs coming from the application TIERS. (Supply Chain team) andthe estimation of duties between 2 plants and for material per month => coming from P&L from moth M-1.
See the example below to understand how this works:
Example
Default Freight Cost vs Freight Costs (TIERS) & Duties – Case: Freight costs from China to South Korea – May 2018
For this process, we can outline the following information regarding this topic:
To get the information to calculate the integrated cost in P&L, based on this information gathered in the 2 steps, exist a program Z_M_INT which will fill the data into P&L for the respective headings.
Program Z_M_INT
The Z_M_INT program it's very complex and huge to calculate the integrated margins for P&L.
On a high level view this is the process for the Z_M_INT:
| Steps | Definition |
|---|---|
| Step 1 | Identification of ALL CCR (Costing Run) Items to address: Process TOP/DOWN |
| Step 2 | Removing duplicate CCR (we keep the lowest CCR) and classification CP / CNP / AMO of the CCR lines |
| Step 3 | Calculation of CP/CNP/AMO: DOWN/TOP process |
This is done for each mateiral/plant/variant.
In all Costing Run found and "treated“, program removes duplicate Costing Runs , keeping only the Costing Run from the first component at the most detailed level of the BOM. Each component is calculated one time in the global process.
When we have 2 plants:
Business Rule
- We search CP / CNP / AMO components of the origin plant calculated for the current month in the previous steps and add it to CP / CNP / AMO of the treated material.
- Case of currency change or unit change are taken into account
- For the currency conversion TCURR table is used type CAR1. The exchange rate is the one on the 1st day of the month of the Costing Run.
- Case of currency change or unit change are taken into account
- We add Freight costs from Tiers freight table at the Plant of origin / Destination Plant /material level & Duties Costs
- If nothing is found in the Tiers Tables we use the default Table at Plant of origin / Destination Plant level
- if the route does not exist it is creates an entry in the default Freight Table in BW with a Freight cost = 0
- no currency change for freight cost. Freight costs must in Departure plant currency
- The lot size changes are taken into account in the calculations.
The program uses the issuing plant (8035) of the Costing Run . This plant in the Costing Run is coming from the Special Procurement Code of the material (105180 of the plant 7900)
After these calculations the program will provide the final values to be in the P&L headings specific for this scope. Normally they are identified with a "C".
The transaction Z_INT_COST - Integrated costs Details after the program execution we have the values for CP/CNP/AMO/Freight calculated and this transaction is "dynamic" in "live" computation with the elements currently in BW.
For this process we have some specific cases:
- CPX Cases;
- LER Cases;
- Cross-System Cases.
Please find below a brief definition for each case.
CPX Cases
CPX is a mini BW system of SOLSTICE and for the integrated margins they have a special process to have the values into the Z_M_INT program.
1 - Transfer source data from WBP regarding the WP1 costing runs and the average freight costs to CPX.
2 - Calculation of IM in CPX side with data from WBP and WPX.
3 - Transfer the result of IM in CPX (with no details of the components) to WBP and take it into account in the calculation of IM in WBP.
Legal Entity Reduction (LER) Cases
Just a note: The flow exists but since 2022 the logic is the same for the business rules.
Cross-System Cases
Sales in PF1 / production in WP1
In a few specific flows , Sales for GBU Novecare are done in the PF1 system and production is done in WP1 system. Integrated Margin calculation done in PF1 for those sales are not reflecting IM costing in WP1.
Due to these situations it was created an enhancement where:
1) Identify these flows
2) Check eligibility of each posting for WP1 valuation
3) Exclude PF1 IM calculations to avoid duplicated count in report
4) Valuate PF1 sales with WP1 IM costing.
In this way, we can have the correct integrated margins for PF1.
For this process it's required to convert PF1 Material code in WP1 material code.
Eligibility Test:
After this, if we have a qualified posting then we need to delete the IM PF1 data founded and calculate the new lines for PF1 data.
See below the example:
Aero Fields: Solstice Companies (Composite Materials)
When the Cytec companies were acquired by Solvay they had to integrate the companies under the project Solstice.
Before the acquisition the data from these companies were reported into Cognos (from BAAN and PE1 system). The project was to migrate the data into WP1 in different steps were they started 1st with the BAAN companies to be in WP1 system and after the PE1 companies.
Within the migrations several processes were adjusted to integrate these companies and for the integrated margins this was adjusted to have the correct information also with there way to report under the guidelines for their scope.
To see more information please check the following links:
In this way, exist a special workflow for this please see on a functional perspective the flow:
Flows
Flow 1: The list of Spec is sent to the Sales Order line (C_ITM_SD) as a master data place;
Flow 2: sent this data to the Dynasis scope.
Then the P&L side receives the information from the invoices (flow A);
After on Dynasis side the data sent to the C_ITM_SD is the Main Spec information (flow 3) and that information will add content to the P&L side (flow B).
Also on Dynasis side they work the information and split the data with a different structure and values with the Program, Market and Program Percentage (flow 4) which will be sent to the P&L to complete the information for reporting purpose (flow C). This part runs on a daily basis to update in P&L side the fields Program, Market and the Program Percentage to be align on the way the users analyze the data for these companies.
UPIS- Unrealized Profit in Inventories
This information was to provide data for logistic side to be available for the Integrated Margin concept. It's coming from a upload of a file in ZPL_UPIS Transaction. (for the moment not used)
ZBW_INT_MARGIN Transaction
This transaction as the main goal with a BW enhancement of the current IM process allowing manual corrections in some steps of this automatic process.
The idea is to let key users loading flat files in order to manage needed corrections in 4 distinct steps of the current BW IM process
- A) Manage default values in Freight & Duties tables
- B) Manage correction entries in Freight & Duties tables normally populated thanks to a Tiers query (at Material / Plant level)
- C) Manage correction entries in IM Cost unit table
- D) Manage exclusion entries in COPA line items table, so they can be excluded from the P&L IM calculation
A – Manual update of IM Transport Costs & Duties at plant level:
When to use it ?
When the system (Tiers) can not calculate a unit transport cost for a material/plant when for instance when there is no historical data. It can also be used when duties are missing.
How to use it ?
Load an excel file with an estimation of the transport & duty cost between 2 plants.
Template:
B – Manual update of IM Transport Costs & Duties:
When to use it ?
When the system (TIERS) calculates a unit transport cost that is wrong. It can happen for instance when there has been an exceptional transport that distorts the calculation. The table can be used to replace the calculation done by TIERS.
How to use it ?
Load an excel file with the average transport cost to be used in the IM calculation (by material/plant). It will replaced the one calculated by Tiers.
In order to ensure the traceability of the changes, the date and the user who loaded the changes are stored and it will be possible to add some explanations about the change (ticket etc.)
Template:
C – Manual update of IM Cost Unit:
When to use it ?
When the unit integrated costing calculated by the system is wrong. For instance when the standard costing is wrong in WP1 and it is too late to rerun it.
How to use it ?
Load an excel file with the unit integrated margin cost to be used. It will replaced the one calculated by the IM program.
In order to ensure the traceability of the changes, the date and the user who loaded the changes are stored and it will be possible to add some explanations about the change (ticket etc.).
Template:
D – Exclusion of COPA postings:
When to use it ?
Some COPA postings are taken in the calculation of the integrated margin while it should not be as some amounts are duplicated. It can happen with manual COPA posting with wrong quantities. The aim is to exclude these postings.
How to use it ?
Load an excel file with the list of COPA postings to be excluded
In order to ensure the traceability of the changes, the date and the user who loaded the changes are stored and it will be possible to add some explanations about the change (ticket etc.).
Template:
For more information we have the following documents to support this:
5.0 Non-functional Descriptions
5.1 Usability
as per standard.
5.2 Regulatory Compliance
as per standard.
5.3 Security
as per standard.
5.4 Performance
as per standard.
5.5 Reliability
as per standard.
5.6 Scalability
as per standard.
5.7 Compatibility
as per standard.
5.8 Availability
as per standard.
5.9 Refresh of the Data
This process to have the integrated margins is done once per month in the closing time.