MRP_Excel.zip from Production-Scheduling.com is a free Manufacturing Resource Planning tool in an Excel workbook. It uses no macros or VBA code and it's not locked: anyone who knows Excel can customize it to suit his or her needs.
Excel-based MRP systems have several advantages, such as the ability to use schedules and other data already entered in Excel; greater familiarity and less complexity than a full-scale MRP system; and, best of all, lower cost. MRP offers some significant features, including a Purchase Action Report that identifies schedule-critical purchase orders, a single-level Bill of Material Structure, and a built-in Inventory Rationing tool that analyzes existing inventories and product lines and rations materials to make the inventory cover as much production as possible.
MRP_Excel.zip comes with sample data for a new line of shampoo. Clicking the extracted download opened the workbook inside Excel with the Introduction sheet displayed. Aside from a Table of Contents, the first page of note is the Table of Links, which depicts those worksheets that are linked to other worksheets as well as chained PivotTables. This table saves a lot of time, especially for inexperienced users, since there are about 20 links.
The next sheet, The Business, displays data for the fictional small company that manufactures shampoo, including Process, Bill of Materials, and Capacities. Reviewing MRP_Excel.zip's sample materials could serve as a thumbnail education in manufacturing production planning.
The rest of the workbook's sheets also feature data for the fictitious product line, grouped by categories. For example, the Data Files section features the Item Master, Supplier Master, Bill of Materials (BOM), and Purchase Orders sheets. The Finite Schedule section includes a sheet entitled Make-to-Inventory Logic Explained. MRP Calculations let us schedule production runs, allocate inventory, and make other calculations. The final section is the Inventory Rationing tool.
While MRP_Excel.zip is a serious, enterprise-ready tool that can serve capably in companies of all sizes, it's easy enough to use to be appropriate for individuals, family businesses, and other small manufacturing businesses. The Inventory Rationing tool seems especially useful for managing for lean supply chains as well as lean times.
From the developer: "A fully functional Manufacturing Resource Planning (MRP) system in Excel that has no macros or VBA code, nothing is hidden, the system may be modified by an Excel literate person, and adapted for use by a range of manufacturing companies. The system is loaded with sample data from a fictional shampoo manufacturer who makes 3 variants each in 3 pack sizes on a single bottling line.
"Working from a sales forecast and finished inventory, the system schedules the bottling line so that the desired inventory cover is maintained. It also explodes the production requirements through a bill of materials (BOM), and calculates the raw materials to be purchased, taking into account the materials that are in inventory and on order from suppliers. There is also a module which determines the best mix of products to make with the raw material inventory on hand, taking into account that some of the materials are common to several products, while others are unique."