A fully automated material planning, capacity planning and finite scheduling tool built with ZERO coding in Excel.
The disruption of the global supply chains due to recent events makes production scheduling tasks quite challenging: prior to 2019, the availability of purchased items was taken for granted in many businesses, therefore no special care was dedicated to it when scheduling production.
Fast forward to 2022, many business sectors are suffering shortages: material availability is a critical constraint.
Furthermore, the majority of planning systems fail to consider the impact of supply delays or cancelled orders: in this case you can use the Fast Excel Development Template to build your system instead. Here is a fully-integrated material-constrained and production scheduling system. It can work as a pilot system or as a robust software tool.
I prepared for you a production scheduling tool that is also constrained by the materials availability. Both on-hand stock and also inbound supply that takes into account purchase orders and lead times. I used the Fast Excel Development Template and the Fast Excel Development Methodology.
If a purchase order is postponed it affects immediately the schedule as well as the lead time.
I considered a case where finished products have a three-level bill of materials, with purchased components, a lead time based operation and five final scheduled operations. We will take into account: Sales and Purchase Orders, procurement and internal lead times, inventory on-hand, work-in-progress for the scheduled operations, work centers and their working calendars.
We need to perform material planning first to determine the quantities to make, the quantities to purchase and from the latter, the material availability constraints.
Then, before moving to scheduling, some capacity planning is needed: if capacity and workload are not balanced, the consequent long delivery times could push away customers.
Finally, there’s a multi-operation finite scheduling.
I built this tool from scratch in less than 30 hours thanks to the Fast Excel Development Template.
Attached to this article there’s a fully automated tool based on the fictitious cycle manufacturer P_S Cycles Ltd. data set.
It bundles the working tool, a data set and the Fast Excel Development Template I used. In addition to that there’s an old and precious document: Tony Rice, the P_S founder, collected the principles of Scheduling by Spreadsheets and wrote this gem. My first steps on the Fast Excel Development Template were 20+ years ago using it; by the way, this makes me the second oldest Fast Excel Development Methodology user in the Production-scheduling team.
To download the complete system, dataset and template that was used to build it complete this form and we will email you the download link.
Material-Constrained Scheduling Tool Download
Take some time to follow the steps in getting it running on your computer.
- Use the form above to access the download.
- Download the ZIP folder and unzip directly to your C Drive.
- You should have the following folders: C:\P-S_MaterialConstrained_Scheduling and C:\P-S_MaterialConstrained_Scheduling/Data
- Open the tool: PS_MaterialConstrained_Scheduling_v01.xlsm
- Press the “Update from Local” button
- That’s it. It will run with PS Cycles data. Use this dataset to learn about the tool. When you are ready..
- To run with your own data, replace the input files in the location: C:\P-S_MaterialConstrained_Scheduling/Data
- All columns must be the same and data must be consistent and match up.
And remember: no human wrote VBA code during the production of this fully automated piece of software.
NEXT statement, the new FEDT feature enabling looping and branching.
Before deep diving into the details of the algorithm and the tool, let me recap what is the Fast Excel Development, completed by the announcement of a new feature I’m particularly proud of.
MRP, CRP and Finite Capacity Scheduling are fairly complex algorithms, if you ask to IT people, they will come back with days and weeks 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. Or you can buy a specific piece of software: license and consultancy for such a project make it significantly expensive (capital expenditure range).
Well, it is not complicated at all for us at Production-scheduling and its community 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.
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 your spreadsheet – keep it limited as much as you can and export/backup it, there’s an output button that exports the data as text file, which is reimported at run time
- 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.
For those who are very into security: the FEDT VBA code is open source, hence you can inspect it and if you like you can modify it.
When I submitted my last article to my friends and partners at Production-scheduling, our founder and my FEDT sensei Tony Rice suggested a brilliant algorithm that was requiring nonlinear elaboration: till very recent days the FEDT automation tool was working in a linear way from left to right, sheet after sheet with no looping or branching capability. I answered to Tony that for the time being I was keeping my simpler and automatable algorithm but I accepted the midterm challenge to make branching and looping available on the FEDT automation system.
After a few months I was talking about the good old times of Assembly programming with a colleague of mine: something clicked into my mind and in a few hours I coded the new feature, based on the keyword NEXT set in row 6, where all the automation keywords are set.
Now the FEDT automation system is able to branch and loop.
A few words about NEXT: it enables the control of the calculation workflow with branches and loops. In other words, it is possible to control what will be the next processed sheet by the result of a formula, the next sheet can be to the right (forward jump) or to the left (backward jump) of the current one.
When a NEXT statement is processed, the macro automation processor creates a named range <SheetName>_Next in row 5 above the Next statement. It accepts a number of sheet names plus the special string TheEnd as parameters: the lattest enable to go to…The End and exit the elaboration.
The value in <SheetName>_Next must be one in the parameters passed to Next, an error message box is displayed if it’s not. The next sheet processed is the value of <SheetName>_Next.
<SheetName>_Next typically will contain a formula returning one in the possible next sheets.
Only one NEXT statement is accepted each sheet.
Prv_Sht, a named range not referring to cells, is used to keep track of the previous sheet elaborated: it can be used to set up formulas depending from which was the previous sheet.
Important: a misusage of the NEXT statement could generate infinite loops, if correctly used enables branches and loops.
With the announcement out of the way, let’s deep dive into the workflow.
The process flow logic
We need the following steps:
- Product BOM generation, from the Bill of Material for each distinct item in the orders book
- Material Requirements Planning (MRP) calculations, in other words explosion of the material requirements
- Materials Availability and date constraints
- Capacity Requirements Planning (CRP): before scheduling, we need to know how the workload fits (or does not fit) in the capacity profile
- Finite Scheduling: three pass scheduling, push ahead if there’s no capacity, pull from the due date if the workload is compatible with the capacity, start only if the materials are available
We need to feed the calculations with the following data:
- Sales orders (or Forecast)
- Bill of Materials (Parent-Child-Qty per)
- Inventory (On hand, total for each item)
- Items (basic pieces of information each item e.g. Lead time)
- Purchase Orders
- Work centers (Characteristics of the work centers)
- Calendars (working hours / availability patterns)
- Routing (sequence of operations to be performed to make an item from its components)
- Work in Progress (operation already started status)
And from the calculations we will obtain three reports:
Material constraints: when the purchased materials will be available for each sales order
Workload per work center: a chart that shows the saturation of the work centers capacity over the time
Scheduling Gantt: the well know chart
A number of other useful reports can be built from the data calculated: if you wish, that’s an assignment for you.
Product BOM
The typical ERP Bill of Material has the basic structure father – son – quantity per; however to perform a MRP we need to explode the independent demand (sales orders in our example) through the semi-finished products down to the purchased components and in order to do that, we need a Product Bill of Material, which looks like product – parent – child – qty per.
In my previous article on advanced inventory analysis, I built the Product BOM by a linear 9 levels explosion, no more levels and not less can be processed that way.
Should I have to copy it? Well, now I have the NEXT statement and I used it: the Product BOM is generated by looping back and forth within two sheets, one for even levels and one for the odd levels of the BOM, it automatically stops when there’s no more level to explode and virtually it can explode an unlimited number of levels. For peace of mind, I set a 20 levels limit in order to avoid infinite looping due to inconsistent data, you can remove or modify it by changing it in the PARA worksheet.
A Product BOM is generated each distinct item in the Sales orders book, in order to optimize the calculations.
Material Requirements Planning
We use the Product BOM, the Sales orders and the Inventory on hand.
Let me keep it simple: the independent demand is propagated through the Product BOM for each sales order, taking into account the Inventory on hand, thus calculating the net demand, which becomes the gross demand for the following level.
In other words, 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.
This elaboration generates a list of items and quantities to be either purchased or produced, for each sales order.
Materials Availability and date constraints
Let’s consider the list of requirements of purchased items from the MRP step.
The quantities on hand have been used by the MRP, therefore we have the list of the parts we cannot withdraw from the warehouse.
On the other hand, we have a list of released purchase orders and we know the lead time to source each item.
A specific material will be available either when a purchase order of it will be delivered or after the lead time (assuming that a suitable purchase order is released timely).
To calculate the availability date, we use a smart technique: we first separately cumulate each item purchase orders sorted by ascending delivery date and each item requirements sorted by sales order priority, secondly by item we sort the cumulate quantities, mixing requirements and supplies. This way we discover which PO delivery is covering which requirement, and therefore when the requirement will be served, and if there’s no PO coverage, we know that the lead time will determine the availability date.
Play the explanation clip below: it explains beautifully the logic of this availability trick. By the way, it’s a presentation from a webinar hosted by Kien Leong.
We know when the production can start, of course because we schedule only the finished products, if there’s an internal step before the finished item, we add its lead time.
Perfect: we have the material availability constraint.
Capacity Requirements Planning
In order to evaluate the workload and tune the capacity of the work centers, we explode the list of the items to make through the routing each item and calculate the workload each work center. Regarding the time phasing, we start from the due date of each sales order by the lead time and within the operations of one item +by the time offset included in the routings.
To calculate the capacity over the time, we benefit from the calendars each work centers to create a list of dated available capacity each work center.
By stacking capacity and workload, we can build a chart that shows the saturation of the capacity.
Why do we need this tool? Well, the finite capacity scheduling will level perfectly the workload inside the availability profile over the time, however:
- peaks of workload generate delivery delays
- excess of capacity could become money wasting
Once we have managed the capacity to the best, we can move to finite scheduling.
Three pass scheduling through multiple work centers constrained by purchase items availability
What’s three pass scheduling?
We first schedule ahead starting from today: the first job starts immediately, the second just after the first and so on. The jobs are pushed through the production as soon as possible. This is called push1
As second step we schedule backward from the due date and therefore we can have a job start in the past. The jobs are pulled by their due date. We call it pull.
The third step is the wise one: it starts today, it pushes if there’s limited capacity and it pulls if the capacity available allows to meet the due dates. We call it push2.
Here below you find an example from Tony Rice’s Scheduling Excel.
And which role does our list of material availability constraint play? It acts in the push1 phase: a job cannot start before the latest in its materials is available, therefore the push2 phase will push ahead from the availability date if the order if the pull start date is before.
Now the multiple work center logic: when pushing the next job operation cannot start before the previous one is completed, when pulling starting from the due date and going backward, the end of one operation cannot happen before the start of the previous one.
Let’s put it all together and we have three pass schedule logic through multiple work centers constrained by the availability of purchased items.
On top of all this, I considered also the work in progress, WIP in short: some parts and operations of one specific order could be already completed, in other words we can have some orders ongoing: in order to take into account this I made two assumptions. The first assumption, each order moves to the next operation only when completed, therefore if I find parts in operation 3 I assume that there will be nothing left dodo for that job on operation 1 and 2 and the second one, the materials are withdrawn from the inventory only when the order is completed.
The companion files bundle
Material-Constrained Scheduling Tool Download
With a free registration above you will receive a link for downloading a bundle containing:
- A folder with the data used in this example
- The planning and scheduling tool described above
- A true gem: the scheduling by spreadsheet manual written by our founder Tony Rice – by the way it’s the document from which I started my endless Fast Excel Development story 20+ years ago –at Production-scheduling we consider it such a milestone that we refer to it as “The Tony’s Bible”
- The last release of the professional Fast Excel Development Template, the one I started from
If you want to run the tool, you have to un-compress the zip folder under the root of your c: drive.
The first time you open the workbook a warning could come along: enable what you’re asked for.
On the worksheet named Menu, the first to the left, there’s a button named “Update from local”: by clicking on it you run the full elaboration.
What can you do now?
- Experiment with the tool
- Run the tool with your data
- Re build it from scratch by the FEDT
- Improve/adapt the tool to fit your needs
- Study the Tony Rice’s doc
- Experiment the FEDT – read my article about the basic FEDT 4.0 features, also the Quick Reference spreadsheet include into the FEDT is helpful.
As a 20+ years Fast Excel Development user, and recently developer, if I may, here is a tip for you: practice to master better and better the Fast Excel Development Methodology, as the more you use it the more you learn.