PLEASE SHOW WORK USING EXCEL FORMULAS FOR THE EXCEL FILE SCREENSHOT REQUIREMENTS.

CASE STUDY Selecting Transportation Modes for China Imports Jackie Chen, vice president of China Imports, was look- times cheaper than air freight. Ocean shipping, bowever. ing to design a framework to select transportation modes took longer and was less reliable. The average lead time for various products imported from China to the United using ocean shipping was nine weeks, with a standard States. His basic eptions were to either use air freight of deviation of three weeks. ocean shipping in 20 -foot containers. Air freight was faster and more reliable, but ocean shipping was much Product Characteristics cheaper. He decided to evaluate the shipping decision Weekly demand for smariphones averaged 1,000 and for two very different product categories-consumer had a standard deviation of 400 . Each smarphone cost electronics, such as smartphones, and decorative hard- $300 and weighed 0.1kg. The typical life cycle for a ware, such as door handles and hinges. smarphone was about one year, it was critical to not lose China Imports provided a variety of products to its demand early in the life cycle because of a lack of prodcustomers from a warehouse near the port of Long uct availability. Weekly demand for decorative handware of 25 pereent on all imentory held and aimed to provide averaged 5,000 , with a standard deviation of 1,000 . Each 998 pereent cycle service level on its products. The high unit of decorative hardware cost $20 and weighed 1kg. level of service aligned with the high quality of products Decorative hardware tended to have a long life cycle-that the company imported the company was still selling door handles and hinges that were introduced more than a decade carlier. Transportation Options from China Air freight and ocean shipping were the two options Study Questions available to move product from China to the United 1. What is the annual cost of using air freight to import Sates. Air freight charged 510 per kilogram shipped and smartphones? What is the ansual cos of wing aceas ship: required a minimum shipment of 50kg. Besides being ping to import smartphones? 2. What is the anmasl cost of using air ficight to impont doofast, air freight was also quite reliable. The average lead rative hardware? What is the annual cost of eving oceas time on air freight was one weck, with a standard deviashipping to impert desorative hardware? tion of lead time of 0.2 weeks. Ocean shipping was much 3. What other factors should be considered in the chwice of cheaper and cost \$1,200 per 20-foot container. Given transportation mode? What mode would you recomment that each container could hold up to 15,000kg, the ship. for each product? Sugget a geneal framenokk that lackie ping cost per kilo by ocean was more than a hundred can use across all product categorien. CASE STUDY Selecting Transportation Modes for China Imports Jackie Chen, vice president of China Imports, was look- times cheaper than air freight. Ocean shipping, bowever. ing to design a framework to select transportation modes took longer and was less reliable. The average lead time for various products imported from China to the United using ocean shipping was nine weeks, with a standard States. His basic eptions were to either use air freight of deviation of three weeks. ocean shipping in 20 -foot containers. Air freight was faster and more reliable, but ocean shipping was much Product Characteristics cheaper. He decided to evaluate the shipping decision Weekly demand for smariphones averaged 1,000 and for two very different product categories-consumer had a standard deviation of 400 . Each smarphone cost electronics, such as smartphones, and decorative hard- $300 and weighed 0.1kg. The typical life cycle for a ware, such as door handles and hinges. smarphone was about one year, it was critical to not lose China Imports provided a variety of products to its demand early in the life cycle because of a lack of prodcustomers from a warehouse near the port of Long uct availability. Weekly demand for decorative handware of 25 pereent on all imentory held and aimed to provide averaged 5,000 , with a standard deviation of 1,000 . Each 998 pereent cycle service level on its products. The high unit of decorative hardware cost $20 and weighed 1kg. level of service aligned with the high quality of products Decorative hardware tended to have a long life cycle-that the company imported the company was still selling door handles and hinges that were introduced more than a decade carlier. Transportation Options from China Air freight and ocean shipping were the two options Study Questions available to move product from China to the United 1. What is the annual cost of using air freight to import Sates. Air freight charged 510 per kilogram shipped and smartphones? What is the ansual cos of wing aceas ship: required a minimum shipment of 50kg. Besides being ping to import smartphones? 2. What is the anmasl cost of using air ficight to impont doofast, air freight was also quite reliable. The average lead rative hardware? What is the annual cost of eving oceas time on air freight was one weck, with a standard deviashipping to impert desorative hardware? tion of lead time of 0.2 weeks. Ocean shipping was much 3. What other factors should be considered in the chwice of cheaper and cost \$1,200 per 20-foot container. Given transportation mode? What mode would you recomment that each container could hold up to 15,000kg, the ship. for each product? Sugget a geneal framenokk that lackie ping cost per kilo by ocean was more than a hundred can use across all product categorien