Skip to Content
Technical Articles
Author's profile photo guruprasad pandurangi

New Inventory Ageing report

Introduction:

Inventory ageing report gives an overview about the distribution of available stock of materials from over a period of time, from a selected key date in reverse chronological manner.

For example, a particular material might have a stock of 100 PCS on a selected key date. This stock could have been purchased in 2 lots of 50 and 50 between 6 to 12 months and before 12 months. This material might not have been consumed and be in stock as non moving item.

This report aims at giving details of such stock distributions plotted over different periods known as buckets.

By segregating and analysing such materials, the finance and purchasing teams will be able to plan and manage inventories in an efficient manner.

The buckets of time could be anything within a month, within 6 months or for more than a year .

How the New Inventory ageing report logic differs from the regularly used custom ageing reports:

Brief overview of the logic employed in the new ageing report:

The report does three activities predominantly.

  1. Calculating the stock of the material on a selected key date.
  2. Deriving the periods of time, otherwise known as buckets like stocks within a month, within 2 months….etc, dynamically based on the inputs given on the selection screen.
  3. Calculating the stocks of the material in the various bucket periods, arrived in previous step, and giving the output.

 

Logic of working in detail:

Please refer the input screen.

  1. Once the plant / material types and key date is entered, valid materials in the selected plant and material type are fetched.

The encircled (red) fields represent the buckets or periods to be analysed.

The buckets are flexible, and can be changed by user as per requirement.

(For better results and practical reasons, it is suggested to set key date as last date of previous month and report be executed in 1st week of current month. This is to get optimum results improvising the working logic of MBEWH table, in which stock is updated only at the close of the month.)

For the above entered combination of buckets and key date, the ageing buckets derived automatically by the system will be like:

Buckets In months From Year / period To Year period
1 Within 1 month 2019 / 11 2019 / 12
2 Between 1 to 2 months 2019 / 10 2019 / 11
3 Between 2 to 3 months 2019 / 09 2019 / 10
4 Between 3 to 6 months 2019 / 06 2019 / 09
5 Between 6 to 12 months 2018 / 12 2019 / 06
6 Greater than 12 months 2018 / 12

 

  1. Calculation of material stock on key date:

Note: The company code in example uses an april to march fiscal year period.

Pass the material / plant (bwkey) field to MBEW table and fetch fiscal year and period.

The year / period for material as per MBEW is 2020 / 01, and year / period of the key date given (31.03.20) is 2019 / 12.

  1. If the year / period fetched in MBEW is greater than key date year / period, we need to take the stock on key date by passing the respective key date year / period to MBEWH table and get the stock from LBKUM field.
  2. If the year / period fetched in MBEW is less than or equal to the year / period of key date then fetch the stock of the material from LBKUM field of MBEW table. This is the key date stock in this case.

3. Constructing the stock details for missing periods per fiscal year.

Please note the below screen shot.

 

 

Fiscal Year 2019 has one entry for period 12. There no entries for other periods in year 2019. Similarly in fiscal year 2018, periods stocks in 12, 11, 10, 6, 5 are only listed.

The logic for building the stock details in missing year / period is as below:

The stocks in year / periods not listed have same stock as that of immediately available / listed year / period above the missing period.

If we take year 2017, we have stocks on period 10 and 12. But details for year 11 is missing.

As per above logic the stock in period 11 is simply the stock in immediate available period just above 11, i.e 12 which is 18,219.00.

Note: 2017 / 11 is 28.02.18 and 2017 / 12 is 31.03.18.

Stock on 31.03.18 for material :

Stock on 28.02.18 for material :

The stock details are built for the periods determined automatically by the system in step 1.

 

4. Building the bucket stock information.

The bucket stocks are filled using following logic.

Buckets From Year / period To Year period Bucket stock derivation
1 2019 / 11 2019 / 12 Stock in period 2019/12 – stock in period  2019/11
2 2019 / 10 2019 / 11 Stock in period 2019/11 – stock in period  2019/10
3 2019 / 09 2019 / 10 Stock in period 2019/10 – stock in period  2019/09
4 2019 / 06 2019 / 09 (Stock in period 2019/09 – stock in period 2019/08) + (Stock in period 2019/08 – stock in period 2019/07) + (Stock in period 2019/07- stock in period 2019/06).
5 2018 / 12 2019 / 06

