Copyright Institute of Industrial Engineers, Inc. May 1994Enterprise systems, which link by computer several functional organizations within a firm, are becoming sources of competitive advantage as companies seek to coordinate cross-functional decisions. A well-implemented enterprise system provides the same invisible hand economic guidance that cost centers do while avoiding the disadvantages -- isolation, zero-sum competition -- of cost centers. With appropriate computing tools, the traditional opposition between sales personnel, who wish to accept every customer order, and operations personnel, who fiercely protect their production schedules, can now be managed in a cost-efficient, integrated manner. In addition to managing large information databases, enterprise systems also allow for integrated management decisions that determine, for example, the effect of buying new equipment on sales quotes as well as on shop floor performance. Clearly a firm would seek to make scheduling and batchsize decisions so as to minimize job costs, since this would increase the profit bottom line.
Unfortunately, many companies are hesitant to invest in large-scale enterprise systems that may require over six months of development time and cost over $100,000 for complete installation and consulting fees. We have found that a spreadsheet-based prototype enterprise system can serve as an effective tool for project engineers trying to sell an enterprise system to cautious managers. In some applications, it is capable of demonstrating the basic functionality of the large-scale system, such as scheduling and job costing, but without a large time and financial investment. This was certainly true for Video Copy Services Inc. (VCS),Atlanta, Ga., a medium-sized video duplication firm with over 20 employees and a thriving continuous improvement program. In response to their need for a system that could integrate management, sales and operational decisions, a team led by the authors developed a prototype decision methodology and implemented it on a personal computer spreadsheet system (Excel for Macintosh, version 4.0). The company received two major benefits from our spreadsheet-based prototype. They are using the prototype system in a limited manner (For example, to provide a more consistent pricing policy, insight into daily job scheduling and one-time studies of labor-force size policy and potential equipment acquisitions), and they have begun the development of a large-scale enterprise system employing our spreadsheets as a major part of the requirements definitions.
ADVANTAGES AND DISADVANTAGES
Our project with VCS has allowed us to identify several advantages and disadvantages of a spreadsheet-based prototype enterprise system, as shown in Figure 2. We found that although closed-form computations, such as economic order quantity (EOQ) calculations, are easy to implement, the spreadsheet system computes them slowly. Some of our spreadsheets took over 10 minutes to recalculate a few hundred cells. This sluggishness in the prototype did not bother managers, who readily appreciated that the production-level system would function on a significantly faster hardware and software platform.
Most spreadsheet packages lack the capability to serve as a relational database, although they can often link to a separate database program. This can complicate computations based on information that must be extracted from a database. For example, our scheduling heuristic uses order data. A large-scale enterprise system must import data from a database management system (DBMS), run in conjunction with a DBMS or run as an application of a DBMS. Our prototype, by contrast, works with data entered manually into the spreadsheet. Again, managers readily appreciated the distinction.
The spreadsheet genre of user-interfaces, although easy to work with and powerful for an experienced user, is unfriendly for ordinary users. In an attempt to correct this software restriction, Microsoft has built into its IBM version of Excel for Windows the capability to build elaborate user-interfaces, complete with menu buttons. Our Macintosh version of Excel did not have this functionality. There have been successful demonstrations of a production-quality user-interface using Excel for Windows for a prototype economic lot-sizing application. Such an interface requires additional development effort, but may be useful in providing the look and feel of a full system in demonstrating a prototype.
User-interface difficulties in a prototype are not as easily explained away as other difficulties. There was no question that the final system would be fast despite the prototype's sluggishness, and that the final system would import data despite the need to key data into the prototype. But masterfully handling an awkward user-interface in demonstrations did not allow interface utility any more than watching an accomplished pianist would allow a novice to play the piano effectively. Fortunately, the involved managers had such a detailed understanding of the quantities and functions being manipulated by the system that they knew any straightforward user-interface design would be usable.
Macrosheets are a feature of spreadsheets that can be used to automate cell manipulations and perform repetitious calculations through custom functions. We found that macrosheets are flexible and powerful in terms of adding new functions and serving as the center of a series of dynamically linked spreadsheets. When multiple spreadsheets are dynamically linked, each spreadsheet changes in real-time upon alterations in any of the others. Herein lies the power of spreadsheet packages to integrate several decision spreadsheets that represent different components of a full enterprise system. This functionality mimics the information flow in an enterprise system, which similarly links departments throughout the business to coordinate real-time, integrated decision-making. To further explore this concept of dynamic linking, we now propose a basic system structure suitable for spreadsheet-based prototype enterprise systems.
A PROPOSED SYSTEM STRUCTURE
Figure 1 shows the basic structure underlying our system for VCS, with a macrosheet at the center of functionality. (Figure 1 omitted) The macrosheet contains the computational tools used throughout the system. In our system for VCS, the macrosheet contains mainly custom functions used to compute job costs, batchsizes and other strategic quantities. These functions are then implemented as needed throughout the series of other spreadsheets for sales, management, etc. The functions themselves are designed to take a flexible combination of input parameters. For example, our function mdubcost() returns the marginal cost of one video copy and takes as input such free parameters as order quantity, video tape length, batchsize and number of production workers. Depending on the application, a macrosheet can contain computational tools other than custom functions through the use of command routines that automate cell manipulations.
There are two immediate advantages to using macrosheets, as opposed to building the functionality directly into each spreadsheet separately First, it is convenient to maintain the system's computational functionality all in one place, where it can be easily modified and updated. Otherwise, we would have had to alter many cells throughout the system to make a single functional change. A second advantage is the ability to easily link all functionality with a common parameters database. The custom functions and parameters together are used throughout the system in spreadsheets for operations, management and sales that dynamically integrate decision making.
OPERATIONAL DECISIONS
In our system for VCS, a series of spreadsheets have been designed to aid operational decisions. A job is described by a due date, an order quantity, a video tape length and a source format. In order to process a job, operators must select one source machine for the master tape along with a group of up to 150 parallel duplicating machines. Some questions arise in this production system, and spreadsheets can be used to aid the operators.
* What is the optimal batchsize for a given job? -- One of the main objectives of the operations department is to process each job in the most cost-efficient manner while meeting due dates. The batchsize decision is critical to this objective. To aid this decision, the system can generate optimal batchsize grids. The user can first go into the parameters spreadsheet, alter relevant parameters (such as the number of working duplicators) and then generate a new grid. This grid may then be posted in the production area to assist operators with batchsize decisions.
The optimal batchsize computation has a closed-form solution and is a function of resource costs. This analytical function, called optbatch(), is stored as a custom function in the macrosheet. Note that the optimal batchsize depends on how many jobs are in the backlog, i.e., how many unfilled orders the sales department has taken. The more work there is to be done, the faster jobs must be processed to meet due dates. This relationship between order backlog and optimal batchsize is controlled through a parameter, called the throughput cost penalty, which doubles the batchsize for every 30 cents of additional penalty. Thus, as sales accepts new jobs, operations can adjust its production rates to get the work done more quickly. This increased production rate, set by operations, can then be used in the sales interface (described below) to re-price jobs to reflect any resulting changes in operational expenses. This dynamic job costing is done automatically within the prototype system.
* Which job should we start next? -- There are typically 70-100 ready jobs for the operator to choose from at any given moment. To aid the operator, a spreadsheet for operations contains a prototype order database and a series of calculated columns that yield a priority ranking for each job, according to the parameters specified in the Parameter Spreadsheet. The ranking methodology uses a minimum-slack-time scheduling heuristic that considers both duplicators and source machines. This order database may then be sorted to become a Hot List Spreadsheet of priority jobs to be posted or displayed on a computer monitor. Whereas we would have preferred to use a relational database of orders that could be linked to and analyzed, we found for the prototype that it was much easier just to manipulate directly the cells of the spreadsheet-type database. A major drawback to this manipulation in the prototype is that the user has to initiate a series of calculations and row sortings to generate a hot list. With several thousand cell calculations, this process can take over 15 minutes. Since an order database already exists and is in use, another drawback is that order information has to be entered.
The Hot List Spreadsheet for operations is integrated with management through the Parameter Spreadsheet. As the parameters are altered, the optimal batchsize printed on the hot list changes. Also, as the sales department accepts new orders, they may be entered at the bottom of this spreadsheet. The spreadsheet may then be recalculated to generate a new hot list. This is another way in which sales decisions are integrated with operational decisions.
SALES DECISIONS
* How much should we charge for a job? -- A sales spreadsheet provides a prototype of the primary sales interface. It is divided into three sections: Order description, Price breakdown and Commission. To generate a quote for a customer, the salesperson simply inputs the order information in the designated cells. Since the spreadsheets are dynamic in nature, the price breakdown automatically changes as one alters the order description. Fundamental engineering economy provides the pricing policy to never charge below marginal cost, avoid charging below total cost and seek to charge the suggested price. Marginal cost, total cost and price grids may also be printed and distributed to the sales force. Note that the cost of the job depends on parameters set in the Parameter Spreadsheet. As management and operations alter the cost and operational parameters, an altered price structure is automatically generated. A commission calculator is also provided to give the salesperson a simultaneous account of commission potential for a job.
* Can we meet the requested due date? -- After order parameters are entered into the spreadsheet, the system potentially could reference the order database stored in the Hot List Spreadsheet for operations and print "Accepted" if the due date is attainable. It would easily be possible to add a new macro to the macrosheet that could implement such a due-date checking algorithm. Such an algorithm would compare the required processing time against current source and duplicator loads. We did not implement this because we felt it was not critical in selling our system to managers. Also, we feared that such an algorithm would have substantially slowed down the prototype.
MANAGEMENT DECISIONS
* Should we buy more duplicators or source machines? -- This decision may be made by using a spreadsheet for management that computes source and duplicator rental costs. The computed rental and maintenance costs are linked into the corresponding cost cells in the Parameter Spreadsheet for use throughout the system. This spreadsheet for management works like a traditional spreadsheet for cost analysis, except for the dynamic linking into the Parameter Spreadsheet. After a new machine is added, new cost grids may be generated and compared to see the effect on job costs. Since additional duplicators would affect the optimal batchsize decisions in operations, it is possible that purchasing more machines may actually decrease job costs. Note that if the machines were purchased, the effect would be felt through the system in both the operations and sales departments. This effect is prototyped in our system.
* How many duplication workers should we employ? --By now it should be clear that a manager can alter the worker parameters in the Parameter Spreadsheet and note the effect on the various cost and batchsize grids. We found that in many cases it is actually less expensive per job to have more workers than current policy provides.
CONCLUSIONS
The system is a powerful prototype model of a large-scale enterprise system that integrates decision-making across departments. By dynamically linking spreadsheets around a centralized macrosheet and parameters database, one can perform dynamic job costing and batchsizing. A proposed system structure can be used to construct departmental spreadsheets that change in real-time as other departments make decisions. Such a prototype enterprise system may be used to demonstrate the benefits of such integrated decision-making. Computational sluggishness, limited database capability and awkward user-interfaces are disadvantages that must be dealt with by an engineer seeking to build a quick prototype enterprise system using spreadsheets.
Other investigators working with the authors have reached similar conclusions in different contexts. A prototype system was developed for dynamic lot-sizing decisions, and showed that Microsoft Excel for Windows allowed development of a user-interface and computation engine (1,000 lines of macro code) that were logically worthy of a full system and very impressive as a prototype. However, it was computationally slow and required manual data entry. An investigation of several uses of various spreadsheet products in prototype applications revealed similar findings. Spreadsheet-based prototypes can do a good job of demonstrating basic functionality at low development cost. They can do an excellent job of providing high-quality user-interfaces, but not at particularly low development cost; and they do not handle large amounts of data very well. For many potential enterprise-system applications whose basic value must be demonstrated to management before a full commitment can be achieved, we recommend consideration of spreadsheets as a low-cost prototype platform.
FOR FURTHER READING
Alcala, Fernando, Michael Bridges and Davy Widianto, "Investigating the Appropriateness of Spreadsheet Computing in Decision Support System Development: An Empirical Approach," May 1993, unpublished report.
Widianto, Da, "DLSP-Solver: An Excel Application to Solve Dynamic Lot-Sizing Problems," May 10, 1993, unpublished report.
Dan Adelman is a Ph.D. candidate in industrial engineering at the Georgia Institute of Technology, Atlanta, Ga. He is a member of IIE.
Donovan Young is associate professor of industrial and systems engineering at the Georgia Institute of Technology. He is a member of IIE.
FIGURE 2
SPREADSHEET ADVANTAGES
ADVANTAGES
* Effective selling tool
* Low cost
* Readily available commercial spreadsheets
* Ready-made user interface
* Flexibility to add new functions
* Ideal for closed -form analyses
* Dynamic linking of spreadsheets
* Quick development time
DISADVANTAGES
* Computationally slow
* Limited database capability
* Inflexible user-interface
* Hard to implement into daily operations.
My Research