Excel Linier Programming Tutorial

Linear programming (LP) is one of the most widely applied O.R. techniques and owes its popularity principally to George Danzig's simplex method (Danzig 1963) and the revolution in computing. It is a very powerful technique for solving allocation problems and has become a standard tool for many businesses and organisations. Although Danzig's simplex method allows solutions to be generated by hand, the iterative nature of producing solutions is so tedious that had the computer never been invented then linear programming would have remained an interesting academic idea, relegated to the mathematics classroom. Fortunately, computers were invented and as they have become so powerful for so little cost, linear programming has become possibly one of the most widespread uses for a personal PC.

There are of course numerous software packages which are dedicated to solving linear programs (and other types of mathematical program), of which possibly LINDO, GAMS and XPRESS-MP are the most popular. All these packages tend to be DOS based and are intended for a specialist market which requires tools dedicated to solving LPs. In recent years, however, several standard business packages, such as spreadsheets, have started to include an LP solving option, and Microsoft Excel is no exception. The inclusion of an LP solving capability into applications such as Excel is attractive for at least two reasons. Firstly, Excel is perhaps the most popular spreadsheet used both in business and in universities and as such is very accessible. Second to this, the spreadsheet offers very convenient data entry and editing features which allows the student to gain a greater understanding of how to construct linear programs.

To use Excel to solve LP problems the Solver add-in must be included. Typically this feature is not installed by default when Excel is first setup on your hard disk. To add this facility to your Tools menu you need to carry out the following steps (once-only):

1. Select the menu option Tools | Add_Ins (this will take a few moments to load the necessary file).
2. From the dialogue box presented check the box for Solver Add-In.
3. On clicking OK, you will then be able to access the Solver option from the new menu option Tools | Solver (which appears below Tools | Scenarios ...)

To illustrate Excel Solver I will consider Hillier & Lieberman's reasonably well known example, the Wyndor Glass Co. problem (Hillier & Lieberman, 1995). The problem concerns a glass manufacturer which uses three production plants to assemble its products, mainly glass doors (x1) and wooden frame windows (x2). Each product requires different times in the three plants and there are certain restrictions on available production time at each plant. With this information and a knowledge of contributions to profit of the two products the management of the company wish to determine what quantities of each product they should be producing in order to maximise profits. In other words, the Wyndor Glass Co. problem is a classic, albeit very simple, product-mix problem.