3.3.5.2D Remaining Line Items , Reorder flags

Prithi
Prithi
in Academy Discussions

Hello

can you help me in perfecting my formula ?

  • Reorder Flag: Identifies the week that an order must be placed before the ending inventory would drop below 0. 

image.png

image.png
image.png

As you see in the week 11 FY20 column, the reorder flag is not ticked even though the value is negative

My doubt is, as per the hint, they suggested the use of the OFFSET formula, and as per logic, when the ending inventory value for the future week is less than zero based on the shipping time week for the product SKU , then reorder flag should be raised

so I have written this formula OFFSET(Ending Inventory, -Shipping Time Weeks, 0) < 0,

As per my understanding, the current week ending inventory value will be copied to the concerned future week value based on the shipping time weeks and then it comapres the value is zero or not ? AM I right?

but how to check the value on the future ending inventory value is less than zero by using shipping time weeks using offset function? (because offset function just copies the values to the destined cell )

Tagged:

Best Answers

  • umangsinghal
    umangsinghal
    Answer ✓

    Your formula for Reorder Flag is correct, just a small correction needed. Remove negative sign (-) before Shipping Time Week.

    Regards,

  • umangsinghal
    umangsinghal
    Answer ✓

    To understand the offset function better, create another lineitem Future Ending Inventory. Put the OFFSET formula there. Then, you will see negative Shipping weeks (-2) is taking the values from 2 weeks earlier in the lineitem. Eg: Future Ending Inventory = 0, 0, Value of 1st week of Ending Inventory, Value of 2nd week of Ending Inventory and so on.

    Keeping Shipping week positive will return you the values of ending inventory 2 weeks ahead. Eg: Future Ending Inventory = Value of 3rd week of Ending Inventory, Value of 4th week of Ending Inventory and so on.

    So, reorder flag is boolean lineitem and checking for values<0 so, you will notice that this will be True where you see Negative Values in the Future Ending Inventory.

    Hope this helps!

    -Umang

    Regards,

  • umangsinghal
    umangsinghal
    Answer ✓

    To make it more clear, Formulas for,

    Future Ending Inventory = OFFSET(Ending Inventory, Shipping Time Weeks, 0)

    Reorder Flag = OFFSET(Ending Inventory, Shipping Time Weeks, 0) < 0

    Regards,

Answers

Categories