File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do X Cut Arial - 10 A A E ab Wrap Text General Copy - Paste Format Painter BIU . IDA . BEE Merge & Center - $- % " 4.0 .00 .00 +.0 Conditional Format as Cell Insert Delete Format Formatting * Table . Styles Clipboard Font Alignment Number Styles Cells 018 X V fx A B C D E F G H J K M N O 1 Atlanta Birmingham Columbia Jackson Jacksonville Louisville Memphis Miami Nashville lew Orlean Orlando Capacity Fixed Cost 2 Atlanta $ $ 0.15 $ 0.21 $ 0:40 $ 0.31 $ 0.42 $ 0.38 $ 0.66 $ 0.25 $ 0.48 $ 0.43 15000.00 $ 3,600 3 Birmingham $ 0.15 $ 0.36 $ 0.25 $ 0.46 $ 0.36 $ 0.26 $ 0.75 $ 0.19 $ 0.35 $ 0.55 15000.00 $ 3,600 4 Columbia $ 0.21 $ 0.36 $ 0.60 $ $ 0.50 $ 0.62 $ 0.64 $ 0.44 $ 0.69 $ 0.44 15000.00 $ 3,600 5 Louisville $ 0.42 $ 0.36 $ 0.50 $ 0.59 $ 0.73 $ $ 0.38 $ 1.09 $ 0.17 $ 0.70 $ 0.86 15000.00 $ 3,600 6 Memphis $ 0.38 $ 0.26 $ 0.62 $ 0.21 $ 0.69 $. $ $ 1.00 $ 0.21. $ 0.41 $ 0.78 15000.00 $ 3,600 7 Nashville $ 0.25 $ 0.44 5 0.41 $ 0.56 $ 0. 17 $ 0.21 $ 0.91 $ $ $ 0.69 15000.00 $ 3,600 8 Orlando $ 0.43 $ 0.55 $ 0.44 $ 0.70 $ 0.14 $ 0.86 $ 0.78 $ 0.23 $ 0.69 $ 0.65 $ 15000.00 $ 3,600 9 Volume Ne 5000 3000 1400 2200 8800 3000 7800 4400 6800 5800 2200 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Q2 Q3-4-5 Q9-10 +Speedy delivery provides two-day delivery service of large parcels across the US. Each morning, the dispatcher organizes trucks to ensure all deliveries are met. The company has three drivers available today that need to be allocated into three of the possible 'attractive' routes you pre-determined. The table with the routes in in the Excel file. The '1's mark the delivery locations each route visits. Find which three routes should be allocated to the three drivers so that each delivery location is visited at least once and that minimizes the total travel time. Which routes should be used? (list your answer in numerical order separate by commas with no spaces, e.g., 1,2,3 or 3,4,8) (Hint: Check the last video of Week 5) (10 Points) Enter your answerExcel Aminata Ly File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Cut Arial - 10 - A A ab Wrap Text General EX AutoSum - AYO Copy + Fill - Paste Format Painter BIU - -LA Merge & Center $ ~ % " 6.0 .00 Conditional Format as Cell Insert Delete Format Clear Sort & Find & .00 .0 Formatting * Table Styles Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing E29 X V A B C D E F G H 1 J K L M N O P Q R Routes Delivery Location 2 3 4 5 6 7 8 9 10 A B 1. C 1 D 1 1 A W N J J C O V Q UI A W N - 1 G 1 - - I 1 Time in Hours 6 4 7 5 4 6 5 3 7 6 15 16 17 18 19 20 21 22 23 24 25 Q2 Q3-4-5 Q9-10 +Excel Aminata Ly File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Cut Arial - 10 - A A ab Wrap Text General EX AutoSum - AYO Copy + Fill - Paste Format Painter BIU - -LA Merge & Center $ ~ % " 6.0 .00 Conditional Format as Cell Insert Delete Format Clear Sort & Find & .00 .0 Formatting * Table Styles Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing E29 X V A B C D E F G H 1 J K L M N O P Q R Routes Delivery Location 2 3 4 5 6 7 8 9 10 A B 1. C 1 D 1 1 A W N J J C O V Q UI A W N - 1 G 1 - - I 1 Time in Hours 6 4 7 5 4 6 5 3 7 6 15 16 17 18 19 20 21 22 23 24 25 Q2 Q3-4-5 Q9-10 +10 What would be the total travel time of all three drivers summed if taking the 3 optimal routes? (10 Points) 13File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do X Cut Arial - 10 A A E ab Wrap Text General Copy - Paste Format Painter BIU . IDA . BEE Merge & Center - $- % " 4.0 .00 .00 +.0 Conditional Format as Cell Insert Delete Format Formatting * Table . Styles Clipboard Font Alignment Number Styles Cells 018 X V fx A B C D E F G H J K M N O 1 Atlanta Birmingham Columbia Jackson Jacksonville Louisville Memphis Miami Nashville lew Orlean Orlando Capacity Fixed Cost 2 Atlanta $ $ 0.15 $ 0.21 $ 0:40 $ 0.31 $ 0.42 $ 0.38 $ 0.66 $ 0.25 $ 0.48 $ 0.43 15000.00 $ 3,600 3 Birmingham $ 0.15 $ 0.36 $ 0.25 $ 0.46 $ 0.36 $ 0.26 $ 0.75 $ 0.19 $ 0.35 $ 0.55 15000.00 $ 3,600 4 Columbia $ 0.21 $ 0.36 $ 0.60 $ $ 0.50 $ 0.62 $ 0.64 $ 0.44 $ 0.69 $ 0.44 15000.00 $ 3,600 5 Louisville $ 0.42 $ 0.36 $ 0.50 $ 0.59 $ 0.73 $ $ 0.38 $ 1.09 $ 0.17 $ 0.70 $ 0.86 15000.00 $ 3,600 6 Memphis $ 0.38 $ 0.26 $ 0.62 $ 0.21 $ 0.69 $. $ $ 1.00 $ 0.21. $ 0.41 $ 0.78 15000.00 $ 3,600 7 Nashville $ 0.25 $ 0.44 5 0.41 $ 0.56 $ 0. 17 $ 0.21 $ 0.91 $ $ $ 0.69 15000.00 $ 3,600 8 Orlando $ 0.43 $ 0.55 $ 0.44 $ 0.70 $ 0.14 $ 0.86 $ 0.78 $ 0.23 $ 0.69 $ 0.65 $ 15000.00 $ 3,600 9 Volume Ne 5000 3000 1400 2200 8800 3000 7800 4400 6800 5800 2200 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Q2 Q3-4-5 Q9-10 +4 Southeastern Foods wants you to include the operating costs of the warehouses into account. The monthly fixed cost for operating one of these ware-houses is estimated at $3600 a month (it is the same for all of them). Because each warehouse has the same operating cost, it is easy to see that the optimal shipping strategy should still remain the same as before. What is the new total cost (fixed warehouse costs plus variable distribution cost) for the optimal configuration? Hint: the only new cost we are adding is the operational cost of each warehouse. The distribution cost remains the same as the strategy is unchanged. (10 Points) The value must be a number\f9 X V fx A B C D F G H J K Destination Source Y X Z A Capacity 6 $ 7 $ B 4 40 5 $ 8 $ 6 60 Demand 30 40 30 25 Q2 Q3-4-5 Q9-10 Ready6 Consider the graph below. The edges are in miles. Note that the arrows are unidirections (e.g., you can go from A to C, but not from C to A). What is least amount of miles that will take for me to go from A to H? (10 Points) 12 15 17 N 13 C 16 11 G 18 10 14 D 99 The value must be a number5 Southeastern is debating whether to close some of these warehouses to avoid paying the fixed $3600 for each of them. Update the problem to see if they could close some facilities and save money while still meeting the volume needed. What is the new total distribution cost (fixed warehouse costs plus variable distribution cost) for the optimal configuration (with some closed warehouses that won't be incurring the $3600 no more)? Hint: include the in-use constraint (10 Points) The value must be a number2 In the Excel file, you will find a table representing two distributors A and B who need to ship products to clients X, Y, and Z. The table contains the number of items available at each distributor (capacity) and the demand of each client. Find the optimal shipping strategy that minimizes the overall shipping costs to meet all demand and that does not surpass the capacity of each distributor. What would be the minimum distribution cost? [, (20 Points) The value must be a numberStill using the graph from the previous question, what's the shortest path between nodes C and I? Write your answer as a sequence of capitalized letters with no space, starting from C and ending in I (e.g. CBHI) [ (10 Points) Enter your answer3 Southeastern Foods has hired you to analyze their distribution system design. The company has eleven distribution centers, with monthly volumes as listed in the Excel file. Seven of these sites have warehouses, in terms of the infrastructure available. Information has been compiled showing the cost per carton of shipping from any potential warehouse location to any distribution center in the Excel file. Southeastern has standardized its warehouse design so that all such facilities can handle up to 15,000 cartons in a month. What is the total distribution cost for the optimal configuration (Just shipping - do not include operating costs)? (10 Points) The value must be a number9 X V fx A B C D F G H J K Destination Source Y X Z A Capacity 6 $ 7 $ B 4 40 5 $ 8 $ 6 60 Demand 30 40 30 25 Q2 Q3-4-5 Q9-10 ReadyAn amateur bodybuilder is looking for supplement protein bars to build his muscle fast, and there are 2 available products: protein bar A and protein bar B. Each protein bar A contains 15 g of protein and 30 g of carbohydrates and has total 200 calories. On the other hand, each protein bar B contains 30 g of protein and 20 g of carbohydrates and has total 240 calories. According to his nutritional plan, this bodybuilder needs at least 20,000 calories from these supplements over the month, which must comprise of at least 1,800 g of protein and at least 2,200 g of carbohydrates. If each protein bar A costs $3 and each protein bar B costs $4, what is the least possible amount of money (in $) he can spend to meet all his one-month requirements? [, (20 Points) The value must be a number7 What's the shortest path between A and H? Write your answer as a sequence of capitalized letters with no space, starting from A and ending in H (e.g. ABH) [ (10 Points) Enter your