80.2K

Verified Solution

Link Copied!

My excel doesn't work please help

TZ Edge - Golf Club Distribution Plan Plant Shipping Costs (per Case) Plant Number of Cases Shipped Demand Distribution Center Atlanta Baltimore Boise Dallas Denver Jacksonville New York City San Diego Total 215 80 315 65 145 225 135 1,310 Chicago $62.25 $98.75 $29.95 $89.00 $53.60 $86.65 $95.00 $53.10 St. Louis $31.50 $27.80 $75.00 $38.40 $50.00 $42.80 $33.90 $91.25 Kansas City $21.25 $49.50 $65.55 $25.85 $35.60 $26.20 $53.70 $67.95 Chicago 62 99 30 89 54 87 95 53 568 St. Louis 32 28 75 38 50 43 34 91 391 Kansas City 21 50 66 26 36 26 54 68 346 Total Number of Cases Shipped 115 176 171 153 139 156 183 212 1,305 Total Cost $5,318.88 $12,974.65 $10,818.81 $10,063.78 $6,640.32 $10,026.50 $13,057.90 $15,763.38 $84,664.21 Initial Inventory Remaining Inventory 525 43 475 84 375 29 1,375 70 Instructions: Use Solver to create a plan for shipping cases of golf clubs while minimizing the total cost according to the following constraints. Constraints: ***ALL of the golf clubs in inventory must be shipped. Remaining inventory should = 0. ***Total number of cases shipped to each distribution center must meet or exceed its demand. ***You cannot ship more cases from a plant than it has in the initial inventory. ***No partial cases and no "negative" cases may be shipped. Hint: Click on the red triangles for help. Questions to be answered What is the total cost to ship all golf clubs? How many total cases were shipped to Dallas? What is the total demand for all Distribution Centers? What is the total cost to ship to Baltimore? Which Distribution Center received more cases than demanded? How many cases did Kansas City ship to Dallas? What was TZ Edge's total cost for shipping cases from St. Louis? $ $ $ $ 84,664 153 1,310 12.975 cells in red require a formula for Solver to work properly cells in blue will be filled in by solver $ $ 26 391 Steps: In column I, Create a formula to Sum the Cases shipped in F through H In column J, Calculate the SUMPRODUCT to calculate the total costs of the cases. SUM row 13 (red cells) In row 16, calculate the remaining inventory (difference between row 13 and 15 red cells) Start Solver to minimize costs of shipping golf clubs (J13). TZ Edge - Golf Club Distribution Plan Plant Shipping Costs (per Case) Plant Number of Cases Shipped Demand Distribution Center Atlanta Baltimore Boise Dallas Denver Jacksonville New York City San Diego Total 215 80 315 65 145 225 135 1,310 Chicago $62.25 $98.75 $29.95 $89.00 $53.60 $86.65 $95.00 $53.10 St. Louis $31.50 $27.80 $75.00 $38.40 $50.00 $42.80 $33.90 $91.25 Kansas City $21.25 $49.50 $65.55 $25.85 $35.60 $26.20 $53.70 $67.95 Chicago 62 99 30 89 54 87 95 53 568 St. Louis 32 28 75 38 50 43 34 91 391 Kansas City 21 50 66 26 36 26 54 68 346 Total Number of Cases Shipped 115 176 171 153 139 156 183 212 1,305 Total Cost $5,318.88 $12,974.65 $10,818.81 $10,063.78 $6,640.32 $10,026.50 $13,057.90 $15,763.38 $84,664.21 Initial Inventory Remaining Inventory 525 43 475 84 375 29 1,375 70 Instructions: Use Solver to create a plan for shipping cases of golf clubs while minimizing the total cost according to the following constraints. Constraints: ***ALL of the golf clubs in inventory must be shipped. Remaining inventory should = 0. ***Total number of cases shipped to each distribution center must meet or exceed its demand. ***You cannot ship more cases from a plant than it has in the initial inventory. ***No partial cases and no "negative" cases may be shipped. Hint: Click on the red triangles for help. Questions to be answered What is the total cost to ship all golf clubs? How many total cases were shipped to Dallas? What is the total demand for all Distribution Centers? What is the total cost to ship to Baltimore? Which Distribution Center received more cases than demanded? How many cases did Kansas City ship to Dallas? What was TZ Edge's total cost for shipping cases from St. Louis? $ $ $ $ 84,664 153 1,310 12.975 cells in red require a formula for Solver to work properly cells in blue will be filled in by solver $ $ 26 391 Steps: In column I, Create a formula to Sum the Cases shipped in F through H In column J, Calculate the SUMPRODUCT to calculate the total costs of the cases. SUM row 13 (red cells) In row 16, calculate the remaining inventory (difference between row 13 and 15 red cells) Start Solver to minimize costs of shipping golf clubs (J13)

Answer & Explanation Solved by verified expert

See Answer