This download is two-in-one. There is a fully functional Excel MRP System. And an optional Excel MRP Model that uses the same modules but also generates transaction data. The model means that you can step through week by week and see the impact of the new order recommendations.
Each of these systems is populated with example data from our PS Cycles company. We recommend that you use this data and get to know both the system and the model before you attempt to populate it with your own data. When you get to this stage, keep the PS Cycles system as a reference, save the download in a new folder and then use the “Update Text File Locations” as described below.
Download and Installation Instructions
- Download the system folder as a zip file with this download link. This is a zip folder that contains a folder called C:\P-S_LiveCo.
- Unzip the contents directly to your C Drive. You should have these folders:
- In P-S_LiveCo, right-click the first Excel file and select “Properties” and if it appears, check “Unblock”.
- If you do not see “Unblock” then you do not need to do this step.
- Repeat for every Excel file in the PS-LiveCo folder.
Running Macros for the first time
- If you have not run macros or used external data before then do this:
- File > Options > Trust Center > Trust Center Settings >
- .. Macro Settings >
- ..Check “Enable All Macros (this is highly recommended as useful code can run)”
- .. Check “Trust Access to the VBA Project Object Model”
- .. External Content > Check “Enable All Data Connections (also recommended)”
- OK > OK
- You will only have to do this once per machine.
- Open the file “PS_MRP_System_Master_v01.0x.xlsm”, hit “Update from Local” to run the system.
- To run the model, open the file “PS_MRP_Model_Master_v01.0x.xlsm”, hit “Update from Local” to run the system.
PS MRP System
This system is set up as an example. It runs once with the data that is supplied in the “Input” folder. This is a system that runs in a series of modules. To run the system, open the file PS_MRP_Systems_Master_v01.04 and hit the “Update from Local” button.
No data is stored in Excel. Each of the modules runs, outputs data as text files and then closes. The Master file also has the reports.
For more information on the advantages of developing in a modular system, please check this article.
These input tables are in CSV format. The first module processes the CSV into TXT format and can also be used to transform tables if your input data has a different format.
PS MRP Model
The model system has a module called “PS_Data_Gen_v01.06” and this will generate changes in inventory, sales orders, purchase orders and production orders that result as they would in a manufacturing company. If sales orders and production orders are supported with available inventory, then the order is filled. If there is any shortage, the sales order or production order is recorded as unfilled and cancelled. There is no backlog or late customer orders.
The Model Master file runs the system and also automatically raises new supply orders. Supply orders are purchase orders together with Production Orders in the same table.
Each time you hit the “update from Local” button, the date will advance a week, the transactions will update accordingly and the system will run again. Hit the button 52 times and you will step through an entire year. The OrderFill Report will show how well the company has performed.
The Items sheet allows you to make changes to the min-max days, MOQ and Lead-Times.
To run the model, first hit the “Prepare Starting Data for Model” on the Menu sheet to set up the model as of 2-Jan-2023.
The demand is a mix of sales orders and forecast. Some products have a forecast as an input (error is between 10-40% at a weekly level). For those which do not, the moving average from the sales history is used for the entire longest cumulative lead-time of the product. This means that if you have to order 90 days ahead, then there is some demand to use to run MRP and convert Min-Max days into quantities.
There is semi-finished goods inventory but no decoupling buffers. The logic is broadly MRPII together with some dynamic replenishment.
This system, and inventory settings, are far from perfect. The idea is to learn about what works and see how a combination of manual judgment and order recommendations can combine for the best balance of inventory and service-level.
To Create a New Instance of MRP System for Your Own Data
- Save a fresh version of the PS_LiveCo folder with a new name, also directly in the C Drive.
- Open PS_MRP_System_Master and on the Menu sheet, change the “FilePath Data Local” value to match the new filepath for the data folder.
- On the PARA sheet, check that the value for PARA_FilePath_Input_Local has updated and there is a folder called input in this location.
- You need to have all the correct text files in Data and Input place before you go further. If you do not, the system will give you an alert and show which are missing.
- Hit the “Update Text File Locations” button. This will change all the query sheets to import data from the new folder.
- If no error message appears then it will have been successful.
- You can now swap out the Input files for your own data. Make sure the data are all consistent.
- This is a free system and a very powerful tool. Do not expect to be able to run it with meaningful results without some work in getting your data into shape.
To Set Up the Model to Run with your own data
This is going to be much harder and will involve some re-engineering of the Data Generation module. IF you would like to explore this please contact me at [email protected].