How to build a fully automated Inventory Analysis tool with ZERO coding in Excel.
Here is a case study of a real inventory analysis tool I built for a customer.
The business is based on the production of plastic containers finished based on customer’s specific surface decorations. The supply chain is a fairly articulated mix of internal and external operations, the bill of materials is multilevel and there’s a continuous renewal of the products portfolio.
I deployed the tool by the Fast Excel Development Template in less than a week, the most part of which was dedicated to data collection and consistency control.
Here’s a fully automated inventory analysis Excel tool based on the fictitious cycle manufacturer P_S Cycles Ltd. data set. If you would like a copy, please complete this form and I will email a download link to you:
It bundles the working inventory analysis tool, a data set and the FEDT template I used. I’ve included instructions for use at the end of this article. BUT, please read the article first as it will make a lot more sense!
Starting with this template, no human wrote any additional VBA code during the production of this fully automated Excel Inventory Analysis tool.
The traditional inventory level analysis is based on the past.
Why? Because the current inventory levels themselves are the result of what happened, therefore the traditional ABC (or Pareto) value-based classification is important from a financial point of view but it offers a very limited help when it comes to supply chain decisions on inventory levels strategy.
What if an A class SKU is the next shift requirement only? Or if it is sourced with a long lead time?
Let’s introduce the concept of day of consumption: we aggregate the past consumption records within an historical horizon (e.g., the past 24 months) of each SKU, calculate the average day of consumption and compare the average daily consumption to the current inventory level.
By doing so, we can express inventory levels in terms of Days of Cover (AKA Days of Supply, Days of Demand). This is far better than a financial value only- for example, we can accept a 2-weeks cover for an oversea sourced component or take action for a four weeks covered commodity we can receive the next day we order it.
The main advantage of this approach is that we can consider the consumption history of each item, no matter its bill of material level, therefore the calculations are quite simple even if they could be intensive in case of a large number of records.
Why we need to look into the future and how we can do it?
There’s a critical point: we are assuming the past to be a good representation of the future.
But what if an SKU used to be a top seller in the past will be reaching its end of life in the next month? There are different scenarios, like the customized products market or tech competitors, in which for whatever reason the future could very different from the past. Not to mention the recent disruptive events pandemic related.
Can we take into account the future in our inventory analysis and use the concept of cover days at the same time?
Well, companies use to have sales order books and forecast: we can look at them as the digital representation of the future, our crystal ball.
These pieces of information are related to the finished goods only, or to the so-called independent demand: what about the semi-finished items and raw materials?
We need to explode the demand through a bill of materials, but that’s not yet accurate: in order to calculate the cover we need to take into account the inventory level at each level of the bill of material.
If a semi-finished product has an inventory level covering the next six months, the derived requirements for its components in the next six months will be zero, which is not resulting by a simple demand explosion through the bill of materials.
We have to calculate the gross requirements of each level of the BOM and we have to calculate the net requirement, the gross requirement minus the inventory level available if greater than zero, and to extend it to the lower level of the BOM as gross requirement.
It should sound familiar to supply chain professionals: it’s the MRP algorithm.
We have calculated the gross requirements, or the demand for each item based on the sales book or the forecast, or a mix of them, as an example sales book up to the first two months in future and forecast for the next third and fourth month.
Once we have decided the horizon we consider in the future, we calculate the day of demand as total gross requirement divided by the number of days of the chosen horizon.
Afterward we can define cover classes and evaluate for each one in the classes the number of items and related value.
Brilliant, isn’t it?
The Fast Excel Development Template implementation.
But what about the implementation of a software tool? The MRP is a fairly complex algorithm, if you ask to IT people they will come back with days of coding needed; as an alternative you can go with do it yourself on Excel, and after days of hard work you’ll end up with unreliable spreadsheets full of formulas. And next time you’ll need to start again from the beginning.
Well, it is not complicated at all for us at Production-scheduling and its members:
- we have the Fast Excel Development Template, which starting from the release 4 does not require any coding ability to build a fully automated process
- we have training courses on how to build a customized MRP system by spreadsheets
- we have examples and templates available for free download
The Fast Excel Development Template is a software development platform that brings the best practices of software engineering on your desktop, because yes you have Excel there, and makes you able to set up a fully automated piece of software without writing a single line of code.
How is this possible?
We developed over the years five main worksheet templates:
- Query template: where you connect to the external data sources
- Table template: main calculations template, you filter rows or column, you explode one table into another by it and much more
- Stack template: the tool that makes it easy to stack data from different columns in different worksheets and makes them normalized in one single table
- Parameter template: where you store the data you must keep in you spreadsheet – keep it limited as much as you can and export/backup it
- Pivot template: the main reporting tool and sometime a powerful calculation tool too
On top of that a number of brilliant macros are doing the automation job for you behind the scenes. One out of them is a very special one: it is a macro that writes the automation macro on your behalf. How cool is that?
For those who are very into security: the FEDT VBA code is unlocked, hence you can inspect it and if you like you can modify it.
Where you can learn how to benefit from the FEDT? There’s a number of free resources for the free subscribers of our community or if you want to access to our “How to build” series you can buy it for a reasonable fee.
Back to the subject, I built the tool in less than a week by the FEDT, and the most intense process has collected the input data and checked their consistency, and not the code development. In fact, no human wrote a single line of code: the FEDT automation system did it all for me.
I ended up with a fully automated tool integrated with the company database.
Here is the process.
- sales order book / forecast
- inventory levels
- items / item costs
- bill of materials
- cover classes
All the above pieces of data were connected to external sources by the FEDT Query template.
A few words about the BOM before deep diving into the algorithm: we can have either a single level BOM, also known as flat BOM, when the finished products are produced from raw materials or a multilevel BOM, when the finished products are made out of one or more levels of semi-finished products down to the raw materials.
While the flat BOM can be used intuitively on spreadsheets calculations, a multilevel BOM requires a more complex set of formulas combined with sorting instructions to propagate the requirements and to take into account the inventory available.
The typical BOM record we find in the ERP systems is:
Product Component QtyPer
In order to perform the explosion, we need a product BOM.
The Product BOM record structure is:
Level Product Parent Component QtyPer ComponentOffset
Do you notice the difference? In the bill of materials we have a simple parent component relationship, while in the Product BOM we read the structure of the product. A good example is the item FR188. The Component Offset in the Product BOM informs us how many rows above the parent is starting from the current row, in other words 1 means the same row, 2 the row above and so on.
We need to generate the Product BOM from the BOM we receive from the ERP.
In the Excel example available for download we calculate the Product BOM in a progressive way from level 0 up to 9 levels, in my experience more than enough for many companies.
If it is needed, the calculations can be easily adapted to accommodate more levels.
Of course, these calculations are automated, no human coding is required.
At Production Scheduling we have also an optimized module, the BOM Processor, fast and with no limits on the BOM depth which is offered in bundle when you take one of our training courses.
First, we prepare a summary of the Sales Orders within the horizon we decide (it’s a paramenter in the PARA sheet)
Second, only for the items in the list above wewe prepare the Product BOM
Third, we explode the Sales orders by the Product BOM, which is done on a Table Template. In the same table template the MRP calculations are processed: from there we calculate the total gross requirement for each component.
Fourth, there’s the calculation of the future daily consumption for each component: day of demand. This is completed by a dedicated Table Template
Fifth, we match the inventory levels with the day of demand of each item: we calculate how many days of demand the inventory level is for each component, or in other words the cover days for each item.
Sixth, based on cover classes either stored in a Parameter Template or imported in a Query Template we club the items in cover categories, we also get some pieces of information from Items: cost, item type, type of material.
Inventory report: for each class we chart the value and the number of items; another most used option is to chart the number of items and the value. That’s a Pivot template.
And another Pivot Template give us a report by item type and value.
This is a tool that can highlight improvement opportunities:
- Too low or no cover: this lead to out of stock crisis
- Excessive cover: variation in demand or errors in product life cycle management
- No movers: why are they there?
The Excel Inventory Analysis tool how to: PS_InventoryAnalysisPro.xlsm
Download the file by completing the form at the top of the article. We will send you a download link straight away by email.
Extract it directly to your C Drive. You should have a folder that looks like this: C:\P-S_InventoryAnalysisPro. Why this? This way it’s not needed to take care of the user name, which is different for each and every user.
You find a Data folder with 5 txt files – this is the data set, the last FEDT template (DevTemplate_Professional_v4.1.6.xlsm) and the Inventory Analysis Tool.
What you can do with the tool:
- To study it as a FEDT deployment case
- To play with it to see what is happening if you change the data
- To swap the fictitious data in Data folder with your company real data and run the inventory analysis tool for your company (it’s free)
- To use it as a starting point for more sophisticated analysis e.g. lead time and demand variability
That’s it. Even if it sounds fairly complex, the Fast Excel Development Template and the best practices we developed over 20+ years of activities makes it robust, simple and fast to develop. And it is affordable in terms of software license costs too: the fast Excel Development Template is free and Excel is part of the standard software tool set in many companies.