(Stock in period 2019/06 – stock in period 2019/05) + (Stock in period 2019/05 – stock in period 2019/04) + (Stock in period 2019/04- stock in period 2019/03) +

(stock in period 2019/03 – stock in period 2019/02)+

(Stock in period 2019/02 – stock in period 2019/01)+

(Stock in period 2019/01 – stock in period 2018/12).

6 2018 / 12 Stock in period 2018/12.

 

Here for e.g., stock in period 2018/12 means stock at end of the 2018/12.

 

To check above logic with standard MB5B.

Suppose we have input bucket months as 24, 48, 54, 72, 96 for material .

 

The bucket details will be as below.

Buckets In months From Year / period To Year period
1 Within 24 month 2017 / 12 2019 / 12
2 Between 24 to 48 months 2015 / 12 2017 / 12
3 Between 48 to 54 months 2015 / 06 2015 / 12
4 Between 54 to 72 months 2013 / 12 2015 / 06
5 Between 72 to 96 months 2011 /12 2013 /12
6 Greater than 96 months 2011 / 12

 

Converting the year / period info to dates.

Bucket 1: 2017/12 – 31.3.18         2019/12 – 31.3.20

Bucket 2: 2015/12 – 31.3.16         2017/12 – 31.3.18

Bucket 3: 2015/06 – 30.9.15         2015/12 – 31.3.16

Bucket 4: 2013/12 – 31.3.14         2015/06 – 30.09.15

Bucket 5: 2011/12 – 31.3.12         2013/12 – 31.3.14

For bucket 6 the stock on 2011/11 – 31.3.12 is taken.

 

The ageing report output obtained is :

Comparing the above report with standard MB5B within the same periods:

 

Stock on key date 31.3.20 as per report : 19319.

Stock as per MB5B.

 

Bucket 1 period stock comparison:

Bucket 1 period : Bucket 1: 2017/12 – 31.3.18   to       2019/12 – 31.3.20

Note: For bucket stock calculation , the difference of total goods receipt and issues within the period must be considered in MB5B.

 

Stock as per report : 1100.

Stock as per MB5B: (1100 = 1100 -0) / (Total Receipts – Total issues) in this period.

Bucket 2 period stock comparsion:

Bucket 2 period: 2015/12 – 31.3.16    to           2017/12 – 31.3.18

As per report: 150.

As per MB5B: (150 = 180 – 30)

Bucket 3 period stock comparison:

Bucket 3 period : 2015/06 – 30.9.15 to 2015/12 – 31.3.16

Stock as per report : 14846.5

As per MB5B : (14846.5 = 14850.5 – 4)

 

Bucket 4 stock comparison:

Bucket 4 period: 2013/12 – 31.3.14 to 2015/06 – 30.09.15

Stock as per report: 3222.5

Stock as per MB5B : (3222.5 = 6958.5 – 3736)

Bucket 5 stock comparison:

Bucket 5 period: 2011/12 – 31.3.12 to 2013/12 – 31.3.14

Stock as per report: 0

Stock as per MB5B: 0.

Bucket 6 stock comparison:

Bucket 6 is stock on 2011 / 11 – 31.3.12.

Stock as per report : 0.

Stock as per MB5B: 0

Sum of bucket stocks matches with stock on key date.

Special case:

There are scenarios when the last updated stock figure (most recent) in MBEWH could be something like year and period combination of 2020/04 and we are in current period of 2021/01. To get bucket stocks, we need to reconstruct or have all required period end stocks in place. Since in this case MBEWH is incomplete we look for an alternate method to get them.

We assume a April to March Fiscal period.

We wish to execute ageing from 30th april 2021 and current period is May 2021.

Material code is MAT1.

As per the discussed logic, we check table MBEW.

MBEW:

Material Lbkum(qty) Year Period
MAT1 100 2020 05

Date derivations

Ageing cut off date Year Period
30-apr-21 2021 01
MBEW Year MBEW Period
2020 05

Since the year / period combination in MBEW is less than those of  cut-off date, the stock of material MAT1 on cut-off date is same as stock in MBEW. (100 Units).

