DePont is an international chemical company. One of its key products is release-ease: a chemical compound that helps items to release easily from metal molds after compression molding. Ease-release is sold as a dry powder at $200 per thousand lbs., the same price globally. The company operates three manufacturing facilities internationally: Mexico, China, and Frankfurt. These three facilities also represent the three key market regions: Americas (Mexico), Asia (China) and Europe (Frankfurt). Release-ease is a mature product with stable demand. The product is also patent protected, so DePont is essentially the monopolist in this market. The average annual sales for release-ease (in thousand lbs.) in the three regions are given in Table 1; the company cannot sell more than the annual sales forecast. Table 1 also shows the production cost per thousand lbs. (in \$) and the annual production capacity of the production facility in that region. DePont feels that it is reasonable to assume to have the same sales for next year. The company uses the product produced in a year to meet the demand in that year only. Also let's assume that production and shipment are instantaneous (to make things simple) so any quantity produced in a region in a year can be used to meet demand of any region in the same year. DePont may use the release-ease produced in a region to meet the demand in other region. For example, ease-release produced in the Mexican facility can be shipped to the Chinese facility to meet the demand in Asia. In that case, the product is subject to transportation cost and customs duties. The following table shows the transportation costs (\$) per thousand lb. from a region (facility) to another. Customers pay for transportation from each facility so the only transportation cost on DePont is the one between its facilities. Table 2: Transportation cost (\$) per thousand lb. International shipment also incurs customs duties. The importation tax for each country is as follows. Table 3: Import customs duties For example, suppose 2,000lb. of ease-release is produced in Mexico and shipped to China, then the total cost would be ($112(1+20%)+$7)2=$282.8 For another example, suppose 3,000 lb. of ease-release is produced in Mexico for the Americas market, then the total cost would simply be 3$112=$336 since there is no customs duty or transportation cost. The company wonders what would be the best way to produce and ship the product so that its profit is maximized in a year? Specifically, how much Release-ease should be produced from each facility and how much should be shipped from one facility to another? Build a spreadsheet model in Excel and solve this problem. You do NOT need to provide a mathematical formulation for this problem. DePont is an international chemical company. One of its key products is release-ease: a chemical compound that helps items to release easily from metal molds after compression molding. Ease-release is sold as a dry powder at $200 per thousand lbs., the same price globally. The company operates three manufacturing facilities internationally: Mexico, China, and Frankfurt. These three facilities also represent the three key market regions: Americas (Mexico), Asia (China) and Europe (Frankfurt). Release-ease is a mature product with stable demand. The product is also patent protected, so DePont is essentially the monopolist in this market. The average annual sales for release-ease (in thousand lbs.) in the three regions are given in Table 1; the company cannot sell more than the annual sales forecast. Table 1 also shows the production cost per thousand lbs. (in \$) and the annual production capacity of the production facility in that region. DePont feels that it is reasonable to assume to have the same sales for next year. The company uses the product produced in a year to meet the demand in that year only. Also let's assume that production and shipment are instantaneous (to make things simple) so any quantity produced in a region in a year can be used to meet demand of any region in the same year. DePont may use the release-ease produced in a region to meet the demand in other region. For example, ease-release produced in the Mexican facility can be shipped to the Chinese facility to meet the demand in Asia. In that case, the product is subject to transportation cost and customs duties. The following table shows the transportation costs (\$) per thousand lb. from a region (facility) to another. Customers pay for transportation from each facility so the only transportation cost on DePont is the one between its facilities. Table 2: Transportation cost (\$) per thousand lb. International shipment also incurs customs duties. The importation tax for each country is as follows. Table 3: Import customs duties For example, suppose 2,000lb. of ease-release is produced in Mexico and shipped to China, then the total cost would be ($112(1+20%)+$7)2=$282.8 For another example, suppose 3,000 lb. of ease-release is produced in Mexico for the Americas market, then the total cost would simply be 3$112=$336 since there is no customs duty or transportation cost. The company wonders what would be the best way to produce and ship the product so that its profit is maximized in a year? Specifically, how much Release-ease should be produced from each facility and how much should be shipped from one facility to another? Build a spreadsheet model in Excel and solve this problem. You do NOT need to provide a mathematical formulation for this
