Please raise your hand if you’ve ever panicked when your boss asked the question how many items are Out of Stock from Made to Stock list? Or How many items we are carrying too much excess stock? And it is not unusual that your boss wants this information in next 5 minutes! This is where inventory management and control report comes into play.
Just to clear one point from start, I am not talking about inventory management and control reporting system from warehouse physical inventory control perspective but I am talking about inventory management and control report from a planning/management perspective.
It is very possible that you already have some kind of ERP generated report or Excel report which gives you your stock out information. Or many be you need to pull together 2 or 3 different reports and vlooks to come up with the Excess Inventory Report which is developed by a clever planner in Microsoft Access and if he leaves or goes on holiday then no one knows how to fix any Access query links!
Well, there’s good news: creating such an inventory management and control report doesn’t have to be that daunting. With the right template and data tools at your disposal, you could easily create an engaging, visual control — all without fancy inventory programs, huge budgets, or blue analytics guys!
Download this free Microsoft Excel template, and we’ll walk through how to use it right now in this very blog post. When we’re done you’ll know exactly how to create a stunning visual inventory management and control report that I have successfully used and deployed in many different plants, and seen quick benefits such as:
- Reduction of stock out and its related cost.
- Identifying how many items are Out of Stock (Red), Below Safety Stock (Yellow), covering safety stock and imminent orders (Green) & Over Stock items (Blue). See details in next section.
- Track reduction of Reds & Blues.
- Quickly see what PO’s are open and what deliveries on the way.
- Decide quickly shipping mode for supplier depending on Traffic Light Status.
- Ask your planning team and/or suppliers to fill in comments when Red items will be in stock or when blue will reduce.
- You can extend this report to your 1st tier suppliers and ask them to fill in for A Class or Kanban items which they might be supplying with short lead time from stock! If they are not sending you advance shipping notice (ASNs).
Ready? Let’s dive in.
Inventory Management and Control Report Steps
Step 1 – Identify the definition of Traffic Light Color
You can define your own definition suitable for your business. But this is what I typically recommend:
Red Items: items with inventory less than demand for next 7 days and safety stock.
Amber Items: items with inventory to cover demand for next 7 days but less than safety stock.
Green Items: items with inventory to cover demand for next 7 days and up to safety stock
Blue Items: items with inventory to cover demand for next 7 days and greater than 150% of safety stock
– this is my rule of thumb, you can define your own!.
Step 2 – Identify the list of Made-to-Stock items you want to include in the list.
From your Made to Stock (MTS) and Made to Order (MTO) list select your MTS list and corresponding Total Stock, Safety Stock and Net Stock.
As you can see from the image below, Net Stock is the difference of On-Hand stock and Safety Stock. The main purpose of Safety Stock is to cope with lead time variation and demand variation. In simple terms we wants safety stock to be available most of the time, if not all of the time!
I have also tested in some plants the ‘net-off’ current orders due in the next 7 days to arrive at Net Stock number. However, it depends on the type of business and market you are in. So I’ll let you decide if you want to do this or not.
Then mentioned is the average monthly usage which provides a quick glance on run rate demand and the identifies the ABC classification for those MTS items. My experience tells me that A Class items should cover your 80% of Value, Volume and Frequency and if you have them in stock then you can almost guarantee 80% of your On Time Delivery Performance!
It is possible that all of this information is not directly available from your ERP system and you have to manually pull in some of the information. However, these columns are the most critical in this inventory management and control report.
Step 3 – Look into future Demand, Receipts & Open PO
The third step in this inventory management and control report is to list down what the future demand in next few weeks would be and what are the future receipts to see the gaps. The reports shows demand profile and what you are expecting to receive from supplier or have planned in manufacturing. Another key data set here is Open Purchase Order (PO) or Open Work Order quantity which shows total quantity outstanding.
Depending on your business requirements, you can also add columns to project stock, for example:
Stock (Projected) = Current Stock + Delivery (Planned) – Demand (Projected)
In the same file you can also add columns like “Late to Request Date” which shows suppliers how much quantity they are late by, which acts as continual reminder to them.
Step 4 – Enlist Comments, Actions, Priority and Shipment Mode
Next action is to use this report to make it ‘actionable’. Add a section for comments/reasons where planners/supply schedulers or suppliers can add comments on why they are late or what they are doing to expedite it in order to bring the inventory into the Green.
Furthermore, you can add column like Special Action Required- Yes or No. What would be the priority for production and/or supplier in terms of allocating capacity and what would be the shipment mode based on the criticality of out of stock supplier.
Step 5- Start Reporting on Weekly Basis to All Stakeholders
The main purpose of this report is to keep SKUs in Green, Eliminate Reds ASAP and move yellow items into Green as quick as possible. I have find this weekly track as inventory management and control very useful. When I was plant supply chain manager I had set-up a quick 30 minutes weekly review with my team to look into RED items and see what actions we were taking to eliminate those ASAP.
It has done miracles for me in terms of Improving On-Time Delivery Performance and Stock Fill Rate for MTS items. As you can see from the image below, we have pushed to reduce REDs, and as consequence Yellow has gone down and Green has gone up!
You can download a sample inventory management and control Traffic Light Report from here. First thing you will notice is there are no formulas and second I am not showing Blues! I have done this deliberately so the readers can fit formulas as per their business needs. As far as Blues (Excess Stock) are concerned, you can add this in your Traffic Light Report if there are obvious problems of Excess & Obsolete Inventory. The main purpose of this blog is to share the concept not the template with formulas!
Summary & Criticism
The obvious criticism supply chain gurus will have about this report is, it is “manual” and it is “outside the system” or “it will require extra resource”. Most of the points will be fair and valid. However, in my defense, I have used many ERP system and still have not found one system which gives me the information like this is one consolidated report as inventory management and control. If you know one such system, please do share with me.
I am just sharing my experience, and I hope you share yours by writing comments below!