USE EXCEL

Additional Information: a) Please round up to the following whole number when calculating the number of containers. For example, assume that Polaris would pay for a whole container, not a fraction. If the number of containers is 520.15; use 521 containers. b) Assume that goods imported from China are delivered directly to distribution centers. c) If you need lead time, please use the information below. China: Average Lead Time =25 days SD of lead time =9 days Mexico: Average Lead Time =2 days SD of lead time =2 days d) If you need C.S.L. (Cycle service level), let C.S.L. =97.725% e) Discount rate =8% ( given 7% in the textbook) Revised: Table 15-9 Labor Cost Assumptions (p 461) Monthly Wages Revised: Table 15-12 Exchange Rate History (P 463) Part 1: Provide everything to support your recommendation based on the information given. (e.g., if forecasting is used; please use HW-7-Answer) Yearly Total Expense (2011-2015): The total cost of the U.S., Mexico, and China without/With discounts are based on the forecast. Part 2: a) state your recommendation. b) If you have any considerations other than the information given to you to make a more accurate decision, please describe any further concerns and how you can use these additional considerations to make decisions. 1. Yearly Total Expense (2011-2015: Production + Labor + Transportation + Tariffs) The total cost of the U.S., Mexico, and China without discount is based on the forecast. Labor Cost: Safety Stock: 1 yr =5 days 52 weeks. Suppose 1yr=365 days; you will have a different number of safety stocks. Safety Stock: 1 yr =5 days 52 weeks. Suppose 1yr=365 days; you will have a different number of safety stocks. Production Cost including Safetv Stock (S.S.) Transportation Cost including S.S. Tariff (5\%) Total cost without applying a discount rate. N.P.V of the total cost with Discount Rate =8% N.P.V of the total cost with Discount Rate =8% 2. Recommendation based on N.P.V. 3. What other factors should Suresh Krishna and his team consider when recommending the manufacturing location