How to Build A Business Grade Planning System with Spreadsheets with ZERO Coding and ZERO Cost for Software Licenses- Fast Excel Development Template 4.0

This post is also available in: Italian

The pandemic economy is making it impossible to justify software spend that is not related to smarter working. However there’s no reason to put your planning systems development on hold.

If you have Microsoft Excel on your computer, you can build your Excel based system with ZERO budget for software license costs and ZERO coding.

Are you in? Keep reading.

You can leverage the power of the productivity suite available on every desktop. Microsoft Excel in particular. And you do not need any coding skills.

We at Production-Scheduling.com provide tools and guidelines to efficiently build Excel-based reliable business systems.  Our method has proved itself to be successful on hundreds of Planning and Scheduling applications, but also with many other business functions, including budgeting, financial reporting and generic data processing.

Our methodology is called Fast Excel Development Method and it is supported by a template, the Fast Excel Development Template.

The Fast Excel Development Template has a long story, here is its evolution over the time:

  • 1990 Quattro Pro for DOS: key stroke macro recording for paste downs etc.
  • 2000 Excel, dynamic named ranges and recorded macro
  • 2007 Macro Maker to replace recorded macros
  • 2010 Introduction of sheet templates and associated functions automates named range creation and reduces the VBA coding required
  • 2020 Zero VBA coding with the automation of the macro creation

Live Webinar to Learn How To Use The New Template

Thursday 15 October
4pm UK Time (GMT+1)
5pm CET, 8:30pm IST
11am Eastern, 8am Pacific
Register here https://us02web.zoom.us/webinar/register/WN_vJXBwaVSRKCr4T_bPlTMiw or use the form below

— To attend a Session Tuesday 13 October at 8am UK Time (GMT+1) then use this link https://us02web.zoom.us/webinar/register/WN_E-YFB8qvQ9iQ0X774-fV9Q

We will run a demo of this- building up a simple demand planning tool from scratch with zero VBA code written manually anywhere in this webinar!

What you will get from attending:

— Learn how to build and automate any planning tools with no VBA knowledge.
— Watch as we build a simple demand planning tool live.
— Learn how the Fast Excel Development Method has updated – whatever your level of VBA
— Download the completed Demand Planning tool.
— Download the new version 4.0.0 of the Development Template.

FEDT v4 Webinar Registration

FEDT – Fast Excel Development Template

At Production-scheduling.com, we provide tools and guidelines to efficiently manage these types of data processing systems powered by Excel. The method is called Fast Excel Development Method and we make available for free the basic development tool, called FEDT – Fast Excel Development Template as well as some application examples. Of course, videos, webinars and consultants for training, support and implementation are also available.

The structure we suggest provides that each user who contributes to the process operates on local data, the result of which is exported for sharing in text format, the most resilient file format,  and made available in a sharing point, for example a shared folder on a company server or a cloud service like Dropbox, where the ERP system data exports are stored too.

The data made available on the network are then imported and used according to the process sequence by the different contributing users, until the final result is achieved.

Distributed system scheme

Each one of the nodes in the process has its own Excel based system, built by the Fast Excel Development Methodology: what you can do is limited only by your imagination.

Since 2010, the basic elements of the FEDT in a nutshell are:

  • process control sheets (menus, data folder parameters) and documentation sheets (keyboard shortcuts, list of sheets, instructions for use)
  • 5 basic template sheets:
    • Query, dedicated to data import,
    • Parameter, dedicated to local management and data export,
    • Table, dedicated to calculations, filters and one-to-many explosion,
    • Stack, used to append different tables
    • Pivot dedicated to pivot tables calculations and reporting
  • A set of powerful ready-made functions, whose code is accessible, which guarantee the automation and speed of development activities

Given that the FEDT tool is extremely powerful and the code it contains is open source, in order to approach the development of an automated procedure by the FEDT is necessary:

  • knowledge of the process, this is the fundamental ingredient
  • basic knowledge of Excel (formulas, formatting, pivot table basics)

The previous Fast Excel Development Template was requiring also:

  • a minimal VBA knowledge: macros and functions are already built in, but you needed to write the code to run them in sequence and to obtain an automated process

