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