Has anyone built a financial model that incorporates LIFO/FIFO for COGS?

Hey everyone,

I'm working on a long-term financial model and wanted to incorporate inventory accounting using either the LIFO or FIFO method for calculating COGS. I understand the basic conceptual difference, but I’m looking to implement it in Excel in a way that can dynamically adjust as inventory is purchased and sold over time.

Has anyone here built a model that handles this effectively? If so:

• How did you structure the inventory layers?
• How did you link purchases and sales to the respective layers?
• Did you use formulas or VBA/macros?

If you're open to sharing a sample Excel file (with dummy data is totally fine), it would be hugely helpful as a reference.

Thanks in advance!