Tutorial to Excel Solver for the Solution of Linear Programming Models

By Dr Muhammad Al-Salamah

Excel Solver has proven itself to be the most easily accessible utility for mathematical programming and the solution of mathematical equations in general. Solver comes with Excel, hence, no further expenses are incurred. For some users, the appeal of Solver lies in its graphical interface, which does not require variable definitions necessary in other optimization software packages. Solver has its own options and setups; but the default values most often are adequate and are rarely changed.

This tutorial is built around a manufacturing planning decision problem, and the problem will be utilized to show how Solver can be used to solve it.

Manufacturing Planning Decision

Bright Man Company manufactures shmaq and qutra for East Alkobar store. East Alkobar accepts all the production supplied by Bright Man. The production process includes cutting of sheets, sewing, and packaging. Bright Man employs 25 workers in the cutting department, 35 in the sewing department, and 5 in the packaging department. The factory works one 8-hour shift, 5 days a week. The following table gives the time requirements and profits per unit for the two garments:

The industrial engineer has to plan the weekly production, which requires him to determine how many shmaq and qutra to produce. Knowing that his company is driven by profit and better efficiency means more money, he chooses to find the optimal production mix that will maximize the profit. The prices of the two garments are as:

The variables in this production planning problem are the production amounts of shmaq and qutra:

x₁= number of shmaq produced in a week

x₂= number of qutra produced in a week

Hence, the linear programming model can be written as

Excel Worksheet Setup

The graphic interface provided by Excel makes the presentation and entering the problem data easier. There are two rules in preparing the problem for Solver:

1. Every decision variable has to have its own cell.
2. Every function (objective function and constraints) is defined in an individual cell, with reference to the decision variables.

The production mix problem can be concisely represented in a table form inside Excel, and the cells containing the definition of the variables and the functions are added to the table:

Once the worksheet is setup, Solver can be called to solve the problem.

The Solver dialog box has five areas that are necessary to define the components of the LP model to Solver, and these areas are illustrated in the following figure:

The Set Target Cell should point to the cell containing the definition of the objective function. The direction of optimization has to be specified by choosing either 'Max' or 'Min'. The variables and their cells are referenced in By Changing Cells area. The constraints are defined in the constraint area Subject to the Constraints. The constraints are defined by entering the reference cell, the direction of inequality, and the resource availability. The Solver options are necessary to specify the non-negativity requirement. The Solver dialog box should look like this:

When the definition of the LP model is entered, Solver can be called to solve it by pressing 'Solve' button. If the problem has a solution, Solver should report that 'Solver found a solution':

Clicking OK will remove the box and keep the solution on the worksheet. For the production mix problem, the optimal solution is for the industrial engineer to plan to produce 480 units of shmaq and 840 units of qutra every week.

Raw mathematical model in Excel

The excellent graphical interface Excel provides makes it possible to enter the mathematical model in many forms. Another way to write the mathematical model in Excel is to write it as it is given; that is, to write in the function definition the coefficients of the variables.

In a similar way, each variable and function has to be defined in an individual cell. For the current LP model, there are 2 variables and 4 functions; hence, 6 cells have to be selected. The worksheet with the proper definitions of variables and functions looks like this:

Now, Solver can be called with the proper references to the cells in the worksheet.

Discrete Variables in Excel

Discrete variables, whether integer or binary, are frequently used in most decision and planning models. In Excel, a particular decision variable is designated as integer or binary by adding a constraint to the original constraints. When the Add Constraint dialog box is opened, the nature of the variable is specified from the dropdown menu in the middle of the dialog box:

برنامج تعليمي لحل نماذج البرمجة الخطية باستخدام إكسل