Domain: Costing

Responsibility area: Supervise Inventory Valuation

Table of contents 


Scope

ERP

PF2

WP2

Frequency

References

Forms

Attachments


 

Previous OP << I calculate inventory reserves >> Next OP



1. Objective and Scope

1.1. Objective of this Operation

The purpose of this document is to explain how to generate the proposition and how to account in ERP the valuation of the stocks at the “fair value” in order to give relevant information about the “real value” of the company to all economic actors (share-holders, legal authorities...).

1.2. Scope

 This procedure is applied to all companies WW.

2. Definitions

Finance - Glossary

3. Tasks description

3.1. Background 


* Hard-Close instruction for Inventory Reserve (please check attachment)

Objectives

 The purpose of this document is to explain how to generate the proposition and how to account in ERP the valuation of the stocks at the “fair value” in order to give relevant information about the “real value” of the company to all economic actors (share-holders, legal authorities...).   

Before the closure in the months of March, June, September and (November and or December)*. The final validation and postings should be done until D-5 at the latest. 

General Rules - Frequency (*Best Practice)

  • On a quarterly basis, review the impairment of inventories based on the matrix of inventory and impairment type that is recommended and update an existing inventory reserve.
  • At least once a year, justify slow-turn inventory reserve in November for hard-close and NRV inventory reserve in December for year-end close.

  • Mandatory for all companies included in the consolidation under full integration
  • Recommended for "joint operation", joint-ventures and where Solvay is considered as "associate"
  • All types of inventories that need to be written down by below impairment cases:
    • Slow-turn
    • Obsolescence (end of shelf-life)
    • Off-specification / bad-quality
    • Damage
    • Net realizable value
 Slow-turn

Obsolescence / Off-specification / Bad-quality / Damage

NRV
To be validatedIn NovemberIn NovemberIn December
Finished GoodsXYESYES
IntermediateYESYESX
RMYESYESX
Spare PartsYESYESX
PackagingXYESX

*YES means "designated impairment types should be checked for the concerned inventory type

WHOWHATWHEN
FRA
  • Ask Service Center to reverse the slow-turn inventory reserve of Q3.
  • Ask PIM to initiate an inventory reserve for obsolete, off-spec and damaged inventories and validate it.
  • In WP1 perimeter, ask Service Center to calculate a slow-turn inventory reserve based on the stocks at the end of October and validate it.
  • In PF1 perimeter, calculate a slow-turn inventory reserve based on the stocks at the end of October.
  • Ask Service Center to post a new slow-turn inventory reserve for November closing.
November
PIM
  • Validate an obsolete, off-spec and damaged inventories and propose an inventory reserve to FRA.
November
GCCO
  • Identify the NRV invnetory reserve of Q3 and if no NRV reserve posted in Q3, validate it if there is no impairment indication by NRV.


  • Ask Service Center to recalculate the NRV inventory reserve based on the stocks at the end of November and validate it in WP1 perimeter and calculate it in PF1 perimeter.

November

 

December

Technical expert for spare part
  • Propose an inventory reserve for obsolete, off-spec and damaged spare parts.
November
Service Center
  • Calculate an inventory reserve in WP1 perimeter and report it to FRA, according to this procedure.
  • Post an inventory reserve that is validated by FRA (slow-turn) or GCCO (NRV).
November
RCOM
  • Follow up external audit requirements for hard-close in Audit WIKI.
December
CAM
  • Review stock accounts for inventory reserve based on the CAM Guidebook.
December D4

 

 

General Rules - Depreciation

3.2. Responsibilities

SU MAC runs the transactions and prepares the Excel files with proposal of rotation and financial depreciation.

Controllers are responsible to analyze the posting proposal sent by SU MAC  and provide the final validation for the depreciation amounts to be posted, indicating the changes to be done to the original posting proposal. In some cases, an additional comment on the largest variances between depreciation proposed and validated is required.

Regarding Spare Parts and Gen & Tech (exc. Packaging) the Accounting Platform Team is responsible to perform IFRS vs LOCAL GAAP postings  - see specificity on point 3.3) 1) For Spare Parts 1) PF1: Industrial Supplies - DE Specificities

3.3. I run inventory valuation reports


STEP 1

