Project 17: Shipping Costs One of the main products of the Fairway Woods Company is custom-made golf clubs. The clubs are manufactured at three plants (Denver, Colorado; Phoenix, Arizona; and Dallas, Texas) and are then shipped by truck to five distribution warehouses in Sacramento, California; Salt Lake City, Utah; Chicago, Illinois; Albuquerque, New Mexico; and New York City, New York. Since shipping costs are a major expense, management has begun an analysis to determine ways to reduce them. For the upcoming golf season, the output from each manufacturing plant and how much each warehouse will require to satisfy its customers have been estimated. The CIO from Fairway Woods Company has created a data file for you, AYK17_Data.xlsx, of the shipping costs from each manufacturing plant to each warehouse as a baseline analysis. Some busi- ness rules and requirements you should be aware of include: The problem presented, which involves the shipment of goods from three plants to five regional warehouses. wwledge Goods, which can be shipped from any plant to any warehouse, cost more to ship over long distances than over short distances. 1. Your goal is to minimize the costs of shipping goods from production plants to warehouses, thereby meeting the demand from each metropolitan area while not exceeding the supply avail- able from each plant. To complete this project, it is recommended that you use the Solver function in Excel to assist with the analysis. 2. Specifically, you want to focus on: Minimizing the total shipping costs. Total shipped being less than or equal to supply at a plant. Total shipped to warehouses being greater than or equal to the warehouse demand. Number to ship being greater than or equal to 0. Data file: AYK17_Data.xlsx A B C D 1 Fairways Woods Company Shipping Analysis 2 3 4 5 Plants 6 Denver 7 Phoenix 8 Dallas 9 Totals: 10 11 123 111 12 13 21 22 23 0 0 14 Demands by Whse --> 15 Plants 16 Denver 17 Phoenix 18 Dallas 19 Shipping: 20 Number to ship from plant to warehouse: Total Sacramento Salt Lake Chicago Albeq Supply 310 260 280 $83 $10 $6 $3 $19 $8 EA 180 80 200 160 Shipping costs from plant to warehouse: $6 $4 65 $5 EA CA un 00 E $4 $17 191 LL 0 $15 355 6353 $3 $5 0 $13 G New York 220 4539 $6 $19 0 H