Optimacros: Optimization with spreadsheet macros

Volume 6, Number 2

OPERATIONS RESEARCH LETrERS

May 1987

OPTIMACROS: OPTIMIZATION W I T H SPREADSHEET MACROS James K. H O Management Science Program, C.~leg.~ of B~iness Administration, Universi~ of Tennessee, Knoxville, TN 37996-0562, USA R~ceived August 1986 Revised March 1987

Macro programs for linear and dynamic programming using only commonly available spreadsheet software are introduced. They enable users of spreadsheets, the number of whom is ever growing in academia, bus!~ess, industry and government, to model and solve LP's and DP*s with single keystrokes or mouseclicks. OptiMacros are avmlable for Lotus 1-2-3 (Release 2) on IBM-PC and compatibles, and for Microsoft Excel on the Apple Macintosh. finear programming, dynamic programming, microcomputer software, spreadsheets • macros

1. Spreadsheet macros

2. OptiMaeros for linear programming

Electronic spreadsheets tabulate textual and numerical data in rectangular arrays of cells. Data in different cells can be related by mathematical functions. As values in cells are altered, the necessary calculations will be performed automatically to reflect the changes. From obvious applications in accounting and financial analysis, the popularity of such software extends to many other forms of quantitative analysis for decision support. Since linear and dynamic programming are the basic quantitative methods in OR/MS, there is a logical demand for their implementation in the spreadsheet environment. Since the basic spreadsheet is not set up for complex computations of an algorithmic nature, several more or less indirect approaches as described in Ho [1] can be followed. The most 'native' approach relies on built-in capabilities of existing spreadsheet programs. These are known as macros. They consist of sequence,, of commands, i.e., ~ program recorded on the spreadsheet, that are equivalent to manual oper,ations such as selecting a range of cells, copying and moving cell contents, specifying formulas, etc. Using the allowable commands as a computer language, it is then possible to implement various decision models and optimization algorithms. Hence my coinage of OptiMacros.

Model formulation is interactive and completely self-guided. For users familiar with the fundamentals of both LP and spreadsheets, practically no training is required. A concise and natural format is used. The actual formatting is automatic and totally transparent to the user. Solution is by the Simplex Method. The optimai levels of activities with their marginal costs, the slacks or surpluses of constraints with their marginal values are returned to complete the LP model as a spreadsheet which can then be stored, retrieved and edited as separately named files. 2.1. LPI23

This is for use with Lotus 1-2-3 (Release 2) on IBM-PC's and compatible microcomputers. LP123 is the worksheet on the suppiied disk which can be retrieved with t h e / f r commands and specifying drive: LPI23 when prompted for the file name. The names of four macros appear on the top of the worksheet. \ M LP-Modeler: formulates an LP by guiding the user step by step, \ S LP-Solver: creates a work region, solves the LP and returns results to the model,

0167-6377/87/$3.50 © 1987, Elsevier Science Publishers B.V. (North-Holland)

99

Volume 6, Number 2 4 Activities Level Marginal Cost

OPERATIONS RESEARCH LETrERS Stock B Stock C 1250 333.333 0 0

Stock A 375 0

Objective

7.20

2.80

6 Constraints Total Fund Return S~x:kA Stock B SlockC Stock O

80.0 9.6 80.0 0.0 0.0 0.0

40.0 3.6 0.0 40.0 0.0 0.0

StockD 0 0.18

Name: Date:

2.70

Min=

3.60

May 1987

Portfolio Selection 8115186

7400 Slack or Surplus

60.0 4.2 0.0 0,0 60,0 0.0

30,0 3.3 0.0 0.0 0,0 30.0

-. > -< < <_ <

0 0 20000 0 30000 50000

100000 9500 50000 50000 50000 50000

Marginal Value -0.018 -0.6 0 0.002 0 0

Fig. 1. Example of LP formulated and solved with LPI23.

LP-Excd is the macrosheet on the supplied disk that can be opened from the desktop by double clicking its icon or from within Excel with Open.. on the File menu and then selecting the file by name. With LP-Excel in the active window, select Macros on the menu. A dialogue box offers two macros: LP-Modeler: which creates a new worksheet and guides the user through the formulation of an LP, LP-Solver: which solves the LP and returns results to the worksheet in the ac-

