inventory management and control

How to Develop Traffic Light Inventory Management and Control Report in Excel to Reduce Stock Outs

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.

Identify the list of Made-to-Stock items

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.

Look into future Demand, Receipts & Open PO

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.

Comments, Actions, Priority and Shipment Mode

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!

Start Reporting on Weekly Basis to All Stakeholders

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!

10 thoughts on “How to Develop Traffic Light Inventory Management and Control Report in Excel to Reduce Stock Outs

  1. Thank you Very much for the great article on Inventory Traffic light. Very good article indeed. Can you help me share the work sheet with formulae / syntax, for me to apply for my data. I keep reading all your articles and find they are very informative and interesting for application with my data.

    Thank you once again !!!!

    1. Hi Phani – as I mentioned formulas can be devised as per business requirement and what logic you want to apply. This is just conceptual framework. Thanks, Muddassir

    1. Hi Cyril – as I mentioned formulas can be devised as per business requirement and what logic you want to apply. This is just conceptual framework. Thanks, Muddassir

  2. I am currently working with Safexpress Private Limited, India. The idea you shared is good, but one cannot also deny that it is very much manual and updating the reports on regular basis is not very much practical and may lead to lots of human errors.

    In my organization we use two types of software :-
    1. Propel, purely an ERP software
    2. Spotfire, software for preparing and fetching various kinds of MIS reports.

    The software Spotfire is linked with Propel database and you can pull out any kind of report you need just feeding your query to Spotfire

  3. I’m one of those people that use MS Access to pull this type of info out of an ERP/MRP system, however, using your idea you could use MS Query on your Excel spreadsheet to quickly populate and update the data from the system.

  4. This is an awesome guide. Thanks for sharing this Dr. Muddassir! Good practical, specific guides to improve inventory management aren’t easy to find

  5. You note, “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”. Good point, but looking out side the system is exactly what one needs to do to improve visibility and performance. And yes, at least at first, that is almost always manual – applying mental effort to understand and figure out what is going on.

    A real life example: Remember a basic premise of MRP is to balance supply and demand. To do that it makes suggestions and ASSUMES the suggestions will be carried out.

    To better understand what was really going on, we used our system ad hoc report writer to “code” a different version of the MRP reports to answer the question, “What happens if we don’t…follow MRP?” The end result was that we could look at our inventory all the way from individual items up to enterprise aggregation in both units and dollars. And we could easily see where we had issues.

    Parts scheduled to come in late. MRP says pull in. Unless acted on, there are still due out in the future. MRP says cancel orders. Until the orders were canceled showed as excess inventory.

Leave a Reply

Your email address will not be published. Required fields are marked *