Forum Discussion

Luke_Lee's avatar
Luke_Lee
Copper Contributor
Mar 29, 2021
Solved

How to calculate inventory carry months (Excel)

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.

 

 

 

excel
  • tauqeeracma's avatar
    tauqeeracma
    Mar 29, 2021

    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)

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

     

    Thanks

    Tauqeer

1 Reply

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Mar 29, 2021

    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)

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

     

    Thanks

    Tauqeer

Resources