How Calculate Stock Age ?

[pranvinilesh]

Hi Friends.

I need to calculate current stock aging and current stock value. I am so confused about how to calculate the current stock age and value. Please help.

I have attached sample file.

Thanks in Advance.

[AliGW]

You need to tell us what results you are expecting - not question marks, but numbers!

[pranvinilesh]

Thanks for Reply. Actually I need to know how many days the parts available in the stock and its current value.

[AliGW]

So what results do you expect to see in your sample? Numbers, please!

[pranvinilesh]

Yes in numbers

[pranvinilesh]

like stock age is 15 Days and amount is 2000 Rs

[menem]

I'm confuse in your sample data because all your data are the same Part#.
Formula for all the same part is difference from mixed part also.

Another, I think that you want to know [ when stock is xx QT,
what age is it and how much it's cost ] but you didn't say about
when will you start to count from [ from old date or from current date ]
because natually about stock issue , most are ask about in FIFO basis.
(And in your sample didn't say about stock issue also.)

Or you just want to know , when I (you) purchase the Part# reach xx QT
(this mean equal to or greather than)
how many day pass from first arrival date and the total cost of it.
(Thus this requirement may cause some issue because the formula may have to
average for the last purchase too)

Regards.

** I may unable to help because this case may too difficult for me. >_<" **

[pranvinilesh]

@ Menem

Thanks for Reply.

We assume stock issues on a FIFO basis because there is no stock issue date available in our company database.

So I want to know how many days the part available in the stock (Purchase date - Current Date)

[menem]

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter

This formula can tell you the n'th row of data that reach QT you ask for.
(Base on all data are the same part), as a result of the formula (9) so it's mean
QT will => 74 (as you ask for) a 9th row of the range (Row 11th of worksheet
and sum of QT is 75)

At this point you can get date of the last purchase (that make QT reach 74+) by
Index($A$2:$A$20, 9) => 13/03/2019
or the last amount of purchase by
Index($D$2:$D$20, 9) => 435.76133

But I still can't figure out your stock age >_<".
And can't find them in a reversed order too.

This formula may find the value in a revers order as I wondor for.

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter

Regards.

[menem]

I may misunderstand your request from the begining. T_T

Think again, now, I think that you want to know
[ If my stock is xx Qty, how long did it stay in my store room and how cost is it ? ] right ?

If YES, this mean you want to summary as a reverse order to until Qty is reach your asking for.

So, I've rewrite a little in your form.

These a calculation flow in this file.

After you have enter Part # (in H3) and Stock Qty (in I3)
M3 will try to find out which row of data (not worksheet) that make summary from that point
until the end of data >= Qty you ask for.
=MATCH(SUM(C:C)-$I$3,SUBTOTAL(9,OFFSET($C$3,,,ROW($C$3:$C$20)-ROW($C$3))),1)
Pres Ctrl-Shif-Enter for ARRAY_formula
20 can be change depend on your data.

N4 locate for date of purchase (form M3 + header row)
=INDEX(A:A,M3+ROW(A2))

K3 calculate different between TODAY() and purchase date (you may +/- 1 if you want to)
=TODAY()-N3

J3 and L3 show the exact Qty, Amount (at purchase point until the end)
J3
=SUMIF(A:A,">="&N3,C:C)
L3
=SUMIF(A:A,">="&N3,D:D)

And L4 is calculate portion (on Moving Average : not FIFO) for your Amount of asking Qty.
=L3/J3*I3
and for FIFO on L5
=SUMIF(A:A,">"&N3,D:D)+(SUMIF(A:A,"="&N3,D:D)/SUMIF(A:A,"="&N3,C:C)*(I3-SUMIF(A:A,">"&N3,C:C)))

Please note that, these formula is base on these condition:
- Only 1 part# in data list
- Purchase only 1 record for each date ( no duplicate date recorded : must be summary to 1 row )
- Data must be sorted by date (min to max)

Regards.

[menem]

And in this file was re-write again by use helper column to accumulate sum qt (I don't know how to use
ARRAY_formula for this type of calculation) for find the row that match a condition (samp part and qt match).

The limitation of this file still
- Purchase only 1 record for each date of each part#.
- Data must be sorted by date (min to max)

** concept of calculation is the same (just change the cell location for easier to use with multi-part no).


Regards.

[menem]

Arrr. I've found some mistake in the fomula.

Please re-verify in this file again.

Sorry for make you trouble. >_<"


Regards.

[pranvinilesh]

Bro thanks for your help, I am trying it for my data and revert you ASAP.

[pranvinilesh]

@ menem: can it be matched with 2 data categories. In your example you have created for Item wise only. Now if I want to add location. Let's say inventory at diff locations. It should give us values for location wise and item code wise. Is it possible?

[menem]

By adding 2nd helper column (for specific Location and ALL location : with *)

Please re-check in this file.

Regards.

[pranvinilesh]

Thank you very much. And sorry for the delayed in the reply.

[pranvinilesh]

@menem : Thank you very much. And sorry for the delayed in the reply.

[menem]

You're welcome. ^_^