Hello, I’m working on Case 7.1 Removing Snow in Montreal (Chapter 7, Problem 1C) on the “Sp

Hello, I&#39;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

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)

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%