Goutte is a Canadian company manufacturing and distributing soft drinks. Manufacturing plants are used to satisfy the demand of sales districts. The production process makes use of water, available anywhere in Canada in an unlimited quantity at a negligible cost, and of sugar extracts which compose a modest percentage of the total weight of the finished product. For this reason, supply costs are negligible compared to finished product distribution costs. The management would like to determine the plant configuration that minimizes the sum of weighted transportation costs and fixed facility opening costs. The maximum acceptable distance between a potential plant and a sales district is 75 km. Demand can be split among facilities. The fixed costs and capacities of potential plant locations are given in Table 1 and sales district demands are given in Table 2. Table 3 provides per-truckload-transportation costs between potential plants and sales districts, and Table 4 provides distances.

Draw a graph (network representation) of this logistics network, where the potential plant locations and sales districts form the nodes of the network and arcs represent available connections between the nodes.

Formulate a mixed-integer program appropriate for this problem and include it in your homework submission. This should be written using the type of modeling notation we use in class; not in Excel.

Implement and solve this model in Excel, provide a graphical representation of the solution (similar to how we have depicted solutions in class). Note that because demand can be split among plants if needed, the edges in your graph should be labeled with decision variable values representing the fraction of demand from a sales district that has been assigned to a particular plant.

Table 1: Fixed costs and capacities of potential plant locations

Potential plant location	Fixed cost ($)	Capacity (hectolitres)
Brossard	81,400	22,000
Granby	83,800	24,000
LaSalle	88,600	28,000
Mascouche	91,000	30,000
Montreal	79,000	20,000
Sainte-Julie	86,200	26,000
Sherbrooke	88,600	28,000
Terrebonne	91,000	30,000
Valleyfield	79,000	20,000
Verdun	80,200	21,000

Table 2: Demands of sales districts

Sales district	Demand (hectolitres)	Demand (truckloads)
Brossard	14,000	94
Granby	10,000	67
Sainte-Julie	8,000	54
Sherbrooke	12,000	80
Valleyfield	10,000	67
Verdun	9,000	60

Table 3: Per-truckload transportation costs

	Brossard	Granby	Sainte- Julie	Sherbrooke	Valley-field	Verdun
Brossard	0	140.02	55.94	256.50	133.58	21.53
Granby	140.02	0	130.64	142.05	265.88	154.01
LaSalle	38.27	170.94	86.85	287.22	87.40	21.53
Mascouche	100.65	208.47	97.34	344.45	171.12	83.17
Montreal	24.84	157.32	51.52	273.61	123.83	17.11
Sainte-Julie	55.94	130.64	0	254.29	173.88	70.10
Sherbrooke	256.50	142.05	254.29	0	382.54	270.30
Terrebonne	87.95	195.96	85.01	331.57	159.53	71.58
Valleyfield	133.58	265.88	173.88	382.54	0	116.66
Verdun	21.53	154.01	70.10	270.30	116.66	0

Table 4: Distances

	Brossard	Granby	Sainte- Julie	Sher-brooke	Valley-field	Verdun
Brossard	0	76.1	30.4	139.4	72.6	11.7
Granby	76.1	0	71	77.2	144.5	83.7
LaSalle	20.8	92.9	47.2	156.1	47.5	11.7
Mascouche	54.7	113.3	52.9	187.2	93	45.2
Montreal	13.5	85.5	28	148.7	67.3	9.3
Sainte-Julie	30.4	71	0	138.2	94.5	38.1
Sherbrooke	139.4	77.2	138.2	0	207.9	146.9
Terrebonne	47.8	106.5	46.2	180.2	86.7	38.9
Valleyfield	72.6	144.5	94.5	207.9	0	63.4
Verdun	11.7	83.7	38.1	146.9	63.4	0

Required

Give a detailed explanation of this Case Study.

Answer rating: 100% (QA)

Formulas K18 IFB18 75B310000 ... View the full answer