a$)$ Develop a spreadsheet model to determine the optimal waste collection plan for the upcoming

year to minimize the total costs. What is the optimal solution? How much will it cost the city

to implement this plan? Save this model on the same file in a new worksheet named Part a$).$

$($Hint: Since each sector should be assigned to only a single disposal site, any feasible

assignment plan automatically determines how much waste is sent to each site. It is not

necessary to specify any logical constraint here.$)$

b$)$ Now assume that there is a fixed annual operating cost $($for each disposal facility$)$ which is only incurred if any positive amount of waste is processed regardless of the quantity. These fixed costs are given below.

Disposal Site Annual Fixed Operating Cost

Table: Annual fixed costs of operating disposal facilities

Modify your model to accommodate this change and find which facilities should be used and

the resulting collection plan to minimize the total transportation and fixed operating costs.

Construct your model and save it in a new worksheet named Part c$)$ on the same file.

$1$

$2$

$3$

$150,000130,000100,000$