Hi folks,
This is a pretty simple add/subtract problem, but I'm not familiar with advanced Excel formulas, so wanted to get some tips. I'm trying to generate an inventory outlook/forecast which will show inventory levels for the Monday of every week for 10 weeks. I start with an on-hand stock. Sometime during the course of those 10 weeks there will be an inventory replenishment that will come in. The inventory outlook is generated by simply taking on-hand inventory + replenishment - depletion. I want to make sure that when I account for inventory replenishment on a certain date, I'm counting it only once on the Monday that will follow immediately, and not on subsequent Mondays. I can only think of something like IF(Date>Replenishment Date,Add Replenishment amount,0) type thing, but that would double count on all following Mondays.
I've attached a screen shot with an example. In this example, I'm starting with 50,000 units of inventory. I have the depletion outlook provided by date. On 4/24/2014 I get a replenishment of 25,000 units (which would therefore be accounted for on 4/28/2014, and not beyond that date). Any ideas would be appreciated.
Thanks!
Bookmarks