“If this is a template, why are most of the sheets blank?”
“What is a modular system? How will this help my planning?”
We will answer these questions, and more.
We have a new version 4.2.0 of the Fast Excel Development Template. This article will show you the new features and how they work. For a more detailed tutorial on the Automation and No-Code features of the template, see this article:
For a in-depth training of the full Development Template please see our Fast Excel Development Template Fundamentals Course.
For a discussion of modular systems and why you might want to use them, please see this recent article:
Both of these versions were developed by Gabriele Tettamanzi who is a partner with Production-Scheduling.com. There is a lot of smart problem-solving in this template including developing an entire no-code system. He has mastered VBA programming so you don’t have to. I know VBA and I find the no-code features to be hugely powerful and they have speeded up the development process considerably. Bravissimo, Un lavoro ben fatto, Gabriele!
A Development Template is not a Planning Template
“If this is a template, why are most of the sheets blank?”
First, let’s say a few words about templates. If you are familiar with the Fast Excel Development Template you can skip this bit. If you came searching for template to help with your planning, you might have expected it to have a lot of content. Cells to put your products, demand, etc. I understand this.
What you seek is a Planning Template. We don’t do much in the way of planning templates. Here’s why. Planning templates have a lot of logic built in and makes a lot of assumptions about your processes. Both the operational processes and the planning process. We believe it is better to have a planning tool that is designed to fit the business, rather than trying to fit the business around someone else’s planning process. This article shows an example of a material planning template, contrasts with a development template and argues this latter is better.
This Fast Excel Development Template can be used to build any kind of planning tool. And will allow you to use it to do planning in the way it should be done. Yes, it will take a bit more time to learn. But let’s face it: if you could download an Excel file, throw some data into it and then suddenly attain planning excellence, then everyone would be doing it and planning would be a problem long solved.
Companies spend millions on software that is meant to do planning, and yet they still don’t get effective planning. They still go looking for a planning tool that works for them. This solution is unlikely to be found in a free Excel file and a few minutes’ work. The solution can be found if we can combine your process knowledge with our planning skills and Excel development chops.
You, as an operations or supply chain professional, will know the process that needs to be followed. What you might not have is the programming skills. This matters not, as we can help. The Fast Excel Development Template is free. The learning about how to use it will cost you time. And maybe some training.
We have lots of free educational content on this site, so many people don’t pay for anything. And we are fine with that. Nevertheless, you should expect to invest some of your time. I am highly confident that it will be an investment that pays off, big-time.
Fast Excel Development Template Version 4.2.0 – New Features
Here is a summary of the major new features. We will then take a section to explain each below:
- Develop Modular Systems Using the New ModuleList Template. This template sheet allows you to list a number of other workbooks that perform a planning function. This template sheet will then open and run them automatically one by one.
- New, more flexible text file functions. Now you can use the Row 6 commands to output text files to:
- The default location (usually C:\FolderName\Data) with filename matching the sheet
- A specified location and filename
- A table range from the source sheet other than the “SheetName_Table2”
The ModuleList Template
The example shown above is a simple planning system that has the following functionality:
- Data Interface – Maps and transforms data from the host ERP system. Creates inputs for all below.
- Demand Planning – Takes the forecast and sales orders and calculates a consolidated demand.
- Material Planning – Takes consolidated demand, inventory BOM and supply orders and calculates net requirements.
- Capacity Planning – Takes production requirements and calculates the load and available capacity for each week.
- Production Scheduling – Takes production requirements and generates a finite schedule.
- Reports – The user interface for all of the above outputs.
The sheet “Module_Flow” is built with the ModuleList template. It will open, run and close each of the modules. The modules output text files and this is the data that links them all together. The Reports book is kept open and that is the one that the users will view.
Why don’t we put all of the function into one excel workbook? This article answers in detail, but here is a summary of the benefits:
- Defining a flow and the inputs and outputs to each step means that the whole system has much better control.
- You can build re-usable modules and snap them together into a system like lego.
- Each module gets a shorter quality feedback cycle and will improve faster.
- The system is visible and easier to understand.
- It can better handle the complexity of modern enterprise systems.
How To Use the ModuleList Template
- Build your modules using the Fast Excel Development Template.
- Select the ModuleList_template and hit Ctrl-Shift-O to copy the sheet. Rename it.
- List your modules, in order that you wish to run, including the file extension .xlsm.
- Specify the macro to run in each module. If you used the Automation features then the macro will be “RunAllAuto”. Left blank the template will default to this macro.
- Specify the filepath where the module can be found. Recommend this is a local folder (or cloud folder like OneDrive) not a network drive.
- Choose whether to keep the file open after it has run. If it is already open it will remain so. If it is closed, it will run and then close again without saving.
- Go to the Menu sheet, scroll down and hit the “Switch Automation On” button
- The module list will run whenever you use the “Update from Local” button (or the RunAllAuto) in the workbook that has the module list.
the Text File Output functions
The template versions from v4.0 onwards have the ability to put certain commands in row 6 and then use them to automatically generate macros that will follow these instructions. The above picture is a Stack template with a series of Sorting commands and then the TXT_Int. This latter will output a text file .txt using tab delimiter, CSV with a comma delimiter and international date and number formats.
These commands now have a single term e.g. “TXT_Int” and two optional parameters, separated by spaces. If there is one optional parameter for instance, “TXT_Int C:\OtherFolder\data\Filename.txt”, then that will specify the file name and location. If there are two parameters, e.g. “TXT_Int Table2 Filename.txt” then this will specify the table range to be outputted and the filename.
The details of how to use these commands are explained in the QuickReference sheet. However, here are some examples:
|Will send a tab-delimited text file to the PARA_FilePath_Data_Local location (as set in the PARA sheet) with the filename equal to the sheet name
|Will send a tab-delimited text file Filename.txt to the location “C:\OtherFolder\data”.
|TXT_Int Table3 Filename.txt
|Will copy the range SheetName_Table3 and then send it in a tab-delimited text file Filename.txt and then PARA_FilePath_Data_Local folder
|TXT_Int ‘C:\Folder with spaces\Filename.txt’
|Will send a tab-delimited text file Filename.txt to the location “C:\Folder with spaces”. Note the single inverted comma ‘ that is used for filepaths or filenames with spaces.
This same logic works for CSV_Int (CSV file, international settings) , TXT_Loc (.txt file and local regional settings) and CSV_Loc (CSV file and local regional settings)
This article intends to show you the new features of the Fast Excel Development Template version 4.2.0. It supports modular systems development and can enable you to quickly build powerful planning systems in Excel. The template is free and will always be. There is no hidden charge. If you would like help with using the template or building a system then please get in touch.
Here is a webinar on 1-November-2022 where we will go through these functions in detail with a working demo system.
Any questions or comments, please use the comments below. Happy developing!