I need to optimize this process so that all demand is met, I am lost as to how to do this and am seeking professional help. This last screen shot is the work I have so far trying to figure out this process. Please help!!!

Case: Cox Cable and Wire Company Meredith Ceh breathed a sigh of relief. Finally, all the necessary figures seemed to be correctly in place, and her spreadsheet looked complete. She was confident that she could analyze the situation that John Cox had described, but she wondered if there were other concerns she should be addressing in her response. Mr. Cox, president of Cox Cable and Wire Company and grandson of the company's founder, had asked Meredith to come up with plans to support the preliminary contract he had worked out with Midwest Telephone Company. The contract called for delivery of 340 reels of cable during the summer. He was leaving the next day to negotiate a final contract with Midwest and wanted to be sure he understood all of the implications. According to Mr. Cox, he had been looking for a chance to become a supplier to a large company like Midwest, and this seemed to be the right opportunity. Demand from some of Cox Cable's traditional customers had slackened, and as a result, there was excess capacity during the summer. Nevertheless, he wanted to be sure that, from the start, his dealings with Midwest would be profitable, and he had told Meredith that he was looking for a profitability target of at least 25%. He also wanted her to confirm that there was sufficient capacity to meet the terms of the contract. He had quickly mentioned a number of other items, but those were secondary to profitability and capacity. Background The Cox Cable and Wire Company sold a variety of products for the telecommunications industry. At its Indianapolis plant, the company purchased uncoated wire in standard gauges, assembled it into multiwire cables, and then applied various coatings according to customer specification. The plant essentially made products in two basic families-standard plastic and high-quality Teflon. The two coatings came in a variety of colors, but these were changed easily 151 by introducing different dyes into the basic coating liquid. The production facilities at Indianapolis consisted of two independent process trains (semiautomated production lines), referred to as the general and national trains, after the companies that manufactured them. Both plastic-coated and the Teflon-coated cable could be produced on either process train; however, Teflon coating was a faster process due to curing requirements. Planning at Cox Cable was usually done on an annual and then a quarterly basis. The labor force was determined by analyzing forecast demand for the coming year, although revisions were possible as the year developed. Then, on a quarterly basis, more specific machine schedules were made up. Each quarter, the process trains were usually shut down for planned maintenance, but the maintenance schedules were determined at the last minute, after production plans were in place, and they were often postponed when the schedule was tight. Due to recent expansions, there was not much storage space in the plant. Cable could temporarily be stored in the shipping area for the purposes of loading trucks, but there was no space for cable to be stored for future deliveries. Additional inventory space was available at a nearby public warehouse. Meredith had become familiar with all of this information during her first week as a summer intern. At the end of the week, she had met with Mr. Cox, and he had outlined the Midwest contract negotiation. The Contract The preliminary contract was straightforward. Midwest had asked for the delivery quantities outlined in Exhibit 4.1. Prices had also been agreed on, although Mr. Cox had said he wouldn't be surprised to find Midwest seeking to raise the Teflon delivery requirements during the final negotiation. Exhibit 4.1 Contract Delivery Schedule and Prices Month Plastic Teflon June 50 30 July 100 60 August 50 50 Price $360 $400 Meredith had gone first to the Production Manager Jeff Knight for information about capacity. Jeff had provided her with data on production times (Exhibit 4.2), which he said were pretty reliable, given the company's extensive experience with the two process trains. He also gave her the existing production commitments for the summer months, showing the available capacity given in Exhibit 4.3. Not all of these figures were fixed, he said. Apparently, there was a design for a mechanism that could speed up the general process train. Engineers at Cox Cable planned to install this mechanism in September, adding 80 hours/month to capacity. We could move up our plans, so that the additional 80 hours would be available to the shop in August," he Jeff had provided her with data on production times (Exhibit 4.2), which he said were pretty reliable, given the company's extensive experience with the two process trains. He also gave her the existing production commitments for the summer months, showing the available capacity given in Exhibit 4.3. Not all of these figures were fixed, he said. Apparently, there was a design for a mechanism that could speed up the general process train. Engineers at Cox Cable planned to install this mechanism in September, adding 80 hours/month to capacity. We could move up our plans, so that the additional 80 hours would be available to the shop in August," he remarked. But that would probably run about $900 in overtime expenses, and I'm not sure if it would be worthwhile." Exhibit 4.2 Production Capabilities, in Hours per Reel Process Train Plastic Teflon General 2.0 National 2.5 2.0 Exhibit 4-3 Unscheduled Production Hours Month General National June 140 250 July 60 80 August 150 100 1.5 152 After putting some of this information into her spreadsheet, Meredith spoke with the plant's Controller, Donna Malone, who had access to most of the necessary cost data. Meredith learned that the material in the cables cost $160 per reel for the plastic-coated cable and $200 for the Teflon-coated cable. Packaging costs were $40 for either type of cable, and the inventory costs at the public warehouse came to $10 per reel for each month stored. That's if you can get the space," Donna commented. It's a good idea to make reservations a few weeks in advance; otherwise, we might find they're temporarily out of space." Donna also provided standard accounting data on production costs (Exhibit 4.4). According to Donna, about half of the production overhead consisted of costs that usually varied with labor charges, while the rest was depreciation for equipment other than the two process trains. The machine depreciation charges were broken out separately, as determined at the time the machinery was purchased. After putting some of this information into her spreadsheet, Meredith spoke with the plants Controller, Donna Malone, who had access to most of the necessary cost data. Meredith learned that the material in the cables cost $160 per reel for the plastic-coated cable and $200 for the Teflon-coated cable. Packaging costs were $40 for either type of cable, and the inventory costs at the public warehouse came to $10 per reel for each month stored. "That's if you can get the space," Donna commented. It's a good idea to make reservations a few weeks in advance; otherwise, we might find they're temporarily out of space." Donna also provided standard accounting data on production costs (Exhibit 4.4). According to Donna, about half of the production overhead consisted of costs that usually varied with labor charges, while the rest was depreciation for equipment other than the two process trains. The machine depreciation charges were broken out separately, as determined at the time the machinery was purchased. For example, the general process train originally cost $500,000 and, for tax purposes, had an expected life of 5 years, or about 10,000 hours; hence its depreciation rate of $50 per hour. Exhibit 4.4 Accounting Data for Production Cost Category General National Machine depr. $50.00 hour $40.00 hour Direct labor 16.00 16.00 Supervisor 8.00 8.00 Production Ohd 12.00 12.00 The Analysis Meredith was able to consolidate all of the information she collected into a spreadsheet (Exhibit 4-5). Making what she felt were reasonable assumptions about relevant cost factors, she was able to optimize the production plan, and she determined that it should be possible to meet the 25% profitability target. Nevertheless, there seemed to be several factors in it that were subject to change-things that had come up in her various conversations, such as maintenance, warehousing, and the possibility of modifying the contract. She expected that Mr. Cox would quiz her about all of these factors, and she knew it would be important for her to be prepared for his questions. 8 In hours per real Process Train Plastic General National ) 1 Teflon 2 2.5 1.5 2 2 3 4 5 5 7 Unsheduled production hours Month General National June 140 250 July 60 80 August 150 100 3 PG TG TG TN 3 June ) PG PN TG 1 47.5 2.5 2 2 2.5 3 4 Objective (Minimize Hours) 467.5 3 TN 30 1.5 July PN 0 2 0 TN 40 1.5 16 2.5 August PN 37.5 12.5 2 2.5 PG 20 2 50 1.5 0 2 2 2 RHS 2 1.5 2 1.5 140 60 150 250 2 1.5 2.5 2 2.5 2 2.5 LHS sign 140