LP Transportation Problem/Excel Solver. Attached you will find a copy of the case entitled Applichem. Read the one page case and answer the following 3 questions: a. (20 points). Evaluate the cost associated with the way Applichem's plant capacity is currently being used. b. (20 points). Determine the optimal use of Applichem's plant capacity using the Solver in Excel. c. (10 points). What would you recommend that Applichem management do? In your analysis for this third question, consider forcing any closed plants (from your optimal solution in part b) to be open, and re-evaluate the cost with this forcing constraint using Excel's Solver once again. What is the cost increase and what would you recommend? CASE: APPLICHEM - THE TRANSPORTAT 20 Applichem management is faced with the difficult problem of allocating to its customers the capacity of manufactur- ing plants that are located around the world. Management has long recognized that the manufacturing plants differ greatly in efficiency but has had little success in improving the operations of the ineffi- cient plants. At this time, management has decided to focus on how best to use the capacity of its plants given the differences in manu- facturing costs that currently exist. They recognize that this study may result in the significant reduction of output or possibly the shut- ting down of one or more of the existing plants. Applichem makes a product called Release-ease. Plastics mold- ing manufacturers use this chemical product. Plastic parts are made by injecting hot plastic into a mold made in the shape of the part. After the plastic has sufficiently cooled, the fresh part is removed from the mold and the mold is then reused to make subsequent parts. Release-ease is a dry powder, applied as part the manufac- turing process, that makes it easy to remove the part from the mold. Applichem has made the product since the early 1950s, and demand has been consistent over time. A recent study by Applichem's market research team has indicated that demand for Release-ease should be fairly steady for the next five years. Although Applichem does have some competition, particularly in the European markets, management feels that as long as they can provide a quality product at a competitive cost, customers should stick with Applichem. Release-ease sells at an average price of $1.00 per pound The company owns plants capable of making Release-ease in the following cities: Gary, Indiana; Windsor, Ontario, Canada; Frankfurt, Germany; Mexico City, Mexico; Caracas, Venezuela; and Osaka, Japan. Although the plants are focused on meeting demand for the immediate surrounding regions, there is consider- able exporting and importing of product for various reasons. The following table contains data on how demand has been met during the past year: PRODUCT MADE AND SHIPPED DURING PAST YEAR (x 100,000 POUNDS) FROM/TO UNITED MEXICO CANADA VENEZUELA EUROPE STATES JAPAN 3.0 6.3 7.9 2.6 Mexico City Windsor, Ontario Caracas Frankfurt Gary Osaka 4.1 5.6 20.0 12.4 14.0 4.0 Differences in the technologies used in the plants and in local raw material and labor costs created significant differences in the cost to produce Release-ease in the various locations. These costs may change dramatically due to currency valuation and labor law changes in some of the countries. This is especially true in Mexico and Venezuela. The capacity of each plant also differs at each loca- tion, and management has no interest in increasing capacity any. where at this time. The following table gives details on the costs to produce and capacity of each plant: TON PROBLEM PLANT PRODUCTION COSTS AND CAPACITY PLANT PRODUCTION COST (PER 1,000 LBS) PLANT CAPACITY (x100,000 LBS) 22.0 3.7 Mexico City Windsor, Ontario Caracas Frankfurt Gary Osaka 95.01 97-35 116.34 76.69 102.93 153.80 47.0 18.5 In considering how best to use the capacity of its plants, Applichem management needs to consider the cost of shipping prod- uct from one customer region to another. Applichem now common- ly ships product in bulk around the world, but it is expensive. The costs involved are not only the transportation costs but also import duties that are assessed by customs in some countries. Applichem is committed to meeting demand, though, and sometimes this is done even though profit might not be made on all orders. The following table details the demand in each country, the cost to transport product from each plant to each country, and the current import duty rate levied by each country. (These percentages do not reflect current duties.) Import duty is calculated on the approximate production plus transportation cost of product brought into the country. (For example, if the production and shipping cost for 1,000 pounds of Release-ease shipped into Venezuela were $100, the import duty would be $100 x .5 = $50.) TRANSPORTATION COST (PER 1,000 LBS), IMPORT DUTIES, AND DEMANDS FOR RELEASE-EASE UNITED PLANT/COUNTRY MEXICO CANADA VENEZUELA EUROPE STATES JAPAN o 11.40 11.00 14.00 7.00 9.00 11.00 6.00 11.00 o 7.00 10.00 Mexico City Windsor, Ontario Caracas Frankfurt Gary Osaka Total demand (x100,000 lbs) Import duty 10.40 11.20 11.50 13.00 o 10.00 14.20 10.00 11.50 6.00 13.00 2.6 10.00 13.00 14.30 13.30 12.50 0 12.50 11.00 12.50 16.0 O 14.00 13.00 26.4 3.0 20.0 11.9 0.0% 0.0% 50.0% 9.5% 4.5% 6.0%