FIFO (First in first out) Inventory

schillin

We currently have two main sheets: one where we buy inventory stock (and the same material code may be entered on the sheet multiple times but with different dates) and then a second sheet for orders from customers. Once customers order, we need the inventory to deduct from our sheet from the OLDEST items first. For example, if we ordered the following for our stock: 20 units on 1/1/21, then 20 more units on 2/1/21 and another 20 on 3/1/21, and then a customer bought 30 units, we would need to see that all 20 on 1/1/21 were consumed and none are left, that 10 on 2/1 were bought and 10 are still remaining from 2/1 and then that none from 3/1 were consumed and all 20 from 3/1 are still remaining.

We currently have a formula set up in the first sheet but even though we have not maxed out smartsheet's requirements for columns/rows we actually are getting errors that we have too many formulas, so I am looking to see if anyone else has ideas.

Genevieve P.

Hi @schillin

Would you be able to post screen captures of both sheets, either with dummy data or blocking out sensitive information? Can you also confirm if your intake inventory will always be sorted with the oldest at the top of the sheet?

We could potentially use an IF statement formula to say the following logic:

If the SUM of the Quantity from the top of the sheet to this current row (for this specific Inventory item) minus the value from the deduction sheet is less than 0 (aka, negative), then return 0.

If the SUM of the Quantity from the top of the sheet to this current row (for this specific Inventory item) minus the value from the deduction sheet is greater than the quantity in this row (meaning this row should not have anything deducted), then return this row's quantity.

Otherwise, take the SUM of the Quantity from the top of the sheet to this current row (for this specific Inventory item) and minus the value from the deduction sheet.

Here's the formula structure:

=IF(SUMIF(inventory & quantity) - DeductedQuantity < 0, 0,

IF(SUMIF(inventory & quantity) - DeductedQuantity > Quantity@row, Quantity@row,

SUMIF(inventory & quantity) - DeductedQuantity

So in my sheet, I pretended the Deducted value of 30 was in a specific cell and put the formula example below it:

Here is the formula I used, if the deducted value was in the cell of row 9:

=IF(SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - [Column6]$9 < 0, 0, IF(SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - [Column6]$9 > Quantity@row, Quantity@row, SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - [Column6]$9))

In your instance, it's likely that you would want to use another SUMIF statement that's looking across sheets to SUM the quantity from the orders that have left for that specific item. That's what we'd use instead of the [Column6]$9 reference.

For example:

=SUMIF({Inventory}, Inventory@row, {Quantity Range})

Now we'd replace that wherever my cell reference is:

=IF(SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - SUMIF({Inventory}, Inventory@row, {Quantity Range}) < 0, 0, IF(SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - SUMIF({Inventory}, Inventory@row, {Quantity Range}) > Quantity@row, Quantity@row, SUMIF(Inventory$1:Inventory@row, Inventory@row, Quantity$1:Quantity@row) - SUMIF({Inventory}, Inventory@row, {Quantity Range})))

Let me know if this has helped! If not, in addition to screen captures it would be helpful to know what formulas you're currently using as well.

Cheers,

Genevieve

schillin

HI Genevieve,

Thanks for the response, but it seems to be very similar to what I am already doing. Since there are 1500 rows in the sheet, all smartsheet points of contact that I have done screen share sessions with have told me that smartsheet just can't handle all the formulas- so I somehow have to do this without formulas? Seems like your solution would be really similar to mine and cause the same crashing issues I am already experiencing.

Thanks

Genevieve P.

Hi @schillin

Are there other formulas in your sheet that would cause the sheet complexity to be increased? Is there any way you could split up your data so you have less rows per-sheet, then use a Report to gather the data you need consolidated? (This Help Article notes a few things you can do to reduce sheet complexity).

I hope this helps!

Genevieve

schillin

The formulas to deduct inventory from the oldest first really need to look at the collective pool of inventory to do so. It seems like you have some of the similar suggestions as other smartsheet contacts I have talked to, but I don't think that is an option because we can't add the formula to the report anyway.

Thanks anyway