Start the transaction ZCO_SPAREPARTS


Industrial Supplies Depreciation: initial screen



STEP 2

  1. Select variant: SLOWTURN-SP

  2. Enter the company code 

  3. Enter Year and Period ( period must be end of month before quarterly. Feb May Aug and Oct for Hardclose)
  4. Execute  to display the report or execute in background to save the report to SQ00



STEP 3

The report is displayed

Save the report  by pressing in order to have it available in the SQ00 query ZCO_SPAREPARTS. These will be the final figures to use in the posting. If you have not saved the report, some inconsistencies may be created.




STEP 4

extract the report and put in the inventory reserve template

  1. there are 2 provision type in the report

  2. Unrestricted provision is calculated by slow moving day defined by the rule

  3. Blocked provision is calculated by amount of inventory in blocked status (100% provision)


STEP 5






Tasks to be completed when documenting an SAP Transaction, Report or Message code.

1. Title of the page = SAP Transaction code, SAP Report or SAP Message

2. Add the following Labels, respectively:

    • SAP Transaction => "sap_transaction", Transaction code "xxxx"

    • SAP Report => "sap_report"

    • SAP Message => "sap_msg"

3. On the left section, describe the steps. On the right section, insert respective print screens and additional guidelines, if needed (e.g. Main selections of the transaction...)

STEP 1

Use transaction code: ZCO_SPAREPARTS_POST


ZCO_SPAREPARTS_POST- Spare parts provision posting: Initial Screen



This program is for automatic posting of spare parts provision which data is saved in SQ00 table ZCO_SPAREPARTS


The data must be available in SQ00 before processing this transaction







STEP 2

This program is used to post/reverse spare parts provision

There are 3 processing type for this transaction

  1. Posting 

  2. Reverse

  3. Display




STEP 3

Fill the selection

Selection Parameters:

  1. Company code
  2. Valuation area put * to select all valuation area or specific the value
  3. Provision calculation period: period that the data is extracted to SQ00 (provision period = Feb, May, Aug and Oct)
  4. Processing type:
    - Posting = put the posting date as end of quarterly month
    - Reverse = put the same date of the posting to repost the provision or put it in next quarter
    - Display = only display the data
    Then execute the transaction





STEP 4

Output results:

Posting

  • When Test mode is selected:  you can save and output will give you the status whether it can be posted (in case of the error from Master data is missing please contact SU MAC)












  • When test mode is not selected: when you save the output will return with document number and the items will be locked with document number












Reverse

  • Steps are similar to the posting 


Provision data will be frozen in the table SQ00 after document is posted by the program





NOTE:

In case it's necessary to update the CC automatically used, this should be done directly in transaction ZCO_SP_POSTSCHEME



Tasks to be completed when documenting an SAP Transaction, Report or Message code.

1. Title of the page = SAP Transaction code, SAP Report or SAP Message

2. Add the following Labels, respectively:

    • SAP Transaction => "sap_transaction", Transaction code "xxxx"

    • SAP Report => "sap_report"

    • SAP Message => "sap_msg"

3. On the left section, describe the steps. On the right section, insert respective print screens and additional guidelines, if needed (e.g. Main selections of the transaction...)

STEP 1


Use transaction code: Z1K_SPAREPARTS

 

You will be able to find four sections:

  • Selection Screen
  • Layout
  • Download to excel

PF2 - Report Z1k_SPAREPARTS: This report will provide you extraction of several fields of the master data of the material as well as stock quantities, values and consumption-



This report is mainly used to provide information for the calculation PF1 inventory reserves:

  • Raw Materials
  • Spare Parts
  • Finished goods (limited)






STEP 2 Selection Screen

In the Selection screen, the following data needs to be selected:

  • Company code
  • Valuation class (Of the material)
  • Year
  • Month
  • The other fields, can be selected depending on the level of detail available/needed
  • Layout can also be selected if available (Otherwise can be define after execution)



STEP 3 Layout

 

Depending on the information needed, select the different fields to prepare the report


First screen after execution:


Layout selection:



STEP 4 Extraction to excel


It is possible to extract to excel in two different ways:

  1. Excel
  2. Local File


Go to list - > Export

and select the option needed















IFRS accounts

