Integrated Supply Chain Management with Excel Based Modules
We have recently made a radical and exciting change to the way we develop systems for our clients – we are now using standard plug-together modules.
It is a change from our previous approach, which was to custom develop systems in a single workbook. The P-S Cycles system is an example of this.
Custom vs. Package Systems
We still believe that a custom built system is superior to an off-the-shelf package, as it is tailored to the client, reflects the way they think, and is much quicker to implement. Although our new modular approach looks more like a package, it still allows us to offer custom configured solutions, and they are now quicker and easier to build. There are 20 modules available, and we select the ones that are appropriate for the client’s needs. Customization is confined to the Data Interface and the Reports, whilst the rest of the processing is undertaken by the standard modules.
We started using spreadsheets for supply chain management in 1990, with Lotus and Quattro Pro, but Excel soon became the de-facto standard, and happily, its capabilities continue to improve. Everybody has Excel, so there is no need to justify the introduction of additional software. However, having Excel, and knowing how to use it, is not enough. In order to use Excel to do ambitious things, you have to use it in a very structured and disciplined way.
Fast Excel Development Method
So, we established, and teach, the Fast Excel Development Method (FEDEM), and we have eLearning modules on the subject. Some of the principles behind FEDEM include:
- Automating with one click of a button
- Interfacing with data stored in the legacy ERP system
- Separating data, calculations, and reporting
- Normalized data tables
- Pasting down formulas and replacing them with values
- Text files for moving data between workbooks
- Reporting and data manipulation with PivotTables
The next major step forward was our Fast Excel Development Template (you can download it here). It is an Excel workbook with a set of VBA functions built-in, which make the development of applications in Excel very quick and easy. We use the Development Template ourselves for all applications, and we continue to refine and improve it.
Using the Development Template we have taken the techniques we have used in hundreds of supply chain applications, and isolated them in 20 separate workbooks (modules). Each module passes the results of its calculations by “putting down” text files in a folder, which are then “picked up” by the next module. The macros in a module may be run stand alone, or all of them can be run from the Master Module.
We have already built several applications with the modular approach, and once the modules are thoroughly tested by us, we will make them available to our subscribers. The modules are:
- Master Module – Opens, runs, and closes each module in turn. Contains the list of selected modules
- Data Interface – Custom module to convert the client’s data into the standard format, and output as text files.
- Parameters – Contains Shift Calendar, Work Center, Target Inventory Cover, Set-up Matrices and other user maintained parameters.
- Sales Forecasting – Takes demand (or sales) history, and applies an algorithm to generate a forecast. The forecast may then be modified with manually applied market intelligence.
- Forecast Accuracy – Compares recent sales history with previously generated sales forecasts, and calculates the accuracy of the forecasts (demand predictability)
- Demand Variability – Analyses the variability of demand history, and recommends inventory cover buffer levels, to be used in the Product Replenishment module
- Forecast Consumption – Combines Sales Orders and Forecast according to rules contained in the Parameters. Also breaks a monthly forecast into weekly buckets.
- Product Replenishment – Calculates immediate and future inventory replenishments required, according to the Target Inventory Cover in the Parameters.
- Multi-Depot Replenishment – Suggests immediate and future quantities of finished product that should be sent from a distribution center to regional depots. Includes “inventory rationing”, and “top-up” logic, to make up a load.
- BOM Processor – Takes live products, and explodes through each level of a multi-level BOM, to generate a “Product BOM”. There is no limit to the number of levels in the BOM.
- Material Cost Roll-Up – Takes the purchased cost of raw materials and rolls it up through each level of the BOM, to calculate the material cost of each manufactured assembly and finished product.
- Inventory Management – Measures how well inventory is being be managed by calculating cover (how long it will last), and comparing with the Target Inventory Cover
- MRP Multi-Level – Explodes the demand through the BOM, deducts inventory and WIP, to calculate “what to make” and “what to buy”
- Capacity Planning – Explodes “what to make” through the routing, calculates the load on each work center, and compares this with capacity.
- Capable-to-Promise – Uses capacity constraint logic and raw material availability, to calculate when each sales order will be available.
- Set-Up Reduction – Groups together products with like attributes, color, diameter etc. to reduce set-up times.
- Finite Scheduling – Calculates the start and stop time of each production run to better than a second, using our 3 pass scheduling logic. Displays the results as a Gantt Chart and other formats
- Purchasing – Projects when the inventory of raw materials, to support the schedule, will be depleted, compares with open purchase orders, and recommends purchase orders to be placed, chased, deferred or cancelled.
- Job-Shop Progress – May be used in conjunction with Finite Scheduling for single piece job shops. It displays the schedule as a matrix, highlights operations that are late, and is used to capture completed operations.
- Reports – Customized module that puts all the reports into one workbook, with a Reports Menu. The module, and personalized variants of it, may be installed on individual user’s computers, and will be refreshed via the network.
If you’d like to know more, then please fill in this form. We will be in touch with some more information and the chance to see a demonstration.