HOMEWORK ASSIGNMENT 1 CASE 22: ASSESSING THE VALUE OF SUPPLY CHAIN MANAGEMENT OPTIMIZING SHIPMENTS One of the main proclucts 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: Albuquerque, New Mexico; Chicago, Illinois; and New York City, New York. Because shipping costs are a major expense, management is investigating a way to reduce them. For the upcoming golf season, an estimate las been created as to the total output needed from each manufacturing plant and how much each warehouse will require to satisfy its customers. The CIO from Fairway Woods Company has created a spreadsheet for you,FAIRWAYS.xls, of the shipping costs from each manufacturing plant to each warehouse as a baseline analysis. SOME PARTICULARS YOU SHOULD KNOW 1. The problem presented involves the shipment of goods from three plants to live regional warehouses. 2. Goods can be shipped from any plant to any warchouse, but it costs more to ship goods over long distances than over short distances, 3. The challenge presented is to determine the amounts to ship from each plant to each warehouse at a minimum total shipping cost in order to meet the regional demand, while not exceeding the plant supplies. 4. Specifically you need to focus on: a. Minimizing the total shipping cost. b. Total shipped must be less than or equal to supply at plant. c. Totals shipped to warehouses must be greater than or equal to demand at warehouses. d. Number to ship must be greater than or equal to 0. 5. File: FAIRWAYS.xls (Excel file). Please see the spreadsheet, fairways.xls with the information needed to complete this assignment. The objective is to be able to ship the following quantities to the warehouses in the most cost-effective (cheapest) manner from the three available plants: Qty. 180 Sacramento Qty. 80 Salt Lake City Qty. 200 Chicago Qty. 160 Albuquerque Qty. 220 New York City These are available from three plants with different shipping costs: Qty. 310 Denver Qty. 260 Phoenix Qty. 280 Dallas The problem is not tricky at all. We have 850 available and we need to ship 840. You have to come up with different combinations until you come up with the cheapest total shipping costs. As with any problem, there are various ways to solve a problem to get the desired result. I would get you started and then you need to work your way to find the solution. 1. Make all the "1s" in cells C6 to G8 zero. Which means, we haven't started shipping yet. 2. When you look at the costs, focus on the cheapest streams. For instance, it only costs $3.00 each from Dallas to Sacramento and only $3.00 each from Phoenix to Albuquerque. These I calll them as "low hanging fruits." 3. The shipping total in cell B18 is $0. This number will change once you start allocating from plants to warehouses. 4. Dallas has available a quantity of 280 and we need only 180 in Sacramento. Also, Phoenix has available a quantity of 260 and we need only 160 in Albuquerque. 5. Enter the value "180" in cell C8. Once you do that, the total in B18 would change to $540. 6. Enter the value "160" in cell F7. Once you do that, the total in B18 would change to $1,020. 7. You have satisfied the quantities needed in Sacramento and Albuquerque. Now, continue the process until all the warehouses have the required quantities. Just make sure that you do not take more than what's available in the 3 plants. Fairways Woods Company Shipping Analysis Plants Denver Phoenix Dallas Total 5 5 5 Number to ship from plant to warehouse: Sacramento Salt Lake Chicago Albeg New York 1 1 1 1 1 1 1 1 Totals: 3 3 3 3 3 220 Demands by Whse --> Plants Supply Denver 310 Phoenix 260 Dallas 280 180 80 200 160 Shipping costs from plant to warehouse: 10 6 5 6 5 4 3 3 4 5 5 41 6 9 Shipping: $83 $19 $17 $15 $13 $19 HOMEWORK ASSIGNMENT 1 CASE 22: ASSESSING THE VALUE OF SUPPLY CHAIN MANAGEMENT OPTIMIZING SHIPMENTS One of the main proclucts 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: Albuquerque, New Mexico; Chicago, Illinois; and New York City, New York. Because shipping costs are a major expense, management is investigating a way to reduce them. For the upcoming golf season, an estimate las been created as to the total output needed from each manufacturing plant and how much each warehouse will require to satisfy its customers. The CIO from Fairway Woods Company has created a spreadsheet for you,FAIRWAYS.xls, of the shipping costs from each manufacturing plant to each warehouse as a baseline analysis. SOME PARTICULARS YOU SHOULD KNOW 1. The problem presented involves the shipment of goods from three plants to live regional warehouses. 2. Goods can be shipped from any plant to any warchouse, but it costs more to ship goods over long distances than over short distances, 3. The challenge presented is to determine the amounts to ship from each plant to each warehouse at a minimum total shipping cost in order to meet the regional demand, while not exceeding the plant supplies. 4. Specifically you need to focus on: a. Minimizing the total shipping cost. b. Total shipped must be less than or equal to supply at plant. c. Totals shipped to warehouses must be greater than or equal to demand at warehouses. d. Number to ship must be greater than or equal to 0. 5. File: FAIRWAYS.xls (Excel file). Please see the spreadsheet, fairways.xls with the information needed to complete this assignment. The objective is to be able to ship the following quantities to the warehouses in the most cost-effective (cheapest) manner from the three available plants: Qty. 180 Sacramento Qty. 80 Salt Lake City Qty. 200 Chicago Qty. 160 Albuquerque Qty. 220 New York City These are available from three plants with different shipping costs: Qty. 310 Denver Qty. 260 Phoenix Qty. 280 Dallas The problem is not tricky at all. We have 850 available and we need to ship 840. You have to come up with different combinations until you come up with the cheapest total shipping costs. As with any problem, there are various ways to solve a problem to get the desired result. I would get you started and then you need to work your way to find the solution. 1. Make all the "1s" in cells C6 to G8 zero. Which means, we haven't started shipping yet. 2. When you look at the costs, focus on the cheapest streams. For instance, it only costs $3.00 each from Dallas to Sacramento and only $3.00 each from Phoenix to Albuquerque. These I calll them as "low hanging fruits." 3. The shipping total in cell B18 is $0. This number will change once you start allocating from plants to warehouses. 4. Dallas has available a quantity of 280 and we need only 180 in Sacramento. Also, Phoenix has available a quantity of 260 and we need only 160 in Albuquerque. 5. Enter the value "180" in cell C8. Once you do that, the total in B18 would change to $540. 6. Enter the value "160" in cell F7. Once you do that, the total in B18 would change to $1,020. 7. You have satisfied the quantities needed in Sacramento and Albuquerque. Now, continue the process until all the warehouses have the required quantities. Just make sure that you do not take more than what's available in the 3 plants. Fairways Woods Company Shipping Analysis Plants Denver Phoenix Dallas Total 5 5 5 Number to ship from plant to warehouse: Sacramento Salt Lake Chicago Albeg New York 1 1 1 1 1 1 1 1 Totals: 3 3 3 3 3 220 Demands by Whse --> Plants Supply Denver 310 Phoenix 260 Dallas 280 180 80 200 160 Shipping costs from plant to warehouse: 10 6 5 6 5 4 3 3 4 5 5 41 6 9 Shipping: $83 $19 $17 $15 $13 $19