Contrasting the Front & Back End Components of Dollar-value LIFO Calculations

All dollar-value LIFO calculations are composed of two separate components to arrive at the end result (some would consider the end result to be the current year end LIFO reserve; others may consider it to be the current year end LIFO inventory balance). We call those two components the front & back end of a LIFO calculation.

Front End Calculation Component

The front end of the LIFO calculation is the current year or cumulative index calculation (or inflation calculation). We call this process the front end because the back end component of the LIFO calculation is dependent on the front end’s outputs or results, meaning the back end can not be completed until the front end outputs have been developed. The specific steps for front end calculations are dependent on if inflation is calculated using internally or externally developed inflation indexes.

Front End Internal Index Calculations

For internal index calculations, current quantities on hand are multiplied by their current & prior or base year item/unit costs on an item-by-item basis in order to calculate the sum of the extensions, divide the current & prior or base period extended costs & compute a current year or cumulative inflation index (or indexes by LIFO pool if there are multiple pools). The front end internal index component also involves many other considerations such as how new items are to be treated, how to deal with exceptions, performing analysis to identify outliers treatment and determining if certain outliers should be included or excluded from the index calculation.

Front End External Index Calculations (i.e. IPIC CPI/PPI)

For external index or IPIC calculations, Bureau of Labor Statistics (BLS) Consumer or Producer Price Indexes (CPI/PPI) are used to calculate inflation. Items are assigned a BLS category & the inventory balances by BLS category are then multiplied by each category’s current year inflation index (taken by dividing the current year BLS index by the prior or base year index). The front end external index component also involves many other considerations such as if items need to be segregated into what are called 10% groupings, if BLS weights of relative importance need to be applied to the respective 10% groups, and how the establish the appropriate LIFO pools.

Back End Calculation Component

The back end can be thought of as the LIFO inventory or reserve calculation & described as the exercise of plugging the current period’s front end outputs (i.e. current year and/or cumulative index(s) & inventory balance(s) by LIFO pool) into the prior period’s back end outputs to compute the current period’s LIFO inventory, reserve & expense (income)(LIFO expense (income) = change in current vs. prior period LIFO reserve; increase in CY vs. PY LIFO reserve = expense & decrease = income). Together, these two components are integral to completing dollar-value LIFO calculations. The illustrations below provide a summaries of what’s been outlined above:

Internal Index Calculation Components

External Index Calculation Components

Contrasting the Front & Back End of Calculations in the LIFOPro Software

• Internal Index Module – Completes the front & back end for internal indexes
• External Index Module – Completes the front & back end for IPIC LIFO calculations using Bureau of Labor Statistics (BLS) Consumer or Producer Price Indexes (CPI/PPI)
• Screen 3
  • Completes the back end of LIFO calculations upon the user entering the following front end calculation outputs:
    • Inventory balances at cost by LIFO pool – these are the inventory balances in your general ledger being tracked without regards to LIFO (i.e. cost), which will typically be FIFO, average cost or sometimes EAC, standard cost or some other non-LIFO method (note: this will be a single amount if you have 1 LIFO pool)
    • Current year indexes by LIFO pool (single index for 1 LIFO pool)
  • Can also be used to complete mid-high level interim estimates & ad-hoc projections

How the Internal & External Index Modules Work (Front & Back End)

• Front end
  • Internal Index
    • User loads data input sheet with the following fields (additional fields required depending on desired settings to be applied):
      • Item number or description
      • Quantity on hand
      • CY & PY or base year item/unit cost
    • User applies desired item cost & analysis settings
    • LIFOPro software completes CY index calculation
    • Complete set of front end reports automatically generated & saved
  • External Index
    • User applies desired settings in IPIC Module Menu screen
    • User loads data input sheet with the following fields (additional fields required depending on desired settings to be applied):
      • BLS category code/number (CPI/PPI category/code)
      • Year end inventory balance for all BLS category codes/numbers included in data input sheet (can be a summarized or detailed list)
    • LIFOPro software completes CY index calculation
    • Complete set of front end reports automatically generated & saved