What’s new in FEDT 4.0: Automation with ZERO coding, International and Local Data Format, Elegant Colours

We added to the solid base described above a smart interpreter and a small set of directives for sorting tables and for generating output in the form of text files.

The interpreter, which is a VBA macro, is analyzing the sheets in the template and reading the directives, and from these pieces of information it is writing the VBA macro that automates the process, from the inputs, trough the calculations, to the reports and the output text files.

The processes that can be automated without the developer to write any VBA code are fairly complex: a full MRP or a capacity planning and scheduling system are just two examples.

In short, we added a macro that is coding a VBA macro on your behalf: cool, isn’t it? But it is not all.

If you consider a distributed process with a number of contributing user nodes in a multinational environment, a problem raises: there are some pieces of information that are managed in a number of formats depending on the regional settings either of Windows or of Excel.

As an example, the decimal separator is a period in some countries and a comma in others, or the CSV file separator is a comma in some countries and a semicolon in others. Or the way Excel is specifying the date formatting, based on local language.

While the Excel data import wizard gives you a good level of control, when generating text output Excel is using the regional settings only and it is not translating the date format in the =TEXT(value, format) formula.

We added a spreadsheet function =TEXTI(value, international format) that is accepting international format as a parameter, therefore it is working independently from regional settings and a detailed level of control of the text output generation.

Furthermore the Query template is automatically interpreting the strings TRUE and FALSE in imported text files as logical values when the regional setting is using a different representation (e.g. Italian regional setting: VERO is true, FALSO is false).

Of course all of this is included in the automatic macro generation: if you are in USA and you are receiving a text file from your colleague in Spain, you can set up the data output to be in international format and import it with no issue.

Lastly we added an elegant touch: our template includes a beautiful color palette, which is used on charts and sheet tabs.

P-S palette

Let’s see in details how the new template is working.

How to Automate Your Excel-Based System

Below I’m explaining the rules, but before of that, how do we generate the macro?

From a practical point of view the automatic macro generation is launched every time you click on the “Automation ON” button in the Menu sheet.

The generated macro, if there are no errors, is assigned to the button “Update from local”, therefore clicking on this latter runs the macro.

The caption of the “Automation ON” button becomes “Automation OFF”: if you click on it, it disables the automated macro and its caption becomes again “Automation ON”. The next click will generate the automation macro again.

Some Conventions

Row 1 is used to keep the reference to the column letter

Row 6 is for controls on the sheet function

Row 7 is to define multiple formula ranges

Row 8 is the only place where we have the live formulas we will use for calculations – this makes the procedures built by FEDT easy to be debugged and resilient to bugs.

Row 10 is where all the tables start: it is the Field Headers row.

General Workflow Automation

Build your process strictly from left to right, one sheet after the other and inside the sheet one column after the other left to right. Duplicate the template sheet by CTRL+Shift+o and rename it.

The macro creator will look at visible sheets, it will understand which one in the sheet templates you used and it will write the proper code according to this.

If you’re using different sets of formulas in a sheet the macro from left to right is reading it in row 7 and it is preparing for you the correct statements. Remember to build your calculations from left to right inside the sheets too.

Formatting Columns

Formatting columns

In our standard methodology, the formulas in row 8 are copied   down alongside the data, calculated and afterwards pasted as values; however, if you want to preserve formatting, you can simply use an Italics font on the row10 column headers: this will direct the macro creator to paste down also the format of the formulas in row 8.

Sorting Automation

There are use cases in which you need to sort your data.  We have prepared for you a set of sort instructions to be written in row 6 over the formulas range you want to sort. In our experience it is rare that you need to sort by more than 3 key fields, almost impossible that you need to sort on 5 fields or more.

For this reason, we offer sort statements from 1 to 4 key fields.

Sorting tables

 Here is the syntax

Sort1 Field1

Sort2 Field1 Field2

Sort3 Field1 Field2 Field3

Sort4 Field1 Field2 Field3 Field4

Optionally you can add ascending or descending order directives, like this:

Sort2 Field1 Field2 xlD xlA

