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.

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 where a material had stock in Dec Y-1 but not in current year : in this case the material cannot be inventoried this year and the value will show as not inventoried.
• 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 :

• Storage location : By default this variable is set to exclude the "#" storage location, which basically means excluding all stocks not inside a storage location. Those stocks are usually excluded from this report because the inventory indicator only works for materials stored in a storage location..
• Company : This variable is mandatory because it's the authorization object for the BW stocks data. In the case where you have access to all companies, you can select all by entering "* as value.

Filters

General filters

• Vendor consignment : any material in vendor consignment is excluded from the report (Note : this filter is done directly in the key figures formula and will not show in excel)
• Storage location : By default the report will exclude the stocks that are not in a storage location. If you want to include those materials you can change the default value of the Storage Location variable in the query prompt.

Conditions

There are 3 conditions built into the report. These conditions are used to filter the data to show only the materials which actually had stock at the end of the year. Without that the report may show all the materials existing in the master data (included obsolete).

• Qty Y-1 & Qty Y-2 >0 : Active by default. If active report will only show materials which had stock at end Y-1 and end Y-2
• Qty Y-1 >0 : If active report will only show materials which had stock at end of Y-1
• Qty Y-2> 0 : If active report will only show materials which had stock at end of Y-2
  
  

Characteristics

By default the report displays 3 columns :

• Material plant : it's a concatenation of the plant number and material number (Ex : 7897/10422).
• 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 : December 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

FAQ

Why use Y-1 price to value both years ?

Normally the Stock value of Y-1 should be calculated with the Y-1 unit price and the Y-2 stock value with the Y-2 unit price. Unfortunately currently BW only stores the previous year end of December price so it was decided to use that unit price for both years.

The result is not perfect but provides a reasonable estimation.

Can I remove the Valuation Type from display ?

You can remove it safely if all the materials in your perimeter only have 1 valuation type.

If some material has several valuation types (Ex : # and ZR) then the end result will be incorrect since normally 2 different prices should be used.