Expendable vs Recyclable Questions (Group Assignment) EZ Truck Mfg is evaluating whether to continue using corrugated paper containers or reusable plastic containers. They produce 400 TXX trucks a day, 255 days a year. Each person should create their own spreadsheet This assignment can take students as much as 3-4 hours to do. This is a group assignment, and a learning exercise so I would like it if some students show others how to do things like vlookup tables and other advanced Excel tricks. But everyone should turn in their own spreadsheets. The deliverable is one table giving the costs of each alternative in a spreadsheet that has all the computations showing how the answers was derived (totals, subtotals for all costs associated with the trucking company, Transfreight, Suppliers, who provide the containers) of each alternative and your recommendation. You are to calculate the annual cost of containers and the annual cost of shipping for each alternative. Determine how many years must the reusable containers last to be make it cost-competitive with the corrugated. Upload spreadsheets showing your work. On your paper identify your name and Case Set (A, B, C, D, E, F) John Doe Case Set E Alt 1 Alt 2 Transfreight XXX Suppliers Container Cost XXX Total XXX XXX Each member of a group is to be assigned a different Case Set- A, B, C etc with its corresponding variable X1, X2, X3 Table 1-values to be used by different members of each group Case Set X1 X2 X3 A 30 0.90 B 50 1.00 0.30 0.50 XXX XXX CDEF 0.70 70 0.90 90 1.10 110 1.20 120 Make following changes to the Table 2 Table 2 Shipping Container Comparisons Container Type Cost per Unit Expendable Cost per Unit Returnable 1 $X1 $50.00 2 0.60 $X2 3 0.75 80.00 4 3.00 110 Also in Alternative 2 the cost to Transfreight to run the designated milk runs is to be changed from $1.00 per mile to $ X3. The Alternative 1 run is at $1.30/mile Looking at Table 3 below, one sees the supplier information, the part supplied, the number of parts per truck, and the number of parts that can fit in each container. The new reusable plastic containers will be designed to be identical in carrying capacity as the current corrugated paper containers. As an example, looking at the first line item, one sees that it is shocks supplied by Bluegrass Industry. Each TXX requires four shocks. It uses Container Type 3, and eight shocks can fit in each Container Type 3. In using corrugated boxes, the trucking firm picks up the parts from Bluegrass Industries (see table 4) and transports them to EZ multiple times daily. The boxes are disposed of after use. For the reusable plastic boxes, a milk run is used. The reusable plastic boxes flow continuously within the milk run (see Table 5). On Route 2, a truck would leave from EZ with some empty Containers of Type 1, 2, and 3 and deliver some of them to KY Industry and pick up the waiting full Containers of parts. Then it will go to Bluegrass Industries, and do the same thing and then deliver the full Containers to EZ. On this Route 2, the truck will repeat this process twice more in the day. At the end of the day, the truck goes back to the depot. The next day the cycle starts again at EZ, then Kentucky Industries, then Bluegrass Industries then EZ. For Route 2, this occurs three times a day. 1.10 1.20 1.30 1.40 The value of the column "Days in Shipping Loop" in Table 3, represents the approximate number of containers needed to keep the daily requirement of shocks circulating in this manner. To provide concrete numbers, EZ produces 400 trucks a day, 255 days a year. It need 4x400 or 1600 shocks a day. At 8 pieces/container, it needs 200 of container Type 3. Imagine a set of containers sitting being unloaded and circulating by the assembly line at EZ, and at the same time one set sits empty at the EZ dock awaiting pickup, one set is on the truck going to Bluegrass Industries, and another set is sitting being unloaded at Bluegrass Industries and one there awaiting pickup. There would be multiple runs on the route during the day, at staggered time, so EZ and its suppliers would be receiving three deliveries a day. Thus it may require 5.5 sets on any given day to circulate the daily requirement of shocks. That is the equivalent of requiring 5.5 days for each container to make the full circuit. Table 3 Supplier Information on Parts and Container Types (you should be able to copy these table values off the pdf.) Pieces/ Truck Container Pieces/ Type Supplier Location Parts Supplied Container Bluegrass Ind. Lexington 4 3 8 KY 1 3 5 1 3 5 24 1 100 Blues, Inc. St. Louis 1 8 12 KY Ind. 50 Frankfort KY 6 12 6 32 MO Ind. Fenton 12 MO 4 4 20 12 Reds, Inc. Cincinnati, 5 Shocks Steering column Steering wheel Lugnuts Stereo system (tuner/CD/cass.) Stereo speakers Instrument panel molding Interior quarter trim (pillars) Interior door handles Exterior door mirror Interior rearview mirror Over-fender plastic Glovebox Center console Fuel tank protector Fuel tank cap Front grill L4L2 1 2 2 1 4 1 1 1 1 1 34 1 2 2 3 3 4 4 3 3 2 4 Days in Shipping Loop 5.5 5.75 5.75 5.5 6.5 5.5 7.5 5.5 6.5 6.5 7.5 5.5 5.5 5.5 6.5 6.5 5.75 OH Passenger side airbag Shoulder-strap seatbelts Table 4 Expendable Shipping frequency and distances Mileage to EZ Shipments per day to EZ Supplier Bluegrass Ind. 192 3 Blues, Inc. 174 3 KY Ind. 170 3 MO Ind. 195 3 Reds, Inc. 223 2 Table 5 Reusable Container Frequency and Distance Stop 1 (cumulative) Stop 2 (cumulative) Stop 3 (cumulative) Origin EZ Route 1 Missouri Ind. EZ Blues, Inc. St. Louis, Princeton, Fenton, MO IN 174 miles 253 miles 448 miles Bluegrass Route 2 EZ Ind. EZ Ky Ind. Frankfort, KY Princeton, Lexington, KY IN 170 miles 195 miles 387 miles Route 3 EZ EZ Reds, Inc. Cinti, OH Princeton, IN 223 miles 446 miles 2 3 Nw 2 Frequency of Route 3/day 3/day 2/day 6 5.5 5.5 Expendable vs Recyclable Questions (Group Assignment) EZ Truck Mfg is evaluating whether to continue using corrugated paper containers or reusable plastic containers. They produce 400 TXX trucks a day, 255 days a year. Each person should create their own spreadsheet This assignment can take students as much as 3-4 hours to do. This is a group assignment, and a learning exercise so I would like it if some students show others how to do things like vlookup tables and other advanced Excel tricks. But everyone should turn in their own spreadsheets. The deliverable is one table giving the costs of each alternative in a spreadsheet that has all the computations showing how the answers was derived (totals, subtotals for all costs associated with the trucking company, Transfreight, Suppliers, who provide the containers) of each alternative and your recommendation. You are to calculate the annual cost of containers and the annual cost of shipping for each alternative. Determine how many years must the reusable containers last to be make it cost-competitive with the corrugated. Upload spreadsheets showing your work. On your paper identify your name and Case Set (A, B, C, D, E, F) John Doe Case Set E Alt 1 Alt 2 Transfreight XXX Suppliers Container Cost XXX Total XXX XXX Each member of a group is to be assigned a different Case Set- A, B, C etc with its corresponding variable X1, X2, X3 Table 1-values to be used by different members of each group Case Set X1 X2 X3 A 30 0.90 B 50 1.00 0.30 0.50 XXX XXX CDEF 0.70 70 0.90 90 1.10 110 1.20 120 Make following changes to the Table 2 Table 2 Shipping Container Comparisons Container Type Cost per Unit Expendable Cost per Unit Returnable 1 $X1 $50.00 2 0.60 $X2 3 0.75 80.00 4 3.00 110 Also in Alternative 2 the cost to Transfreight to run the designated milk runs is to be changed from $1.00 per mile to $ X3. The Alternative 1 run is at $1.30/mile Looking at Table 3 below, one sees the supplier information, the part supplied, the number of parts per truck, and the number of parts that can fit in each container. The new reusable plastic containers will be designed to be identical in carrying capacity as the current corrugated paper containers. As an example, looking at the first line item, one sees that it is shocks supplied by Bluegrass Industry. Each TXX requires four shocks. It uses Container Type 3, and eight shocks can fit in each Container Type 3. In using corrugated boxes, the trucking firm picks up the parts from Bluegrass Industries (see table 4) and transports them to EZ multiple times daily. The boxes are disposed of after use. For the reusable plastic boxes, a milk run is used. The reusable plastic boxes flow continuously within the milk run (see Table 5). On Route 2, a truck would leave from EZ with some empty Containers of Type 1, 2, and 3 and deliver some of them to KY Industry and pick up the waiting full Containers of parts. Then it will go to Bluegrass Industries, and do the same thing and then deliver the full Containers to EZ. On this Route 2, the truck will repeat this process twice more in the day. At the end of the day, the truck goes back to the depot. The next day the cycle starts again at EZ, then Kentucky Industries, then Bluegrass Industries then EZ. For Route 2, this occurs three times a day. 1.10 1.20 1.30 1.40 The value of the column "Days in Shipping Loop" in Table 3, represents the approximate number of containers needed to keep the daily requirement of shocks circulating in this manner. To provide concrete numbers, EZ produces 400 trucks a day, 255 days a year. It need 4x400 or 1600 shocks a day. At 8 pieces/container, it needs 200 of container Type 3. Imagine a set of containers sitting being unloaded and circulating by the assembly line at EZ, and at the same time one set sits empty at the EZ dock awaiting pickup, one set is on the truck going to Bluegrass Industries, and another set is sitting being unloaded at Bluegrass Industries and one there awaiting pickup. There would be multiple runs on the route during the day, at staggered time, so EZ and its suppliers would be receiving three deliveries a day. Thus it may require 5.5 sets on any given day to circulate the daily requirement of shocks. That is the equivalent of requiring 5.5 days for each container to make the full circuit. Table 3 Supplier Information on Parts and Container Types (you should be able to copy these table values off the pdf.) Pieces/ Truck Container Pieces/ Type Supplier Location Parts Supplied Container Bluegrass Ind. Lexington 4 3 8 KY 1 3 5 1 3 5 24 1 100 Blues, Inc. St. Louis 1 8 12 KY Ind. 50 Frankfort KY 6 12 6 32 MO Ind. Fenton 12 MO 4 4 20 12 Reds, Inc. Cincinnati, 5 Shocks Steering column Steering wheel Lugnuts Stereo system (tuner/CD/cass.) Stereo speakers Instrument panel molding Interior quarter trim (pillars) Interior door handles Exterior door mirror Interior rearview mirror Over-fender plastic Glovebox Center console Fuel tank protector Fuel tank cap Front grill L4L2 1 2 2 1 4 1 1 1 1 1 34 1 2 2 3 3 4 4 3 3 2 4 Days in Shipping Loop 5.5 5.75 5.75 5.5 6.5 5.5 7.5 5.5 6.5 6.5 7.5 5.5 5.5 5.5 6.5 6.5 5.75 OH Passenger side airbag Shoulder-strap seatbelts Table 4 Expendable Shipping frequency and distances Mileage to EZ Shipments per day to EZ Supplier Bluegrass Ind. 192 3 Blues, Inc. 174 3 KY Ind. 170 3 MO Ind. 195 3 Reds, Inc. 223 2 Table 5 Reusable Container Frequency and Distance Stop 1 (cumulative) Stop 2 (cumulative) Stop 3 (cumulative) Origin EZ Route 1 Missouri Ind. EZ Blues, Inc. St. Louis, Princeton, Fenton, MO IN 174 miles 253 miles 448 miles Bluegrass Route 2 EZ Ind. EZ Ky Ind. Frankfort, KY Princeton, Lexington, KY IN 170 miles 195 miles 387 miles Route 3 EZ EZ Reds, Inc. Cinti, OH Princeton, IN 223 miles 446 miles 2 3 Nw 2 Frequency of Route 3/day 3/day 2/day 6 5.5 5.5