This will sort according to Field1 in descending order and according to Field2 in Ascending order.

If no sorting direction directive is specified, the ascending order is assumed.

Note the space used as separator within the words: you do not want column header names with spaces, use underscore instead (“_” e.g. This_field_name) or a capital letter at the beginning of each word (e.g. ThisFieldName)

The sorting directives are applied AFTER the formula calculations of the formula range where the Sort directive

Pivot Filtering Preservation

What is happening if a filtered value we’re using on a pivot table disappear? Excel resets the filter.

In our calculations we want that specific value to be restored on every run.

The macro generator is looking at field filters on Pivot template-based sheets and if the value is different from <All>, it prepares a statement to re set the proper filtering value.

If the value is <All> our assumption is that the filter value is not important to the calculations and  the user will select a different value each time.

By the way, Pivot template based sheets are frequently used as reports: by the presssing CTRL+Shift+r the sheet tab color is set to our standard color  for report identification.

Output Automation

At some point you would like to generate an output, because you are building a multiuser system or you want to pass the results of your Excel system to the company ERP.

We have 3 options, all of them to be used in row 6.

Tab separated text files.

Select the headers you want to export and use the shortcut CTRL+Shift+x, this will generate a named range <sheetname>_Table2.

If you write in row 6 one in:

TXT_int: the content of <sheetname>_Table2 will be exported as a text file tab separated stored as <sheetname>.txt in the folder Data defined in the PARA sheet, using the international format

Text file output

TXT_loc: the content of <sheetname>_Table2 will be exported as a text file tab separated stored as <sheetname>.txt in the folder Data defined in the PARA sheet, using the local format

CSV files

Select the headers you want to export and use the shortcut CTRL+Shift+v, this will generate a named range <sheetname>_TableCSV.

If you write in row 6 one in:

CSV_int: the content of <sheetname>_Table2 will be exported as a csv file stored as <sheetname>.txt in the folder Output defined in the PARA sheet, using the international format

CSV_loc the content of <sheetname>_Table2 will be exported as a csv file stored as <sheetname>.txt in the folder Output defined in the PARA sheet, using the local format

Generic text file format

Select a range of headers: the CTRL+Shift+t shortcut allows you to generate a table with the name you prefer. The OUT statement, which is accepting up to 8 parameters, is enabling the output of a text file in very flexible format with whatever name and location you want. It has to be written in row 6.

Here is the syntax:

OUT TableName ListSeparator DecimalSeparator TextIdentifier TrueString FalseString ExportFileName ExportFileLocation

Important: only the TableName parameter is mandatory the following 7 are optional, all the parameters have to be written within double quotes character and separated by a space e.g. OUT “MyTable” “|”

Download the Template

Please visit our download page to download the new Fast Excel Development Template.

If you are registered on the site, you may have to log-in first.

Lost your Password?

If you are not registered you can do so for free here:

Conclusion

Our Fast Excel Development Template is leveraging the power of VBA for you, but we run it all on your behalf behind the scenes.

You can build your own reliable and business grade system without any coding knowledge.

Join us on Production-scheduling.com and attend our webinars. If you need help we’re happy to listen from you: just drop us an email:

 [email protected]

 [email protected]

Here below there’s a form to subscribe for the next webinar, in which we build live starting from an empty FEDT a simple automated system.

Thursday 15 October
4pm UK Time (GMT+1)
5pm CET, 8:30pm IST
11am Eastern, 8am Pacific
Register here https://us02web.zoom.us/webinar/register/WN_vJXBwaVSRKCr4T_bPlTMiw or use the form below

— Learn how to build and automate any planning tools with no VBA knowledge.
— Watch as we build a simple demand planning tool live.
— Learn how the Fast Excel Development Method has updated – whatever your level of VBA
— Download the completed Demand Planning tool.
— Download the new version 4.0.0 of the Development Template.

— To attend a Session Tuesday 13 October at 8am UK Time (GMT+1) then use this link https://us02web.zoom.us/webinar/register/WN_E-YFB8qvQ9iQ0X774-fV9Q

FEDT v4 Webinar Registration

Leave a Comment

Scroll to Top