2020/11/14

FIFO Inventory Valuation in CTRM

by Lucido Group

Background

Inventory valuation is a month end process that accounts for inventory and produces margin reports on a firm’s inventory network. The inventory valuation process determines the cost of inventory for accounting and provides useful data to measure the profitability of a consumption asset or marketing network as well as record inventory journal entries. The inventory valuation methodology can vary from company to company. The most commonly used methods are FIFO, LIFO, and Weighted Average.

Most CTRM/ETRM systems can accommodate basic business processes, but many cannot handle some common yet nuanced scenarios out of the box. These scenarios include:

  • In Transit Inventory
  • Gain/Loss adjustment valuation
  • Negative inventory valuation
  • Prior Period Adjustments

This article will focus on the FIFO inventory method calculation issues that can arise and some of the approaches you can take to resolve them. Another paper covers the weighted average cost of goods, or WACOG method.

First In First Out

The FIFO method matches the oldest inventory with the most recent sales. This increases inventory profit in times of rising prices as older, cheaper inventory is sold at current higher prices. While profits increase as prices rise, the opposite is true when commodity prices fall. Older, higher cost inventory is paired with falling sales prices meaning a reduction in profit. The method overall is easy to understand, but implementing the method within a CTRM system presents many complex issues.

The FIFO method is dependent on proper inventory layers being built and sales relieving those layers in the proper order. In an organization with hundreds of purchases, thousands of inventory movements, and thousands of sales, the data quickly can become overwhelming for back office. Further, the precise layering required for FIFO is susceptible to a tremendous amount of human error. If a purchase is accidentally entered on the wrong day or a bulk scheduling movement is corrected after the fact, the layers downstream of the error are thrown off, resulting in more work for the back office to track down and correct.

More detailed scenarios can become complicated due to the way the layers are built in the system. Inventory that is left in transit at the end of a month needs to carry over to the next month with the layers that it had in the prior month. If a firm values gains and losses at the inventory cost, those gains and losses need to receive the correct layer price at the time they are entered. Depending on a company’s business processes, negative inventory can be valued at market price or at the last known layer price. Lastly, prior period adjustments can be a pain point for firms. A change at a critical point can cause a cascading effect to all the affected layers downstream.

Real World Scenarios

The scenario below highlights some of the complex scenarios with FIFO. The in-transit movement from Location A to Location C must use the inventory layer price from the September month and not be changed to the October price from location A when it delivers. The loss at location D at the end of the month would need to have the value of inventory included to write off the value of lost inventory. Some CTRM systems do not handle this scenario and do not track the value when making loss adjustments to inventory.

September 2020

October 2020

CTRM Issues

FIFO is a difficult method for a CTRM system to handle properly. We have seen firms attempt to implement inventory valuation, but because of the aforementioned complexities many revert to spreadsheets. This is not the right answer. Reverting to spreadsheets creates risk in data quality and prevents a firm from maximizing their CTRM investment dollars. There are ways to address these issues within the system, including scripts to valuation data, code adjustments, and automated physical adjustments. The solutions we have utilized in the past can handle negative inventory, in transit, gain/loss value, prior period adjustments, and much more.

We Can Help

Our team has decades of experience working with CTRM systems and complicated inventory valuation processes. If you are in need of expertise to properly configure or troubleshoot your inventory valuation, please contact us.