Developing a system
The considerations you will make in developing a system will usually go something like this:
Specify
Many of the people who request us to develop systems want to start with every possible feature. In the words of the song “we want it all, and we want it now”. Smart developers begin with a limited scope, and grow from there. Identify what hurts most in your business. Do you want to:
- Make better customer delivery promises?
- Know which raw materials to order?
- Control finished inventory levels?
- Have better visibility of demand?
Choose just one of these to begin with.
The first task is to assemble all the data you need, in the form of normalized data tables.
The data extracted from your ERP system is likely to already be normalized.
The data in Excel workbooks may have to be converted from a matrix to a normalized table.
The data in someone’s head, for example product attributes or customer groups, will need to be described in tables.
Design
If you are an operations person, rather than an IT person, you should have an idea of reports that you want to see. When thinking through the design, separate the reports from the input data and the calculations:
If you were to prepare the reports by hand, what input data would you need, and where does it currently reside:
- In your ERP system?
- In Excel workbooks?
- In someone’s head?
The first task is to assemble all the data you need, in the form of normalized data tables. The data extracted from your ERP system are likely to already be normalized. The data in Excel workbooks may have to be converted from a matrix to a normalized table. The data in someone’s head, for example product attributes or customer groups, will need to be described in tables.
Data Structure is the key to good design. Excel 2003 will hold 65000 rows of data, Excel 2007 will hold over a million rows, so don’t be scared of large numbers of records. Try and gather data at a low level of granularity, not summarized. Try to gather data for the company as a whole. You may wish to have reports by division or department, but that does not mean that the input data have to be in separate files for each division or department.
Build
The Fast Excel Development Methodology does not require you to write VBA program code. It uses the following techniques:
- Imports text files from the host ERP system
- Applies Formulas with Excel functions to ten-thousands of results
- Uses macros to repeat the task in a robust and reliable way
- Summarizes and filters data with standard PivotTables
- Explodes, joins and stacks tables
Download the Fast Excel Development Template workbook, which has module macros and other useful utilities built into it. Import the input data and think through each stage of the data manipulation and calculations as if you were doing it manually.
Use live data and real life examples to validate your logic at each step. Keep each type of data on a separate sheet and don’t be scared of using many sheets in your workbook. Our P-S Cycles model system contains 123 sheets.
Work from left to right, and as you complete each sheet, use the Macro Maker to automate the sheet, before moving on to the next.
You don’t have to be 100% clear on the design before you start to build. The Methodology is change compliant. It is very easy to go back to add and change the logic, and insert additional sheets. The finer points of the design will crystallize as you work with the data and think through examples.
Integrate
Input data flows in from your materials and production planning software system and other sources. It is also a good idea to break the application into separate workbooks for manually maintained parameters and reports, leaving most of the calculations to be done in an “engine”. The job of the engine is to pull in data, and push out the results of its calculations for reports.
Ironically, the fastest and most resilient way of moving data between one Excel workbook and another, is via flat ASCII text files, not via Excel files.
Deploy
Generally, there is only one person within the organization who runs the engine, but many people who view the reports. Some users run the engine automatically, every hour, untouched by human hand.
Most of the reports are PivotTables, and the people who view them are given training on PivotTable manipulation. Each individual is given their own copy of the reports workbook, and encouraged to manipulate the PivotTables to analyze the data and drill down into it. That way they cannot interfere with each other’s view of the data. Each time the engine runs, their reports are updated.
When the first version of the new system is deployed, it is important to set realistic expectations with the users. They should understand that this is the start of a period of prototyping.
Improve
Introducing automated planning and scheduling into an organization has a profound and lasting impact upon it. People work and interact in different ways, so it is not possible to anticipate in advance, what information will be needed to support the organization as it matures. The Methodology is change compliant, and allows fast, “on-the-fly” changes and improvements, to allow the system to grow and mature with the organization.
Next: Use our free specification tool to develop your own planning software system.