Stock Age Report

Hi,

Can anybody explain possibility of getting stock age report.
I checked possibility of getting report via custom reports, but i couldn’t find a solution.

Thanks in advance…

1 Like

What do you mean by “Stock Age”?

Inventory items are all identical - each one is considered the same as the nextl

report like following (this is just a image from internet, refer Content)

Each purchase item may having its own age.
Eg. Item A has been purchased over a year ago but still not sold.
Item B has been purchased with in a month
Like wise

two similar reports in Debtors and Creditors “aged Payable” and aged Receivables

having report you can identify which item lying over a long period.

How is the value calculated - for example 39.0 for HDD SH-1?

  1. With the Managers’ cost of sales calculation calculation methodology, it is quite doubtful according to my understanding.

  2. But Qty should be able to get ??? isnt it ?

I think the value is calculated using the last purchase date but not sure

The Aged Receivables and Aged Payables are based on data in the Due Date field

There is no Due Date field for inventory items

You could create a custom field for Inventory Items and update it manually when you purchase an item but that seems like a lot of work

No Value is calculated kind of weighted average.

since the table structure not given to customized reports, hard to confirm the possibility. but inventory purchase date is available (it is being used to identify cost of sales) and with information on inventory dashboard balance stock qty could be aged as i think.

Hope developer will see this and answer.

I would question that the interesting thing is how long stock is sitting there - what’s more interesting is when was the last sale, not the last purchase

If you have stock that doesn’t sell, then you should get rid of it at and write it off if necessary

A stock turnover ratio would be more useful ie quantity sold in last 12 months divided by quantity in stock

This is calculated based on the dates on invoices that remain in inventory based on FIFO allocation method.

Suppose we have 100 of item X on 31 Dec 2020 for which we made the following purchases. The aging can be worked this way:

Date Ref. Qty. In Stock Age
1/1/2020 A 40 0 12 months
1/3/2020 B 20 15 10 months
1/8/2020 C 55 55 5 months
1/12/2020 D 30 30 1 month
Total 145 100

That doesn’t suit the auditor’s requirements. The auditors ask for this information in order to estimate an allowance for obsolete and slow moving items. They usually do so with reference to expiry dates, policies or conventions.

Suppose a product expires in 3 months, it’s turnover in days says it’s 2 months old. Does that mean that all inventory hasn’t expired? Or did have of it expire and the other half is good? There’s no way to tell without looking at the aging.

1 Like

Ok. This information is not readily available in Manager.

Stock is managed using an average value not FIFO

if the information is available on hand, you can analyze and take action.

even if you need to write it off, you need information on when purchased and how long it lying on stock.
without these information you cannot decide whether to write it off or keep it.

even short expiry items can be identified prior to expiry and you can consider sell at lower price rather write it off (If you have age)
So information is very much important rather wait till train leaves.

That is the point @Joe91 was trying to make. The information is not available in the program. Manager does not distinguish in any way between inventory items as to age. All units of an inventory item are considered identical once they are purchased. They are not associated with individual purchase actions.

If you require this information you have three choices:

  • Create separate inventory items for each lot purchased.
  • Monitor stock age in the warehouse through a physical process of separating items and using oldest stock first. (Manager will not provide any information to support this approach.)
  • Use a different accounting program with first-in-first-out (FIFO) inventory tracking capability. Manager does not have that. Of course, this option must also be combined with the option above so you can locate stock by age.
1 Like

Just as a side note, you don’t have to maintain your inventory using FIFO in order to be able to obtain the stock aging report. There’s a neat trick to reverse engineer the report while only using information readily available in the Inventory Quantity Summary report, albeit multiple instances of the report – for now at least.

For simplicity, let’s say for example you want to do display three aging buckets: 0-6 months, 6-12 months and 12+ months as on 31 December 2020.

What you do is get three values:

  • closing stock on 2020-12-31 (C)
  • purchases from 2020-07-01 to 2020-12-31 (P[1])
  • purchases from 2020-01-01 to 2020-60-30 (P[2])
  • purchases from 0001-01-01 to 2019-12-31 (P[3]), not really required but just so the formulas or code doesn’t break.

Sorry for my lacking speech skills but I tried to put this in simple English and I couldn’t :sweat_smile: so I am just going to put it in pseudo code instead. It goes like this:

For i in item 
    For x in buckets
        x.Qty = Minimum ( C , P[x] )
        C = Maximum ( 0 , C - P[x] )

And This is how it can be done in Excel.

I don’t think that this is necessary as this can already be achieved in spreadsheets using manager reports with moderate effort. So I don’t see why such a report can’t be provided as standard, especially since the inventory valuation does not have to be changed under the hood.

@Tut Please consider adding this to ideas.

2 Likes

What you have described, @Ealfardan, might provide information about inventory turnover from a financial perspective. But it does not help with the basic need @premathilake is trying to meet. That is, it does not track individual units of an inventory item or tell you their expiration status (near their expiry dates, past their dates, etc.). So, although it could tell you how much inventory you need to write off, it does not a provide a method for identifying that inventory on the shelf or in the bin. Even if it did, you would still need a physical identification process in the warehouse to identify the actual units to be written off or sold at discount.

In some situations, such capability is necessary. But Manager’s inventory management functions are currently a very long way from being able to provide it.

2 Likes

I agree Manager does not have the information to accurately generate the required report.

A solution would be to add optional sub accounts to inventory items (analogous to capital accounts). The functionality could then be used for batches, colour, size or some other property of the inventory item which does not effect it’s purchase or sale price but needs to be recorded. Custom reports could then be used to track quantities (as is done for Capital sub accounts).

If most of your stock items don’t expire in practice, it would still provide a function some may find useful. Such as:

  • know what item to look at in detail for expired stock
  • know what will be written off when physical stock is examined.

On balance if I was the developer however, it is not an approach I would code for as the end result would be of partial use so other users would immediately complain.

Instead enhancing custom reports so this and other similar reports are possible. Such as supporting

  • Comparison time periods
  • possibly calculated values ( but that may be better to leave to Report Transformation type approach)

That would be only a very slight improvement, though, as it would just subdivide into smaller categories or batches. There would still be no mechanism for associating any transaction with a specific unit in stock. Every unit in the smaller subdivision would still be indistinguishable from others in either the main inventory item or the subdivision.

What is suggested here is orders of magnitude more complex than anything in Manager. If someone has not actually physically managed inventory personally, it is easy to think of the process as an accounting function. But there are difficult logistical challenges involved that have to be linked to the accounting solution. Often, it is easier to separate stock aging issues from the accounting system completely. It is also cheaper, because the inefficiency of carrying out part of the process independently is more than overcome by the difficulty of integrating everything in software.

For inventory where a sub account was used, clearly the sub account would need to be used every time stock movement occurred other wise the stock balance would be meaningless.

I agree it is more complicated than for Capital sub accounts because

  • Sub accounts would be inventory item specific (not the same for all inventory items.
  • Different uses would have different requriments for adding new sub account values (Colours & sizes via purchasing manager, Batch numbers via incoming stores Manager).

That works for me as I don’t anticipate I will use it. Others may want to use and enhance Managers inventory system.

1 Like