Finite scheduling calculates the begin and end of each job to be done in production. It considers standard times and quantity for the workload and availability of the workcenter by a calendar for the capacity.
In the standard Fast Excel Development Methodology this requires smart and complicated formulas to deal with dates and time. These formulas are always required, even with the simplest form of finite scheduling: a single-pass logic with push-only . Of course you can copy and adapt the formulas from other examples and let the Fast Excel Development Template do the automation and the VBA coding for you.
20+ years ago on this site, our founder Tony Rice wrote a detailed tutorial on how to do scheduling in Excel, also known as “Tony’s Bible”. It is still our reference when it comes to scheduling and it explains a number of different scheduling cases, like 3-pass scheduling and multiple workcenter operations. I include it to the bundle you can download; I strongly recommend you to take the time to go through it: it is gold.
My most recent past articles were about demonstrating how far you can go with our no code Fast Excel Development Template, therefore I analyzed how to build rather complex systems.
This article is about making finite capacity scheduling simpler by an algorithm based on cumulation and sorting, and by some Power Query use. And as always there is a companion tool that implements the logic of the article in a fully automated tool. To download it, fill in the form at the bottom of this article. But, as always, it will make much more sense if you take 13.5 minutes to read about it first.
The Use-Case and Limitations of Push Logic in Finite Scheduling
On first glance, the concept of finite scheduling seems simple. I have a number of jobs to do. These jobs require workcenters to do the work. The workcenters have a limited number of resources and each resource can only do one thing at once. The workcenters can only do work when they are available on-shift and not when the factory is shut. A job has a start-date and time. The job gets set-up and run. When the job is finished, the next one can start. So far, so good.
You can choose “push” (or “forward”) scheduling where you start now and then calculate when you will finish. Or you can do “pull” (“backwards”) scheduling where you finish before the due-date and calculate when you will start.
Now we have defined some terms, I would like to talk about a limitation of “push” scheduling.
And as we always have the spirit of “Show, not just Tell” there is a companion tool that implements the logic of the article in a fully automated tool.
There are several use-cases in which the requirement is simple: A routing with only one workcenter, and a push schedule starting from a defined start date and time.
Let’s go a bit deeper into some constraints of this new technique I am showing you here.
In this case, we are scheduling a single workcenter for each family of jobs. It doesn’t work with a sequence of interconnected operations requiring multiple workcenters. You can have work done in parallel by using different workcenters for different parts and process families. But you can’t have work done in series by using different workcenters for the same part.
Second, a push schedule is fine with you, you have a list of jobs to be done and you want to know when they will be completed. the jobs are sorted by priority and due-date and you work on them until they are all complete. You can adjust the capacity to match the average customer demand rate.
While these constraints look quite tight, there are many real-world situations that require exactly this logic. Consider the following two use cases.
First example, you need to give feedback to your customers’ requests on the availability date of their orders and you know that there is a specific resource that is determining the delivery date.
Second example, you are scheduling a workflow of a few manufacturing steps with a bottleneck determining the throughput pace: you can schedule by this technique the bottleneck and plan upstream and downstream of the scheduled work-center priorities and timing by applying lead-times.
Do the two cases above sound familiar? They do to me: as supply chain lead or consultant I had a good number of similar use-cases fitting into the constraints, whether I was applying Theory of Constraints, DDMRP or a lean workflow.
The Simplified Scheduling algorithm explained
With the introduction out of the way, then what is the technique?
You can meet the workload (measured in hours) when the workcenter capacity (also measured in hours) is equal or greater than it.
This works in a cumulated way. If a job workload is 24 hours and its work-center runs 8 hours per day, it will take three days from now to complete it, in other words the workcenter completes the job when its cumulated capacity reaches the workload.
If the next job workload in sequence is 16 hours it will take up to the fifth day from now to complete it: the cumulated workload is 24+16=40, the workcenter cumulated capacity reaches 40 on the fifth day: 8 x 5 = 40. Till now the workload and the capacity in some points in time are matching perfectly.
Let’s consider a third job of 12 hours: we already know that it will be completed during the seventh working day because it is when the cumulated capacity will be greater than the workload. But when exactly? It will complete 4 hours before the end of the shift: this is the difference between the cumulated capacity, 8×7=56, and the cumulated workload, 24+16+12=52.
When we add a fourth 6 hours order, it will start 4 hours before the end of the seventh day and will end 6 hours before the end of the eighth day.
Are you nailing the logic?
We are scheduling using cumulated hours for both the capacity and the workload.
The people already familiar with our methods know that at Production-Scheduling.com we use a smart technique based on cumulated quantities and sorting for determining the availability of materials by comparing demand and supply. In the case of scheduling the workload is hours demand, the capacity is a hours supply, the equivalent of the item we analyze in materials availability is the workcenter.
Here is the example above.
First, we have a list of jobs, the workload. and a calendar, the available capacity.
Second, we stack workload and capacity, and calculate separately for each of them the cumulated hours.
Third, we sort by “Cumulate” in ascending order and by Type in descending order.
Why the Type descending sorting?
Because when the workload and capacity cumulated hours match exactly we will have the capacity row just below the workload row it serves.
The workload of a job is the sum of the setup time, generally dependent from the previous job and of the quantity times the unit time.
The capacity of a workcenter is determined by its calendar, which is a list of time buckets.
A quick but important reminder: Excel expresses time in days and fractions of days, examples 1 hour = 1/24 days, 1 minute is 1/1440 days.
Practically, we cumulate by work-center the workload generated by each job and the capacity available according to an availability calendar separately and then we merge them by sorting the cumulations in ascending order. When the cumulated capacity equals or overtakes the cumulated workload with a job as the last added to the cumulation, that job is completed, the difference within the two cumulated quantities gives the anticipation in the time bucket.
And what about the start time of a job? This is a push schedule: a job starts when the previous one ends.
There is an exception: the very first job on the work-center does no have a preceding job, it will start at the beginning of the work-center capacity horizon, typically from today or from the next shift.
Why do I say this is a simplified alternative to our standard push scheduling algorithm?
First, it does not require a number of combined =MATCH() formulas do determine start and stop of each job: it does it by cumulating and sorting.
Second, it does not require to navigate through calendars with date and time calculations: the calendar is used only to generate the work-center capacity.
Third, it is simple to nail the underlying concept.
The Companion Workbook: Capacity Planning and Sales Orders Finite Capacity Planning
I prepared for you a tool with three main feature:
- capacity planning: workload vs. available capacity analysis;
- sales orders availabity dates based on finite capacity;
- workcenters finite capacity scheduling.
I bundled with the tool and its dataset:
- the Fast Excel Development Template version 4.3.9 I used for this build;
- the Scheduling_Excel.xls file by Tony Rice, a precious source of information about scheduling by spreadsheets I mentioned at the top of the article.
You can download the tool by filling the following form: the download link will be sent quickly to the email address you specify. No spam emails, promised.
New Algorithm For Finite Scheduling 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 folder: C:\P-S_SO_Scheduling and C:\P-S_SO_Scheduling\Data
- Open the tool: P-S_SO_Scheduling _v1.xlsm
- Press the “Update from Local” button
That’s it. It will run with fictitious 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_SO_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.
I built the companion tool by the FEDT 4.3.9, the very last released.
It includes 6 templates and a set of smart macros and functions working behind the scenes to make your development easy, fast, and reliable:
- Parameter template, is where you manage user defined data and export them;
- Query template, dedicated to importing data, but in conjunction with Power Query also for merging/exploding tables or to stack them;
- Table template, this is the king of calculations;
- Pivot template, mainly dedicated to reporting but also to summarizing data;
- Stack template, to stack tables in the FEDT traditional way;
- ModuleList template, which enables the orchestration of different modules/workbooks.
There is also a very useful worksheet, named QuickReference, where you find hints about the Fast Excel Development Template features.
If you want to deep dive into the power of the FEDT for building fully integrated, modular and automated supply chain systems, I strongly recommend checking the webinar series hosted by Kien Leong out, partner at Production-scheduling.com and good friend of mine.
In this example I considered two families of products and two workcenters each one dedicated to one in the two families.
The first family comprises 8 products (1A, 1B, 1C, 1D, 1E, 1F, 1G, 1H) produced on the work-center WC1, the second family has 4 products (2U, 2X, 2Y, 2Z) produced on WC2. Yes I know, very little imagination indeed, bear with me.
Input data:
- Sales orders;
- Change over: set up time to switch a work-center from an item to another one;
- Routing: work-center and unit run time each item;
- Calendar: workcenters availability over the time;
The sales orders are linked to the routings and their sequence to the changeovers: this generates the workoload.
The work-center calendar gives the available capacity taking into account the start of scheduling for each in the two workcenters.
The SalesOrders worksheet allows you to change the order sequence. Do it first and then click on the Output button: this will generate a text file that will be imported at full system run time.
Changing the sequence is affecting the planning in two ways:
It changes the setup time because of the change-overs
It changes the order in which the available capacity is consumed to cover the sales orders, in other words which order is served first.
Similarly, on the Calendar worksheet you can change the availability of the two workcenters by changing their calendars and then clicking on the Output button. In the example the workcenter WC1 in working 5 days per week in two 7 hours shifts, WC2 is working a 10 hours/day shift.
To run the system, go to the Menu worksheet and press the Update from local button.
Here is a graphical summary of the process flow.
Output reports:
- Completion date each Sales Order in the form of a due date vs. actual scheduled date report;
- Workload vs. Capacity report per week each work-center, showing also the setup time;
- Work-center Schedule by day and with no conditional formatting for lightness sake.
For people keen to the technical details, in this build I explored the Power Query potential benefits.
Power Query is available since Excel 2010 as a free add-on and since Excel 2016 integrated as standard tool. At first glance the Power Query editor is overwhelming and introducing you to Power Query is beyond the scope of this article.
However, with a very limited knowledge of it you can import data from several different sources, you can sort, filter, combine, merge tables, calculate columns and even ask Power Query to guess what calculations you want in column based on examples. The column Key in the ChangeOver worksheet is an example of this latest feature.
Is it worth to spend some time learning Power Query in the context of the Fast Excel Development Method? In my opinion yes, because it reduces the need of data processing done by formulas and therefore simplifies and speeds up the development. As additional benefits, the table size you can handle is not limited by the one million rows of the Excel worksheet and on large tables you will not need to have hundreds of live formula cells recalculated, with advantages in terms of processing time.
In this example I used Power Query for importing the data and to calculate the workload, the WorkLoad worksheet technically needs to be a Query template because it runs a query, however it does calculations we usually do by Table templates and several formulas in row 8.
Here is why I used Power Query in this example.
Power Query is flexible when importing data: I had txt files, but Power Query is ready to import for the most used databases, from the web, from pdf files and much more
Power Query makes easy to join tables – we call this “explosion” in FEDT lingo : as an example, the WorkLoad is the result of joining sales orders, ChangeOvers and Routing; I avoided to write 16 formulas by a few clicks, and I could have avoided to load in two worksheets the Changeovers and Routing txt files – I loaded them for clarity sake.
Power Query simplifies the development of a system in general: the Power Query editor allows you to join, sort and stack tables in few clicks, without writing any formula.
Power Query helps in reducing the processing time: its engine is designed for table operations, while the traditional FEDT uses formulas, of general use and cpu intensive when recalculated; of course this is not perceivable on small datasets like the one of the example.
Power Query is (partially) self documenting the relations within the tables and the external data sources: this is useful in case of complex table relationship. See below the documenation of our companion workbook.
The StackWorkLoadCapacity worksheet, which I built in the standard FEDT way to ease the understanding of people already acquainted to material availability calculations, is the one where the magic happens: it stacks workload and capacity, calculates the cumulations by work-center and type of data (capacity / workload), sorts and determines the shift, or time bucket if you wish, in which each sales order will be completed, and calculates the exact end and start of each sales order/job.
Important note: the availability cumulation starts from the date set in PARA_SchedulingStart in the PARA worksheet, in the example I set it to a fixed date to keep the full dataset consistent, in a real use case this should be manually updated each planning run or set to =TODAY() or =NOW().
Conclusion
This example is basic but shows the full power of the scheduling algorithm simplicity and brings to you sales orders dating, capacity planning and workcenters scheduling.
You learned a simple finite capacity schedule algorithm, you can discover how I benefitted from PowerQuery from the workbook.
With limited effort, this tool can be transformed in a module to be run as a component of a more complex system where the jobs to be scheduled are calculated in an MRP, a Min Max inventory level manager or a DDMRP decoupled system module.
Your next steps:
Run the tool with the dataset I prepared for you
Try it with your data
Modify it to match your specific “scheduling” requirements
Do you need help?
For training check the courseware bundles on Production-scheduling.com
For consultancy and FEDT based system development, get in touch with us.