Now since we have an entry in MBEW with year / period 2020/05, it is clear that the last entry in MBEWH table (descending) will be 2020/04.

Since we predominantly rely on MBEWH period end stock to get bucket stocks, and in this case we don’t have any data above 2020/04 in MBEWH, we need to reconstruct them.

The reconstruction is simple. We can see that last update to stock was on 2020/05 (MBEW) and is same on 2021/01 as well.

We can utilise a custom table to populate the missing stock figures above 2020/04 (w.r.t MBEWH), which will serve as sort of extended MBEWH table.

Material LBKUM(qty) Year Period
MAT1 100 2020 12
100 2020 11
100 2020 10
100 2020 09
100 2020 08
100 2020 07
100 2020 06

We know stock on 2021/01 is 100 units which is same as on 2020/05, and hence we substitute stock for all periods (end) from 2020/06 and above as 100 units.

We have stock figures for 2020/04 and 2020/05 (and hence 2020/01) available already, and we try to populate the missing figures only.

 

Summarizing:

 We saw how to check if a material is slow moving, fast moving or non moving item, in a very simple manner using combination of valuated stock tables MBEW / MBEWH.

In my experience of developing this report using MKPF / MSEG tables, often it was a question of accuracy vs performance. If more accuracy is needed, it was required to use all the movement types executed for the material. In doing so however, performance use to take a hard hit.

This new logic however bridged the gap and now both accuracy and performance are equally achievable.

Even if it is felt that employing MKPF / MSEG tables have more choices to play with, but in my opinion even this report will be giving only a overview of stock information and a actual drill down will be required to micro analyse the material movement.

This said so, we can say that the same results are achievable in a swift manner using the new logic.

Note: This report can be tweaked to make it more flexible to display Ageing at storage location level and have bucket period in days by using combination of MARD/MARDH along with a minimal use of MSEG tables.

https://blogs.sap.com/2021/01/18/easy-method-to-derive-closing-stock-on-a-selected-date-with-minimal-reference-to-mseg-mkpf-table./

