Calculate Running Total With Stock On Hand

[djmurray]

Hey Guys,

I am trying to calculate the stock on hand vs what we have on backorder on a FIFO bases.

I have

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Item
[/TD]
[TD]Qty
[/TD]
[TD]Backorder Qty
[/TD]
[TD]Stock On Hand
[/TD]
[TD]Stock Avilable
[/TD]
[/TR]
[TR]
[TD]2014-09-01
[/TD]
[TD]iPad64
[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]17
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]2014-10-01
[/TD]
[TD]iPad64
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]17
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]2014-01-01
[/TD]
[TD]Kindle
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way to do this within a pivot table or similar?

 

[djmurray]

Any ideas

 

[mcmahobt]

Can you explain more on what you're trying to accomplish? I have little business background, and a quick google search explains what FIFO is, but what exactly are you trying to show?

 

[djmurray]

Can you explain more on what you're trying to accomplish? I have little business background, and a quick google search explains what FIFO is, but what exactly are you trying to show?

Hi mcmahobt.

Basically I want to see which orders I can fulfil with stock on hand and which I cant - thus I need to order stock.

The reason I need it to calculate is because of the dates of the order - First in first out.

So if I have 10 iPad's and my first order was for 8 and my second order was for 3 i must first complete my first order then partially fulfill my second - then any other orders will show as no stock.

 

[bongoboyza]

Did you ever get a solution to this ? I am try to calculate a running total of stock on hand after the sales order is fulfilled. Any ideas would be great

 

[mcmahobt]

In the information you provided, the Qty and the Backorder Qty are the purchase demands I am assuming. What is the difference between the Stock on Hand and Available Stock then? From which column does the order pull from the available stock?

 

[djmurray]

In the information you provided, the Qty and the Backorder Qty are the purchase demands I am assuming. What is the difference between the Stock on Hand and Available Stock then? From which column does the order pull from the available stock?

Hi Mcmahobt.

The total avaiable is just a sum of Backorder Qty - Stock On Hand



[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Backorder Qty
[/TD]
[TD]Total Backorder Qty (For this Item)
[/TD]
[TD]Stock On Hand
[/TD]
[TD]Stock Avilable (Running Total)
[/TD]
[/TR]
[TR]
[TD]2014-09-01[/TD]
[TD]iPad64[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]17
[/TD]
[TD]12 (Backorder Qty -Stock Av.)
[/TD]
[/TR]
[TR]
[TD]2014-10-01[/TD]
[TD]iPad64[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]17[/TD]
[TD]10 Backorder - Stock Av.
[/TD]
[/TR]
[TR]
[TD]2014-01-01[/TD]
[TD]Kindle[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0 (Backorder Qty - Stock Av.
[/TD]
[/TR]
</tbody>[/TABLE]