1

Tutorial on Using Excel to Run LP

The objective of this tutorial is to guide you through the mechanics necessary to execute a LP model in Excel. Obviously, the first step is to open Excel. Assuming Excel is open, an LP model is executed as follows. This tutorial uses the Astro / Cosmo TV production example used to introduce LP in class. The LP problem (see class notes) is

Step One - Entering the Model

Defining decision variables. Need to define a set of decision variables. In the example this is labeled as decision variables – Astro and Cosmo. Under this set leave the cells blank. The row labeled decision variables will eventually be your optimal solution.

Defining objective function values. The next step is defining a set of cells for the objective function values. The easiest procedure is to use the cells directly below or above the decision cells. In these cells place the values associated with the objective function.

Objective function value. In the cell labeled objective function value, place the equation (formula) for the objective function, in this case the formula is =c7*c8+d7*d8. This formula is equivalent to the equation 20A +30C. The formula is used in the LP execution to calculate the objective function value.

Resource Constraints. Each constraint is a separate row. Place the coefficients associated with each constraint (aij’s) into the appropriate cells. The nonnegativity constraints do not have to be entered.

Constraint equations. In the cells labeled constraints, the equation (formula) for each constraint has to be entered. In this case, the following formulas are entered:

Cell e12 =c8*c12

Cell e13 =d8*d13

Cell e14 =c8*c14+d8*d14

In Excel a blank cell is considered a zero when running a LP model, the following general formula could have been entered into cell e12

Cell e12 =$c$8*c12+$d$8*d12

and then copied to cells e13 and e14

Cell e13 =$c$8*c13+$d$8*d13

Cell e14 =$c$8*c14+$d$8*d14

Again the formula’s have to be equivalent to the constraints given in the problem formulation.

Right hand side (RHS). Place the constraints’ RHS values into the appropriate cells below the title RHS.

NOTE: In this example, red colors is where formula’s are entered and blue is where a simple number is entered. The colors are for the tutorial only and not necessary to run an LP model.

When entered, your worksheet should look like the following.

Step Two - Obtaining the Main LP Menu Pop up Box

Substep one:Click on “Tools” - a pop up box will appear as shown in the following figure. The procedure is very similar to running regressions in Excel.

Substep two:Click on “Solver” –this will give you the LP box. The worksheet should look as follows. If “Solver” does not appear, you must add in this procedure. To do this go to “tools”, “Add-Ins”, click on the box for “Solver Add-in”, and then click on OK.

Step Three – Entering Necessary Cells to Run a LP Model

Substep one –entering data: In this box, the following information is entered.

Set target cell – value should be the cell containing your objective function formula, in the example F8.

By changing cells – click on the icon and place the decision cells in this box, in the example cells c8 and c9.

Max or min. – click on the appropriate circle depending on the problem.

At this point your spreadsheet should look like the following:

Substep two- Adding constraints: To add a constraint click on the add box. At this point your worksheet should look like the following.

Adding Constrains: To add a constraint, click on the icon for cell reference. An add constraint box appears. Constraints can be added one at a time or as a set of constraints. If you highlight a set of constraints, all constraints must have the same inequality . The cells to highlight must contain the equations (formulas) for the constraints. Then click on the icon to denote you are done with the constraint equation.

Next click on the icon for constraint. This gives an input box to place the RHS resource levels. Again, highlight the necessary cells. Be careful, your constraints equations (cell reference) and RHS (constraints) need to be compatible. If you add one constraint at a time, add only one RHS, if you add multiple constraints, then you must add multiple RHS. Click on the icon when finished.

Your worksheet should look like the following. Click on OK when done adding constraints to go back to the main LP menu.

Step Four – Entering Options

At this point, your worksheet at this point should look like the following.

Next click on options and then on assume linear model. Click on OK to return to the main LP menu.

Step Four – Solving

Clicking on “Solve” gives the following menu. Click on “Keep Solver Solution” and highlight both reports “Answer” and “Sensitivity” by clicking on them. Then click on OK. Your LP solution will occur in the decision variables and Obj. Fn. Value cells and in separate worksheets.

Your final worksheet should look like the following. Notice, there is a worksheet for Sensitivity Report and Answer Report. See class notes / tutorial for interpretation of these worksheets.