Please help me answering

a)

b) (Please show this answer in excel showing steps and formulas)

\f376 CHAPTER 7 Linear Optimization FIGURE 7.17 VP The Manufacturing ENG. organization at DEC. & MFG VP SALES & SERVICE VP MFG. US EUR EXTERNAL CHIP MEMORY COMM. DISK SYS PROCUREMENT MFG. MFG. & NAC MFG. MFG. MFG US INTL MFG. EUR MFG INT'L MFG. SUPPLY REVENUE P&L separately as "add-ons" to customers' computer systems (such as extra hard disks or ad- ditional memory) or are used by the in-house computer System manufacturers. The com- puter System groups manufacture completely configured computer systems for sale to customers. The Geographic Manufacturing groups are responsible for generating shipments to customers. They are the interface between Field groups and Manufacturing groups. It is their job to ensure that demand and supply are in balance and that or- ders are properly filled. The Sales & Services organization (Field) generates sales and is responsible for the company P&L (profit and loss). The Corporate Demand/Supply organization has an oversight role and ensures integration across the diverse Manufacturing and Field organizations, Background A new family of general purpose computer systems and workstations was an- nounced in Q2-1988 (i.e., second quarter, 1988). (See Table 7.15 for a brief descrip tion.) This family represented a significant price/performance improvement over the existing products. Customer acceptance of the new product family was very strong. Shipments of this new family of products had commenced in Q3-1988 and ramped slower than planned during the current quarter, Q4-1988. Two problems pre- vented supply from meeting overall demand. Chip supply from the in-house sup- plier had been limited as the chip manufacturing group worked to de-bug the new chip process technology. Similarly, the in-house disk drive manufacturing group was7.9 Case Modules 377 TABLE 7.15 GP-1 High-end general purpose computer system. Large memory, disk storage, and Product description expansion capability. GP-2 Intermediate general purpose computer system. Moderate memory, disk storage, and of new family of expansion capability. products. GP-3 Interim replacement for GP-2 using older disk storage devices. WS-1 High-end workstation based on the GP-2 system, WS-2 Entry-level workstation using older disk storage devices and enclosure. Limited memory and expansion capability. TABLE 7.16 System List Price Revenue for each GP-1 $60,000 system. GP-2 $40,000 GP-3 $30,000 WS-1 $30,000 WS-2 $15,000 . List price is an average across the 3 major Geographies. Effect of varying memory and new disk content on list price and mark-up is negligible. . Allowances and discounts are roughly the same across the products and Geographies. . Mark-up percentages are roughly the same across the products and Geographies. * Most costs can be considered fixed over the one quarter time-frame. TABLE 7.17 System # CPU Chip Set CPU chip set GP-1 requirements. GP-2 GP-3 WS-1 WS-2 . Chip Manufacturing is a long lead time process. Chip supply is fixed at 7,000 sets for Q1-1989 with no possibility of an increase. This volume was based on prior market sizing estimates. de-bugging the new state-of-the-art disk manufacturing process, While it wasn't un- usual for problems to exist during the initial phases of a new product ramp, the an- ticipated continuation of these problems into Q1-1989 was becoming a serious concern. The Revenue managers (whose job is to ensure that supply of products is delivered to meet demand) had responded to the shortages in a simple, straightforward way in Q3- 1988. On the supply side, the majority of the scarce CPU chips were used to build the more valuable general purpose computer systems. (See Table 7.16 and Table 7.17 for product list price and chip set requirements.) On the demand side, customers were en- couraged to order these more valuable systems through short-term marketing programs. At least one of the new type of disk drives is required for the GP-2 and WS-1 sys- tem (see Table 7.18) and is a popular "add-on" in extra numbers on these two systems as well as the GP-1 system. (See Table 7.18. For example, the average demand for the new disk drive in the GP-2 system is 1.7 drives per computer system.) Due to the scarcity of these disk drives, the placement of disk drives were reduced as shown in the third column of Table 7.18 with a commitment to those customers who wanted ad- ditional add-ons to provide the additional drives when these restrictions were lifted. Several new developments occurred in the middle of Q4-1988 that had a further impact on supply of systems in Q1-1989. The in-house disk supplier reduced com-\f7.9 Case Modules 379 TABLE 7.19 System 256K Boards Per System 1-Meg Boards Per System Memory usage of GP-1 DRAMS. GP-2 GP-3 WS-1 WS-2 - I . Use of 1-meg DRAM boards on GP-1 has been technically qualified but not yet announced An announcement could be made quickly. - . 1-meg DRAM boards will not be technically qualified on the other systems until Q2-1969, * 256K DRAMs are used on roughly a dozen different steady state memory and system products. A 10% shortfall in Q1-1989 on a base of several million DRAMs is expected. Assume enough sets of 256K DRAMs will be available to support 10-15,000 memory boards. . 1-meg DRAMs are used on several new memory and system products and are in tight supply. Assume enough sets of 1-meg DRAMs will be available to support 4,000 1-meg boards. TABLE 7.20 System Estimated Maximum Q1-1989 Customer Demand Demand mix. GP-1 1,800 GP-3 300 GP FAMILY 3,800 WS FAMILY 3,200 . GP-1 demand capped due to market saturation in the prior two quarters. . GP-3 is a temporary fill-in due to lack of GP-2 availability. Demand is limited. . Total GP and WS family market size is based on previous market studies, though lack of supply in the prior two quarters could distort demand. TABLE 7.21 System Estimated Minimum Acceptable Supply Customer GP-2 500 satisfaction data. WS-1 500 WS-2 400 . Supply had been skewed to the higher valued general purpose systems in the prior two quarters. Key customers are demanding that their workstation orders be filled as well. Probably the most important uncertainties are the availability of the new disks and the availability of 256K DRAM boards. From the notes in Table 7.18, disk unit availability will be in the range of 3,000-7,000 units. From the notes in Table 7.19, 256K DRAM board availability will be in the range of 10,000-15,000 units. Assignment: (a) Formulate a linear optimization model that describes DEC's manufacturing problem. (b) The spreadsheet DEC.XLS contains various data for this case. Using the data in this spreadsheet, construct a spreadsheet version of your linear optimization model and solve the model for the optimal solution. (c) Solve other versions of the model for different assumptions on the data values and to help analyze some of the key issues in the case. What conclusions can you draw? (d) Prepare a brief (two pages or less) but pointed memo of summary recommenda- tions for action addressed to the manufacturing staff.Resource Product GP-1 GP-2 GP-3 WS-1 WS-2 Chip Sets 1-Meg Memory 2 256K Memory 4 2 2 Disk Drive Average (Constrained) 0 Disk Drive Average (Unconstrained) 0.3 1.7 1.4 List Price ($1,000) $60 $40 $30 $30 $15 Resource Policies/Limits Availability Chip Sets 7,000 1-Meg Memory 4,000 256K Memory 15,000 Disk Drive 3,000 Minimum Maximum GP-1 Demand 1,800 GP-2 Demand 500 GP-3 Demand 300 WS-1 Demand 500 WS-2 Demand 400 GP Family Demand 3,800 WS Family Demand 3,200