# Question Solved1 AnswerHistorical Orders: Order Dt Str Nbr Supplier Product Id Order Qty Product Weight Order Weight 8/4/2014 1 A 1 4,338 2.2 9,544 8/4/2014 1 A 2 5,006 1.2 6,007 8/4/2014 2 A 1 3,600 2.2 7,920 8/4/2014 2 A 2 5,173 1.2 6,208 8/4/2014 1 B 3 1,890 8.3 15,687 8/4/2014 2 B 3 2,536 8.3 21,049 8/11/2014 1 A 1 4,598 2.2 10,116 8/11/2014 1 A 2 5,463 1.2 6,556 Historical Shipments: Shipment Date Str Nbr Supplier Weight 8/4/2014 1 A 15,551 8/4/2014 1 B 15,687 8/4/2014 2 A 14,128 8/4/2014 2 B 21,049 8/11/2014 1 A 16,671 8/11/2014 1 B 14,425 8/11/2014 2 A 13,172 8/11/2014 2 B 16,733 8/18/2014 1 A 15,800 8/18/2014 1 B 14,791 8/18/2014 2 A 11,660 Can anyone fill out the summary tab with detailed explanation of steps and approach to the solution Tool Depot is a U.S. home improvement retailer with two stores. They currently have two suppliers for the three products that they sell: wrenches, drills, and saws. Suppliers ship product directly to the stores. Product Supplier Wrench Supplier A Saw Supplier A Supplier B Drill Supplier Supplier A Supplier B Supplier A Supplier B \$0.80 \$0.82 Store 1 Assume today is 7/30/2015. Beginning 8/3/2015, Tool Depot would like to start selling hammers. Both Supplier A and Supplier B make hammers and have submitted bids to supply hammers to Tool Depot. The hammers from both suppliers will be identical. The costs provided are for the product only and Tool Depot must pay for shipping costs from the supplier to their stores. Both suppliers would ship the hammers with their existing items. Hammer Cost Store 2 For shipping, Tool Depot has two choices in carriers from each supplier and can use one or both carriers each week. They always use the cheapest carrier for a given origin-destination pair • Carrier X charges a flat rate per shipment for each origin-destination pair • Carrier Y charges a variable rate per pound for each origin-destination pair. . Both carriers can fit up to 44,000 pounds per shipment. Tool Depot expects that hammer orders will be the same as wrench orders. Tool Depot also expects that orders for all of its products will grow 10% year over year. Tool Depot must decide which supplier to choose for supplying its hammers for the next year They would like to use the same supplier for both their stores. To determine which supplier they should choose, calculate annual product costs and annual transportation costs under each supplier scenario. For transportation, calculate your annual costs by modeling out the transportation costs by week, choosing the cheapest transportation carrier for each week. Please fill out the relevant costs in the "Summary" tab of the Excel file provided, and be prepared to discuss the problem in the debrief session at the end of this exercise. "Summary Tab": Supplier A \$ Supplier B \$ Diff Product Info: Product Id X X X Carrier Rates: XX X Y Y Hammer Cost Per Unit Y Y 1 Wrench 2 Saw 3 Drill 4 Hammer Carrier Supplier Str Nbr Cost 1 \$2,540 2 \$1,640 Product Desc Qty A A 0.80 0.82 BBA ABB Hammer Product Cost Package 1 1 1 1 Weight Measure 1 \$1,200 2 \$1,200 1 \$0.12 2 \$0.07 1 2 \$0.06 Transportation Cost \$ 0.07 Weight Unit of 2.2 LB 1.2 LB 8.3 LB 2.0 LB Total Cost

Historical Orders:

 Order Dt Str Nbr Supplier Product Id Order Qty Product Weight Order Weight 8/4/2014 1 A 1 4,338 2.2 9,544 8/4/2014 1 A 2 5,006 1.2 6,007 8/4/2014 2 A 1 3,600 2.2 7,920 8/4/2014 2 A 2 5,173 1.2 6,208 8/4/2014 1 B 3 1,890 8.3 15,687 8/4/2014 2 B 3 2,536 8.3 21,049 8/11/2014 1 A 1 4,598 2.2 10,116 8/11/2014 1 A 2 5,463 1.2 6,556

Historical Shipments:

 Shipment Date Str Nbr Supplier Weight 8/4/2014 1 A 15,551 8/4/2014 1 B 15,687 8/4/2014 2 A 14,128 8/4/2014 2 B 21,049 8/11/2014 1 A 16,671 8/11/2014 1 B 14,425 8/11/2014 2 A 13,172 8/11/2014 2 B 16,733 8/18/2014 1 A 15,800 8/18/2014 1 B 14,791 8/18/2014 2 A 11,660

Can anyone fill out the summary tab with detailed explanation of steps and approach to the solution

