Closing QTY and Value assessment using FIFO Closing

[Buddhi_DB]

Hi Alteryx Community,

I am working on an inventory management project and need assistance with calculating the closing inventory quantity and cost using the FIFO (First In, First Out) method in Alteryx. I have transaction records for three item codes, including opening and closing quantities and costs for each item.

Case Description:

For example, the item code VERNON has the following details:

• Closing Quantity: 830
• Cost Posted to G_L: 159,388.29

To verify the accuracy, I manually tracked the remaining quantities from the latest purchases:

• Latest purchase (Entry No: 4346382): Quantity 720, Purchase Unit Cost 193.25478
• Remaining quantity (110 out of 830 - 720) from Entry No: 4291681: Quantity 110, Unit Cost 184.04407

As per my manual analysis, the total quantity matches the closing entry, and the cost comparison has a negligible difference (0.000699999975040555), which is acceptable.

Attached Data:

I have attached an Excel file with two sheets:

1. "Example result": Contains the sample result and manual calculation.
2. "Data for Alteryx": Contains the transaction data for analysis.
3.  

Objective:

I need to replicate this FIFO analysis in Alteryx to assess the closing quantity and cost accurately for each item code without manually calculating each time. Specifically, I want to:

1. Filter and sort transactions by item code and date (or entry number) to apply the FIFO method.
2. Calculate the remaining quantities and costs to determine the closing inventory.
3. Compare the calculated closing quantity and cost with the posted G_L entries.

Request:

Could someone guide me on how to set up this workflow in Alteryx? Any suggestions on tools or configurations needed to automate this FIFO inventory costing method would be greatly appreciated.

Thank you in advance for your help!

[apathetichell]

Where are you in your workflow build? What kind of errors are you seeing? 

[Buddhi_DB]

@apathetichell  I haven't created the workflow yet because I'm not entirely sure how to get started with this FIFO inventory costing method in Alteryx. I am struggling with conceptualizing how to set up the workflow to accurately calculate the closing quantities and costs based on FIFO.

[apathetichell]

The running total tool (grouped by inventory item) is very helpful. Play around with that and see how far you get.

[Buddhi_DB]

The problem is  the running total tool can't analyze the latest purchase near to closing quantity

[KGT]

Running total will allow you to "assign", and then you can use other tools to cut that at the right point based on formulas. If you wanted to do it all at once, then the Multi-row may work better but it will get more complicated.

There is a sample under "Help > Use Scripting... > Build a macro > Assign Supply to Demand" that does similar to what you are after here except deals with multiple locations and closest warehouse etc. It's a lot more involved than what you've described but might also give an idea to solve for your situation.

I would use a "Sort/Running Total/Filter" and then whatever else was needed after that allocation. The Running Total being the key in here and I believe that would be the method @apathetichell was referring too as well.