Optimization Problem using Excel add-in Solver

This problem requires the use of Excel and the add-in, called Solver. The course is Excel-based and Solver is the optimization application used for all problems. This particular problem comes from Cliff Ragsdales's " Spreadsheet Modeling and Decisoin Analysis" and is case problem 6.3.

The problem appears in this text box and is also attached as a MS Word file, so it is sure to transmit legibly. The MS Word version will likely need to be the document from which to work, as the text box does not allow tables to be transmitted well (see attached MS Word doc below).

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 sub-freezing 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 sectors and snow removal operations are carried out concurrently in each sector. In Montreal, accumulated snow is loaded onto trucks and hauled away to disposal sites (e.g. rivers, quarries, sewer chutes, surface holding areas). For contractual reasons, each sector may be assigned to only a SINGLE disposal site. (However, each disposal site may receive snow from multiple sectors.) The different types of disposal sites can accommodate different amounts of snow due to either the physical size of the disposal facility or environmental restrictions on the amount of snow (often contaminated by salt and de-icing chemicals) that can be dumped into rivers. The annual capacities for five different snow disposal sites are shown in the following table (in 1,000s of cubic meters).

Disposal SiteDisposal SiteDisposal SiteDisposal SiteDisposal Site
1 2 3 4 5
Capacity 350 250 500 400 200

The cost of removing and disposing of snow depends mainly on the distance it must be trucked. For planning purposes, the city of Montreal uses the straight-line distance between the center of each sector to each of the various disposal sites as an approximation of the cost involved in transporting snow between these locations. The following table summarizes these distances (in kilometers) for ten sectors in the city.

Disp. Site Disp. Site Disp. Site Disp. Site Disp. Site
Sector 1 2 3 4 5
1 3.4 1.4 4.9 7.4 9.3
2 2.4 2.1 8.3 9.1 8.8
3 1.4 2.9 3.7 9.4 8.6
4 2.6 3.6 4.5 8.2 8.9
5 1.5 3.1 2.1 7.9 8.8
6 4.2 4.9 6.5 7.7 6.1
7 4.8 6.2 9.9 6.2 5.7
8 5.4 6.0 5.2 7.6 4.9
9 3.1 4.1 6.6 7.5 7.2
10 3.2 6.5 7.1 6.0 8.3

Using historical snowfall data, the city is able to estimate the annual volume of snow requiring removal in each sector as four times the length of streets in the sectors in meters (i.e it is assumed each linear meter of street generates four cubic meters of snow to remove over an entire year). The following table estimates the snow removal requirements (in 1,000s of cubic meters) for each sector in the coming year.

Estimated Annual Snow Removal Requirements
1 2 3 4 5 6 7 8 9 10
153 152 154 138 127 129 111 110 130 135

1. Create a spreadsheet that Montreal could use to determine the most efficient snow removal plan for the coming year. Assume it costs $0.10 to transport one cubic meter of snow one kilometer.

2. What is the optimal solution?

3. How much will it cost Montreal to implement your snow disposal plan?

4. Ignoring the capacity restrictions at the disposal sites, how many different assignments of sectors to disposal sites are possible?

5. Suppose Montreal can increase the capacity of a single disposal site by 100,000 cubic meters. Which disposal site's capacity (if any) should be increased and how much should the city be willing to pay to obtain this extra disposal capacity?