Planning systems are suffering from ageing issues: they may have been the perfect match with the business when they were installed. But the business changes over the time and modifying the system is complex and expensive. When the planners start answering “The system is doing it, it’s not my fault” it is time for a check up of your planning system.
In the recent past I worked with a supplier with a poor delivery performance, yet carrying high inventory levels for finished-goods. The planners were saying “It’s the system, not me!”.
I built a tool, based on the Fast Excel Development Template, to compare their actual planning with the nominal demand requirements in terms of materials and capacity. This article and its companion tool are inspired to that experience. To download the tool, fill in the form at the bottom of this article. But, as always, it will make much more sense if you take 12.4 minutes to read about it first.
Benefitting from Power Query while building a system by the Fast Excel Development Template
In this article, I will explain how to check the results of a planning system. I offer you a companion tool you can use and adapt to your needs.
But there is more. I’m also sharing how Power Query and the Fast Excel Development Template work nicely together. Since the introduction of the Fast Excel Development Template automation back in 2020, no VBA coding is required to build a completely automated Excel tool, now with the integration of Power Query the number of formulas to be used is dramatically reduced.
Power Query was firstly added to Excel in 2013 as an Add In, it is available for Excel 2010 and following versions, it can connect to a number of different data sources, it can handle very large tables (bypassing the ~1 million records limit of an Excel worksheet), it has a friendly user interface where you can complete in an easy way a number of data manipulations. Behind the scenes, the graphical user interface is powered by the M language, a powerful data processing language. Of course, the M language is accessible to the users keen to coding. The web is full of useful Power Query/M language resources, therefore the AI assistants are able to help effectively.
The Fast Excel Development Template is well known to the aficionados of Production Scheduling: six templates, Parameter, Query, Table, Stack, Pivot, ModuleList and a number of powerful features that make possible to create a a fully automated Excel based system with no VBA coding as the automation is writing the code on your behalf.
Nevertheless, when it comes to building a system, a lot of repetitive formula work is still required when exploding or stacking tables. The good news is that you can reduce that amount of work by Power Query. Of course there is a learning curve to face.
Planning System Check Up: the Logic and the Process Flow
To check an existing planning system, assuming that the data base is correct, I mean inventory levels, bill of material, routing, lead time, we need to:
- evaluate the inventory coverage against the actual demand exploded through the bill of materials
- compare the production orders and the purchasing orders with the theoretical demand from the independent requirements (sales orders in this example)
- execute a rough cut capacity planning to analyze how the capacity is used:
In this example, purchasing and production orders are supposed to be generated by the ERP planning system and exported in one single txt file.
For simplicity sake, the production order components are supposed to be the same listed in the bill of materials with no preemptive inventory allocation or partial consumption. This means that the calculation of the production order components consumption is a simple one level BOM explosion anticipated by the lead time.
The sales orders, inventory on hand, routing and bill of materials are similarly supposed to be exported from the ERP as txt files.
The holidays calendar, the work centers and the weekly shift patterns are parameters managed in the tool.
The analysis outputs are:
- evaluation of the inventory coverage
- availability generated by the production and purchasing orders
- capacity vs. nominal demand workload
- capacity vs. existing production orders workload
The calculation steps are:
- Product BOM generation by processing the BOM
- MRP, taking into account inventory, sales orders and lead time
- availability calculations, considering the net demand from the MRP, the supplies from purchase and production orders, the consumption from production order components
- Workload calculations, from routings, net demand and production orders
- Capacity calculation each work center based on the weekly shift patterns and the holidays calendar
- Workload vs. capacity comparison, both nominal from MRP net requirements and actual based on the production orders workload
About the availability calcs, the steps are illustrated by the images below: this technique was explained several times in our contents, I even posted how to use it to calculate a simple forward finite capacity scheduling.
Technical building details and tips
From a technical point of view, the tool is made by 5 modules, or workbooks if you prefer, as explained below.
- PlanCheckUppq_v4.xlsm – with the Work Center, Shift Patterns and Holidays data, – is launching in sequence the other four workbooks and showing the four final reports, Inventory Coverage, Availability, Capacity vs. demand Workload, Capacity vs. production orders workload.
- ProductBOMpq_v4.xlsm: this generates the Product BOM (Product-Parent-Component) from the traditional BOM (Parent-Component), it explores 20 BOM levels down from level 0; this is not a recursive Product BOM algorithm, however a M language recursive BOM processor is included in the download, the workbook ProductBOMpqRec
- MRPpq_v5.xlsm: MRP calculations with explosion in Power Query and net demand and lead time calculations in Excel
- Availabilitypq_v2.xlsm: it calculates the production order components consumption, it stacks the supplies and the demand and with the cumulative method calculate the availability date for each net demand from the MRP
- RCCPpq_v3.xlsm: rough capacity planning, meaning work centers availability from shift patterns and holidays, workloads from routings, MRP net demand and from production orders
The above modules are exchanging data by text files in the Data folder.
Here below is the ModuleList worksheet of PlanCheckUppq that launches the other four workbooks.
The key features of the Fast Excel Development Template I used are the Query, Parameter, Pivot and ModuleList worksheet templates and of course the Automation.
Recently we added the automatic synchronization within the PARA worksheet parameters and the Power Query parameters, available in this tool based on the FEDT 4.4.12: I used it to compose the Data path in Power Query – you find more more about this feature below.
Note that I didn’t use any Table and Stack template: in the traditional Fast Excel Development Template builds these two templates are doing the heavy duty calculations, in this build I did the explosions and the stackings by Power Query, without writing any formula. By the way, in the Power Query lingo “exploding” is named “merging” and “stacking” is named “appending”.
And what about Power Query? Here are some notes about how I used it.
To access the Power Query editor you can select the Data tab from the ribbon, then Get Data, Launch Power Query editor or press Alt+F12. Also Data and Query & Connections opens a pane on the right and you can go straight editing one in the queries listed by double clicking it or doing a number of action by the right click drop down menu, including accessing the Properties pane.
As already mentioned, Power Query is doing all the merging and appending needed.
But also the data fetching is done in Power Query. Also consider that I didn’t load any data on a worksheet unless it was needed for performing calculations easier to be done in Excel or to show reports or to export a txt file.
What are the calculations easier to be done in Excel?
Well, this boils down to the M language knowledge of the user: when it comes to data manipulation, M is by far more powerful than Excel.
For the time being, my thumb rule is to use Power Query as much as I can work on it by the graphical user inteface, plus a limited M tweaks for performance sake or for avoiding to return data to a worksheet and bring it back with additional simple calculations done by Excel formulas.
A practical example is the MRP module: I did the sales order / product BOM explosion by Power Query and the net demand calculations by Excel.
Let’s go through the most complicated M language calculations you find in the tool.
But first of all an important advice: DISABLE THE BACKGROUND REFRESH by unchecking the Enable Background Refresh in the query Properties, the last item in the right click dropdown menu. If you don’t do this the FEDT automation could malfunction and raise error messages.
Unpivoting the Shift Patterns: I made the parameter worksheet easy to be read and edited by a human, but that tabular format is not good for calculations. Power Query makes very easy to transform the original one by unpivoting the original one by Transform, Unpivot Columns in a normalized table ready for data processing. Here below the original table and the unpivoted one.
Stopping BOM explosion in ProductBOM calcs: this is the trickiest one, it counts the rows of the previous BOM level, if greater than 0 then calculate the next level else return the previous empty BOM level (which means no processing needed)
if Table.RowCount(PreviousLevel) > 0 then
<Do the explosion>else PreviousLevel
The ProductBOMpq is exploring 20 levels in sequence down from level 0. I included in the download bundle also a recursive Power Query version of it, ProductBOMpqRec, which is slower than the previous and its M code quite complicated – you can swap the two ProductBOM modules in the PlanCheckUp workbook or simply test them alone if you like.
We need to create a calendar from the Shift Patterns and the Holidays list, from the earliest to the latest date of the work center workloads.
Here is the how to.
First, create an empty query, give a sensible name to it – I used MinDate and MaxDate – and type
= Table.Min(RCCPWorkload, each [Date])
and
= Table.Max(RCCPWorkload, each [Date])
where RCCPWorkload is the table and Date is the column for which you want the minimum or the maximum.
Then right click on the field you want and choose Drill Down, [Date] in this case.
From now on that query name is returning an object which can be used like it was the returned value of a function, in my case the earliest and latest date.
Now to create the calendar dates list, again start with an empty query and type:
= {Number.From(MinDate)..Number.From(MaxDate)}
This is the M language to generate a list from MinDate to MaxDate. Note the curly brackets, used in M language for the lists.
We need also the day of the week: in Excel 1 is 1st January 1900, which was Sunday, 2 is Monday and so on, therefore the remaining of the integer division by 7 gives us the day of the week from Sunday to Friday and 0 for Saturday; however I want 1 to be Monday and Sunday to be 7:we can have this result if we subtract 1 to the date we are interested in and we substitute the 0 with a 7.
In M language this is:
=if Number.Mod([Date] -1, 7) = 0 then 7 else Number.Mod([Date]-1, 7)
I didn’t calculate the Monday of the week in Power Query in this build, but it could be useful to you:
=Number.IntegerDivide([Date] -2, 7)*7 + 2
For the people more inclined to coding: the M Language includes a number of functions for date manipulation, but I didn’t test them yet.
Some more useful tips below.
- PARA parameters and Power Query parameters: the FEDT automatically generates a twin Power Query parameter of every folderpath-like PARA parameter, i.e. containing “/” or “\”; also if there is a Power Query parameter with the same name of a PARA parameter the value of the former is automatically updated to the value of the latter.
- Parametric data sources in Power Query: when an external source is imported in Power Query, by clicking the cog beside “Source” it is possible by the Advanced tab to compose the path by a mix of parameters and strings – I used the PARA_FilePath_Data_Local parameter
and the text file name as shown below.
- Bringing an Excel table into Power Query: click anywhere in the table and on the Data menu click on From Table/Range; be aware that if the range is not an Excel table, the table will be automatically generated
- Loading a Power Query table to a Query Template: select cell A10, then right click on the connection and choose Load to => Table, Existing worksheet; warning: make yourself sure that the first column is NOT including null values, because this will break the Query Template
- Loading a Power Query table to a Pivot Template: remove the default pivot table that comes with the template, select the cell A10, then right click on the connection and choose Load to => Pivot Table report
- Power Query grouping and step renaming: it is possible to create groups in Power Query, they work as folders and are useful to club queries for readability sake, also you can add a description to each query (Properties) and rename each step of a query. I like to use the grouping, I prefer to read the M language when it comes to understanding what a query step does.
The tool: PlanCheckUp
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.
Your Planning System Needs a Check Up – 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_PlanCheckUp and C:\P-S_PlanCheckUp\Data
- The former folder contains the workbooks (modules) mentioned above + the FEDT 4.4.12, the latter the input text files + the text file exchanged by the modules
- Open the tool: PlanCheckUppq_v4.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_PlanCheckUp\Data
Note: you can reuse each module in your builds like they where Lego bricks.
Wrapping up
You learned how to check your planning system.
You how you can benefit from the integration within Power Query and the Fast Excel Development Template.
You downloaded a system that includes a Material Planning and a Rough Cut Capacity Planning tools by which you can diagnose your current planning system.
Your next steps:
- Run the tool with your data
- Experiment with Power Query
- Build your own tool
- Reuse the modules to build other tools of yours
And if you need help or you want to report a bug, to share your thoughts or give a feedback, just drop an email to [email protected]: your comments are very welcome.