Question Solved1 Answer 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 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

IOS0NW The Asker · Advanced Mathematics

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
See Answer
Add Answer +20 Points
Community Answer
0YCTSQ The First Answerer
See all the answers with 1 Unlock
Get 4 Free Unlocks by registration

The question is incomplete since the number of orders for each product currently is not given. That must have been provided in the historical data sheet which is not shared in the question. However to explain the apporach, let us assume currently 100 units of Hammer are required to be shipped. ( the calculations and decision making will change with change in units but the approach should be the same) so, Hammer prod cost for Supplier A= 0.8*100= $80 and for B it is 0.82*100=$82 From Product Info table, total weight of 100 Hammers=2*100=200 LB Considering Supplier A, Using carrier X, cost to Store 1= $2540 and cost to store 2 =$1640 Using carrier Y, cost to Store 1= $0.12*200=$24 and cost to store 2=0.07*200=$14 So taking minimum in both the cases, total transportation cost for Supplier A= 24+14=$38 Hence total cost for selecting A = 80+38=$118 Now, Considering Supplier B, Using carrier X, cost to Store 1= $1200 and cost to store 2 =$1200 Using carrier Y, cost to Store 1= $0.07*200=$14 and cost to store 2=0.06*200=$12 So taking minim ... See the full answer