I need help answering the snow removal case in EXCEL BASED and how to build it i

Question

I need help answering the snow removal case in EXCEL BASED and how to build it in EXCEL with the formulas. screen shots with step by step detail would be great for my study guide..

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 2 4 5 Capacity 350 250 500 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% 4 Contaminant Removed 30% 40% 70% 50%

Explanation / Answer

Part1 :

in the matrix below supply quanity is represented .

Part2:The minimum total transportation cost 49×153+84×55+73.5×97+10×154+157.5×138+73.5×127+147×30+269.5×99+217×111+182×74+266×36+108.5×130+112×135+0×161+0×200= $159302.5

Part3:

Mathematical and spreadsheet formulation: The formulation of the transportation problem for this case study is aimed at determining the optimum allocation of Snow removal capacity to each disposal site destination to minimize the cost of shipping. There are ten snow removal sites and five disposal destination under consideration. Hence, i = 1, 2, 3, 4,5,6,7,8,9,10 and j = 1, 2, 3, 4,5.

Let xij = the amount (‘000 cubic feets/annum) to be shipped from Snow removal sites i to demand destination j.

Cij = unit cost of shipping (0.035$/’000 cubic metres/km) from Snow removal sites i to demand destination j.

The objective function is mathematically expressed as follows:

Minimize shipping cost = C11x11+C12x12+C13x13+C14x14+ C15x15+C21x21+C22x22+C23x23+ C24x24+C25x25+C31x31+C32x32+ C33x33+C34x34+ C35x35+C41x41+ C42x42+ C43x43+C44x44+ C45x45+C51x51+C52x52+C53x53+C54x54+C55x55+C61x61+C62x62+C63x63+C64x64+C65x65+ C71x71+C72x72+C73x73+C74x74+C75x75+ C81x81+C82x82+C83x83+C94x94+C95x95+ C101x101+C102x102+C103x103+C104x104+C105x105

The are two constraints. Firstly, the total supply by each snow removal site cannot exceed its capacity. Secondly, each disposal site will receive the required quantity of snow to meet its demand. Therefore, the above restrictions are expressed by Linear Programming constraints as follows:

Supply constraint:

•

153 Supply from P1: x11+x12+x13+x14+x15

•

152 Supply from P2: x21+x22+x23+x24+X25

•

154 Supply from P3: x31+x32+x33+x34+X35

•

138 Supply from P4: x41+x42+x43+x44+X45

127 Supply from P1: x51+x52+x53+x54+x55

129 Supply from P2: x61+x62+x63+x64+X65

111 Supply from P3: x71+x72+x73+x74+X75

110 Supply from P4: x81+x82+x83+x84+X85

130 Supply from P3: x91+x92+x93+x94+X95

135 Supply from P4: x101+x102+x103+x104+X105

Demand constraint:

•

350 demand for D1: x11+x21+x31+x41+X51+X61+X71+X81+x91+x101

•

250 demand for D2: x12+x22+x32+x42+X52+x62+X72+X82+X92+X102

•

500 demand for D3: x13+x23+x33+x43+X53+X63+X73+X83+X93+X103

•

400 demand for D4: x14+x24+x34+x44+X54+X64+X74+X84+X94+X104

200 demand for D4: x14+x24+x34+x44+X54+X64+X74+X84+X94+X104

Since spreadsheet update is not possible here ,i am providind the free link of free solver where you can solve for any linear programming(atozmath.com)

only 3 parts are answered since it is a very large question.

Cost Destination Supply Source 1 2 3 4 5 1 0 153 0 0 0 153 2 152 0 0 0 0 152 3 154 0 0 0 0 154 4 44 94 0 0 0 138 5 0 0 127 0 0 127 6 0 3 0 0 126 129 7 0 0 0 111 0 111 8 0 0 110 0 0 110 9 0 0 130 0 0 130 10 0 0 0 135 0 135 Capacity 350 250 367 246 126

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.