The purpose of this response to Estelles et al is to show that not only is a common place Windows spreadsheet such as Excel far friendlier for students to use, it also allows greater flexibility and speed for post-optimality analysis. The strong appeal of Excel Solver lies in its ease of use and the enlightening nature of spreadsheet work, a point echoed by Hillier & Lieberman (1995), who in discussing the arrival of spreadsheets solvers such as excel, commented that "the convenient data entry and editing features of spreadsheets also are very helpful in constructing linear programming models" (p 131). In addition, many students on UK campuses have a sound grounding in the basic use of Excel as it is the industry standard spreadsheet and spreadsheet use has been widely encouraged in economics education for years (e.g. Judge 1990, Judge 1996). Thus, unlike with GAMS, the student does not have to become familiar with a new syntax or procedure for declaring variables and accessing routines. The recognition of the potential of Excel Solver is also starting to appear in textbooks, particularly Hanna (1996), where others have traditionally relied on LINDO as the standard instructional software (e.g. Wilkes 1989, Winston 1995, Ravindran et al 1987).
|Min C = 0.6x1 + x2|
|s.t.||10x1 + 4x2 >= 20||(calcium)
|5x1 + 5x2 >= 20||(protein)
|2x1 + 6x2 >= 12||(vitaminA)
|x1,x2 >= 0||
Having set-up the cell formulas in the spreadsheet, the user then has to reference the problem in the Solver dialogue box (shown in Figure 2) and simply click 'Solve'.
Unlike GAMS, where the problem solution is buried in a large output file, on clicking 'Solve' the answer is quickly revealed in the spreadsheet cells and an option to create an answer and sensitivity report is provided. The answer report for this simple problem is given in Figure 3.
The answer report reveals not only information about the optimal solution but also the status of the constraints. Should the user get an unexpected answer, the report is also useful for debugging as the constraint formulas are given (although it is just as simple to select the Solver and check them in there).
Consider changing the price of good 1 (x1) from 0.6 to 0.9. The sensitivity report reveals that this is within the range of optimality (i.e. below the maximum allowable increase) and so the optimal values of x1 and x2 should remain unchanged. To test this the student has only to change the one cell in the spreadsheet, click to solve again and the new value for cost and the unchanged decision variables appear instantly (x1 = 3, x2 = 1, C = 3.7). Given this simplicity it is yet more revealing when students put the 100% rule to test (i.e. that for a simultaneous change in objective coefficients, provided the sum of the changes as expressed as a percentage of the corresponding allowable increase/decrease does not exceed 100% the optimal values of x1 and x2 should not change (Note2)). What is more, exploring the meaning of the shadow prices given in the sensitivity report is equally as simple because the RHS values for each constraint are given in separate cells in the spreadsheet. A simple change to the RHS of the protein constraint (the only binding constraint in this problem), say to a requirement of 21 units per day, will reveal to the student, on re-solving, an increase in cost of 0.08, as predicted by the shadow price. As with the objective coefficients, the student could then proceed to test the allowable range for the RHS values by resolving and generating new sensitivity reports to observe the effect on the shadow price.
A particularly useful feature of Excel Solver is the option to have the solution paused at each iteration. This is achieved from the 'Options' button in the Solver dialogue box by checking the option 'Show Iterations'. If we make the change to the original diet problem so that the cost of good one is £1, then using this option the student can see that the LP solution takes 6 iterations. Forcing the changing cells to take integer values then requires 15 iterations to achieve the optimal integer solution. This option can really help students appreciate the computational requirements of integer problems. One example I use in my third year Optimisation in Business course is a simple 5 variable, 8 constraint profit max problem that requires only 6 iterations to solve as an LP. In the lecture I then force the integer requirement and the problem is dramatically transformed. Remembering to un-check the 'Show Iterations' option, the problem is solved after approximately 1000 iterations and around 340 branches, which the students can see occur at the bottom left of the Excel screen in the status bar. None of these facilities are available in real-time in GAMS. Although the GAMS output does provide detailed analysis of resource usage and iteration count, for example, this can only be accessed after the solution is completed and the student has read the output file into a text editor (and spend some time hunting the rather detailed output for this information and the solution, which is right at the end).
Finally, Excel Solver can easily accommodate non-linear or parameterised problems as it can linear and integer problems without any special modifications. Referring to the example in Estelles et al, given in Problem 2, the non-linear problem is entered into Solver in exactly the same way as a linear problem except that the option 'Assume Linear Model' must be un-checked in the Solver Options dialogue box.
Of course, further flexibility can be added to this type of problem by putting the parameters in separate cells which can be subsequently altered and the impact on the solution immediately observed.
Estelles, T. C., Arre, M. M. & Garrido, R. S., 1996, "Economic Optimisation with GAMS", Computers in Higher Education Economic Review, 10 (2) 2-7
Hanna, M., 1996, "Introduction to Management Science - Mastering Quantitative Analysis", South-Western
Hillier, F. & Lieberman, G., 1995, "Introduction to Operations Research", sixth edition, McGraw-Hill
Judge, G., 1990, "Quantitative Analysis for Economics and Business Using Lotus 1-2-3", Harvester-Wheatsheaf
Judge, G., 1996, "Student Created Spreadsheet Models for Teaching & Learning Economics: An initial Self-Evaluation of an Experimental Assignment", Computers in Higher Education Economic Review, 10 (2) 16-18
MacDonald, Z., 1995, "Teaching Linear Programming using Microsoft Excel Solver", Computers in Higher Education Economic Review, 9 (3) 7-10
Ravindran, A., Phillips, D. & Solberg, J., 1987, "Operations Research - Principles & Practice", 2nd Edition, Wiley
Wilkes, M., 1989, "Operational Research - Analysis & Applications", McGraw Hill
Winston, W. L., 1994, "Operations Research -Applications & Algorithms", 3rd Edition, Duxbury (Wadworth)
Note 2: Although for small problems the sum can also safely far exceed 100% without affecting the optimal solution.
· Other articles on the use of spreadsheets in teaching economics