**Problem Context**A contractor, Susan Meyer, needs to transport gravel to three construction sites. She can purchase up to 18 tons from a gravel pit in the north and 14 tons from a pit in the south. The requirements for the construction sites are as follows: 10 tons for site 1, 5 tons for site 2, and 10 tons for site 3. The purchase price per ton from each pit and the hauling cost per ton to each site are provided in the table below.**Cost Table**| Pit | Hauling Cost per Ton at Site | | | Price per Ton ||-------|------------------------------|----------|-----------|---------------|| | 1 | 2 | 3 | || North | $100 | $190 | $160 | $300 || South | $180 | $110 | $140 | $420 |**Transportation Problem Formulation**(a) Susan needs to decide how much gravel to haul from each pit to each construction site in order to minimize the total cost of purchasing and transporting the gravel. This should be framed as a transportation problem with the use of a parameter table.(b) Susan must hire trucks (and their drivers) for hauling. Each truck is limited to transporting gravel from one pit to a single site per trip. Additionally, beyond the variable costs for hauling and gravel, a fixed hiring cost of $150 per truck is incurred. Each truck can carry up to 5 tons, but lesser amounts are allowed, too. For each combination of pit and site, two decisions are necessary: selecting the number of trucks and the amount of gravel to be transported. Susan aims to minimize these decisions for total cost savings.(b1) A Mixed Integer Programming (MIP) model should be formulated for this problem.(b2) Computational solutions to this model should be sought.

Objective functions and constraints: Objective functions: Minimize C = (100+300) X11 + (190+300) X12 + (160+300) X13 + (180+420) X21 + (110+420) X22 + (140+420) X23 X11 – North - Site 1 X12 – North – Site 2 X13 – North – Site 3 X21 – South – Site 1 X22 – South – Site 2 X23 – South – Site 3 Constraints: Subject to North: X11 + X12 + X13 ≤ 18 South: X21 + X22 + X23 ≤ 14 Site 1: X11 + X21 = 10 Site 2: X12 + X22 = 5 Site 3: X13 + X23 = 10 Xij ≥ 0Find the Calculations below: Excel declarations: Solver declarations: Results: Objective functions: Minimize C = (100+300) X11 + (190+300) X12 + (160+300) X13 + (180+420) X21 + (110+420) X22 + (140+420) X23 + 150 (Y11 + Y12 + Y13 + Y21 + Y22 + Y23) X11 – North - Site 1 X12 – North – Site 2 X13 – North – Site 3 X21 – South – Site 1 X22 – South – Site 2 X23 – South – Site 3 Constraints: Subject to North: X11 + X12 + X13 ≤ 18 South: X21 + X22 + X23 ≤ 14 Site 1: X11 + X21 = 10 Site 2: X12 + X22 = 5 Site 3: X13 + X23 = 10 Xij ≤ 5Yij Xij ≥ 0 Yij are integerFind the results below: Total selected sites 4 (2 North + 2 South) Total fixed cost = 4*150 = 600 Total cost = 5970+600 = 6570