Number of Days (Sales) Inventory

Hi there,

I am looking to calculate how many days worth of inventory I'm currently holding (in stock and on order from supplier) based on my sales over the past 30 days.

I've seen a number of formulas around... and honestly am not sure I'm on the right track.

On the attached I have used the following:

(Stock on Hand + Stock on Order) * 30 / ( Units of the item sold in the past 30 days)

Am I on the right track? Appreciate all help.

Originally Posted by Palmetto

Your formula basically returns the interval of days for which stock is turned, not really how much stock you are holding (physically and on order). In short, your formula returns how may days your stock will last until sold.

For example, using the first item in your list:
Four items sold in 30-days = 7 days turn around. With none in stock and one item on order, based on items sold past 30-days, it will remain in stock for 7-days.

Amended formula: =ROUNDDOWN((SUM(B2:C2)*30)/A2,0)

Palmetto.. thanks for your reply. Seems that our formulae are very similar - and give more or less the same results - except that your formula rounds down the results (see attachment).... Is there really any difference in the logic each of us has adopted?

Hello again, let's add another layer to my scenario.

It appears that goods to carry in stock and goods for actual customer orders are ordered separately. So I'd like to introduce two new fields - CustomerOrdersSent & CustomerOrdersUnsent.

If the following is my existing formula..

=ROUNDDOWN((StockOnHand+StockOnOrder)*30/30DaysSales,0)

Is it a simple case of assuming that the CustomerOrdersSent would be similar to StockOnOrder = adds to "positive stock", and that the CustomerOrdersUnsent would detract from the stock availability - and so should be treated as a negative.

Assuming the above is correct I have the revised formula reading as follows:

=ROUNDDOWN((StockOnHand+StockOnOrder+CustomerOrdersSent-CustomerOrdersUnsent)*30/30DaysSales,0)

Does this appear correct?

Thanks in advance...

bump. appreciate any assistance on this...

Originally Posted by mud_shark

bump. appreciate any assistance on this...

I have a meeting relating to this tomorrow (my problem I know)... Would be greatful for some input from anyone out there.

Thanks in advance