• Back end
  • Front end outputs transferred into software LIFO database file
  • Current year summary report displayed on screen showing front & back end results
  • Internal Indexes only: User enters general ledger balances at cost by LIFO pool in Internal Index Module screen  if CY extended cost totals generated from the front end calculation don’t tie to the GL totals
  • External Indexes only: From IPIC reports screen, user selects Go to Screen 3 button to enter general ledger balances at cost by LIFO pool if balances generated from the front end calculation don’t tie to the GL totals
  • User selects close year end button to save back end results to software LIFO database file
  • All back end reports updated to reflect the results of the calculation just completed (last period closed)

How Screen 3 Works (Back End Only)

The LIFOPro software was originally designed to manage only the back end of LIFO calculations. The external index module that handles the front end of IPIC LIFO calculations wasn’t developed until the late ’90s, and the internal index module that handles the front end of internal index calculations was just released this year. Because of this, a back end LIFO calculation still exists today called Screen 3. This module represents the legacy version of the LIFOPro software & provides users with the following functionalities:

• Internal & External Indexes:
  • Automating back end of LIFO calculations after completing the front end of LIFO calculations outside of the software & entering inventory balances at cost & CY indexes by LIFO pool (inventory balances at cost = FIFO, average cost, EAC, etc. These are your general ledger inventory balances gross of your LIFO reserve): this is for users who wish to maintain the back end of their LIFO calculation within the software, but complete the front end of their LIFO calculations outside of the software.
  • Automating LIFO estimates by entering forecasted or projected inventory levels & indices
• External Indexes Only:
  • Year end calculation: Updating data input sheet inventory balances calculated in External Index Module to tie to general ledger balances at cost – this may be required when the data input sheet totals loaded by the user for the CY index calculation (front end) does not tie to the general ledger balances at cost (i.e. FIFO, average cost etc.). Users complete the front end of the calculation in the external index module & select the “Go to Screen 3” button to enter their GL balances by pool at cost into the input cells. The front end CY index calculation values automatically transfer into Screen 3 & the back end LIFO calculation values automatically update after the GL balances by pool are entered.
  • Interim estimates: Users can make what we call a “mid-level” estimate by applying the most recently published BLS inflation indexes to last year’s product mix  & the current period’s actual or forecasted inventory balances at cost by LIFO pool. Users select the Yellow button to load the prior year’s calculation data, press the calculate button to apply the most recently released BLS indexes & select the Go to Screen 3 button the current period’s actual or forecasted inventory balances at cost by LIFO pool. The front end CY index calculation values automatically transfer into Screen 3 & the back end LIFO calculation values automatically update after the GL balances by pool are entered.

Key Takeaways

• The LIFOPro software makes both the front & back end of LIFO calculations
• This means that you can manage nearly all of your LIFO within the LIFOPro software & reduce your manual efforts to the following:
  • Internal indexes:
    • Preparing data input sheet by exporting inventory report from accounting database (often referred to as item detail, stock status or inventory valuation report)
    • Selecting desired calculation & analysis settings within the software
    • Record journal entries & adjust LIFO inventory/reserve account balances to tie to amounts shown in LIFOPro reports
  • External indexes:
    • Exporting inventory report from accounting database
    • Assigning BLS categories to inventory report
    • Selecting desired calculation & analysis settings within the software
    • Record journal entries & adjust LIFO inventory/reserve account balances to tie to amounts shown in LIFOPro reports
• Screen 3 allows users to quickly & easily perform the following:
  • Make quick & easy interim estimates & ad-hoc projections without having to prepare or load inventory reports
  • For external index users, plug general ledger balances into software that should be used for back end of the calculation after making front end of calculation if front end input values don’t tie to general ledger totals (for example, if the data input sheet loaded by the user for the front end calculation only represents 75% of total inventory, the balances at cost by LIFO pool to calculate the LIFO inventory, reserve & expense (income) amounts i.e. back end should be updated to tie to the general ledger totals. Internal index users perform this step within the Internal Index Module screen)
  • Make the back end of the calculation if the front end was completed outside of the software