← Functions

FIFO vs. LIFO Calculations using Visual Basic Modules

How to build FIFO and LIFO inventory allocation calculations in Excel using Visual Basic modules to automate profit recognition timing.

First In First Out (FIFO) and Last In First Out (LIFO) calculations are an integral part of an accounting system. Neither method affects the life-to-date (LTD) allocation, but the choice between FIFO and LIFO affects the periodic recognition of profit or loss — a timing difference that can have a large impact on a company's bottom line.

For small businesses without high-end accounting software, an Excel file with VBA modules can handle these calculations efficiently.

How the Model Works

The Excel model contains two main VBA modules:

SortData — copies and pastes transactions into a temporary worksheet and sorts them either by FIFO or LIFO methodology according to the user's choice.

Allocation — the core calculation module. It creates two arrays (Purchase and Sales) and runs intertwined WHILE loops to allocate sales units against purchase transactions until each sales batch exhausts the available purchase units.

The main module calls them in sequence:

Sub MainProgram()
    Call SortData
    Call Allocation
End Sub

Usage Notes

  • Users must enable macros before running the file.
  • Input purchase and sales data in the "Purchase" and "Sales" worksheets. Blank rows and unsorted data are fine — the VBA module sorts automatically. Do not insert columns.
  • Select FIFO or LIFO in the "Summary" worksheet, then click the Go! button to run the calculations.
  • After calculations, use the "Allocation" worksheet with filters or pivot tables to analyze periodic profit/loss recognition (e.g., Q3 2014).
  • The module checks whether total sales units exceed total purchase units for any inventory item and prompts an error message if so.
  • It is recommended to password-protect worksheets after setup to prevent accidental modifications.

FIFO vs. LIFO: Key Difference

The only difference between the two methods is the sort order applied to sales dates in the SortData module:

If AllocChoice = 1 Then  ' FIFO
    ' Sort sales ascending by date
Else                     ' LIFO
    ' Sort sales descending by date
End If

FIFO allocates the oldest purchases first; LIFO allocates the most recent purchases first. The same allocation loop handles both — only the pre-sort order differs.