\ K LP-Keeper: saves the active LP model as a 1-2-3 worksheet, \ L LP-Loader: retrieves a previously saved LP model. To run a macro, hold the macro key (ALT on IBM-PC's) and press the corresponding letter key, viz. M, S, K and L, respectively, for the four listed above. An example of a complete LP123 model with solution is given in Figure 1. 2.2. LP-Excel

live window.

This is for use with Microsoft Excel on the Apple Macintosh (512K or Plus) microcomputers.

r

~

File

Edit

F

mulo

A262

Format

Data

Since the models are created on separately

Options

Macro

Solution Status:

Product Mix |AcUvtt|es | pro~luct A | p~oduct B! Product CI[ ' ........... I ......................... ~ .................. i " i j ................. T ~ ................. i[ ....................

Window

q

!

" |'

!

ICZZZ3, ........................i,,",.........................i ..........................l .........................

.....................

............1........................ 1 .........................!.........................?.........................i .........................m.........................".........................i

! !Const re| nts

i

I

! I

i..................... i I

i •

i.

i

|

,

i

J

O.Oi

1.0

i

ikr

! ........................i .........................t .........................t ............ ": ....................... I ........................ i ........................ i ............

Iteretion:

Fig. 2. Final screen of LP-Excel on an example LP. 100

!

.................... i i l ......................... ! ......................... i .........................

i

~ ilt,~l

Volume 6, Number 2

OPERATIONS RESEARCH LETTERS

named worksheets, they can be saved and opened with the usual Macintosh operations without additional command macros. The above macros can also be run from the keyboard. An example of the final screen in an LP-Solver run is shown in -Figure2. 3.

OptiMaemsfor dynamicprogranuning

Because of their ability to relate cell contents recursively, spreadsheets are particularly suitable for dynamic programming models. Indeed, for individual DP problems, it is possible to build comp.~etely self-standing spreadsheets so that the solutic,ns will be updatt~ as soon as any changes are made. See, e.g., Ho [1]. However, such customized spreadsheets must be constructed on a case by case basis. To formulate and solve DP's of any user specified dimensions, macro programs are again necessary. Model formulation is interactive and completely self-guided requiring minimal training. A concise and logical format is used. The actual formatting is automatic. By their nature, DP models can be solved as they are being formulated. A separate solver is required only after modifications to the model have been made. 3.1. DPI23

This is for use with Lotus 1-2-3 (Release 2) on IBM-PC's and compatible microcomputers. DP123 ,...

May 1987

is the worksheet on the supplied disk which can be retrieved with t h e / f r commands and specifying drive: DPI23 when prompted for the file name. The names of four macros appear on the top of the worksheet. \ M DP-Modeler: formulates and solves a DP by guiding the user step by step, resolves the DP after changes \ S DP-Solver: have been made, \ K DP-Keeper: saves the active DP model as a 1-2-3 worksheet, \ L DP-Loader: retrieves a previously saved DP model. To run a macro, hold the macro key (ALT on IBM-PC's) and press the corresponding letter key, viz. M, S, K and L, respectively, for the four listed above. An example of one stage of a DP123 model is given in Figure 3. This illustrates the DP tormat used in OptiMacros. Starting from any state, the optimal solution is read off by tracing the optimal policy through subsequent stages. 3.2. DP-Excel

This is for use with Microsoft Excel on the Apple Macintosh (512K or Plus) microcomputers. DP-Excel is the macrosheet on the supplied disk that can be opened ~:rom the desktop by double clicking its icon or from within Excel with Open.. on the File menu and then selecting the file by name. With DP-Excel in the active window, select

,

# of states Opt. Policy West Hub 1 West Hub 1 West Hub 2

3 Opt. Value -9.1 -9.7 -9.5

ST.¢~I:: State Origin 1 Origin 2 Origin 3

1 # of decisions 2 2 1

s_=ae_ Decision West Hub I West Hub 2

Origin 1 State Payoff later Total PaYoff Payoff now -9.1 * -5.5 -3.6 -5.9: -11.2 -5.3

S~.~ Decision West Hub I West Hub 2

Origin 2 State Payoff later Total Payoff Payoff now -9.7 -5.5 -4.2 -10.1 5.9 -4.2

St~'~_ Decision West Hub 2

Origin 3 State Payoff later Total Payoff Payoff now -9.5 -5.9 -3.6

,,

Fig. 3. One stage of a DP formulated with DP123. 101

OPERATIONS RESEARCH LETTERS

Volume 6, Number 2 r

~;

File

Edit

Formula

....

Format

Data

Options

Macro

May1987

Window

For each decision, enter resulting state and immediate payoff I | ,..........°.ql

~cisJon I i. Pe~ff now'['~'[i ..................... Pe~0ffleter ................................................. :'~';H East Hub . ~ ..... ..-............................................... "~i"h~'b"~" .......'[ .............. :..s.....Z.l......................-z.,4 Stets

!

=S...S. -S.Si West Hub 2

l

i

Fig. 4. An intermediate screen of DP-Excei.

Macros on the menu. A dialogue box offeres two macros: DP-Modeler: which creates a new worksheet and . guides the user through the formulation of a DP while solving for optimal policies in the process, DP-Solver: which resolves the DP and returns results to the worksheet in the active wirdow. As explained bef; ~, there is uo need for additional command macros for saving and retrievin8 models with the Macintosh interface. An example of an intermediate screen in a DP-Modeler run is shown in Figure 4.

Adaptability: Customization, modification or extension are relatively easy compared to compiled 'black-boxes'. For example, additional features such as LP sensitivity analysis, tutorials on solutions methods in action, graphical output, etc., can be implemented. Quality, robustness,and reliability: OptiMacros are conceived and completely developed by the author based on extensive experience with both optimization and spreadsheet software. Extensive testing has been performed on practically every textbook problem available. Cost: Inexpensive.

5. Disadvantages of OptiMacros 4. Advantagesof OptiMacros Ease of use: There is absolutely nothing about the formats or commands to memorize. It is actually much easier than spreadsheets because even operations for the latter have been automated. Integrability: Operating exclusively within popular spreadsheet environment, there is no additional software to select and support. It is a tremendous enhancement of software already installed. 102

Solution speed: Because of the nature of macro commands, algorithmic processes _are very slow. Typical textbook problems require from, say, a couple to a dozen minutes. Note that this essentially limits the size of practical problems. However, the modeling portion should be so smooth that total turn-around time can stil! be respectable. Not sta,,d.alone: As no one is expected to buy the required spreadsheet software because of Opti-

Volume 6, Number 2

OPERATIONS RESEARCH LETFERS

Macros, this point has actually been claimed as an advantage earlier.

6. Whom are OptiMaeros designed for?

Any user of Lotus 1-2-3 or Microsoft Excel who would like a truly 'friendly' introduction to linear and dynamic programming should consider OptiMacros. An accompanying monograph by the author (Ho [1]) is available for the IBM-PC/Lotus 1-2-3 applications. Students, especially MBA candidates, whose curriculum involves diverse applications on spreadsheets should find OptiMacros especially appealing both as vehicles to learning OR/MS models and as tools for problem solving, in other disciplines. Faculty designing courseware for quantitative analysis, or administrators planning courseware acquisition may soon find the proliferation of O R / M S software bewildering. Eventually, there will probably be as many packages as there are textbooks. To compare and evaluate, one would likely be using criteria similar to those in Sections 4 and 5. The difficulty is that if these packages are too similar, like clones, then there is little basis for a distinctive choice. If they are all substantially

May 1987

different, there will be a question of commitment to arbitrary standards or conventions. In that sense, OptiMacros have certain generic quality to them that help emphasize basic concepts rather than particular implementations. For quantitative analysts who only encounter small scale optimization problems occasionally, or for whom solution speed is not a major concern, OptiMacros should be a handy and inexpensive enhancement of installed software.

7. Availability of OptiMacms LP123 and DP123 are available on 5 1 / 4 " floppy disks at the nominal cost of $15.00 each, or $25.00 for both. LP-Excel and DP-Excel are available on 3 1 / 2 " micro disks at a cost of $17.00 each or $30.00 for both. Low cost site licenses are available. Checks should be drawn on US fundsand payable to Dr. J.K. Ho, 7313 Westridge Drive, Knoxville, TN 37909.

Reference [1] J.K. Ho, Linear and Dynamic Programming with Lotu.~ 1-2-3, MIS Press, Portland, 1987.

103