Question

Hello, I'm working on Case 7.1 Removing Snow in Montreal (Chapter 7, Problem 1C) on the "Spreadsheet Modeling and Decision Analysis 7th Edition" textbook. But the questions are below(not same as text book questions):

Here is the full question

CASE 7.1 Removing Snow in Montreal Based on: James Campbell and Andre Langevin, "The Snow Disposal Assignment Problem," Journal of the rational Research S ociety, 1995, pp. 919-929 Snow removal and disposal are important and expensive activities in Montreal and many northern cities. Although snow can be cleared from streets and sidewalks by plowing and shoveling, in prolonged subfreezing temperatures, the resulting banks of accumulated snow can impede pedestrian and vehicular traffic and must be removed To allow timely removal and disposal of snow, a city is divided up into several sec tors and snow removal operations are carried out concurrently in each sector. In Mon treal, accumulated snow is loaded into trucks and hauled away to disposal sites (e.g rivers, quarries, sewer chutes, surface holding areas). The different types of disposal sites can accommodate different amounts of snow due to the physical size of the dis- posal facility. The annual capacities for five different snow disposal sites are given in the following table (in 1,000s of cubic meters) Disposal Site 3 500 2 4 5 Capacity 350 250 400 200 The snow transported to various disposal sites is often contaminated by salt and de-icing chemicals. When the snow melts, these contaminants ultimately wind up in lakes, rivers, and the local water supply. The different disposal sites are equipped to remove different amounts of contaminants from the snow they receive. The percentage of contaminants that can be removed from the snow delivered to each disposal site is given in the following table. The amount of contaminant contained in removed snow is relatively constant across sectors Disposal Site 3 20% 2 4 Contaminant Removed 30% 40% 70% 50%

1) First ASsume that by contract each of the 10 sectors can only be assigned to one of the 5 disposal sites so that driver do not have to carry to multiple sites. however, each disposal site can take snow from multiple sectors, up to its capacity. Create a spreadsheet that Montreal Could use to determine the most efficient snow removal plan for the coming year. Assume it costs $0.035 to transport 1 cubic meter of snow for 1km (i.e. $35 per 1000 cubic meters per km)

Answer 1

1. The spreadsheet model and solution using Solver is following:

null

Formulas:

Cell	Formula	Copy to
O3	=SUM(J3:N3)	O3:O12
J13	=SUM(J3:J12)	J13:N13
G17	=SUM(B17:F17)	G17:G26
J17	=B$13*B17-J3	J17:N26
O28	=SUMPRODUCT(J3:N12,B3:F12)*35

_____________________________________________

2. Optimal solution is determined as in part 1.

Lowest cost = $ 191,450

_____________________________________________

3. The revised model and solution is following:

The formulas remain the same. Only the constraint pertaining the condition is removed from the Solver parameter window.

The resulting cost = $ 167,471.5

_____________________________________________

4. The revised model with the objective of maximizing the amount of contaminant removed is following:

Formulas:

Cell	Formula	Copy to
O3	=SUM(J3:N3)	O3:O12
J13	=SUM(J3:J12)	J13:N13
O16	=SUMPRODUCT(J13:N13,J14:N14)

Maximum contaminants that can be removed = 612.8 (1000 m³)