Closing QTY and Value assessment using FIFO Closing - (Reposting)

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 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

Result

	QTY	Cost	Purchase unit cost	Entry No
Latest Purchase 1	720	139,143	193	4346382
Latest Purchase 2	110	20,245	184	4291681
Total QTY	830	159,388.289
Diff	-	0.000700		0.000700

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.  

Alteryx Work that I've built:

I've built a workflow, but I'm not sure I've built it in a nice way presentable way. 

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!

FIFO clsoing QTY and cost assessement - Copy.xlsx

FIFO example.yxzp