Assigned tags

      17 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Damean-BF Chen
      Damean-BF Chen

      Hi .. Thanks for the detail description. I don't recall there's a standard Inventory Aging report in ERP6.  So is this a Custom or standard SAP report? If this is standard SAP report ... is it available in ERP or is this something new in S/4?

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi Damean-BF Chen

      This is a custom development.

      Guru.

      Author's profile photo Ravishankar Venkat Ramanan
      Ravishankar Venkat Ramanan

      Very detailed

      Author's profile photo Jacey Chin
      Jacey Chin

      Sorry.. I’m so new to SAP ERP6.. and auditor requesting inventory ageing.. do you mind to guide me in how to get this report from SAP? Thank you in advance!

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi jacey chin,

      this is a custom developed report and not a standard one.  You can contact me at prguruprasad@gmail.com for further clarification on this report.

      guru.

      Author's profile photo VAIYAPURI SHANMUGASUNDARAM ARUNPRAKASH
      VAIYAPURI SHANMUGASUNDARAM ARUNPRAKASH

      it is very elaborate and detailed.  useful to learners.

      Keep it up Guru

      Author's profile photo Vignesh Veerasamy
      Vignesh Veerasamy

      Great stuff.Keep going.

      Real time scenario whenever we show inventory report to user group they will ask on againg  parlance which is showed above.

      One more add on will be may be first GR date across the stock based on the aging is being used by user group.

      Vignesh

      Author's profile photo Hem Kattamanchi
      Hem Kattamanchi

      Hello guru,

      Thanks for the detailed explanation.
      one question for me is - I followed the same logic and for few buckets I get a negative value . And I cross verified it with MB5B - where the number of Goods Issue is greater than Goods receipt.
      How do you interpret this ? All the stock that was received in this period is Consumed ? As we don't have serialization or batch management for that particular material - I came to an assumption of such.
      Thanks
      Hem
      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi Hem,

      Ageing report gives more of a distribution or trend of inventory over a period mostly giving an idea of usage, and this is different from an regular inventory report.

      Negatives are ok in ageing report but not in a inventory report (where you use regular arithmetic Closing stock = Opening stock + receipts - issues.)

      Negatives merely tells there were no receipts in this period and helps the inventory team to drill down and investigate.

      as we see in this table, there is no procurement of the material below 200 days, and shows the trend of usage of the material in reaching the curr.stock.

      Curr.Stk 0-30 31-60 61-90 91-200 >200
      100 -10 -10 120

      Guru.

      Author's profile photo Linxu Gao
      Linxu Gao

      In such case, should the data be listed as blow?

      Curr.Stk 0-30 31-60 61-90 91-200 >200
      100 100

      Because the current stock 100 EA is lasted for more than 200 days.

      Linux Gao.

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi linxu gao

      It might  be appropriate to display stock as per your suggestion if the negative entries are due to reversal of wrong goods receipts entries. In which case the negative entries have no physical impact and reversal can be deducted from original document posting period.

      but it might make sense to display the negative stock in respective periods of occurrence if they are due to some physical goods issue process. This might help in material usage analysis.

      as I have said in my blog that actual drill down is required to find nature of material movements if the values in specific period exceeds acceptable deviations.

      Author's profile photo Sanjay Ram
      Sanjay Ram

      With due respect, MBEW/MBEWH just stores the closing qty of each period so you can age only at the total qty for each period. This is not a good aging.

      A good detailed aging requires each GR to be aged separately at the respective GR dates. 

      This is explained below where your aging report will show 61 days aging for the entire total qty of 21, whereas a detailed aging report will break up the 21 qty to individual GRs and compute the aging from each GR date.

      Material GR Date GR Qty Key date  Aging In Days Remarks
      M001 5/1/2021 10 2/4/2021 87 Detailed aging report
      M001 10/1/2021 5 2/4/2021 82 Detailed aging report
      M001 12/1/2021 6 2/4/2021 80 Detailed aging report
                 
      Total at  31/1/2021 21 2/4/2021 61 Your aging report

       

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi Sanjay

      wish to thank you for giving me an opportunity to delve deeper on this topic.

      there are certain points I would like to reiterate however.

      1. This concept of ageing purely improvises mbew mbewh logic to improve performance

      2. The difference in stock between month end as available in mbewh is taken for ageing calculation. So this depends on bucket periods chosen.

      3. Storage location and batch not considered

      4. The key date is last date of previous month and bucket periods are in months and not days

      further wish to understand your requirement in detail as how the ageing buckets should be before we derive stock and how close they are to the one as per this blog.

      you can send your detailed requirement to my mail prguruprasad@gmail.com

      guru.

      Author's profile photo Sanjay Ram
      Sanjay Ram

      Hi Sir,

      Thank you for your reply sir. I understand those points, however there is a big question here. If we follow your example the bucket 2 quantity of 150 could have been sold on 1st of April 2018, so we cannot consider the 150 has been aging for many months under bucket 2 on the key date of 31st March 2020.

      What assurance do we have that the 150 was not fully sold on 1st of April 2018 and it is still outstanding therefore should be aged under bucket 2?

      This is the problem if you age based on the total quantity of a period because we do not know whether it was sold off in the subsequent periods hence we cannot consider that period quantity as outstanding on the current key date.

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi Sanjay,

      as I mentioned earlier that batches are not scope of this report and also fifo.

      this bucket period stock is only a logical derivation and facilitates management to take appropriate decisions based on usage trends of a material and this is the scope of this report as well.

      to achieve this objective in a efficient manner is the aim of this concept.

      Using batches we can track easily and accurately  the leftover or ageing stock and we need to take a different approach for this.

       

      guru.

      Author's profile photo Sanjay Ram
      Sanjay Ram

      So can i say this is a stock usage trend report instead of an ageing report?

      Author's profile photo guruprasad pandurangi
      guruprasad pandurangi
      Blog Post Author

      Hi Sanjay

      this report can be used as an ageing report, which shows the usage trend.

      an ageing report as every bucket contains stock remaining at end of the bucket period derived as net difference of receipts and issues. And getting carried over fully or partially.

      as usage trend because we can infer if material is in use actively or moderately by looking at the net quantity and consistency of occurrence in each bucket  over a period.

       

      guru.