Transcribed Image Text: Tool Depot is a U.S. home improvement retailer with two stores. They currently have two suppliers for the three products that they sell: wrenches, drills, and saws. Suppliers ship product directly to the stores. Product Supplier Wrench Supplier A Saw Supplier A Supplier B Drill Supplier Supplier A Supplier B Supplier A Supplier B \$0.80 \$0.82 Store 1 Assume today is 7/30/2015. Beginning 8/3/2015, Tool Depot would like to start selling hammers. Both Supplier A and Supplier B make hammers and have submitted bids to supply hammers to Tool Depot. The hammers from both suppliers will be identical. The costs provided are for the product only and Tool Depot must pay for shipping costs from the supplier to their stores. Both suppliers would ship the hammers with their existing items. Hammer Cost Store 2 For shipping, Tool Depot has two choices in carriers from each supplier and can use one or both carriers each week. They always use the cheapest carrier for a given origin-destination pair • Carrier X charges a flat rate per shipment for each origin-destination pair • Carrier Y charges a variable rate per pound for each origin-destination pair. . Both carriers can fit up to 44,000 pounds per shipment. Tool Depot expects that hammer orders will be the same as wrench orders. Tool Depot also expects that orders for all of its products will grow 10% year over year. Tool Depot must decide which supplier to choose for supplying its hammers for the next year They would like to use the same supplier for both their stores. To determine which supplier they should choose, calculate annual product costs and annual transportation costs under each supplier scenario. For transportation, calculate your annual costs by modeling out the transportation costs by week, choosing the cheapest transportation carrier for each week. Please fill out the relevant costs in the "Summary" tab of the Excel file provided, and be prepared to discuss the problem in the debrief session at the end of this exercise. "Summary Tab": Supplier A \$ Supplier B \$ Diff Product Info: Product Id X X X Carrier Rates: XX X Y Y Hammer Cost Per Unit Y Y 1 Wrench 2 Saw 3 Drill 4 Hammer Carrier Supplier Str Nbr Cost 1 \$2,540 2 \$1,640 Product Desc Qty A A 0.80 0.82 BBA ABB Hammer Product Cost Package 1 1 1 1 Weight Measure 1 \$1,200 2 \$1,200 1 \$0.12 2 \$0.07 1 2 \$0.06 Transportation Cost \$ 0.07 Weight Unit of 2.2 LB 1.2 LB 8.3 LB 2.0 LB Total Cost
More
Transcribed Image Text: Tool Depot is a U.S. home improvement retailer with two stores. They currently have two suppliers for the three products that they sell: wrenches, drills, and saws. Suppliers ship product directly to the stores. Product Supplier Wrench Supplier A Saw Supplier A Supplier B Drill Supplier Supplier A Supplier B Supplier A Supplier B \$0.80 \$0.82 Store 1 Assume today is 7/30/2015. Beginning 8/3/2015, Tool Depot would like to start selling hammers. Both Supplier A and Supplier B make hammers and have submitted bids to supply hammers to Tool Depot. The hammers from both suppliers will be identical. The costs provided are for the product only and Tool Depot must pay for shipping costs from the supplier to their stores. Both suppliers would ship the hammers with their existing items. Hammer Cost Store 2 For shipping, Tool Depot has two choices in carriers from each supplier and can use one or both carriers each week. They always use the cheapest carrier for a given origin-destination pair • Carrier X charges a flat rate per shipment for each origin-destination pair • Carrier Y charges a variable rate per pound for each origin-destination pair. . Both carriers can fit up to 44,000 pounds per shipment. Tool Depot expects that hammer orders will be the same as wrench orders. Tool Depot also expects that orders for all of its products will grow 10% year over year. Tool Depot must decide which supplier to choose for supplying its hammers for the next year They would like to use the same supplier for both their stores. To determine which supplier they should choose, calculate annual product costs and annual transportation costs under each supplier scenario. For transportation, calculate your annual costs by modeling out the transportation costs by week, choosing the cheapest transportation carrier for each week. Please fill out the relevant costs in the "Summary" tab of the Excel file provided, and be prepared to discuss the problem in the debrief session at the end of this exercise. "Summary Tab": Supplier A \$ Supplier B \$ Diff Product Info: Product Id X X X Carrier Rates: XX X Y Y Hammer Cost Per Unit Y Y 1 Wrench 2 Saw 3 Drill 4 Hammer Carrier Supplier Str Nbr Cost 1 \$2,540 2 \$1,640 Product Desc Qty A A 0.80 0.82 BBA ABB Hammer Product Cost Package 1 1 1 1 Weight Measure 1 \$1,200 2 \$1,200 1 \$0.12 2 \$0.07 1 2 \$0.06 Transportation Cost \$ 0.07 Weight Unit of 2.2 LB 1.2 LB 8.3 LB 2.0 LB Total Cost