As Cheerwine looks to expand to all 50 states, you have been placed in charge of analyzing the initial development of a distribution supply chain of the popular drink in Louisiana. In order to forestall any premature leaks of information about their plans to the press, all potential locations for warehouses and the distributors who have already agreed to operate have been made anonymous for this problem. Possible warehouses: Sites Alpha, Beta, Gamma, Delta, Epsilon Each warehouse site has a fixed operating cost per month, and a maximum capacity to ‘supply’ soda. Units are in (000) but that is irrelevant because they are consistent throughout our model. Note that if a warehouse location is selected by the model, another solution requirement is that at least 60% of its maximum capacity is used by the overall distribution solution. Note that this requirement is not a ‘sequential’ requirement - it is implemented at the same time as everything else. MAX COSTS Alpha 125 1005 Beta 150 999 Gamma 210 2125 Delta 205 2005 Epsilon 155 888 The per unit distribution costs between the distributors (A-D) who have agreed to deliver Cheerwine and the potential warehouse locations are shown below along with the demand. Demand 76 100 90 88 Dist A Dist B Dist C Dist D Alpha 18 40 57 25 Beta 11 56 42 46 Gamma 22 29 38 13 Delta 45 45 47 55 Epsilon 57 62 36 24 Based on the number of retail locations that have signed up and how they likely would be allocated to each distributor (an interesting decision problem in itself!), the distribution system needs to be designed to deliver exactly the previously shown number of units to each distributor location per month. Also note that a distributor can be served by multiple warehouses. On the ‘front end’ of the distribution system, 3 existing locations of production have possible supply capacity – 200 units at Salisbury, NC, 185 units at Asheville, NC and 205 units at Rome, Ga plants. Not all of the capacity needs be used. They do not all have to be used – if Salisbury is used, we incur a fixed cost per month of 1515 – if Asheville is used, we incur a fixed cost per month of 1000, and if Rome is used, we incur a fixed cost per month of 1475. Note if we do not use any capacity, we incur no cost. Here is the per unit (same units as throughout) cost of soda traveling from the 3 production facilities to the possible warehouse locations. Salisbury Asheville Rome Alpha 10.5 16 12 Beta 23 21 11.5 Gamma 6.5 7.5 25.5 Delta 21 20 8 Epsilon 11.5 24.5 11 One final requirement for all portions of the supply chain: maximum and minimum truck size. Consider 1 truck maximum in each ‘pair’ of possible routes. The most units sent between a pair of locations is 100 – the least sent (smallest allowed truck size), if the route is used, is 40. So all distributed units need to be between 40 and 100 inclusive, if not 0. Determine how to best meet anticipated soda demand at the distributors by determining where warehouses should be located and which production capacity needs to be used. No inventory is allowed in the warehouses. The metric will be to design the system to minimize overall costs. Overall costs consist of the monthly production costs (accrued only if the production facility is used), warehouse costs (accrued only if a warehouse is used) plus the distribution costs of the soda from production to warehouse, then from warehouse to distributor, calculated by the total number of units sent from the warehouse/distributor pair or production/warehouse pair times the cost given in the table. Once you have your solution (which in theory should be all integers), summarize the solution (in words, pictures, etc.) so that your boss can understand the suggested supply chain solution. Please use Excel to show the optimal solution for this problem.