Inventory Aging Solution with FIFO (First In First Out)

A client asked for a custom business intelligence report to track the aging of her products in quarterly buckets (1-3 months, 4-6 months, etc.). She could not afford to assume that her quantity of stock on hand is actively moving due to one recent goods receipt, as suggested by SAP Standard Content. She needed additional insight into her data.

“Can we build a more accurate inventory aging report? The one provided by SAP ages the entire stock quantity by the latest receipt date. This does not give us a valid aging of the stock that we have on hand.”

While the standard content for SAP Business Intelligence is immensely useful for quickly implementing common solutions and getting critical reports into the hands of business users, the out-of-the-box material do not address specific issues due to the different nature of each industry.

Imagine a grocery store manager who needs to move his inventory of milk and we can begin to see the urgency.

Assuming the manager receives the bulk of his delivery on the first of the month, he will need to to quickly start moving his product by the 15th of the month before his product spoils. This can be accomplished with a sale or promotion. A delivery of additional milk products at the end of the month should not imply that his remaining stock is fresh.

Luckily, most of my clients do not stock perishable goods. Still, having slow or non moving stock hinders a company’s ability to operate since the capital is locked-up as inventory.

Figure 1. The out-of-the-box inventory aging report, which applies the latest goods receipt to the entire stock

First-In First-Out (FIFO) is easily understood on a conceptual level. You ship the material in the order in which you received it.

However, this approach is difficult to implement at the programming level because of the book-keeping overhead. There isn’t a direct relationship between a goods receipt and a goods issue in the logistics module. You will need to keep an account of the movements manually.

Tracking the stock balance while applying the latest goods issue to the earliest goods receipt required an large amount of coding and testing, which we could not afford in our development timeline.

To work around this constraint, I developed a simpler solution. The approach hinged on the assumption that the earliest goods receipts will be consumed by the earliest goods issue. As such, they do not need to be accounted for.

Because I had already helped this client to implement the standard non-cumulative inventory solution to report current stock balances, we already knew the ending balance quantities and values for a given set of materials on a given date.

With the ending balance quantities identified, we simply worked backwards and counted only the goods receipts that contributed to the current stock.

Figure 2. A custom inventory aging report with various buckets for segmentation.

The client loved the simpler approach and I was able to deliver the solution in a few days, well within the constraints of the project timeline. If there were an opportunity to revisit this solution, I would like to further increase the accuracy of the valuation to account for variations in the monthly material valuations (used at other clients). More to come in a future post.

What do you think of this approach? Would you like more detail on this solution? Does it address an issue that you’re currently facing? Send me an email or let me know in the comments below.

P.S. Looking to implement your own custom solution? Get my pre-made guides where I map out the information that you need.

[thrive_leads id=’346′]