Refer to the ERP Decision case. Design a spreadsheet that will assist the Board in evaluating the net benefits of implementing the ERP system.

THE ERP DECISION" During the 1990s, many large companies began to realize that lack of integration among their information systems was leading to serious operational inefficiencies. Further- more, these inefficiencies were beginning to cause many companies to lose ground to other, better-organized firms. At the same time, enterprise resource planning (ERP) software, especially SAP R/3 (http://www.sap.com/), was reaching a high state of maturity as its penetration rate among the Fortune 1000 rose. The decision whether to convert to SAP (or a competing product) was a strategic one for many companies at this time, both because of the common software design, and it shares information as needed with the central database. While converting old systems to ERP is a massive undertaking, once it is accom- plished the firm has one common database, one common definition of business concepts, one central warehouse in which all information resides, and individual modules for each functional area that are compatible but can be upgraded independently. The Situation at Mega Corporation Mega Corporation has for many years been a dominant manufacturer in its industry. As a worldwide firm, it has four main manufacturing sites and sales offices spread across the world. Since most of the growth in the firm occurred in the 1970s and 1980s, before integrated firm- wide software was available, few of the company's infor- mation systems can communicate with each other. This lack of information integration is becoming an increasing burden on the firm. Each of the manufacturing sites has its own hardware and software, and none are linked electro- nically. As a consequence, much of the sharing of infor- mation that goes on among the manufacturing sites is done by telephone, fax, or memo. Each of the main sales offices has purchased and developed its own information systems, and these do not communicate with each other or with manufacturing. Again, this forces the sales offices to use telephone and faxes to share data. The accounting department is centralized at headquarters, but its software system does not interface with the others. Much of their time is spent manually transferring accounting data from the field into their central system. Purchasing is done by each of the manufacturing sites independently, and since their systems do not communicate, the firm cannot keep track of its purchases from single vendors and thus misses out on many discounts. This is just a sample of the problems that Mega suffers due to a lack of information integration. As these problems deepened, and the need for some centralized solution became more and more apparent, a conflict arose between the chief information officer (CIO) and the chief financial officer (CFO). The CIO wanted to install an integrated system immediately despite the costs and risks; the CFO wanted to kill any attempt to install this software. Here is a summary of the pros and cons of this decision, as expressed by the two executives. The Case for ERP The CIO argued that partial fixes to the company's current information systems were becoming more expensive and less effective every year. The conversion to ERP was inevi- table, so why not do it now? Once the system was up and running, the firm could expect to see lower inventories both of finished goods and raw materials. Finished goods inven- tories would be lower because Marketing and Manufactur- ing would be able to share common forecasts; raw materials inventories would be lower because Manufacturing would communicate its needs better to Purchasing, which would not have to maintain large stocks of raw materials to cover unexpected orders. In addition, Purchasing would be able to obtain quantity discounts from more vendors by pooling its orders from the various manufacturing sites. Sales would increase because, with better communication between high costs and risks of cost overruns (many SAP imple- mentations had failed or been far more costly than expected) and because of the high risks of not implement- ing integrated software. This case will allow you to explore the analysis done by one typical company for this decision. What is ERP software? An ERP system is companywide software that links all operations to a central database. ERP software is organized by module, one for each functional area such as Finance, Accounting, Manufacturing, Payroll, Human Resources, and so on. Each of these modules has a Marketing and Manufacturing, there would be fewer canceled orders, fewer late shipments, and more satisfied customers. Software maintenance costs would go down because the company would not have to maintain the old, nonintegrated software, much of which existed simply to allow one system to communicate with another. Decision making would also improve with the ERP system, because such basic information as current production costs at the product level would be available for the first time. Finally, once the basic ERP system was in place it would become possible to install more sophisticated software such as a customer-relationship management or CRM system. A CRM system sits on top of the ERP system, using its database to help answer questions such as "Are we making money selling products to our customers in the Northeast?" and "Is our sales force in East Asia fully productive?" The Case against ERP The case against ERP was made forcefully by the CFO. ERP hardware and software costs are high and must be paid in full before any benefits come in. ERP systems. change almost everyone's job, so the retraining costs are enormous. Some people will even leave the company rather than retrain on the new systems. No one within the company has any experience with ERP, so an expensive group of consultants must be hired over many years. Even after the consultants are gone, the company will have to hire a substantial number of highly trained and highly paid. systems people to maintain the ERP system. Improved decision making sounds valuable, but it is hard to quantify, and besides, if the company has as much difficulty as some firms have had implementing ERP, the "benefits" may well be negative! The only rational way to develop an understanding of the likely costs and benefits of implementing ERP, and perhaps to settle this argument, is to develop a model. You have been asked by the Board to do just that. Your model should be complete in that it accounts for all the possible costs and benefits from both an ERP system and from installing a CRM system on top of the ERP system. The model should be flexible, so that alternative assumptions can easily be tested. It should be robust, in that nonsensical inputs should be rejected. It should also provide insights, so that the Board can use it effectively to decide under what circumstances the ERP/CRM project would make sense. Some of the initial assumptions on which the Board would like the model to be built are described next. Assumptions First, the model should cover 20 years, from 2005-2024. Second, it should account for changes in sales (and reven- ues) from the ERP and CRM systems, as well as changes in inventories. Finally, it should include the costs of hardware, software, consultants, permanent employees, training of nonprogramming staff, and maintenance of old systems. Specific numerical assumptions follow: . Without ERP, sales are expected to hold steady at about $100 million per year over the next 20 years. Sales can be expected to grow about 1 percent/ye an ERP system is fully operational, which will years. 490