During a casual “Morning Coffee” chat at work, our Inventory Control department’s manager explained his frustration on how he wasn’t getting enough support from other department to lower the on-hand inventory which is nothing but stagnant money in the company. His reason on why he wasn’t able to get the buy-in to purge the excess inventory is because the Engineering team wanted to hold on the inventory hoping that one day we will have the demand to sell the product. However, I was told we have been holding on to the inventory for several years now. And these products were seasonal.

The very first instinct I had was to keep optimal inventory quantity and purge the excess. So, to begin calculating the optimal inventory quantity, I started digging into the list of the parts we have in excess quantity. From Accounting department, I learned that we pay 20% of the total inventory on hand as holding cost per year. I also gathered information on last 3 years demand and usage report to understand average demand on those parts per year. Then, from Purchasing team, I learned how much they spend to order the parts and time taken order-to-receive. Understanding order-to-receive is important because we don’t want to exhaust all the on-hand inventory if we can’t get the parts on time from the supplier which will put us on backordering risk.

A classic math model for inventory management is:

$$ Q=√(2DS/H) $$

Q= Inventory Quantity

D=Demand

S=Setup cost/Purchasing cost

H=Holding cost

This equation will tell you, estimated quantity of inventory we can hold on-hand. However, this is a linear-math model and would not work if the product is seasonal.

After research, I came across to the Wagner-Whitin Model - a dynamic approach to manage the inventory and created a simple Inventory Calculator in Excel.

If we input unit cost, holding cost (usually 20% unit cost of the on-hand inventory), Ordering cost/setup cost, planning year, demand, and purchased quantity, the calculator will output how much excess inventory we have on-hand, what should be the optimal quantity to store on-hand, how much we can save by obsoleting excess inventory. (Programming this on Python would have been more easier. But due to limited resources, I chose to write an Excel program instead.)

With this, we were able to show the data to the leadership team to look into Excess and Obsolete program (E&O) since inventory is nothing but stalled money.