excel is preferred

Question 2 (30 points) The Maxpro Company has plants in two cities: Lawrence, KS and Osaka, Japan, and sells a specialty powdered chemical in 100-1b. bags. Since the chemicals produced at both plants are identical, either can be used to satisfy demand. Production cost per 100 lbs. in US dollars, is $25 in Lawrence and 3,960 Yen in Osaka. Production capacity is 9,000,000 lbs. in Lawrence and 7,000,000 lbs in Osaka, Maxpro ships to costumers from its DCs in Osaka (for Asian demand), Frankfurt (for European demand), and Lawrence (for US demand). Demand in each of the zones is as shown below: Lawrence Frankfurt Osaka Demand (in lbs.) 5,000,000 4,000,000 3,000,000 The retail price for a 100-1b, bag (expressed in local currency) is $100 in Lawrence, 6150 in Frankfurt, and 17.000 Yen in Osaka. If the chemical is shipped from Lawrence to Europe, it is sent via inland freight from Lawrence to the New York port, from New York (by ship) to Hamburg port, and then moved by inland freight from Hamburg to Frankfurt. Similarly, if Lawrence ships to Osaka, the chemical is shipped from Lawrence to the New York port, from New York (by ship) to the Japan port, and from Japan port via inland freight to the DC in Osaka, Similarly, goods from Osaka to Frankfurt would go via inland freight to the Japan port, from the port to Hamburg, and then via inland freight to Frankfurt. The cost of shipments by sea, in US dollars, is as shown below ($/100-Ib bag): Hamburg port New York port 0 40 Japan port 40 New York port Japan port 300 Inland shipments between the ports and the DC locations are shown below (S/100-1b bag): Inland Freight New York port Lawrence 5 Hamburg port Frankfurt 10 Japan Port Osaka 5 Shipments between countries incur duties as a percentage of value as shown in table below: FROM Lawrence Osaka Osaka To Frankfurt 25 200 Lawrence 0 10 Page 3 of 6 Please note that you should consider both a product when evaluating duties for the chemicals. note that you should consider both the transportation and production costs in the value of 1 Exchange rates are as follows ($1=114.36 Yen=0.94): USD TO Yen 11436 FROM USD Yen Euro Euro 0.94 0.0082 1 1 0.0087 106 12166 a) Provide a matrix of the profit in US dollars per 100 lbs. for production at each plant and sale at each of the DCs. [7 points] b) What will be the optimal solution that maximizes profits for Maxpro (i.e., from which plant to which DC and what quantity)? What is the associated total profit in US dollars for Maxpro? [8 points) A consultant suggests that due to strengthening dollar (i.e., the U.S. dollar has increased in value compared to other currencies), the exchange rates may change to those shown in the table below: USD Yen 165 1 FROM USD Yen Euro Euro 1.14 0.0069 0.0061 088 144.74 c) What is the impact of these new exchange rates on the total profits associated with the solution in part b (i.e., calculate the total profit without changing the allocation you found in part b and compare it to your final answer in part b)? [7 points) d) Given the new exchange rates above, what is the new optimal solution? What is the associated new profit in US dollars for Maxpro? Compare your answer to that of in part c. 18 points