Recently the Fast Excel Development Template automation system was improved with the ability to launch other workbooks: now you can build modular systems with no coding.
With the release of the 4.2 series of the Fast Excel Development Template we added the capacity to launch and orchestrate external workbooks without writing a single line of VBA code.
This enables two main features:
- you can develop smaller workbooks dedicated to a specific function (of course with ZERO coding): this helps to keep each workbook simple
- you can re-use the same block in different buildings: each module is like a Lego component
Before deep diving into the subject of this article, I strongly recommend to read Kien Leong’s great article What Is an Excel-based Modular System? How to Build Your Own? And, More Importantly, Why?, in which he explains the principles behind modularity.
There is a second Kien’s article you should read, Fast Excel Development Template v4.2.0 – Now Supporting Modular Systems, in which he describes the new features of the FEDT 4.2 series and how to use the new ModuleList template.
And finally, Kien as host and I as guest had a webinar on Nov, 1st 2022 in which Kien taught with his usual mastery the modularity features. By the way, we had also good time.
It’s all good and interesting stuff Gabriele, but what is this article about then?
In a nutshell: This article shows how I built a system that calculates replenishment requirements of a distribution network. It borrows some Demand Driven MRP (DDMRP) criteria and re uses the same module/workbook for each node/stocking point in the network, several times. And of course I used the Fast Excel Development Template: no human wrote a single line of VBA code.
When we talk about Modularity, we mean taking the same workbook (as a module) from one system and re-using it in a different system. This is the ultimate demonstration of this principle. We are effectively taking the same workbook module and re-using it many times for different locations in the same system. I have chosen as simple but powerful example of product distribution with multiple stocking points or “depots”.
Downloads
Please do read this article before trying out the system. You can download it below, but it won’t make much sense until you know what it is meant to do and how to run it.
The Distribution Network Model
I considered a very simple retailer distribution network. Following our tradition at Production-scheduling.com, this example network distributes bicycles.
There’s a central deposit named Central that orders with external suppliers and replenishes four nodes: East, North, South and West.
East and West are end-nodes: they sell directly to the public.
The North node replenishes two end-nodes: NorthEast and NorthWest, which are end nodes.
In a similar way, the South node replenishes other two end nodes: SouthEast and SouthWest, which are end-nodes.
In total we have nine nodes.
Each node receives orders, manage its inventory and calculates its replenishment needs.
Central receives replenishment orders from East, North, South and West and orders to its suppliers.
East, NorthEast, NorthWest, SouthEast, SouthWest and West sell to the public and are replenished from the upstream node: they will have variations on the inventory levels due to the day by day customer purchases and they will have huge planned promotion sales.
South and North stay in the middle as hubs: they send replenishment orders to Central and receive replenishment orders from the children depots that they supply.
Modularity Kicks In
Let’s take a minute to think about the above network: each node is very similar to the others, isn’t it? They sell downstream and replenish from upstream.
And if we think one minute more, we come to the conclusion that they are exactly the same from the point of view of the data processing.
In other words, they are nine clones of one single template.
So yes, I built one workbook and I re used it nine times, one each node. Modularity, right?
And because each module/workbook can run other workbooks thanks to the FEDT ModuleList template, it works like this:
- Central runs East, North, South and West
- North runs NorthEast and NorthWest
- South runs SouthEast and SouthWest
Basically it’s nine times the very same workbook with different input and output data.
The nine workbooks are calling each other in a predefined cascade.
The relationship within the nine depots can be descripted by a table, much like a Bill of Materials (BOM).
Here below is the network description table of this example. Note that in this supply schema, the “Parent” of Central is the external suppliers. There also there are 6 children that are not parent: they are the terminal nodes where the sales happen.
I didn’t need to write a single line of VBA, Excel macro language.
Demand Driven Replenishment
We had an overview of the network, it’s time to dive into the single node.
I used a very simple replenishment algorithm based on the Demand Driven MRP principles.
By the way, in 2020 we had some DDMRP action at Production-scheduling.com: check DDMRP in Excel – From Idea to Execution – Webinar Replay out if you are interested in.
The principles behind DDMRP are complex and some strategic reasoning is needed before going with the deployment: I recommend to read the Demand Driven Institute books and/or attend their courses for a deeper understanding of the subject.
I borrowed the concepts and the formulas related to the decoupling buffers and to the buffer replenishment calculations, however I kept it very light and somewhat approximate for the sake of simplicity. An accurate DDMRP deployment, even for a simple case like this requires a deeper analysis of the numbers and of the business.
Decoupling buffers
What is a decoupling buffer?
It’s a dynamic demand-based buffer that smooths the demand variations, protecting and promoting the material flow.
It is built up by three zones:
- the red zone, you can think at it as a safety stock
- the yellow zone, on top of the red one, which is the zone triggering replenishment orders
- the green zone, on top of the yellow one, which is the target to reach by the replenishment orders: this determines the frequency and the size of the replenishment orders
There are two additional zones:
- the dark red one, below the red one, which means material shortage
- the blue one, over the green zone, which means overstock
The Buffer Zones
A fundamental piece of information we need is the Average Daily Usage or ADU for each item code: I considered the past two months.
It is strongly recommended to keep it up to date: daily recalculations is the best option.
For each item we need the decoupled lead time or LT, in our case the lead time from the upstream node and the cumulated lead time or CLT, in our case the full lead time up to the supplier of the item. These parameters are item-specific.
We need also a lead time factor and a variability factor, which for sake of simplicity I set to 80% and 50% respectively.
In very short the lead time factor takes into account the impact of a variation of the lead time, therefore the shorter the lead time the higher should be this factor, while the variability factor takes into account the risk of disruptions of the material flow due to unexpected factors. These two factors are evaluated on the basis of experience and on statistical analysis of the business.
Let’s calculate the size of the three zones of our buffers:
- Green Zone: ADU x LT x LT factor
- Yellow Zone: ADU x LT
- Red Zone: ADU x LT x LT factor x (1 + Variability factor)
TOG (Top of Green) = Red Zone + Yellow Zone + Green Zone
TOY (Top Of Yellow) = Red Zone + Yellow Zone
TOR (Top Of Red) = Red Zone
The safety coverage expressed in days is equal to: TOR / ADU
The number of contemporary active replenishment orders is: Yellow Zone / Green Zone
The Net Flow Position (NFP)
In order to trigger the replenishment orders, we need to compare the buffer zone with the status of already issued replenishment orders, demand and inventory.
The figure that resumes that status is named Net Flow Position (NFP): let’s learn how to calculate it.
NFP = on hand + on order – qualified demand
“on hand” is the current inventory level for the item, “on order” is the sum of the replenishment orders not already received.
The “qualified demand” part is a little more complex: it is the sum of demands past due, demand of the day, daily demands in the order spike horizon (OSH) exceeding the order spike threshold (OST) and daily demands in the order spike horizon 2 (OSH2) exceeding the order spike threshold (OST2).
Definitions:
OST = n x ADU, I chose n = 3
OSH = today + LT
OST2 = N x ADU, I chose N = 6
OSH2 = today + CLT
The OSH / OST parameters take into account unusual demand in the short term that can break the decoupling buffer, in our example it could be a small cycling club doing a group purchase.
The OSH2 / OST2 parameters take into account known huge demand that can break the full sequence of decoupling buffers upstream, in our example it could be a promotion sale.
Important: count each demand once even if it matches more than one criteria.
LT and CLT are stored in the Items table.
Replenishment Orders Issuing and Release Priority
The rule is very simple.
The order quantity is:
- =0 (zero) …. if Net Flow Position (NFP) > Top of Yellow (TOY)
- =Top of Green (TOG) – NFP …. if NFP <= TOY
The release priority index is NFP / TOG, the lower the index more urgent the order is.
In an ideal conditions, the projected on hand (OH) will be within Red and Red+Green, therefore the expected average inventory level is Red + Green/2.
Red <= OH <= Red + Green
Average OH = Red + Green / 2
The NFP colors
In order to capture the different Net Flow Position compared to the Buffer Zones at a glance, we apply the following visual classification:
Blue: NFP > TOG (overstock)
Green: TOY < NFP <= TOG (good, nothing to do)
Yellow: TOR < NFP <= TOY (good, take action and release new replenishment orders)
Red: 0 < NFP <= TOR (we are in the safety zone)
Dark Red: NFP <= 0 (possible shortage)
That’s all I wanted to borrow from DDMRP: it’s just the tip of the iceberg, nevertheless here is the overview of the Net Flow Position by ItemCode and by Depo; it is a visual situation that can drive the replenishment management actions.
The Modular Distribution Planning in Excel
The companion system that comes with this article is organized in modules and folders.
Download the ZIP file and extract it in C:\.
You should have the C:\P-S_ModularDP folder and in it you should find the following folders:
- Central
- Data
- East
- North
- NorthEast
- NorthWest
- Report
- South
- SouthEast
- SouthWest
- Suppliers
- West
Central, East, North, NorthEast, NorthWest, South, SouthEast, SouthWest and West contain a Data subfolder and the module for that depo.
The Data folder hosts the common data, Report is used to insulate the reporting module and its data, Suppliers is where Central places the external suppliers orders.
In C:\P-S_ModularDP you find also the following Excel files:
- DevTemplate_Professional_v4.2.2.3.xlsm
- MDP_DataInterface.xlsm
- MDP_Master.xlsm
- MDP_RandomDataGen.xlsm
If you want to use your own data, you need to:
- place your data in the C:\P-S_ModularDP\Data folder
- uncheck the Random Data Generation checkbox in MDP_Master.xlsm Menu worksheet
The Master module is the one that is running everything: go to the Menu worksheet and press the “Update from local” button.
This will run:
- the random data generation module, which by the way can be excluded by un ticking the checkbox above the button: this generates a random dataset, it emulates the downloading of a data set from a mainframe system;
- the data interface module: this takes the data previously generated, format them as needed and moves them in the correct positions; this is a flexibility module: you can use the same system with different input dataset formats if you standardize them as output of this module
- the Central module: this is the warehouse (Depo) at the top of the distribution and runs East, North, South and West depo module; North and South run NorthEast, NorthWest and SouthEast, SouthWest depo modules respectively; each depo module receives as input Items, the Network description, the orders from other depos, the inventory, the already issued replenishment orders and the historical demand; they calculate the ADU, the buffer zones, the NFP and generate the replenishment orders plus the report.
- the Report module, which show an overview of the Net Flow Positions by article and by depo plus the details replenishment calculations of each depo; it receives data from the depo modules and show them nicely.
Here is a flow diagram that should help to get the processing logic.
And here is the process flow of a node/depot.
Some tech tips
This section is dedicated to the ones keen to FEDT magics.
I benefitted from a few not so known feature of the FEDT.
The button named “Update Text File location” is useful when you change the location of the text files: it changes the queries in Query modules if it finds the file in the new location, it does nothing if the file is not there.
Kien Leong is working on an improved version of this feature and it will be included in version 4.3 of the Development Template.
I built some in the automation statements in row 6 as formulas: it works nicely, mind that their values are interpreted only when the automation is activated and afterward that value is frozen into the automation macro.
I used formulas in the ModuleList: the values are interpreted every time the module runs. How cool is that?
Also in ModuleList, if the length of the workbook name is 0 or it is error, the module moves to the next workbook with no error.
I used the NEXT statement, which enables branching and recursion: this is an advanced topic, check Material-Constrained Production Scheduling Tool – with a New Fast Excel Development Template Feature out for a recursion example built by NEXT.
As the output automation statements TXT_Int, TXT_Loc, CSV_Int, CSV_loc can be used several times in the same worksheets, there are some worksheets that generate 2 or more text files (up to 36).
Text file stacking: I used the FEDT well known Query and Stack templates in the depo modules, but in the Report module, where I used the Excel Power Query features; also in the same Report module there is an interesting filtering technique to get one depo data each worksheet: you find it in the depo reports.
Here is a picture of a ModuleList worksheet.
Next Steps
Subscribe and download the example for free.
Play with the system, change the dataset.
Learn by this example how to benefit from modularity.
Browse the P-S website: it is a gold mine of information.
Thoughts for you
What if instead of distribution network we had a number of workshops?
Do you think that the FEDT can support a demand-driven planning and scheduling system for the above?
Tips: modularity, zero coding automation.
Summary
You learned how to use modularity and the FEDT by a practical example.
You learned how to plan a distribution network in a Demand Driven logic too.
It’s time to take action and practice: this is the only way to learn and benefit from our methodology and tools.
If you need help, you are very welcome at Production-Scheduling: we are a team of consultants and we offer from the simple free tip to the structured consultancy project, FEDT-based system buildings included.