| Table of Contents |
|---|
Basic Information
| Application | IM - Stocks |
|---|---|
| Query Name | BW - Physical inventory coverage rate (Core Query) |
| Query Technical | |
| Name | BW_QRY_CUB_IC001_0028 |
| Multi-Provider | |
| Name | Material Stock |
| Multi-Provider Technical | |
| Name | MPR_IC001 |
| Names & Technical Names of Main Sub-Providers Used | Material stock (CUB_IC001) |
| Usage | |
| type | Direct execution in Analyzer / Workbook broadcast |
| Expected | |
| users | Site controller |
Purpose of this query
This query is used to measure the progress on the Physical Inventory procedure by calculating a a Coverage Rate. This is part of the 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.
...
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 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 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.
...
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
When analysing current year inventory it is adviced to de-activate the "Qty Y-1 & Qty Y-2 >0" condition and activate "Qty Y-1 >0".
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.
| Info |
|---|
The Inventory indicators used in this report are set for each material/plant/storage location. If there is no storage location then the indicator will always consider the material was not inventoried. |
- 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 The coverage rate calculation 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 Coverage Rate = Inventoried Value / Stock 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 ?
...
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.
...