- Email: [email protected]
Some comments on “modeling optimization problems in the unstructured world of spreadsheets”
~
Pergamon PIh
Omega, Int. J. Mgmt Sci. Vol. 25, No. 5, pp. 595-598, 1997 © 1997 Elsevier Science Ltd. All rights reserved Printed in Great Britain S0305-0483(97)00019-4 0305-0483/97 $17.00 + 0.00
Some Comments on "modeling Optimization Problems in the Unstructured World of Spreadsheets" MALCOLM KING Loughborough University, Loughborough, UK (Received December 1996; accepted after revision April 1997) This paper suggests that the use of spreadsheets for teaching students about LP modelling can be further enhanced by supplying students with a suitable template at the introductory stage. With a rearrangement of the layout, which corresponds to a standard pedagogic approach, it is possible to create a template, with locked formulae, which is easy to use and flexible enough to handle problems of different sizes. Such a template would help students concentrate on LP modelling concepts rather than being distracted by spreadsheet errors. Copyright © 1997 Elsevier Science Ltd
Key words--spreadsheet, OR education, LP, end-user computing, modelling
1. INTRODUCTION In a recent paper in Omega [1] Conway and Ragsdale argue that spreadsheets are a blessing and a curse to OR/MS instructors and then go on to give some guidelines for using spreadsheets in LP modelling. The guidelines make good common sense and are illustrated by a product mix example and a transportation problem. A convincing case is presented to support the structure of these two spreadsheets. However, it is possible to imagine further refinements that support these authors' reliability goal but may detract from their modifiability goal. This raises the issue of what is the main aim in teaching students to solve LP problems on spreadsheets. Is it to encourage skills in spreadsheet use? or is it to shed light on the LP technique? There is no dispute that using a spreadsheet for LP problems should encourage greater use of that technique because of the prevalence of LP packages and increasing awareness of how to use them. But there is also
concern that in teaching students via spreadsheets they may become bogged down in spreadsheet errors rather than concentrating on LP concepts. Cragg and King [2] reported on high percentage errors in spreadsheets and others have indicated this is a serious problem. Furthermore, experience in teaching students to use spreadsheets to solve LP problems has shown that much time and effort is expended sorting simple errors in the spreadsheet formulae. Conway and Ragsdale acknowledge that instructors have traditionally used very structured OR/MS packages and one advantage of these is that they force students to model their LP problems in a structured way. If the primary aim of the teaching is to encourage students to be able to formulate complicated management problems into a form appropriate for LP and thereby provide advice for management, it seems a more structured package should support that process. Indeed in the past few years many textbooks have been accompanied by simple LP packages which have such a clear 595
596
King--Modeling Optimization Problems
structure. Thus there is a case for using spreadsheets in an even more structured way to solve problems designed to teach the concepts and techniques of LP. King[3] has suggested a framework for teaching OR techniques which includes a structured approach to LP. This is probably reasonably consistent with what most OR/MS educators do and consists of ensuring that students learn to start by identifying the decision variables, the objective and the constraints. The constraints are then clearly named and the limitation values noted. Only at this point are the structures of the constraints and the objective explored, leading to a summary description in a tabular form. This has led to an effective pedagogic method for helping students learn and apply the concepts and techniques of LP. It therefore makes sense to search for software that supports this modelling approach without any unnecessary distractions. Spreadsheets offer this possibility, along with their other advantages mentioned by Conway and Ragsdale in[l], if students are given an even more constrained structure. The most obvious way to do this is to provide students with (access to) an LP spreadsheet template. A spreadsheet template for the product mix problem could look very similar to many of the simple LP packages available with textbooks. It may also make sense to provide templates for the transportation problem and other well known cases of LP. Students could be instructed to use such a template in their initial attempts to put their LP problems on spreadsheets. This would avoid almost all concern about entering and copying formulae correctly, which is the biggest source of errors in spreadsheets. Once they are familiar with what the solver
does for them and how to interpret the output, they could be encouraged to build their own version of the spreadsheet, following along the lines of the template and using the guidelines provided by Conway and Ragsdale. The primary educational objectives of this step would presumably be, firstly, to ensure the students appreciated that they could use a spreadsheet without a template available and so could use LP wherever a spreadsheet package was available. The second primary objective of this step would be to make it clear spreadsheets can be designed to cope with more complex LP problems, without any artificial limitations imposed by the template. This step would also encourage students to develop their spreadsheet skills along the lines suggested by Conway and Ragsdale, but this should surely be a secondary objective when involved in introductory teaching about LP. A suitable LP spreadsheet template for the product mix problem could look very similar to the spreadsheet advocated by Conway and Ragsdale in [1], but with one significant change to accommodate any number of decision variables in a range from 2 to 15 or 20 say (or whatever upper limit the instructor feels is necessary and wishes to design in their template). This change would be to move all the formulae which Conway and Ragsdale show in the right most column of their spreadsheet to the left most column (i.e. A). They make a powerful case for keeping the constraint labels and limitation values close together and so these should be moved to columns B and C, respectively. This reshuffling of the columns means the data on the structure of the constraints and objective now start in column D rather than column B. The revised version of the product mix problem is shown in Fig. 1.
Fig. 1. The product mix problem in revised format.
597
Omega, Iiol. 25, No. 5
It will be noted that as a further minor modification the optimum values of the decision variables have been moved to row 1 and the names of the decision variables moved to row 2. This has been done to match the changes to the constraints suggested in the previous paragraph. It is accepted that the Conway and Ragsdale version is the most appropriate when the total objective and constraint amounts used are shown on the right of the spreadsheet. There is some advantage in having the decision variable name right at the top of the column in which the data relating to it is stored. However, with the sensible use of colour, shading and boxing it is easy to make the decision variable names stand out, even in row 2. The advantage of having the optimum values of the decision variables in the top row is that now all the numeric output from the solver is displayed round the edge of the spreadsheet (i.e. in row 1 or column A). Further the name of the value is immediately inside that edge in row 2 or column B. This makes it very easy to see all the values inserted by the solver and to interpret what they mean. There is also some advantage in having all the problem specific data removed from the outside edge and within the area bounded by row 2 and column B. Even without the flexible template requirements, the rearrangement of the columns seems to fit better with our modelling approach to the constraints. First, the constraint is identified and named (and put in column B), then the value of the limitation is noted (in column C) and finally the structural constants that relate it to the decision variables are found (and entered in columns D onwards). Positioning the constraint name between the amount used and the amount available seems to improve clarity and reduce the chance of confusion. However, it is accepted this leads to the slightly unusual looking format with the resource limitation value to the left of the structural constants, but this seems unlikely to lead to problems with data entry which must be the main concern about that part of the spreadsheet. It can be argued that the layout advocated in the previous four paragraphs is counter intuitive and violates the sixth guideline suggested by Conway and Ragsdale in [1]. This recommends designs based on the English reading eye movement from left to right and then top to bottom. It is accepted that this is a useful
guideline and that the proposed use of column A and row 1 violates it. Indeed, for this reason, it is not being suggested the proposed layout is adopted for reports to senior managers or even as desirable for any printed reports. It is only being proposed as an "on screen" format for those learning LP and experimenting with the solver. In this context the screen layout, excluding row 1 and column A, follows the English reading eye guideline and is very close to that proposed by Conway and Ragsdale. It is suggested row 1 and column A are coloured or otherwise highlighted so that the eye is aware of their special nature and can easily ignore them initially when entering and checking data. Of course, once the solver has been invoked, it is precisely the values in row 1 and column A that are of special interest and that the eye wishes to pick up quickly and easily. Hence their position at this stage is an advantage. The template aspects are mainly confined to column A (and possibly row 1), with the possibility of some preset shading or colouring in column B and row 2 and some preset values in the solver. Apart from the various pieces of text, the fundamental formula is inserted in A3 and copied down from A7 to A27 or even further if the instructor wishes. The formula in A3 is given by: = $D$1*D3 + $E$1*E3 + SF$1*F3 + SG$1*G3 + SH$1*H3
+ ......
where it is continued across the columns for as far as the instructor needs for the problems they plan to discuss with students. The most important reason for advocating the template approach is to ensure this formula is correctly entered. Experience has shown that students can find a multitude of different ways to mistype the formula, leading to erroneous solutions. This means that class sessions with the computer can be dominated by the instructor having to sort these formulae errors rather than concentrating on the LP aspects. One common difficulty with spreadsheets arises from the ease with which formulae in cells can be accidentally over-written. A standard technique to reduce that risk is to protect certain cells so that they cannot be changed without removing the protection, thus making an
King--Modeling Optimization Problems
598
accidental change much less likely. In the proposed template it would seem sensible to protect all the cells in Column A, so that the key formulae cannot be changed without a conscious effort to modify entries in that column. It would also probably be advantageous to preset some values in the solver part of the template. The objective cell could certainly be specified as A3 and possibly the change variables as DI:E1 with the need to explain to students how this could and should be modified. A few constraints could be added of the form A7 < C7 with the need to explain how these can be modified to greater than or equality and how some can be deleted or others added. Experience has shown that students find it much easier to modify an existing set up rather than create something from scratch and so this approach would be the fastest to enable a simple model to be run. Fortunately, the solver facility in Excel makes it very easy to modify and make these sorts of changes, so presetting some parts of the solver in the template should be a good way to get students started.
general advice offered by Conway and Ragsdale in [1], this feedback is suggesting that, from the point of view of improving the introductory teaching of LP concepts and techniques, their approach could usefully be extended. In particular, a further rearrangement of their recommended product mix spreadsheet is proposed to fit in with a structured approach to teaching LP modelling and to further improve clarity in reading output from the solver. In addition, it is proposed the rearranged spreadsheet could be made available as a template with the key formulae already entered and locked in, to minimize the t i m e spent on correcting spreadsheet formulae rather than LP aspects.
2. CONCLUSION
ADDRESS FOR CORRESPONDENCE: Professor Malcolm King,
Whilst supporting most of the arguments and
REFERENCES 1. Conway, D. G. and Ragsdale, C. T., Modeling optimization problems in the unstructured world of spreadsheets. Omega, 1997, 25, 313-322. 2. Cragg, P. B. and King, M., Spreadsheet modeling abuse: an opportunity for OR? Journal of the Operational Research Society, 1993, 44, 743-752. 3. King, M., A framework for teaching or techniques. Journal of the Operational Research Society, 1988, 39, 1087-1093.
The Business School, Loughborough University, Loughborough, Leicestershire, LEl l 3TU.
Pergamon PIh
Omega, Int. J. Mgmt Sci. Vol. 25, No. 5, pp. 595-598, 1997 © 1997 Elsevier Science Ltd. All rights reserved Printed in Great Britain S0305-0483(97)00019-4 0305-0483/97 $17.00 + 0.00
Some Comments on "modeling Optimization Problems in the Unstructured World of Spreadsheets" MALCOLM KING Loughborough University, Loughborough, UK (Received December 1996; accepted after revision April 1997) This paper suggests that the use of spreadsheets for teaching students about LP modelling can be further enhanced by supplying students with a suitable template at the introductory stage. With a rearrangement of the layout, which corresponds to a standard pedagogic approach, it is possible to create a template, with locked formulae, which is easy to use and flexible enough to handle problems of different sizes. Such a template would help students concentrate on LP modelling concepts rather than being distracted by spreadsheet errors. Copyright © 1997 Elsevier Science Ltd
Key words--spreadsheet, OR education, LP, end-user computing, modelling
1. INTRODUCTION In a recent paper in Omega [1] Conway and Ragsdale argue that spreadsheets are a blessing and a curse to OR/MS instructors and then go on to give some guidelines for using spreadsheets in LP modelling. The guidelines make good common sense and are illustrated by a product mix example and a transportation problem. A convincing case is presented to support the structure of these two spreadsheets. However, it is possible to imagine further refinements that support these authors' reliability goal but may detract from their modifiability goal. This raises the issue of what is the main aim in teaching students to solve LP problems on spreadsheets. Is it to encourage skills in spreadsheet use? or is it to shed light on the LP technique? There is no dispute that using a spreadsheet for LP problems should encourage greater use of that technique because of the prevalence of LP packages and increasing awareness of how to use them. But there is also
concern that in teaching students via spreadsheets they may become bogged down in spreadsheet errors rather than concentrating on LP concepts. Cragg and King [2] reported on high percentage errors in spreadsheets and others have indicated this is a serious problem. Furthermore, experience in teaching students to use spreadsheets to solve LP problems has shown that much time and effort is expended sorting simple errors in the spreadsheet formulae. Conway and Ragsdale acknowledge that instructors have traditionally used very structured OR/MS packages and one advantage of these is that they force students to model their LP problems in a structured way. If the primary aim of the teaching is to encourage students to be able to formulate complicated management problems into a form appropriate for LP and thereby provide advice for management, it seems a more structured package should support that process. Indeed in the past few years many textbooks have been accompanied by simple LP packages which have such a clear 595
596
King--Modeling Optimization Problems
structure. Thus there is a case for using spreadsheets in an even more structured way to solve problems designed to teach the concepts and techniques of LP. King[3] has suggested a framework for teaching OR techniques which includes a structured approach to LP. This is probably reasonably consistent with what most OR/MS educators do and consists of ensuring that students learn to start by identifying the decision variables, the objective and the constraints. The constraints are then clearly named and the limitation values noted. Only at this point are the structures of the constraints and the objective explored, leading to a summary description in a tabular form. This has led to an effective pedagogic method for helping students learn and apply the concepts and techniques of LP. It therefore makes sense to search for software that supports this modelling approach without any unnecessary distractions. Spreadsheets offer this possibility, along with their other advantages mentioned by Conway and Ragsdale in[l], if students are given an even more constrained structure. The most obvious way to do this is to provide students with (access to) an LP spreadsheet template. A spreadsheet template for the product mix problem could look very similar to many of the simple LP packages available with textbooks. It may also make sense to provide templates for the transportation problem and other well known cases of LP. Students could be instructed to use such a template in their initial attempts to put their LP problems on spreadsheets. This would avoid almost all concern about entering and copying formulae correctly, which is the biggest source of errors in spreadsheets. Once they are familiar with what the solver
does for them and how to interpret the output, they could be encouraged to build their own version of the spreadsheet, following along the lines of the template and using the guidelines provided by Conway and Ragsdale. The primary educational objectives of this step would presumably be, firstly, to ensure the students appreciated that they could use a spreadsheet without a template available and so could use LP wherever a spreadsheet package was available. The second primary objective of this step would be to make it clear spreadsheets can be designed to cope with more complex LP problems, without any artificial limitations imposed by the template. This step would also encourage students to develop their spreadsheet skills along the lines suggested by Conway and Ragsdale, but this should surely be a secondary objective when involved in introductory teaching about LP. A suitable LP spreadsheet template for the product mix problem could look very similar to the spreadsheet advocated by Conway and Ragsdale in [1], but with one significant change to accommodate any number of decision variables in a range from 2 to 15 or 20 say (or whatever upper limit the instructor feels is necessary and wishes to design in their template). This change would be to move all the formulae which Conway and Ragsdale show in the right most column of their spreadsheet to the left most column (i.e. A). They make a powerful case for keeping the constraint labels and limitation values close together and so these should be moved to columns B and C, respectively. This reshuffling of the columns means the data on the structure of the constraints and objective now start in column D rather than column B. The revised version of the product mix problem is shown in Fig. 1.
Fig. 1. The product mix problem in revised format.
597
Omega, Iiol. 25, No. 5
It will be noted that as a further minor modification the optimum values of the decision variables have been moved to row 1 and the names of the decision variables moved to row 2. This has been done to match the changes to the constraints suggested in the previous paragraph. It is accepted that the Conway and Ragsdale version is the most appropriate when the total objective and constraint amounts used are shown on the right of the spreadsheet. There is some advantage in having the decision variable name right at the top of the column in which the data relating to it is stored. However, with the sensible use of colour, shading and boxing it is easy to make the decision variable names stand out, even in row 2. The advantage of having the optimum values of the decision variables in the top row is that now all the numeric output from the solver is displayed round the edge of the spreadsheet (i.e. in row 1 or column A). Further the name of the value is immediately inside that edge in row 2 or column B. This makes it very easy to see all the values inserted by the solver and to interpret what they mean. There is also some advantage in having all the problem specific data removed from the outside edge and within the area bounded by row 2 and column B. Even without the flexible template requirements, the rearrangement of the columns seems to fit better with our modelling approach to the constraints. First, the constraint is identified and named (and put in column B), then the value of the limitation is noted (in column C) and finally the structural constants that relate it to the decision variables are found (and entered in columns D onwards). Positioning the constraint name between the amount used and the amount available seems to improve clarity and reduce the chance of confusion. However, it is accepted this leads to the slightly unusual looking format with the resource limitation value to the left of the structural constants, but this seems unlikely to lead to problems with data entry which must be the main concern about that part of the spreadsheet. It can be argued that the layout advocated in the previous four paragraphs is counter intuitive and violates the sixth guideline suggested by Conway and Ragsdale in [1]. This recommends designs based on the English reading eye movement from left to right and then top to bottom. It is accepted that this is a useful
guideline and that the proposed use of column A and row 1 violates it. Indeed, for this reason, it is not being suggested the proposed layout is adopted for reports to senior managers or even as desirable for any printed reports. It is only being proposed as an "on screen" format for those learning LP and experimenting with the solver. In this context the screen layout, excluding row 1 and column A, follows the English reading eye guideline and is very close to that proposed by Conway and Ragsdale. It is suggested row 1 and column A are coloured or otherwise highlighted so that the eye is aware of their special nature and can easily ignore them initially when entering and checking data. Of course, once the solver has been invoked, it is precisely the values in row 1 and column A that are of special interest and that the eye wishes to pick up quickly and easily. Hence their position at this stage is an advantage. The template aspects are mainly confined to column A (and possibly row 1), with the possibility of some preset shading or colouring in column B and row 2 and some preset values in the solver. Apart from the various pieces of text, the fundamental formula is inserted in A3 and copied down from A7 to A27 or even further if the instructor wishes. The formula in A3 is given by: = $D$1*D3 + $E$1*E3 + SF$1*F3 + SG$1*G3 + SH$1*H3
+ ......
where it is continued across the columns for as far as the instructor needs for the problems they plan to discuss with students. The most important reason for advocating the template approach is to ensure this formula is correctly entered. Experience has shown that students can find a multitude of different ways to mistype the formula, leading to erroneous solutions. This means that class sessions with the computer can be dominated by the instructor having to sort these formulae errors rather than concentrating on the LP aspects. One common difficulty with spreadsheets arises from the ease with which formulae in cells can be accidentally over-written. A standard technique to reduce that risk is to protect certain cells so that they cannot be changed without removing the protection, thus making an
King--Modeling Optimization Problems
598
accidental change much less likely. In the proposed template it would seem sensible to protect all the cells in Column A, so that the key formulae cannot be changed without a conscious effort to modify entries in that column. It would also probably be advantageous to preset some values in the solver part of the template. The objective cell could certainly be specified as A3 and possibly the change variables as DI:E1 with the need to explain to students how this could and should be modified. A few constraints could be added of the form A7 < C7 with the need to explain how these can be modified to greater than or equality and how some can be deleted or others added. Experience has shown that students find it much easier to modify an existing set up rather than create something from scratch and so this approach would be the fastest to enable a simple model to be run. Fortunately, the solver facility in Excel makes it very easy to modify and make these sorts of changes, so presetting some parts of the solver in the template should be a good way to get students started.
general advice offered by Conway and Ragsdale in [1], this feedback is suggesting that, from the point of view of improving the introductory teaching of LP concepts and techniques, their approach could usefully be extended. In particular, a further rearrangement of their recommended product mix spreadsheet is proposed to fit in with a structured approach to teaching LP modelling and to further improve clarity in reading output from the solver. In addition, it is proposed the rearranged spreadsheet could be made available as a template with the key formulae already entered and locked in, to minimize the t i m e spent on correcting spreadsheet formulae rather than LP aspects.
2. CONCLUSION
ADDRESS FOR CORRESPONDENCE: Professor Malcolm King,
Whilst supporting most of the arguments and
REFERENCES 1. Conway, D. G. and Ragsdale, C. T., Modeling optimization problems in the unstructured world of spreadsheets. Omega, 1997, 25, 313-322. 2. Cragg, P. B. and King, M., Spreadsheet modeling abuse: an opportunity for OR? Journal of the Operational Research Society, 1993, 44, 743-752. 3. King, M., A framework for teaching or techniques. Journal of the Operational Research Society, 1988, 39, 1087-1093.
The Business School, Loughborough University, Loughborough, Leicestershire, LEl l 3TU.