Page 99 -

SOLVING LINEAR PROGRAMMES WITH EXCEL 79

Appendix 2.1 Solving Linear Programmes With Excel

In this appendix, we will use an Excel worksheet to solve the GulfGolf linear programming
problem. We will enter the problem data for the problem in the top part of the worksheet
and develop the linear programming model in the bottom part of the worksheet.
Formulation

Whenever we formulate a worksheet model of a linear programme, we perform the
following steps:

Step 1. Enter the problem data in the top part of the worksheet.
Step 2. Specify cell locations for the decision variables.
Step 3. Select a cell and enter a formula for computing the value of the objective
function.
Step 4. Select a cell and enter a formula for computing the left-hand side of each
constraint.
Step 5. Select a cell and enter a formula for computing the right-hand side of each
constraint.

The formula worksheet that we developed for the GulfGolf problem using these five
steps is shown in Figure 2.22. Note that the worksheet consists of two sections: a
data section and a model section. The four components of the model are screened,
and the cells reserved for the decision variables are enclosed in a boldface box.
Figure 2.22 is called a formula worksheet because it displays the formulas that we
have entered and not the values computed from those formulas. In a moment we will
see how Excel’s Solver is used to find the optimal solution to the problem. But first,
let’s review each of the preceding steps as they apply to the problem.

Figure 2.22 Formula Worksheet for the GulfGolf Problem

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.