• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to solve this problem using Solver (Simplex LP) in Excel

chemtoli

New Member
A company has three plants that produce ethanol and four clients to whom the ethanol can be delivered. The table below provides the costs per ton of delivering ethanol from each plant to the respective client.

(The "-" sign in the table indicates that a particular plant cannot deliver ethanol to the corresponding client.)

Plant/Client​
C1​
C2​
C3​
C4​
P1​
132​
-​
97​
103​
P2​
84​
91​
-​
-​
P3​
106​
89​
100​
98​

Plant P1 produces 135 tons per year.
Plant P2 produces 56 tons per year.
Plant P3 produces 93 tons per year.

Client C1 requires 62 tons per year.
Client C2 requires 83 tons per year.
Client C3 requires 39 tons per year.
Client C4 requires 91 tons per year.

Find the lowest delivering cost by minimizing the objective function.

132 X11 + 0X12 +97X13 +103X14 +84X21 +91X22 +0X23 +0X24 +106X31 +89X32 +100X33 +98X34

Subject to the constraints:

Plants:
P1: X11+ X13+ X14 ≤ 135
P2: X21+ X22 ≤ 56
P3: X31+ X32+ X33+ X34 ≤ 93

Clients:
C1: X11+ X21+ X31 ≥ 62
C2: X22+ X32 ≥ 83
C3: X13+ X33 ≥ 39
C4: X14+ X34 ≥ 91

Use the Solver (Simplex LP) application in MS Excel to solve the problem.

The result should be:

X11​
X12​
X13​
X14​
X21​
X22​
X23​
X24​
X31​
X32​
X33​
X34​
0​
0​
39​
87​
56​
0​
0​
0​
6​
83​
0​
4​
 

Attachments

  • MINNIMIZE_function.pdf
    78.9 KB · Views: 3
  • Transportation problem.xlsx
    10.4 KB · Views: 3
Back
Top