RES Key Account Dashboard – Finished Goods Inventory & DOS

{toc:maxLevel=3}

Summary description

The “Inventory DOS” tab combines several indicators:

• The Inventory: the total inventory at the end of each month, in KRE or KG, with detail of transit/on hand.
• The Days of Supply (DOS): the number days of sales the inventory is worth (i.e.: how many days in average it the inventory would last)
• The average sales quantity over 3 months (i.e. sum(M + M-1 + M-2)/ 3

Detailed description

Displays

Graphic

Graphic shows:

• In bar the quantity in stock :
  In KG or KRE (unit can be selected on the top right side of the graph).
  With split of the quantity by “in Transit” and “On hand”.
  Scale is on the left Y axis.
• In line, the Days of Supply (DOS).
  In days.
  Scale is on the right Y axis.

Detail Table.

Table shows 4 figures:

• The inventory at end of the period in KG.
• The inventory at end of the period in KRE
• The average sale quantity for shown month and 2 previous ones (Ex : For Jan 2013, it will show sum(Jan 2013 + Dec 2012 + Nov 2012)/3

1. cyclic on the left side of the table allows changing the main analysis axis.

Stock Qty (KRE and KG)

Time dimension

The only time dimension available is the Month It is the date of the inventory snapshot. The inventory shown for each period is the inventory at the end of each period.

Ex: January inventory will be the inventory as of 31th January at 23h59. 2013 inventory will be the inventory as of 31 December 2013 at 23h59.

Note that for transit data, the quantity shown will be the balance at the end of the selected period.

Other Dimensions

The following dimensions are available for this indicator:

The classification between the On Hand and In Transit is done via the source of the data:

• Data from SAP MM is classified as “On Hand”. It corresponds to all the stocks physically in the plant.
• Data from SAP FI is classified as “In Transit”. It corresponds to the General Accounts :

37000300 - GOODS FOR RESALE IN TRANSIT
37000305 - FINISHED PRODUCT IN TRANSIT

Filters

Only Rare Earth GBU materials are shown*. The GBU is defined by the profit center of each material/plant following the standard structure :

Material+Plant ? Profit Center ? VS ? CGU ? BU ? GBU

• Only * inished Products and Traded Products are included*. Valuation class included are :

Z150 – Finished Product

Z130 – Trading goods

• Both on site (MM) and transit (FI) inventories are included.

Assignment of the Ship to KA

Normally inventory is not assigned to a dedicated customer in the system (even if sold to a single customer). For this dashboard, it was necessary to assign this stock by Key Account since otherwise it would be impossible to measure the DOS by Key Account.

Example: Material 11224 has 160kg in inventory. But in SAP, this is not assigned to Key Account.

To assign a Key Account to each Material, a special rule is used:

1. By looking for the total sales quantity in KG of each material / Ship to KA over * and Y-1.*

1. A “Stock Coeff” is calculated to measure the portion of sales for each Ship to KA.

You can check this stock coefficient in the “My Report” tab by:

• Selecting the “Month” as the time dimension (Year/Quarter do not work for inventory)
• Selecting Dimensions “Material” and “Ship to KA”.
• Selecting KPI “Stock Coeff”

Example:

Conversion

The data is extracted in base unit of measure from RCS and then a conversion is applied on each material based on the defined standard Alternative Base Unit of measure (In RCS transaction MM03 ?Additional Data ? Units of Measure).

If a conversion rate was not found, then a 1:1 conversion is applied.
__Note that the conversion is not based on the actual rare earth rate of each batch, but on the standard rate.
__

Example in the MM03 transaction:

Target

There is no target for the stock quantity.

Getting the detail

To be able to get the detail of the inventory, in both KRE and KG and with both transit and on hand, you can use the following BW query:

BW_QRY_MPR_IC002_0011 – “BW - WC Stock Detail (Core Query)”.
In order to execute this report, you first need an access to BW with the application “FIWC – Working Capital” and an access by GBU to Rare Earth.

When running the query, apply the filters described above in the Filters chapter.

Average Sales 3M Qty

Time dimension

The month corresponds to the month of invoicing.
Only the month dimension is available for this KPI.

Note that since the KPY shows the average over 3 months, the displayed month is the last one.

Example: 01.2013 Avg Sales 3M Qty will show the average sales qty for 11.2012 and 12.2013.

Other dimensions

Filters

• Only Rare Earth GBU sales are considered
  GBU code = ZSECT00068
  Intra Group sales are excluded.*
  Intra Group Flag = N
  Some commodities and samples materials are excluded* (see list in the main documentation page).

Conversion

The sales quantity is converted to KG using the standard RCS conversion.

Target

There is no target as this indicator is only used to calculate the DOS.

Getting the detail

The data from this indicator comes from the Business Objects Premier Pilotages Economic (PPE) database.

Most of the reports from the Sales Reports folders can be used to extract the detail of this KPI.

Please make sure you apply the same filters as explained above (Especially on the Intra Group flag and on excluding the list of materials).

Days of Supply (DOS)

Calculation rule

The DOS is calculated directly in the Qlikview dashboard by using the Stock Qty and the Sales quantity:

DOS M = Stock Qty M / (Annualized 3 month sales/365)
= Stock Qty M / ((Sales M + Sales M-1 + Sales M-2) /365)*

Example:

In example B, 1200 Kg of stocks corresponds, in average, to 1 year of sales.

Time dimension

Like the stocks and the sales qty, the DOS is only available by month.

Dimensions

Because the DSO is the result of a calculation based on 2 other indicators, only some common dimensions will be available for analysis.

Target

There is no target.

The best way to get IT support is to use the new Service One Platform.