Correction needed for Outbound Quantity

Hello Everyone,

I believe I found a formula that needs to be corrected to help properly calculate FIFO to account for partial sales of quantity.

I have done some research on how to calculate FIFO, and I found a template that shows an example of calculating FIFO inventory value. I have attached it for reference.

In the calculator attachment, you simply put in a number of units sold in C4, say a value of 600, and you see how it updates cells in column F and the "Totals" row with proper values. I am looking to mimic these in my spreadsheet example.

Attached is an example I have had lots of help with by the great people of this forum. If you look at column J (Outbound Quantity), the formula is very close to what we see in the calculator.
Calculator: =MIN(C7,$C$4-SUM($F$6:F6))
My Spreadsheet: =MIN(F6,-SUM($J$5:J5)-$F$36)

The changes to my formuala I would like to make include removing the '-$F$36' to no longer make it depended upon a lifetime total of inbound and outbound quantity.
And also to use a sort of lookup command to insert after the '(F6,' that will search through column B, find the very next "Sale" text string, and use the value for quantity found 5 columns over to the right.

This is what I am thinking so far: =MIN(F6,LOOKUP_COMMAND_HERE-SUM($J$5:J5))
where LOOKUP_COMMAND_HERE = some sort of VLOOKUP command maybe.

I've tried this, but it can't precisely find only the next "Sale" in the sheet, while ignoring all the ones after it:
=VLOOKUP($B$6:B999="Sell",B6:F33,5,FALSE)

Can somebody help to get this to where it searches B and targets the next "Sale" only, then after that line's calculation, starts over from the next purchase line, to search and lookup the next "Sale" after the previous one, pulling the known value for Quantity (column F) and so on?

And have that looked up value be inserted into the MIN formula to be used to calculate column J (equal to "Units Sold" in the calculator spreadsheet)? I understand this will ruin the calculations for columns K, L, M, N, O, P, and Q. We'll cross that bridge if we can get there.

I'm not sure yet, but if we can get this working, will columns K and N be needed anymore?

Hello everyone,

I haven't been able to mimic the calculator with a good lookup command. Any ideas?

Wow, this is super close to working! Thank you so much, I imagine this took quite a bit of time to work out.

See latest attachment. I blacked out the top rows where everything works perfectly, and a few bottom test rows to make it easier to focus on where the problem comes in.

See J25...when another 100 quantity is sold off in row 28, is it possible to get 107.381613 added to 100 to reflect that 207.381613 units out of the initial 277.1079(F25) have been sold off, and corresponding figures in L, M...etc. to reflect the new values of what is sold off at that point, and remaining quantity and its' worth left over? Please note the value of 0 in J26 is actually a great representation of the additional quantity purchased (F26). The reason why is with 207.381613 units sold, there are still roughly 70 units left over (which should reflect in M25) of that 1st batch. J26 tells us that none of the quantity in the 2nd batch have been sold off, so that is perfect.

Then continuing to row 29...another 100 quantity sold off. So here, J25 should update to add the roughly 70 additional quantity sold off, showing a value equal to F25 as the whole 1st batch was sold off. And then, J26 should equal somewhere around 30 quantity sold out from the 2nd batch, where M26 shows us what is leftover in batch 2.

I hope that lengthy explanation makes sense. Please let me know if I need to try and explain it another way.

Do you guys think this is possible?

Nailed it! This is perfect! Thank you very much!

The only thing I tweaked on it was to hide data that doesn't provide sensible value and may just look more confusing.
=IF(D9<>"","",IF(B9="Sale",F9,MAX(0,MIN(F9,-$F$2-SUMIFS(F$5:F8,B$5:B8,"Purchase")))))

If it is alright, I would like to continue on this thread here with the next issue related to an outbound quantity correction. See attachment, I cleaned up a bunch of things and renamed some version tabs.

Column K needs to result in an average such that when partial sales are made, each row adjusts to incorporate it as we are looking at each line as a "per quantity batch" basis.

The green cells are technically correct, but are only successful earlier because all quantity was sold out at once, so the average price is of course the price at which everything was sold out of. However, I fear adjusting the forumula to account for partial sales will throw these results off potentially.

The red cells are where things need to be corrected. K25 should equal an average of G27, G28, and G29. G29 is special here because its included due to having 9.726287 quantity left in that batch. (To see this, temporarily delete test yellow rows 29 30 and 31 and reference M25. Do undo and redo to see how the values adjust.)

Then K26 should show a value equal to the average of G29 and G30 to average out the rest of that batch (purchase of row 26).

Row 31 is to simply simulate another purchase for a new batch to repeat the process all over again for future sales.

What do you guys think?

Hello Everyone,

I think this will require a formula as complex as the one JeteMc was able to provide before, but I can't seem to put one together to crunch the numbers right. If anyone has the time to lend a hand, I would greatly appreciate it.

Thanks in advance.

Am I heading in the right direction with this? I get a DIV/0 as a result. Any help is appreciated. I tried it in K25.
=IF(D25<>"","",AVERAGEIF($G25:$G$997,MATCH(TRUE,INDEX($F25:$F$997<0,),0)))

Hello and happy Friday (to anyone out there it is currently Friday for)!!!

Does anyone have any other suggestions on this or any other ideas at this point? I thought about a sort of moving or sliding side table if all the formulas can't be consolidated into one column. But that's me thinking outside the box without knowing all the capabilities of Excel.

Hello Everyone!

Since my last post, I have gone through a few other iterations of my spreadsheets to crunch totals for profit loss different ways, but unfortunately all ways come back to successfully calculating outbound price, which should tell us how much each stock or coin is worth over numerous batches of purchases and sales on different dates as that is the goal of calculating by FIFO.

This appears to be the most complex calculation out of everything all my spreadsheets need to be able to do, so I'm struggling to move past my current formula to get it to be an all-in-one cell formula to click and drag it down to get the right results.

Please see my v3 attachment. If you look at column K on Version10 sheet, you see where we came from to get what we have today in Version12 sheet, which are masterful calculations JeteMc was able to put together that truly crunches the right answers for every line in the spreadsheet.

The challenge with the result in Version12 is that it is hard to scale what I'm dubbing the "Sale Amount Tabulator" (Column S and ongoing to the right) across numerous books with numerous sheets. Cell T24 is a seperate formula that drags to the right. Cell T27 is another formula that drags down. The matrix has to scale with each batch of sales. And ultimately, the formula in column K pulls those desired values out of the matrix to crunch and assign for all appropriate rows of past purchase batches associated with the sale.

That is a mouthfull...lol, but that's what it is doing in a nutshell.

So with this showing the right answers and what they should be, I'm doing my best to maybe find another way to replicate these formulas and consolidate all of this wizardry into one formula for column K.

I've tried looking at other Excel functions other than Index and Match, like maybe some advance Vlookup, but I can't put anything together to calculate anything right.

Can anyone take a look please to help find a way?