The Z1K_SPAREPARTS transaction only uses the G/L account 6310000000 on the debit side which is a limitation for Germany companies, once they use dedicated accounts depending on the material. For Spare Parts it should be used the G/L account 6310000020 and for Gen & Tech (exc. Packaging) the G/L account 6310000050, according the following matrix: PF2 Germany WD_Inventories accounts.

To guarantee the correct reporting, a manual reclassification will have to be performed by Accounting Platform Team.

Debit PL account 6310000020 AND/OR 6310000050 (in order to determine the amount to be transferred take into consideration the balance account, whether it is 2119010000 Spare Parts or 2119000000 Gen & Tech (exc. Packaging).

Credit PL account 6310000000

Eliminations accounts

The Z1K_SPAREPARTS transaction only posts in IFRS. Therefore, elimination will have to be manually performed by the Accounting Platform Team:

Debit BS account B119010009

Credit PL account F310000029

  • Gen & Tech (exc. Packaging): not to eliminate (no difference IFRS vs LOCAL GAAP)

Every time there are new users performing this procedure, it's important to maintain them in a specific table, so that they can be considered "Authorized Users to Save Spare Parts Report": T-Code GS02, Table ZCO_USER_SPAREPARTS (to be done by the TL).

NOTE All users can run the report (both in foreground and background), but only the User IDs that are maintained in the set ZCO_USER_SPAREPARTS can save the report result in the customized table. If the Saving part is not Done, we might have inconsistencies between the proposal posting and the real posting performed by the system.


STEP 1

Start the transaction MRN1


Determine Lowest Value: Range of Coverage: initial screen



STEP 2

  1. Enter the company code 

  2. Key date: Last day of the month   = reference date for the calculation of R.O.C.

  3. Material type: ZMAT : Finished products & RM & SF

  4. Click on Valuation level  to open a new window 



STEP 3

Select "Company Code"



STEP 4

Click on 

Enter:

  • “Upper limit” = “key date”
  • “Lower limit” = “Upper Limit” – 12 months

The range of dates corresponds to the period of analysis of the monthly stocks 

and enter 



STEP 5

Click on 

  1. Select and enter the range of date in the first range as the analysis period for the determination of consumptions.

and enter 


The option “Determine consumption via user exit” can be selected to apply the  rules  to calculate the range of coverage (average consumption).



STEP 6

Click on 

It is the standard cost of the month

  • If the transaction is run before the end of the month: 
    • Select the current material price
  • It the transaction is run after the end of the month: 
    • Select the Mat. Price Prev. Month

and enter 




STEP 7

  1. Check “Database Update” and 2 new buttons appear.
    • Never click on “Change Material Price”
  2. Click on 
  3. Check “Tax Price 1” to store the price calculated by the system


and enter 


Then start the calculation by clicking on 

Ignore the warning message by clicking on 



STEP 8

The report is displayed


  1. M aterial Master Data & plant code.

  2. Stock quantity at the end of the month (execution date)

  3. Average consumption on the last 12 months

  4. Number of months needed to consume the whole stock with the average consumption = Average stock (2) / Average consumption (3)

  5. Base price used for the calculation of impairment (= standard cost)

  6. % Deduct calculated in function of range of coverage (4)

    • Range of coverage < 12  = 0 %

    • 12 < Range of coverage ≤ 24   = 50 %

    • 36 < Range of coverage   = 100 %

    • 24 < Range of coverage ≤ 36   = 75 %

  7. New price on which the range of coverage is applied = (100 - % Deduct (6)) x Base price (5)

Tasks to be completed when documenting an SAP Transaction, Report or Message code.

1. Title of the page = SAP Transaction code, SAP Report or SAP Message

2. Add the following Labels, respectively:

    • SAP Transaction => "sap_transaction", Transaction code "xxxx"

    • SAP Report => "sap_report"

    • SAP Message => "sap_msg"

3. On the left section, describe the steps. On the right section, insert respective print screens and additional guidelines, if needed (e.g. Main selections of the transaction...)

STEP 1


Use transaction code: Z1K_SPAREPARTS

 

You will be able to find four sections:

  • Selection Screen
  • Layout
  • Download to excel

PF2 - Report Z1k_SPAREPARTS: This report will provide you extraction of several fields of the master data of the material as well as stock quantities, values and consumption-



This report is mainly used to provide information for the calculation PF1 inventory reserves:

  • Raw Materials
  • Spare Parts
  • Finished goods (limited)






STEP 2 Selection Screen

In the Selection screen, the following data needs to be selected:

  • Company code
  • Valuation class (Of the material)
  • Year
  • Month
  • The other fields, can be selected depending on the level of detail available/needed
  • Layout can also be selected if available (Otherwise can be define after execution)



STEP 3 Layout

 

Depending on the information needed, select the different fields to prepare the report


First screen after execution:


Layout selection:



STEP 4 Extraction to excel


It is possible to extract to excel in two different ways:

  1. Excel
  2. Local File


Go to list - > Export

and select the option needed














STEP 1

Start the transaction ZWFA21A


Determination of Lowest Value: Sales price determination: initial screen



STEP 2

  1. Enter the company code 

  2. Key date: Last day of the month

  3. Material type: ZMAT - Raw materials & Finished goods

  4. Valuation class:

    • Z130 : Trading goods

    • Z150 : Finished products

  5. Sales organisation: All sales organisation of the company

  6. Currency type: Useless (all data in specific table are in local currency

  7. Uncheck Exclude Cross company delivery




STEP 3

  1. Click on "Valuation level" 
  2. Check "Company code"

and enter



STEP 4

Click on 

Select the relationship to Market Price = Lowest val. Comparisn

Select the price used for the comparison:

- “ Mat.Price   Prev.Month ” (material price previous month )

and enter 



STEP 5

  1. Check “Database update” and a new button appears.
  2. Click on
  3. Select “Tax price 2”: field of the material master data in which the calculated price is saved

and enter 


Then start the calculation by clicking on 

Ignore the warning message  by clicking on 




STEP 5

The report is displayed

  1. Material Master Data & plant code

  2. Stock quantity at the end of the month (execution date)

  3. Net Real value most recent “net real value” on the “key date”

  4. Comparison price: Price selected in the “comparison price” box (here “price on the previous month”)

  5. Lowest price: Lowest value of the two prices (3) & (4) followed by the price unit

  6. Price date: Date of calculation of the price

  7. Price Source: Origin of the price 

Unable to render {include} The included page could not be found.

3.4. I calculate inventory reserves


Responsibility area:  Calculate and post Inventory Reserve


STEP 1

Start the transaction ZFI_INVENTORY_SMOG


Inventory SMOG report: initial screen



STEP 2

  1. Select variant:  /SMOG_TEMPLATE

  2. Enter the company code 

  3. Key date: Last day of the month before quarterly

  4. Material type: ZMAT and ZVER
  5. The materials with null stock should not be considered in the calculation. The field must to be flagged. Note: The new materials can be excluded by flagging the field "Exclude material created after" - it is not mandatory, it will depends of the business.
  6. Variant layout: /SMOG_TP


       



STEP 3

  1. Click on "Valuation level" 
  2. Select "Company code"

and enter


Note: The rate of coverage is calculated at company code level and not batch level -The reason is that in some plants we only have the transfer of stock between 2 locations and no sale or consumption, which means that these plants always have 100% depreciation.



STEP 4

Click on 

  1.   Check “Tax Price 1” (where the range of coverage is stored) and “Tax Price 2” (where the Net realizable value is stored)

  2. Check “0=0” Level 1

  3. Click on 

  4. Add “Stock Value” to the already selected values.

    The calculation of impairment will be based on the lowest of the 3 values

    - Stock value (=standard)
    - Value Level 1 (= Tax Price 1)
    - Value Level 2 (= Tax Price 2)

 and enter   if you have a message you can click on 

 



STEP 5


Click on 

Enter:

  • “Upper limit” = “key date”
  • “Lower limit” = “Upper Limit” – 12 months

The range of dates corresponds to the period of analysis of the monthly stocks 



STEP 6

Click on 

  1. Select and enter the range of date in the first range as the analysis period for the determination of consumptions.

Then execute the transaction


The option “Determine consumption via user exit” ca be selected to apply the  rules to calculate the range of coverage (average consumption).

Then start the calculation by clicking on 

Ignore the warning message



STEP 7

SMOG Report Functions

     1.The total stock are summarized by the batch stock, non batch stock and stock in transit.

     2. Reserves and Financial depreciation


  • Fin.Depr: value is calculated from ZWFA21A stored in field tax price 2 in material master data
  • ROC(M):  value is calculated from MRN1 stored in field tax price 1 in material master data
  • %Reserve:

           If ROC<12, then %Reserve = 0 

           If ROC>12 & ROC<24, then %Reserve = 0.5

           If ROC>24 & ROC<36,  then %Reserve = 0.75 

           If ROC>36, then %Reserve = 1

  • Financial Depreciation: based on Solvay standard : If source price is ‘COPA’ and Market price < Total value, then Financial depreciation = Total value - Market price
  • Coverage: (Stock qty * % Reserve)* Standard material price
  • Blocked Qty: (Off spec material) material batch with blocked status then total batch stock is blocked (100% provision).  
  • Blocked Amt: Blocked Qty * Standard Material Price
  • Aged Qty:  (Obsolescence material) When the day until expiration is less than 0, the whole batch stock will be aged (100% provision).
  • Aged Amt: Aged Qty * Standard Material Price
  • NetCover: Coverage - blocked amt - aged amt - blocked amt adj
  • FinReserve: NetCover + blocked amt + aged amt + blocked amt adj
  • Good: Total value - Financial Reserve


IMPORTANT

The program ZFI_INVENTORY_SMOG will prioritize the 3 following rules:


1.Identify any blocking stock of the batch 
If yes, it will be proposed to be 100% reserved

2.Identify the aging of the stock/batch based on Expiration Date
If date expired, it will be proposed to be 100% reserved

3.Identify the Range of Coverage (RoC) based on the last 12 months of consumption ( average consumption / average stock)
Reserved proposed based on following ranges 

If ROC<12, then %Reserve = 0
If ROC>12 & ROC<24, then %Reserve = 0.5
If ROC>24 & ROC<36,  then %Reserve = 0.75
if ROC>36, then %Reserve = 1, so for cases where ROC is equal or ≈ 9.999,00, it means that there is no consume for a long time 

Note: If stock status is blocked or expired → Depreciation is 100%.


The process is currently processed with the support of one excel template that needs to be updated with the extractions from each transaction mention in section 3.3.

Worksheet "Read Me" in the template:

  • The excel template contains a Read Me worksheet with the details of how to extract data and fill in each worksheet.
  • Some files, might contain one Excel Macro to extract automatically the data from SAP and fill in each worksheet.

Prepare Spare Parts data (3.3.1):

  1. Go to report Z1K_spareparts
  2. Select variant /SLOWTURN-SP
  3. Update the company code
  4. Update the year and month
  5. After running, extract to excel
  6. Copy to worksheet "SP reserves", starting cell A2

Prepare Raw Materials & Packaging data (3.3.2):

  1. Go to report Z1K_spareparts
  2. Select variant /SLOWTURN-RM
  3. Update the company code
  4. Update the year and month
  5. After running, extract to excel
  6. Copy to worksheet "RMPACK reserves", starting cell A2

Prepare Finished Goods Data (3.3.3):

  1. Got to SQ00
  2. Select query/user group SXS-PRD1
  3. Run table Z1K_UPIS3
  4. Select Variant "INV RESERVES" with the complany, plant (if necessary) and period/Year
  5. After running, extract to excel
  6. Copy to worksheet "FG reserves", starting cell A2


Select query/user group SXS-PRD1


Run table Z1K_UPIS3


Select Variant "INV RESERVES" with the complany, plant (if necessary) and period/Year



 

3.5. I send the report to the GBUs

3.5.1. I generate the proposition files


Extract the data in spreadsheet format from ZCO_SPAREPARTS and ZFI_INVENTORY_SMOG

and copy the data to inventory reserves template field A2 in both "RMFG reserves" & "Industrial supplies reserves"

 

Having the extractions done in section 3.4 related to PF2, each worksheet needs to be completed

Worksheet "Read Me" in the template:

  • The excel template contains a Read Me worksheet with the details of how to extract data and fill in each worksheet.
  • Some files, might contain one Excel Macro to extract automatically the data from SAP and fill in each worksheet.

Prepare Spare Parts data (3.3.1):

  1. Copy the extraction to worksheet "SP reserves", starting cell A2
    1. Check the last columns are not overwrite, as these contain the formulas to calculate the reserves

Copy the extraction to cell A2

Have in mind that not to overwrite the last columns:


These columns are the ones containing the formulas to calculate the final reserve by material

Prepare Raw Materials & Packaging data (3.3.2):

  1. Copy the extraction to to worksheet "RMPACK reserves", starting cell A2
    1. Check the last columns are not overwrite, as these contain the formulas to calculate the reserves

Copy the extraction to cell A2


Have in mind that not to overwrite the last columns:


These columns are the ones containing the formulas to calculate the final reserve by material


Prepare Finished Goods Data (3.3.3):

  1. Copy the extraction to worksheet "FG reserves", starting cell A2


Worksheet "Full stock extraction" :

  • Extract through BW, the stock of the company for the period in analysis
  1. Refresh this worksheet through Analysis/Prompt
  2. Select date (Last day of the month in analysis)
  3. Select the company code


Go to "prompt" to perform the refresh


Select the date and company

Worksheet "Reserve accounts" :

  1. Refresh this worksheet through Analysis/Prompt
  2. Select date (Last day of the month in analysis)
  3. Select the company code

Go to "prompt" to perform the refresh


Select the date and company

Worksheet "Summary" in the template:

  • The total amount of the columns with the calculated reserve, should be in column G
  • The total amount of company stock retrieved in worksheet "full stock extraction" should be in column C
  • The total amount of the Balance sheet accounts coming from worksheet "reserve accounts" should be in column F



Template

3.5.2. I prepare the proposition files

Ensure all the data in other sheets are reflected into tab Summary 

To finalize the template before send it to the Controllers, some consistency checks need to be done.

Prepare Spare Parts data (3.3.1):

  1. Check that no formula is missing in the last columns

Have in mind that not to overwrite the last columns:

Formula to determine if there is % to be calculated

IF(R2="X";0;IF(AN2>365;10%;0%))

Where column R is related to "critical spare part" and Column AN is related to the "slow days".

The formulas can change, depending on additional criteria implemented for a given company


Prepare Raw Materials & Packaging data (3.3.2):

  1. Check that no formula is missing in the last columns

Have in mind that not to overwrite the last columns:

Formula for Column AO - Material type. Will provide what type of material is.

=IF(C2="Z000";"RM";IF(C2="Z05L";"FUEL";IF(C2="Z05P";"FUEL";IF(C2="Z05Q";"FUEL";IF(C2="Z051";"PACK";"RM")))))

Formula for Column AP - it is going to calculate the range of coverage (average stock consumption of the last 12Months against current stock)

=IF(AO2="RM";IFERROR(AJ2/(AE2/12);100);0%)

Formula for column AQ - to identify what will be the % to be used to calculate the reserve

=IF(AP2<12;0%;IF(AP2<25;50%;IF(AP2<37;75%;100%)))

Here, also the formulas can change, depending on additional criteria implemented for a given company


Prepare Finished Goods Data (3.3.3):


Worksheet "Full stock extraction" :

  • Extract through BW, the stock of the company for the period in analysis
  1. Refresh this worksheet through Analysis/Prompt
  2. Select date (Last day of the month in analysis)
  3. Select the company code


Go to "prompt" to perform the refresh


Select the date and company

Worksheet "Reserve accounts" :

  1. Refresh this worksheet through Analysis/Prompt
  2. Select date (Last day of the month in analysis)
  3. Select the company code

Go to "prompt" to perform the refresh


Select the date and company

Worksheet "Summary" in the template:

  1. There is a table to reconcile all stock of the company and retrieve the total amount of reserves posted Vs calculated
    1. Ensure that column E is zero, as this will confirm that all stock of the company is included in the analysis
      1. Any difference, should be analysed
    2. Column H, at this stage of the preparation, this column will not be zero. It will show the different of the current reserve amount to the proposed new reserve amount
  • Find as well the rules and assumptions for the reserves calculated



3.5.3. I send the proposition files

After the file is completed, send the depreciation proposals to the controllers.

In the message clearly indicate the deadline, which should be D-5 at the latest and request the amount validated for Rotation and Financial Depreciation.