OMGT2087 2550 A3C1 Study 1
Business, Accounting & Economics
29th Oct 2025
1
For this case,
1) An Excel file to demonstrate how you use Solver to identify the solutions. You must ensure that the file is in a workable condition. Each model must be implemented in a separate sheet in the same file.
2) A Word file to explain the mathematical models and to report your answers;
Front page
a. Define decision variables with a measurement unit (e.g. pallets, kg, or km)
b. Provide an objective function and constraints and clearly show how you formulate them with an explanation what each equation means
c. Ensure that decision variables and each equation are linked back to the data
d. Briefly state what answer that you obtain
Problem Statement
Recycling is an important and complex activity in City W. To enable timely operations the city is divided into 10 sectors and recycling operations are commenced simultaneously in each sector. The recyclable garbage is collected from curbside bins, loaded into trucks, and transported to recycling sites. Each site can accommodate different amounts of recyclable garbage because of its available land size at the facility. The annual capacities for five recycling sites are given in the table below (in kilo tonnes):
Recycling Site
|
|
1 |
2 |
3 |
4 |
5 |
|
Capacity |
50 |
34 |
60 |
37 |
40 |
Each recycling site is installed with facilities that have different recycling efficiencies when are summarized in the table below (in percentages):
Recycling Site
|
|
1 |
2 |
3 |
4 |
5 |
|
Efficiency |
32% |
38% |
35% |
43% |
60% |
The cost of collecting and transporting recyclable garbage primarily depends on the distance between the sectors and the recycling sites. The following table summarizes the distances between each sector and each recycling site (in kilometres):
Recycling Site
|
Sector |
1 |
2 |
3 |
4 |
5 |
|
1 |
29.2 |
12.0 |
42.1 |
63.6 |
79.9 |
|
2 |
20.6 |
18.0 |
71.3 |
78.2 |
75.6 |
|
3 |
12.0 |
24.9 |
31.8 |
80.7 |
73.9 |
|
4 |
22.3 |
30.9 |
38.7 |
70.4 |
76.5 |
|
5 |
12.9 |
26.6 |
18.0 |
67.9 |
75.6 |
|
6 |
36.1 |
42.1 |
55.8 |
66.1 |
52.4 |
|
7 |
41.2 |
53.3 |
85.0 |
53.3 |
49.0 |
|
8 |
46.4 |
51.5 |
44.7 |
65.3 |
42.1 |
|
9 |
26.6 |
35.2 |
56.7 |
64.4 |
61.8 |
|
10 |
27.5 |
55.8 |
61.0 |
51.5 |
71.3 |
Using historical data, the city estimates the annual volume of the recyclable garbage for each sector in the coming year shown in the table below (in kilo tonnes)
Estimated recyclable Garbage
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
30 |
10 |
18 |
25 |
22 |
13 |
21 |
29 |
19 |
20 |
It will cost approximately $65.90 to move one kiloton of recyclable garbage for one kilometre. The management would like you to design a solution that simultaneously maximises the amount of recycled garbage and minimises the transportation cost. They consider maximising the amount of recycled garbage to be two times as important as minimising the transportation cost.