Basic Information

Purpose of this query

This query is used to measure the progress on the Physical Inventory procedure by calculating a Coverage Rate. This is part of the IAC 04.13.00.03 control.

The report shows the list of materials that were inventoried and calculates the % of the stock value which was inventoried.

(This query is copied from BW_QRY_CUB_IC001_0028 basically, it is the same concept but we will have a bit different)

About the indicator accuracy

Due to the way the indicator is calculated it is not always completely accurate. There are some cases which will not be correctly taken into account :

• Case stock quantity = 0, the system will consider as not counted. Therefore, the controller needs to mention it in a memorandum (to justify that the system is not taking into account)

• Case stock quantity remains the same compared to previous year (no moving in SAP). the system will consider as not counted. The controller also need to post the inventory count in SAP.

• Case there is some adjustment on stock movement type 711, it will auto post on physical inventory count on the system

• Case where a material was inventoried in the current year but had no stock in Dec Y-1 : although the material was inventoried this year it will not increase the % inventoried since it had no value in December.

Variables screen

Several variables are available in the report to allow filtering. Please note that :

• GBU
• Business Unit
• Company :
• Plant
• Storage location
• Material
• Material Status
• Sub Activity
• Valuation Class

Filters

General filters

• Material Type of spare part will filter out

  Z3M4	MX : Technical Parts
  Z604	Pharma conso/durable good
  Z704	0-Cons & durable good
  Z731	Generic supplies
  Z735	Common DIA techn.Material
  Z7R4	RU : Cons & durable good
  Z9C4	CN : Technical parts
  Z9E4	EG : Technical parts
  Z9K4	KR : Technical parts
  Z9T4	TH : Cons & durable good
  ZABS	Fuels
  ZAIN	Maintenance materials
  ZANL	Not stock material
  ZB04	Cons & durable goods HIBE
  ZB09	No-name art. - supplies
  ZCBS	Fuels
  ZCBU	Office materials
  ZCHI	Auxiliary materials
  ZCIN	Maintenance materials
  ZCNL	Nicht-Lagermaterial
  ZDBS	Fuels
  ZDBU	Office materials
  ZDET	Spare parts
  ZDIN	Maintenance materials
  ZDLG	Borrowing/Empties
  ZDNL	Not stock material
  ZEAG	General Depot        HIBE
  ZEPA	Amortizable piece    ERSA
  ZEPD	Parts Unused         ERSA
  ZEPR	Spare part           ERSA
  ZG04	Cons & durable goods
  ZH04	Cons & durable goods
  ZI04	Cons & durable good
  ZIIG	Mat. General employment
  ZIMM	Material for Immobilized
  ZIPR	Mat. Pieces reciprocation
  ZIRN	MAT. NON GESTITO A STOCK
  ZIRT	Mat. scrap iron
  ZJ04	Stock materials
  ZJNS	Non-stock materials
  ZJSP	Non-stock mat. with bal.
  ZK04	Spares, engineering items
  ZL04	Cons & durable goods
  ZM04	Cons & durable goods
  ZPAG	Armazém Geral
  ZPMD	Material Desmontado (MDs)
  ZPMI	Sucatas
  ZPPA	Peças Reserv.Amortizáveis
  ZPPR	Peças de Reserva
  ZR04	BR : Technical Parts
  ZS04	Spares, engineering items
  ZU04	Spares, engineering items
  ZU09	Generic material
  ZV04	AR : Technical Parts

Characteristics

By default the report displays 3 columns :

• Material plant : it's a concatenation of the plant number and material number (Ex : 7897/10422).
• Plant: Same detail of material plant but it is required from the business in case of analysis and filter
• Material: Same detail of material plant but it is required from the business in case of analysis and filter
• Storage location : the code and description of the warehouse where the stock can be found.

• Valuation Type : This is used to differentiate between normal and damaged/repaired industrial supplies. The unit price for the valuation will be different for damaged/repaire materials.
  • # (Empty) : normal materials
  • ZR : repaired material
  • ZN : damaged material

Key Figures

General

The report contains 2 set of key figures which are duplicated : one set for current year inventory, one for the previous year inventory.

The coverage rate calculation is not done directly by BW query. The report will display the Inventoried Value and the Total value. Then : 

• If you are using the BW Physical Coverage workbook, the Coverage rate will be calculated automatically in an excel cell.
• If you are using directly the BW query, you will have to calculate yourself the coverage rate :

            Coverage Rate = Inventoried Value /  Stock Value

Note that :

• All values are in local currency.
• All quantities are in base unit of measure.

Unit price

• Unit Price Y-1 : November of previous year unit price defined for each material/plant/valuation type.

This price is used to calculate the end of year (Y-1 and Y-2) stock values by multiplying it with the end of year (Y-1 and Y-2) stock quantity.

Calculation of the current year physical inventory

Inventoried Value Y =    (Stock Quantity Y-1)  * (Unit Price Y-1) *  (Material Inventoried Y)

Inventoried Value Y = (Stock Value Y-1) *  (Material Inventoried Y)

Where :

• Material Inventoried (Y) : 1 if the Material/Storage location couple was inventoried during the current year.
• Quantity Stock (Y-1) : Quantity in stock at end of Y-1.
• Stock Value Y-1 = (Stock Quantity Y-1)*(Unit Price Y-1)

Subsequently, the percent is calculated as :

 Coverage Rate Y = Inventoried Value Y /  Stock Value Y-1

Calculation of the previous year physical inventory

 Inventoried Value Y-1 =   (Stock Quantity Y-2)  * (Unit Price Y-1) *  (Material Inventoried Y-1)

Inventoried Value Y-1 =   (Stock Value Y-2) *  (Material Inventoried Y-1)

Where :

• Material Inventoried (Y-1) : 1 if the Material/Storage location couple was inventoried during the previous year.
• Quantity Stock (Y-2) : Quantity in stock at end of Y-2.
• Stock Value Y-2 = (Stock Quantity Y-2)*(Unit Price Y-1)

Subsequently, the percent is calculated as : 

Coverage Rate Y-1 = Inventoried Value Y-1 /  Stock Value Y-2