1. Create a spreadsheet that Montreal could use to determine the most efficient snow removal plan for...

Question:

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 1 cubic meter of snow 1 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?

Snow removal and disposal are important and expensive activities in Montreal and many northern cities. While 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 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 (for example, rivers, quarries, sewer chutes, and 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 given in the following table (in 1,000s of cubic meters).

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 10 sectors in the city.

1. Create a spreadsheet that Montreal could use to determine

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 (that is, 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.