Tool Depot is a U.S. home improvement retailer with two stores. Theycurrently have two suppliers for the

Fantastic news! We've Found the answer you've been seeking!

Question:

Tool Depot is a U.S. home improvement retailer with two stores. Theycurrently have two suppliers for the three products that they sell: wrenches, drills, and saws. Suppliers ship product directly to the store

Product Supplier
Wrench Supplier A
Saw Supplier A
Drill Supplier B

Supplier A-----ships to---------Store A & Store B
Supplier B-----ships to---------Store A & Store B

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.

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.

Supplier Hammer Cost

Supplier A $0.80
Supplier B $0.82

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.

EXCEL SHEET

Carrier Rates

Carrier Supplier Str Nbr Cost
X A 1 $ 2,540
X A 2 $ 1,640
X B 1 $ 1,200
X B 2 $ 1,200
Y A 1 $ 0.12
Y A 2 $ 0.07
Y B 1 $ 0.07
Y B 2 $ 0.06

Product Info

Product Id Product Desc Package Qty Weight Weight Unit of Measure

1 Wrench 1 2.2 LB
2 Saw 1 1.2 LB
3 Drill 1 8.3 LB
4 Hammer 1 2.0 LB

Summary Tab

Hammer Cost Per Unit Hammer Product Cost Transportation Cost Total Cost

Supplier A $0.80
Supplier B $0.82

Diff