First, a disclaimer: My sincere apologies for this (potentially) misleading title. If you are in a low-mix high-volume industry such as computers, communications or consumer electronics, then the odds are that you experience no issues and therefore would gain next-to-nothing, from back-order rescheduling. However, if your business is high-mix low-volume such as industrial, military-aerospace, medical, or perhaps even automotive, then this post is for you. In either case, I encourage you to read on…
The more I work with businesses in the electronic manufacturing industry, the more interested I become in sales and operations planning (S&OP) processes. I’m increasingly convinced that the most efficient way to reduce inventory is to properly filter client demand. However, taking supply constraints into account in the highly volatile electronic components market is a big challenge. Especially in a high-mix, low-volume industry, where planners are the ‘under-recognized’ heroes.
That said, there is a pattern which I see systematically infecting high-mix, low-volume factories. Back-orders - orders you haven’t shipped because you’re late - are simply not rescheduled.
As a result, those factories have 20 to 30 days of late orders and there’s no time in the supply chain schedule in which to catch up. It therefore becomes a 20 to 30 day inventory handicap. No more, no less.
In this post, I’ll demonstrate a simple dashboard which, if this is an issue that you recognize, you should build at least once to see how much you can save through back-order rescheduling.
As with my previous posts containing such simulations, on safety lead-time optimization and shortage-risk ranking, this entire simulation can be completed in Excel.
A back-order dashboard in which the next 30 days are 85% too high resulting in an $808,000 inventory handicap
Step 0: Excel upload [optional]
If you have more than 100k+ rows of data, begin by downloading this Google Sheet in Excel format. It is a lot faster, however you will need to re-build the pivot table once downloaded - it will break in the format change from Sheets to Excel.
Step 1: Data extraction
To build the dashboard, what you will need from your manufacturign resource planning (MRP) are all the quantities required and their respective dates, per article, plus their unit prices. To re-iterate, only required quantities i.e. negative MRP quantities. No stock, no orders, no purchase requisitions. In columns F to H, we are splitting the requirements per rolling 30-day period.
Step 2: Number of working days per 30-day period
If you have downloaded the file to Excel, as mentioned above chances are the pivot table is broken. You’ll need to re-build it: It is just the number of distinct need_date (working days) per period.
Working days calculation per period (unique need dates per period)
As you can see in this example, the factory seems to work just five days per week. And the plant seems to shut down for a week in period four.
Step 3: Result
Now you can enjoy — or cry — at the results:
For this factory, the required value per working day is $88,000 in period one, $51,000 in period two, $44,000 in period three and $47,000 in period four. This means that period one represents 185% of the average of periods two through four.
What does all this mean? It means that back-orders are not being rescheduled properly.
Unless this factory is an exception to every rule and is capable of producing 185% of an average month all at once, then it is working with a $808,000 inventory handicap, or 85% of the average value of any given period.
Of course, this exercise only monitors your back-orders. You still have the rescheduling job to look forward to! But now you have a much clearer indication of the problem and the value of the inventory handicap.
If you know of some good alternatives to looking at or solving this problem, or any tools to reschedule back-orders, then let us know in the comments section below. We’d be interested to hear of alternatives.
My final warning: remember you should never link supply-chain performance to your objectives. Read this story as a must in order to understand the limits of such an indicator… A lesson I’ve learned the hard way.