How to Solve Linear Programming Problems with the Simplex Method in Excel and Python: A Step-by-Step Guide

Linear programming is a mathematical technique that can be used to optimize a given objective function subject to a set of constraints. The Simplex Method is one of the most widely used algorithms for solving linear programming problems. In this article, I will show you how to use the Simplex Method in Excel and Python, using what I have learned from various sources and provide you with a step-by-step guide to solve an diffrenrt example problems.

The problems are made by GPT-4

Simplex Method in Python

You are a farmer who wants to maximize your profit by growing and selling three types of crops: wheat, barley and corn. Each crop requires a diffrent amount of land, water and fertilizer. You have a limited amount of recources and you need to decide how much of each crop to plant. The objective function:

P = 2 * x1 + 3 * x2 + 4 * x3

x1 = Area of land (in acres) planted with wheat

x2 = Area of land (in acres) planted with barley

x3 = Area of land (in acres) planted with corn

The Constraints are:

x1 + x2 + x3 ≤ 10 (You have 10 acres of land available)
x1 + 2 * x2 + 3 * x3 ≤ 15 (You have 15 units of water available)
x1- x2 + x3 ≤ 5 (You have a limited amount of fertilizer available)
x1 + x2- 2 * x3 ≥ 0 (You need to maintain a certain ratio of crops)
x1, x2, x3 ≥ 0 (You cannot plant negative amounts of crops)

Code:

pip install pulp

You need to install pulp package!

Introduction to the Model

#import all classes of PuLP module 
from pulp import *

# Create the problem variable with objective to model
model = LpProblem("FarmerProblem", LpMaximize)

Define the Variables

 # Create 3 variables wheat, barley and corn
x1 = LpVariable("wheat",0 ,None, LpInteger)
x2 = LpVariable("barley",0 ,None, LpInteger)
x3 = LpVariable("corn",0 ,None, LpInteger)

wheat means Area of land (in acres) planted with wheat
barley means Area of land (in acres) planted with barley
corn means Area of land (in acres) planted with corn

4th parameter in LpVariable() is the type of data. There will be LpContinuous or LpInteger.

Define Objective Function

# create objective(max) function
model += 2*x1 + 3*x2 + 4*x3

Define the Constraints

# Create seven constraints
model += x1 + x2 + x3 <= 10, "Land"
model += x1 + 2* x2 + x3 <= 15, "Water"
model += x1 - x2 + x3 <= 5, "Fertilizer"
model += x1 + x2 - 2* x3 >= 0, "Ratio"
model += x1 >= 0
model += x2 >= 0
model += x3 >= 0

Solution of the Model

# The problem is solved using PuLP's choice of Solver
model.solve()

#Print:
# Each of the variables is printed with it's resolved optimum value
for v in model.variables():
    print(v.name, "=", v.varValue)

Result

barley = 5.0
corn = 3.0
wheat = 2.0

Simplex Method in Excel

You are a student who wants to minimize your study time by choosing four courses: math, physics, chemistry, and biology. Each course has a different difficulty level and a diffrent credit value. You need to meet the minimum requirements for graduation and maintain a good GPA. The objective function:

P = x1 + 2 * x2 + 3 * x3 + 4 * x4

x1 = Number of credits taken for Math

x2 = Number of credits taken for Physics

x3 = Number of credits taken for Chemistry

x4 = Number of credits taken for Biology

The Constraints are:

x1 + x2 + x3 + x4 ≥ 5 (You need to take at least 5 credits per semester)
x1 + 2 * x2 + 3 * x3 + 4 * x4 ≥ 10 (You need to have a GPA of at least 2.0)
x1- x2 + x3- x4 ≤ 2 (You need to balance your workload among different subjects)
x1 + x2- 2 * x3- 2 * x4 ≥ -1 (You need to prioritize the courses with higher credit values)
x1, x2, x3, x4 ≥ 0 (You cannot take negative amount of credits)

Excel Sheet

Before the start entring the data you shoul activate Solve add-in on Excel
Excel/File/Options/Add-ins/Solve add-in

I set a 0 to value of variables (insert function of B2,B3,B4,B5 cell is “0”) because they do not affect the result after the algorithm runs.

P = x1 + 2* x2 + 3* x3 + 4* x4

Don’t use to Dollar marks, i used for show insert functions

Click to Solver!

Set Objective:

By Changing Variable Cells:

Now you need to add constraints.

Click to Add!

We need to add every constraints like that. End of that click to OK!
After it should look like this:

If Solving Method is not Simplex LP change it! (choose the simplex)

Click to Solve! and done.

Result

I have attempted to explain the usage of the Simplex Method based on what I learned from the linear programming course I took at university and the diffrent resources on the internet.

Thank you.