Inventory discounting excel formula

[epardo87]

Hello there,

So I need to formulate a matrix out of two tables:

• I have the inventory table has the product number and the current full cuantity available:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]


​

• I also have a matrix and it's arranged in orders of each product per customer per month:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]January[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A[/TD]
[TD]200[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]B[/TD]
[TD]50[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]A[/TD]
[TD]150[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]


​

• The new matrix would be identical to the order matrix but I need a formula which rests inventory out of the original order (to know how much I need to produce)
  • so it would require a formula that uses the inventory table only when the first order of that product appears, but for following orders of same product it would rest from the remaining quantity
  • it also needs to consider remaining quantity of previous months
  • ***don't worry about inventory additions, it's not required for this analysis***

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]January[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A[/TD]
[TD]400(Inv)-200(order)=200(inv); result in this cell =0[/TD]
[TD]50(inv)-100(order)=50(order);
Result in this cell=50[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]B[/TD]
[TD]60(Inv) - 50(order)=10(inv); result in this cell=0[/TD]
[TD]10(inv)-40(order)=30(order);
Result in this cell = 30[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]A[/TD]
[TD]200(inv)-150(order)=50(inv);
result in this cell=0[/TD]
[TD]0(inv)-150(order)=150(order)
result in this cell = 150[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 968"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]starting inventory[/TD]
[TD]A[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]product[/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]B[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]A[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jan[/TD]
[TD]feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]jan[/TD]
[TD]jan order[/TD]
[TD]jan bal[/TD]
[TD]feb[/TD]
[TD]feb order[/TD]
[TD]feb bal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]start[/TD]
[TD]A[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]425[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]without restocking quantities involved you order N of A where N = sales every month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]whereas B still has 25 in stock[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]easy to factor in monthly restock to bring inventory to original level[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[epardo87]

Thank you Oldbrewer!

I'm actually under a very complicated situation, but the fact is that I have to make the analysis per customer and product, I already have the analysis made summarizing full quantities per month.

in short words, we are transferring production from plants, and the inventory is the remaining inventory in old site, I can schedule orders in my site once the other inventory has depleted. that's why I need to know which month and for which customers will be available to begin production.

 

[oldbrewer]

If 10 customers each order quantities of the same widget, why would you break it down per customer ? Or are there geographical delivery considerations ?

 

[epardo87]

Some of the customers have approved the new facility and some not yet (waiting for quality testings). Actually, it is a little more complicated, the production transferred is a subassembly of the final product that we make, we have to split production of same part numbers in those approved (where we are going to manufacture the subassembly part inhouse) and those not approved (for which we have to continue to install the subassembly part purchased from old site). So in my matrix I have for example: Product A for customer 1 = approved; Product A for customer 2=not approved; and so on. so he formula that im requiring will be part of a greater one, which have conditionals to their approval date. for example if customer B will approve by march, the the formula should begin to discount the inventory beginning on March. I could do this manually but since the projections are changing very often, I would waste a lot of time updating everytime...