OR/MS Today - February 2002|
Quick and easy Excel-based MRP software for small and large manufacturing operations
By Vijay R. Kannan
Resource Manager by User Solutions Inc. provides an intuitive, easy to use, production planning system for a material requirements planning (MRP) environment. The software has the ability to, among other things, build production schedules from forecasts using simple-to-develop bills of resources (BOR), analyze capacity constraints, automatically identify resource loading problems, and develop an array of reports for monitoring and control purposes.
Resource Manager offers two particular advantages over many other production control software systems. First, it is Excel based. Other than familiarizing oneself with the specific functions and procedures of the software, which are easy to learn, the user is not faced with learning a fundamentally new or different software system. Each system module is represented by a single spreadsheet residing within a common workbook. This organization makes moving between the integrated modules fast and simple. The Excel format also gives the software the capability to interface with other systems and data formats. For example, data can be easily imported and/or exported from other planning systems using simple copy and paste operations.
Second, the software is compact, both in terms of hardware requirements, and, more importantly, cost. Users of Resource Manager are not using a costly, data- and resource-intensive application, but an inexpensive yet fully functional system that allows them to manage typical planning activity without unnecessary computing or financial overhead. This makes the software particularly suited to smaller organizations with a need for a fully integrated and functional planning and control system, but that lack the resources or need for added complexity.
Minimum requirements to run Resource Manager are a Pentium running at 200 MHz, 32 MB RAM, 3.2 megabytes of hard disk space, and Excel 97 or higher installed on a stand-alone or network PC. The software was tested on a 933 MHz Pentium 4 with 128 MB RAM. On this configuration, small test scenarios ran rapidly. Larger scenarios running on a slower computer will likely see a noticeable difference in execution speed, but not to the extent that this precludes operation in a real-time mode. Beyond hardware and software requirements, the software requires a working knowledge of Excel, though nothing more sophisticated than data entry, menus, file retrieval and saving, and printing.
Documentation and Installation
Software installation is quick and easy. The software comes as a single file in either floppy or CD ROM format, and unzips to a single .XLS file and readme.txt file. Running the software involves no more than opening the .XLS file from within Excel. Other than revealing the Resource Manager user interface and adding three drop down menus, the screen has the familiar look and feel of an Excel spreadsheet. (The user manual mentions four or five additional drop down menus, but one of these is the standard Excel Help menu with help for Resource Manager included, and the other is for an Accounting option which was not included in the version tested.)
Documentation for the software is well organized. For each module, the documentation walks through the functionality of the module, provides step-by-step directions, and highlights dos and, more importantly, don'ts of software use. In particular, it draws user attention to data entry considerations. Given the Excel platform, data entry is an area where there is potential for the user to compromise the integrity of the software by corrupting system parameters, spreadsheet formulas, formatting, etc. While the software has some post-data entry error checking capabilities related to the operation of the software, these do not extend to preventing users from deleting/editing areas of the spreadsheets that they should not be able to (an area for possible improvement), thus the tips on data entry are particularly valuable.
In addition to the user manual, the software comes with a 12-step tutorial that allows the user to get a feel for the software as well as develop a schedule for a simple product schedule. This tutorial, upon which the review is based, allows the user to develop a feel for the system's capabilities within a very short period of time. The developer also supports a "walkthrough" designed to help individual users understand how the software can be adapted to their specific needs, though this is not necessary to get acquainted and running.
The user interface is well organized and makes system navigation trivial. The opening or Settings spreadsheet, to which the software opens up, consists of a menu of options as well as a graphic representation of how the software works. This graphic illustrates in a logical manner how the different system modules are linked and illustrates the MRP orientation of the system. Modules can be accessed in one of three ways: by double clicking on the corresponding entry in the top portion of the Settings spreadsheet, by clicking on the Menu bar, or by using the F4 key. The latter two options reveal a dialog box listing all system modules, which is organized the same way as in the screen interface (Figure 1). Modules are organized within the menus in a logical manner under the headings Initialization (primarily database functions), Scheduling and Reporting. A useful feature of the opening interface is that for each module, comments have been included that describe the module's purpose as well as providing additional information regarding the module. Comments can be accessed by simply dragging the cursor over the corresponding red flag. This annotation feature can be found throughout the system.
Figure 1. Resource Manager's opening user interface.
Forecasting and Scheduling
Scheduling begins by taking an existing database of products and operations or adding new products/operations to the database. The database of products and operations are contained in a well-laid out Build spreadsheet. For new products, the Build Bills-of-Resource (BOR) function allows linkages between end products, sub-components and operations to be defined in a logical manner. Once products and their BORs have been defined, requirements can be entered via the Initialize Forecast Calendar module. This allows forecast requirements to be entered based either on a production start date or a required completion date.
Figure 2. Data records for products, operations and forecast entry.
The Master Scheduling module automatically links the forecast requirements with corresponding BORs and existing load characteristics to develop a schedule. The output of this process is not only an end-product schedule specifying both start and end dates and product cost information, but also a detailed schedule of sub-component and resource requirements and costs. Once the schedule has been reconciled with inventory information, a final schedule can be easily developed. The Resource Calendar computes several load-related statistics, automatically flagging (in red) problem areas. Graphs of resource availability and load by date and by operation can be generated with a single click of the mouse. For purchased items for which vendor information is known, Resource Manager has the capability to automatically generate purchase orders. In addition, the capability exists to track receipts and automatically update inventory records.
A useful feature of the software is its extensive reporting capabilities. In addition to Gantt charts that graph schedule completion for both sub-components and end products and automatically highlight bottlenecks, Resource Manager provides several reports that summarize both product and operation activity. These reports can be generated in multiple formats (i.e., by product, operation, routing, etc.) and degrees of detail (i.e., product only, product and components).
The only apparent example of the product not delivering all that is advertised is in the area of "what if" analysis. Both the product documentation and user's manual list "what if" analysis as a feature of the software. However, it is not clear from the user's manual what "what if" capabilities exist even though they have a unique listing in the manual. While the ability to run alternative scenarios within the individual modules exists, this does not take the form of a traditional "what if" applications.
Figure 3. Complete MPS schedule and summary product and operation reports for sample product.
Resource Manager is a complete MRP system that is designed to be adaptable to a user's specific needs as opposed to being a one-size-fits-all application. The Excel platform makes it easy and intuitive to use even for a novice. Within an hour or two it is possible to understand the basics of system operation. While the user's manual is comprehensive, the tutorial alone allows one to get up and running rapidly. A more elaborate tutorial test case would, however, be helpful in allowing a new user to get a more complete sense of the software's capabilities.
The software may not have the functionality and breadth of more expensive systems, but it is a complete system suitable not only for managing in a MRP environment, but also for familiarizing those new to MRP. It is also an ideal tool for teaching purposes. Not only could it be used to illustrate the concept of MRP in an introductory operations class, it can be used to bring a practical element to an advanced course in manufacturing planning and control.
Vijay R. Kannan is an associate professor of operations management at Utah State University. He serves as an examiner for the Shingo Prize for Excellence in Manufacturing, and has written several articles in the area of manufacturing planning and control.
OR/MS Today copyright © 2002 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
506 Roswell Street, Suite 220, Marietta, GA 30060, USA
Phone: 770-431-0867 | Fax: 770-432-6969
Web Site © Copyright 2002 by Lionheart Publishing, Inc. All rights reserved.