Basic consumption formula

**Whitehill** · 02-05-2016, 04:48 PM

hi, I am trying to create a formula that does a stock fifo. For instance, I have 2 cases in stock and i buy 5 more cases of beer A over a month (e.g case 1, case 2 etc) in the month I consume 3 cases. I want a formula that shows the first 3 cases I costumed were case 1, case 2 (old stock) and case 3 that I bought. Leaving me with cases 4 to 7 as stock. To make it harder I would like to do this with different types of beer so e.g beer b, beer c....be great if someone could help, I have seen it before but do not have the formula anymore! Thanks

**sandy666** · 02-05-2016, 10:50 PM

Hope I understand well your description. I assumed case 3 is a first date after dates of case1 and case 2.
so
A2:An = dates in (A-Z, Z-A, mixed - doesn't matter)
B2:Bn = name of beer
C2: =IF(ROW(1:1)>3,"",SMALL($A$2:$A$n,ROW(1:1))) and drag down to C4 (because you said, first 3 cases)
D2: =IF(C2="","",INDEX($B$2:$B$n,SMALL(IF(C2=$A$2:$A$n,MATCH(ROW($A$2:$A$n),ROW($A$2:$A$n))),SUM(--(C2=$C$2:C2))))) confirm by CSE and drag down to D4
n change to suitable number of last row of your data.
CSE = Control+Shift+Enter

BeerOnStock.xlsx

sandy

**Whitehill** · 02-07-2016, 11:29 AM

Hi, that is kind of along the lines of what I am trying to do, I have mocked up the logic that I am doing manually below columns F to L, based on total consumption during the month...but the transaction/consumption should be formulas, I think i can work out for the transaction...but the consumption amount is where I am failing... Basically I know total amount consumed during the month, but need to allocate this consumption to when I bought the stock, on a FIFO basis

Consumed during month (units)
4 Budweiser
2 Becks
4 Carlsberf

Beer Transaction in month Transaction date Opening stock Transaction Consumption Closing stock
Budwieser 0 01/12/2015 3 0 3 0
Budweiser 0 10/12/2015 4 0 1 3
Budweiser Buy 2 05/01/2016 0 2 0 2
Budweiser Buy 3 07/01/2016 0 3 0 3
Becks 0 04/12/2015 6 0 2 4
Becks Buy 1 12/01/2016 0 1 0 1
Casrlsberg 0 05/12/2015 2 0 2 0
Carlsberg Buy 1 19/01/2016 0 1 1 0
Carlsberg Buy 2 20/01/2016 0 2 1 1

Opening stock = last months closing stock roleed
Transaction = what I bought or sold during month
Consumption = what I have consumed
Closing stock = opening + (buys - sells) - Consumption

I have seen it done before, but just can not work it out!!!

**sourabhg98** · 02-07-2016, 12:29 PM

Hello
I recently made a FI-FO stock calculator.
Check the file attached if this is somewhat you are looking for.
You need to press the "Calculate Inventory" button.

**Whitehill** · 02-07-2016, 06:03 PM

thanks, I will see if I can tweak it...basically I am trying to say I had 10 budweiser in stock, made up of several purchase, and I have I have consumed some of them, as they are generic, I do not know if I have consumed purchase 1, purchase 2, purchase 3 etc...but I need to show that I have consume say 3 during the month, so I need to allocate this consumption on a fifo, this split is the formula I am struggling with...thanks