SOLVED

How to calculate inventory carry months (Excel)

Occasional Contributor

Hi friends,

 

If anyone knows how to calculate carry months by Excel?

 

Take below for example:

"Product A" current stock is 100, and I can use 1.58 months after.

"Product B" current stock is 100, and I can use 2.37 months after.

"Product C" current stock is 300, and I can use 3.50 months after.

 

Logic is:

"Product A" current stock is 100, January & February demand is 55 & 78.

After January I have 45 left and still cover 45/78=0.58 month of February.

 

Luke_Lee_1-1617009858088.png

 

 

1 Reply
best response confirmed by Luke_Lee (Occasional Contributor)
Solution

Hi @Luke_Lee 

 

With the help of solver columns and below formula you may achieve your requirements:

=IFERROR(IF(($B2-SUM($D2:D2))/E2<0,0,IF(($B2-SUM($D2:D2))/E2>1,1,($B2-SUM($D2:D2))/E2)),0)

tauqeeracma_0-1617015508860.png

You may refer the attached file for your reference. Please let me know if it works for you.

 

Thanks

Tauqeer