Solving Linear Programming Problems with Excel
Guide
Step 1. Activate the Solver Add-in
In the File/Options/Add-ins tab, click the Go(G) button next to Excel Add-ins in Manage(A). A window named Add-ins will pop up like below.
Check the Solver Add-in and click OK.
The Solver feature has been activated under the Data tab in Analysis.
Step 2. Transcribe the Linear Programming Problem
$$ \begin{matrix} \text{Maximize} & & x_{1} & + & x_{2} \\ \text{subject to} &-& x_{1} & + & x_{2} & \le & 1 \\ & & x_{1} & & & \le & 3 \\ & & & & x_{2} & \le & 2 \end{matrix} $$
Let’s solve the following maximization problem as a simple example in $x_{1} , x_{2} \ge 0$. The Shrimp Sushi Restaurant tried to solve this problem by hand using the Simplex Method and knew the answer as $\left( x_{1}^{\ast}, x_{2}^{\ast} \right) = (3,2)$. This linear programming problem can be transcribed as follows.
B1 should be filled with $x_{1}$, and B2 with $x_{2}$. To represent the constraints, fill in the formulas below into B3~B6.
B3: =B1+B2
B4: =-B1+B2
B5: =B1
B6: =B2
Currently, B1 and B2 are empty, thus calculated as 0. C4 to C6 were filled with the right-hand side of the constraints.
Step 3. Find Solution
Clicking on the Solver as in Step 1, the ‘Solver parameters’ window appears like above.
In Set Objective(T), input $B$3. This is our objective function.
In By Changing Variable Cells(B), input $B$1:$B$2. These are our variables $x_{1}, x_{2}$.
Add the constraints in Subject to the Constraints(U).
Check the Make Unconstrained Variables Non-Negative(K) and select Simplex LP in Select a Solving Method(E).
Finally, pressing the Solve(S) button will solve the optimization problem as shown above. It matches the answer $(3,2)$ we knew from theoretical calculation.