Common LIFO Calculation Errors

Manual spreadsheet LIFO calculation formula errors

=IF(F46>0,F46*D46,IF(-F46<C57,F46<C57,F46*D57,IF(F46<C58,F57+((F46+C57*D58),IF(-F46<C59,F58+((F46+C58)*D59, IF (F46<C60, F59+((F46+C59)*D60),IF(F46<C61,F60+((F46+C60)*D61),IF(-F46<C62,F61+((F46+C61)*D62),IF(-F46<C63,F62+((F46+C62)*(D63),IF(F46 <IF(F46<C64,F63+((F46+C63)*D64),IF(-F46<C65,F64+((F46+C64)*D65),IF(-F46<C66,F65+ ((F46+C65+ ((F46+C65)*D66),IF(F46<C67+ ((F46+C66)*D67),IF(F46<C68,F67+((F46+C67)*D68),IF(-F46<C69,F68+(( F46+C68)*D69),IF(-F46<C70,F69+((F46+C69)*D70),ERR)))))

This is an example spreadsheet formula that calculates LIFO inventory balances & all possible layer liquidation possibilities for a company with 15 LIFO layers. These types of formulas are convoluted & require updating when used in subsequent years/pools. The high probability for errors to occur causes manual spreadsheet LIFO calculations to be unreliable.

General LIFO calculation errors

  • Incorrect decrement calculations – These are very common in spreadsheet LIFO schedules.
  • Incorrect calculation of Sec. 263A costs – For taxpayers for which these are an add-on costs to the calculation of Sec. 471 LIFO balances, errors are common for years in which there are decrements.
  • LIFO reserve balances don’t tie to detail – The current and prior year inventory at base and LIFO inventory balances should tie to the LIFO layer history carryforward schedule.

Internal index calculation errors

  • Exclusion of new items in inventory – IRS LIFO Regs. specifically prohibit exclusion of items not in inventory the prior year(link-chain) or base year(double-extension) because doing so tends to overstate inflation. The Regs. allows the prior year or base year index to be set equal to the current year price, but this method understates inflation.
  • Using current year prices for prior periods – The easiest way to handle new items is to set the prior period item price equal to the current year item price. This method is allowable by the IRS but this method will understate LIFO inflation and the understatement can be significant especially for double-extension method taxpayers.
  • Improper representative sampling – The potential for errors of this nature are great and the likelihood of this type of error increases for taxpayers that have a diversity of inventory types and locations.

IPIC Method pool index calculation errors

  • Wrong indexes or BLS weights looked up – The typical supermarket chain’s IPIC calculation, when performed correctly, will make use of approximately 60 (30 current year & 30 prior year) CPI indexes and 20 BLS Weights. This is a total of 80 numbers to be looked up, and there is almost always at least one error when these indexes are looked up manually.
  • Changes in makeup of CPI or PPI categories – There are frequent changes to the PPI Table 6 categories with numerous new categories added and deleted every year. Many taxpayers use the same categories year after year without determining whether indexes are still compiled for the categories used or whether they have inventories in categories added since the prior year end.
  • Inconsistent use of PPI Preliminary or Final Indexes – The IRS Regs. allow use of either preliminary or final PPI Table 6 indexes, however taxpayers are to consistently use either preliminary or final indexes. Most taxpayers use preliminary indexes, but accessing preliminary indexes on the Web is tricky since only final indexes are included in the BLS database available on the Web starting the fifth month after indexes are published.
  • Wrong indexes or BLS weights categories used – An example of this error is the use of the SAF116 Alcoholic beverages CPI category. This category includes SEFX Alcoholic beverages away from home as well as SEFW Alcoholic beverages at home . SEFX is not a commodity-only price index and should not be used. SEFW is the appropriate category to use in the LIFO calculation.
  • Incorrect 10% method rollups – Spreadsheets cannot handle the logic necessary to deal with situations where category indexes are to be calculated based on the 10% threshold being met when this level changes in subsequent years because of inventory mix changes. As a result, category indexes are often calculated at the wrong level.
  • Incomplete accounting of items actually present in inventory – A good example of this error is often seen for supermarket chains in their APPAREL commodities inventory. Because the APPAREL dollars are substantially less than 10% of total inventory, only the total APPAREL commodity dollars need be gathered. Most large supermarket chains carry only a few of the APPAREL commodity categories in inventory, so only the CPI indexes and BLS weights for those categories should be used for the APPAREL commodities index category index calculation. The shortcut commonly used, in error, is to use the overall APPAREL commodities index rather than the correct weighted average using the indexes and BLS Weights for the categories actually present in inventory.
  • Weighted average indexes – Various spreadsheet formula errors are common for both category indexes(using BLS Weights for the 10% method) and for the dollars weighted average pool index calculations.