## Inventory consumption

*Article updated on October 12, 2012*

The problem is with regards to analysing the remaining life of my inventory based on the quantity available and the monthly consumption. Its as follows :

Assuming today's date is 1st of September. I have 20 packets of cheese and the average monthly consumption is 4 packets. Ideally if the shelf life of those 20 packets is 6 months or more, then I can safely say that the total quantity will last for 5 months (20 divided by 4).However, with a slight twist in the data, assume that out of those 20 packets, 6 packets expire on 15 Oct, 7 packets expire on 10 Nov, and the remaining 6 on 15 December. Now I know that the total inventory is not gonna last for 5 months due to various expiry dates. If I calculate this manually, I come to know that the quantity will last for 3 and a half months.

How can i put all this in a formula to get the right answer ?

Please advise.

Thanks

Haroun

**Answer:**

**Array formula in cell E5:**

This formula returns months, rounded down. It can calculate up to 99 months. So how did the formula come up with the value 2? Take a look at this table.

Today is 2012-09-01, a month later (2012-10-01) 4 items are consumed and 0 items have expired, total quantity 16.

2012-11-01 a total of 8 items are consumed and 6 items have expired, total quantity 6.

2012-12-01 a total of 12 items are consumed and 13 items have expired, total quantity -5.

All the inventory items are consumed/expired somewhere between 2012-11-01 and 2012-12-01.

2012-11-01 - 2012-09-01 = 2 months

### Download excel *.xlsx file

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 2 Responses to “Inventory consumption”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi Oscar,

Many thanks for that.

I just have one point to elaborate. If we take the same case above, but assume that the consumption starts in September itself bearing in mind that these 4 items are actually out of the first 6 items expiring on 15th October, how will our months to last be affected in that case ?

Since, I think by the time we reach 15th October, the items expiring will not be 6 but 2 since 4 have been consumed in September.

Can you please help on that ?

Haroun,

I would like to help you out but I don´t know how. Your question is complicated!