- Email: [email protected]
Dynamic programming on an electronic spreadsheet
Comput. & Indus. Engng Vol. 10, No. 3, pp. 203-213, 1986 Printed in Great Britain
DYNAMIC
0360-8352/86 $3.00 + 0.00 PergamonJournals Lid
PROGRAMMING ON AN ELECTRONIC SPREADSHEET
MAHMUT PARLAR Faculty of Administration, University of Bew Brunswick, Fredericton, N.B. E3B 6A5, Canada (Received for publication 5 February 1986) Abstract--We develop a Lotus 1-2-3 spreadsheet which can solve the "stagecoach" problem by dynamic programming. More general network models which have multiple initial and/or final states can also be solved by the same approach and the optimal route(s) can be found from the information generated in the spreadsheet. Another example from oil transport technology with eight stages and a maximum of six states is solved by a larger spreadsheet model and the seven alternative routes are found.
I. INTRODUCTION
Although electronic spreadsheets such as Visicalc were originally designed to deal with accounting type problems (Toong and Gupta[1]), they are now being used to solve OR/ MS type decision problems. In one particular application (Jones[2]) uses the Lotus 12-3 spreadsheet to solve a decision analysis problem with posterior probabilities. He also presents a sensitivity analysis using the/Data Table commands of the program. We have used I-2-3 to find numerical solutions to ordinary differential equations and optimal control and state trajectories to the discrete-time linear-quadratic control problem. We have also used this program to solve discrete stage and discrete state dynamic programs which will be presented in this paper. We will first discuss a prototype "stagecoach" problem (Hillier and Lieberman[3]) and describe the spreadsheet model in detail. A sensitivity analysis will also be presented. A more general network model with multiple initial and final states will also be discussed and an oil transport technology problem mentioned in (Phillips e t al.[4]) will be solved using the more general spreadsheet. II. DYNAMIC PROGRAMMING AND THE "STAGECOACH" PROBLEM
Dynamic programming (DP) is a technique used to find the optimal solutions to timestaged decisions. DP has been applied to problems dealing with inventory management, production scheduling and employment smoothing, capital budgeting, replacement and maintenance among others. Since there is no unique mathematical formulation for DP problems, standard computer packages to solve these problems have not been developed. In this section, after describing a prototypical example in DP, we will present the development of a Lotus 1-2-3 spreadsheet which can solve special types of dynamic programs. A problem specially constructed to illustrate dynamic programming is the "stagecoach" problem (HiUier and Lieberman[3]). It concerns a mythical salesman who had to travel from the west coast (California) to the east coast (New York) in the 19th century. The salesman had to travel through unfriendly Indian territory and he had information on the life insurance premiums he would have to pay between any two states. Since there were several possible routes he could take with different insurance premiums, his problem was that of choosing the cheapest route between state [1] (California) and state [10] (New York). The individual premiums between any two states are provided at the tips of the branches as depicted in the upper portion of Fig. 1. The lower portion of this Lotus 1-2-3 spreadsheet (STGCOACH.WKS) contains the functional equations and their computations which will be described later in this section. 203
204
M . PARLAR
This spreadsheet (STGCOACH.WKS}solves the 'Stagecoach' problem by dynamic programming. Reference: Introduction to Operations Research, 3rd Edition F.S. Hillier & G.J. Lieberman Chapter 7. ................................................................................................................................
:to[2] . . . . . . 2.00
Ito[5] 12] ............ 7.00 I Ito[6] . . . . . . 4,00 I Ito[7] ...... 6.00
:to[8] [5] ............ 1.00
:to[q] ...... 4.00
[8] ...... )to[lO]
Ito[5] :to[5] [I] ............ 4.00
. . . . . . 3.00 Ito[6] [3] ............ 2.00 linT71 ...... 4.00
...... 3.00
Itn[8] ...... 6,00 [6] ...... Ito[9] ...... 3.00
IENO [IO] ....... O,O0 Ito[lO] ...... 4.00 I [g] ......
Ito[5] . . . . . . 4.00
:toT4] . . . . . . 3.00
1
Ito[81
Ito[6] ...... 1.00 I ]to[7]
...... 3.00 I
[4] . . . . . . . . . . . .
;tolg] (7] ............ 3.00
5.00
(STAGE 1) (STAGE 2) (STAGE 3) (STAGE 4) ................................................................................................................................ =================================================== COST FROM ISTATE
TO
TO
[21 STATE
7.00 4.00 6.00
COST FROM OPT. ISTATE
EN0 DEC.
TO
COST TO
OPT,
[5] STATE END DEC, : : : : : : : : : : : : : : : : : : : : : : : : : :
5 11,00 5 1.00 6 11.00 6 4.00 7 12.00 * * * H * I
I=. . . . ===================I. . . . . . . . . . . . . . . . . . . . . . . .
8 4.00 8 1 g B.00 ***eH~ COST I FROM COST : ~STATE TO TO OPT. I . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . [8] STATE END DEC.
: COST
COST : COST 3.00 10 3.00 I FROM COST FROM COST ; FROM COST :STATE TO TO 0PT, :STATE TO T0 0PT. ISTATE TO TO OPT, I Ell STATE ENO OEC. t [3l STATE END OEC, ) (b] STATE ENO DEC. - . . . . . . . . . . . . . . . . . . . . . . . I 2.00 I 4,00
2 13.00 aeeeHI 3.00 3 ll,00 3 2.00
5 7.00 5 I 6.00 6 % 0 0 * * H * * I 3.00
l0
8 9.00 t e e * H l 9 7.00 9 ~ COST
i 3.00 4 ll.O0 4 4.00 7 10.00 **HH) I I............ ============I ................................................
I FROM ISTATE TO [g] STATE
I
COST FROM COST STATE TO TO OPT. [tO] STATE ENO DEC. 0,00
COST TO END
=. . . . . . . . . . . . .
l0 0,00
10
OPT. l===== ..... === ............. t OEC. I
:(Sensitivity Analysis) Minimum cost to ENO [lO] from START [I]: ll.O0
COST FROM :STATE
COST 4.00 I0 4.00 COST FROM COST TO TO OPT. ISTATE TO TO OPT. [4] STATE END DEC. I [ 7 l STATE END DEC. : : : : : : : : : : : : : : : : : : : : : : : : : :
I 4.00 I.O0 5.00
5 o.oo
5:3.00
6 8.00 6 [ 3,00 7 It,00 * * * * * * I : I
10
COST FROM [I] TO [3]
I
o 6.oo
o:
MIN. COST TO OPT, END OEC. .....
~Bi:ik~'ii:~6' I.O0 2,00
I I
3.00 4.00 5.00 6.00 7,00
3 ll.O0 3 ll.00 l,HI II.00 IHl* II.00 eee**
B.00
II.00 H*~
===================================================
O.O0 %00 10.00
S<=NO~
g 7.00 ******~ I
3 3
q.00 II.00 Heae ................................................................................................................................
F i g . 1.
Stagecoach problem.
The user only has to enter the one-stage costs (insurance premiums in this example) at the tip of each branch which will be automatically copied to the proper location in the table at the bottom of the worksheet. For example, suppose that E l 6 is the cell address of the cost of going from state [1] to state [3], its content being $4.00. If B52 is the address of the same cost in the DP table, its content is defined as + E l 6 so that whenever the user changes the cost in the network, it would be automatically copied to B52. Other one-stage costs are copied in a similar manner. The DP table of the S T G C O A C H . W K S spreadsheet automatically computes the optimal route(s) and displays the minimum cost for the entire trip at the lower left portion. We see that there are alternative optima since the optimal decision in state [I] is to go either to state [3] or to state [4] in the next stage. It is not optimal to go to state [2] and hence this appears as a set of asterisks in the very left portion of the table.
205
Dynamic programming on an electronic spreadsheet
The three optimal routes are (i) [l] ---, [3] ~ [5] ~ [8] ---, [10], (ii) [1] ~ [4] ~ [5] --~ [8] ~ [10], (iii) [1] ~ [4] ---, [6] ~ [9] ~ [10]. The minimum cost of all these optimal routes is 11.00 and this is indicated at the lower left part of the table. Clearly, since the costs are automatically copied to the table from the network branches, any change in any of these costs will result in an automatic recalculation of the worksheet resulting in possibly different optimal routes. Although testing the effect of different values of costs on the optimal solution can be done manually, Lotus provides a sensitivity analysis whereby these effects can be observed automatically. Focusing our attention on the lower right section of Fig. 1, we see the results of using "/Data Table I " sequence of commands. This table indicates that varying, e.g. the one-step c o s t C13 of going from [1] to [3] between 1.00 and 9.00 results in different decisions and/or different minimum cost values. Currently, when this cost is 4.00, the optimal decision is to go to state [3] and the corresponding minimum total cost is I 1.00. When the cost c~3 goes up to 5.00 or higher the [I] ~ [3] route is no longer in the optimal solution, but the minimum cost is still 11.00. The graphing capability of Lotus is also utilized to produce the diagram in Fig. 2, which describes pictorially the sensitivity analysis results presented above. The construction of the tables follow the principle of optimality and the related functional equations (recursive relationship): n = 1,2,3,4,
f*(s) = min{cs~. + fn+l(Xn)}, Xn
where x, is the immediate destination on stage n, f*(s) is the minimum total cost of the best overall policy for the remaining stages given that we are in state s and have
STAGECOACH
PROBLEM
(Sensitivity Analysis) 11
0
0
0
0
10 9
8 7 6 5 4 3
0
0
O
2 1 o 1.oo
I
I
I
I
I
I
I
~.00
3.00
4.00
5.00
6.00
7.00
8.O0
UlN. COST TO END
Cost f r o m [1] to [3l o OPTIMAL DECISION
Fig. 2. Sensitivity analysis for stagecoach problem.
9.00
206
M. PARLAR 846: (F2) +046+~MIN($X$51)
35
M
N
0
P O
===================
READY
R
S
T
34 35 COST 36 TO TO OPT, 37 38 STATE END DEC. =. . . . . . ==. . . . . . . . . =. . . . . ' 39 40 8 4,00 8 9 8.00 ******~ COST 41 I FROM COST 42 43 ISTATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . .
~ [8] STATE
V
O
N
X Y Z
Z
I
ENO DEC,
45
46 l0 47 COST 48 TO TO OPT. 49 STATE END DEC. l . . . . . . . . . . . . . . . . . . . . . . . . 50 ] 51 8 9.00 ******~ 52 9 7.00 9 I COST
COST FROH COST STATE TO TO OPT. riO] STATE END DEC. 0.00
10 0o00
T46: $IF(+S46=~HIN(S46),R46,9999999)
tO
READY
H N O P O R S T U V il l Y Z 33 34 35 COST 36 37 TO TO OPT. 38 STATE END DEC. =. . . . . . . ===. . . . . . . . . . . ==' 39 8 4.00 8 40 9 8.00 ***e**l COST 41 FROR COST 42 ]STATE TO TO OPT. ' . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 [8] STATE END DEC. 44 . . . . . . . . . . . . . . . . . . 45 46 3,00 l0 3.00 [ ' ~ COST COST "-"" FROH COST 47 TO TO OPT. l STATE TO TO OPT. 48 49 STATE END DEC. - . . . . . . . . . . . . . . . . . . . . . . . : [10) STATE END DEC. I 50 8 9.00 **e**e~ ~ 0,00 10 0,00 lO 51 9 7.00 9 ~ COST 52 m. . . . . . . . . . . . . . . . . . . . . . . . . .
Fig. 3. Calculations for stage 4.
made the best decision, csx, is the one-step cost of going from state s to x , . Clearly, the objective is to find i f ( l ) and the corresponding policy. Figure 3 indicates the solution on a part of the spreadsheet when there is one stage to go. When in state [8] the cost to [10] is 3.00 + 0.00, or in the spreadsheet Q46 + @ M I N (X51). This corresponds to c8,,o + f~(lO),
f*(lO) = O.
Obviously in this case since the only alternative is to go to [I0], this is what the table indicates as the optimal decision in state [8]. When there are two stages to go and when we are, for example, in state [6] there are two possible routes to take, i.e. [8] and [9]. If we go to [9] from [6] the total cost is 3.00 + 4.00, or in the spreadsheet L52 + (d, MIN($S$57) which corresponds to C6.9 + f 4*9 (),
f*(9) = 4.00.
The table indicates that, if we are in [6], it is n o t optimal to choose [8] as the state to visit in the next stage. This is indicated by the asterisks under OPT. DEC. corresponding to state [8]. Of course, the total cost of going from [6] to [10] via [8] is calculated as + L51 + @MIN($S$46) where $46 is the cost of going from [8] to [10]. The lower portion of Fig. 4 describes the contents of cell 052: @IF( + N52 = @MIN($N$51..$N$52), M52,
Dynamic programming on an electronic spreadsheet
207
9999999). This means "if N52 (the total cost of traveling from [6] to [10]) is equal to the minimum of N51 and N52 then the state corresponding to N52 (i.e. M52 or state [8]) is the optimal state to visit next; otherwise block out the route leading to M52 by assigning 9999999 which produces asterisks." To summarize, when in state [6], the next state to visit would be [9], as shown in Fig. 4. By the same arguments, we observe that when there are three stages to go, and say, when we are in state [3] it is optimal to go to [5] only, with a corresponding total minimum cost of 3.00 + 4.00 which the spreadsheet calculates from G51 + @MIN($N$40..$N$41). This corresponds to c3.5 + f~(5),
f*(5) = 4.00.
The table at the bottom of Fig. 5 indicates how the spreadsheet finds the optimal state to visit in the next stage. The 1-2-3 statement J51:@IF(I51 = @MIN($1$51.. +$I$53), H51, 9999999) does the following: It finds the minimum value of all the COST TO END values corresponding to each state (i.e. [5], [6], and [7]) in the next stage. The optimal N52: (F2) +L52+QHIN($S$57)
READY
H I J K L H N O P Q R S T 40 5 ll.O0 5 ~ 1.00 8 4.00 8 ] 41 6 11.00 6 : 4.00 9 8.00 *÷÷***: COST 42 7 12.00 **eee÷l : FROR COST 43 ]STATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [8] STATE END DEC. 45 46 COST 3.00 10 3.00 10 47 COST FROR COST 48 TO TO OPT. :STATE TO TO OPT. 49 STATE END DEC, [6] STATE END DEC. - . . . . . . . . . . . . . . . . . . . . . . . 5O 51 5 7.00 5 ~ 8 .].,0..0..****÷*I 52 6 9.00 ******:("3.00").. 9JT~ 9 ] COST 53 7 lO.O0 ÷ * ÷ * * * I ~ " " " ' - ' " " - ' - - \ : FROH COST 54 ~ \ ~STATE TO TO OPT. 55 . . . . . . . . . . . . . . . . . . '..................... '~.c= [?] STATE END DEC. 56 57 COST 4.00 [0 (4.00} 10 5B COST FROH COST 59 TO TO OPT. :STATE TO TO OPT.
052: tlF(+H52=eHIN($H$51..$N$52)~H5219997799) H I J K L M N 0 P O R S T 40 5 11,00 5 = 1.00 8 4.00 8 i 41 6 ll,O0 6 ~ 4.00 ? 8.00 i**i**~ COST 42 7 12,00 ******l I FROM COST 43 ISTATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ~ [8) STATE END DEC. 45 46 COST I 5.00 tO 3.00 10 47 COST FROH COST 4B TO TO OPT. :STATE TO TO OPT. 49 STATE END DEC, [5] STATE END OEC, ] . . . . . . . . . . . . . . . . . . . . . . . . 50 51 5 7.00 5 6.00 8 %00 ******I 52 6 %00 ******~ 3.00 9 7.00 ~rt~l COST 53 7 lO.O0 *t****: FROH COST 54 :STATE TO TO OPT. 55 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [9) STATE END DEC, 56 57 COST 4.00 l0 4.00 10 58 COST FROH COST 59 TO TO OPT. =STATE TO TO OPT.
Fig. 4. Calculations for stage 3.
READY
208
M. PAP,CAR 151: (F2) +G51+IMIN($N$40..$N$41)
READY
C D E G H I J L M N O P 34 35 COST COST 36 FROM FROM COST 37 ',STATE TO TO OPT. :STATE TO TO OPT. 38 [2] STATE END DEC. [51 STATE END DEC. 3q 40 7.00 511.00 5 l.OO 865"~ Ri 41 4.00 6 II.00 6 4.00 94[I]~I~I)**z-***~ 42 6.00 712.00 .****',I1 ~ I 43 44 . . . . ======'-. . . . . ==~. . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . '. 45 46 47 ~OST :FRO, COST / :FROM COST '. 48 TO TO O~T. ~STATE TO TO/O,T. :STATE ~0 TO O~T.'. 49 STATE e,0 Dec. ! ~ Dec. i ~] STATE E,D oec. ! 50 51 2 ls oo * , * , , , I L ~ " ~ 5 i 600 o 9.00 52 3 11.00 3 : 27'00 6 -qTO~t*****l 3.00 ? 7.00 9 ', 53 4 11.00 4 I 4,00 7 10.00 **.z.**z.l
,,,,,,i
35h ~IF(+I51=~HIN($1551..$1553),H51,9999999) C D E O H I J L M 34 ;35 COST COST 36 FROM FROM 37 ISTATE TO TQ OPT. ~STATE TO 38 [2] STATE END DEC. [5] STATE 39 40 7.00 5 11.00 5 1.00 8 41 4.00 6 11.00 6 4.00 9 42 6,00 7 12.00 ******I 43 44 45 46 COST COST 47 COST FROM COST FROM 48 TO TO OPT. ~STATE TO TO OPT. ISTATE TO 4? STATE END DEC. : [33 STATE END DEC. I [&] STATE 50 51 2 13.00 ******I 3.00 5 7.00 F'S]: 6.00 8 52 3 11,00 3 I 2.00 6 9.00 ***';'('(I 3.00 9 53 4 11.00 4 I 4.00 7 I0.00 *He*el = = = = = = = = = = = = = = = = = =
READY N
0
COST TO OPT. END DEC. 4.00 8 8.00 ******I
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
COST TO OPT. END DEC. 9,00 ******I 7.00 9 =
Fig. 5. Calculations for stage 2.
decision is to go to that particular state for which the COST TO END value equals the minimum of all those COST TO END values. Otherwise, the program prints asterisks which block the routes to nonoptimal states. The final state calculations and the optimal routes can be seen in Fig. 1. As mentioned before, there are three alternative optima all of which can be easily extracted from the tables in Fig. 1. Although this approach may not necessarily simplify any of the calculations in dynamic programming, it may be useful from an educational point of view. For many students taking management science/operations research courses, dynamic programming has been the most difficult topic to absorb. The spreadsheet approach presented here may be instrumental in clarifying the often puzzling backward induction method common to many problems. Not all the dynamic programming problems have a single starting state and a single ending state. In fact, many problems such as those in equipment replacement, or inventory control (Wagner[5]), when cast in a dynamic programming framework, become stagecoach type problems with multiple initial or final states. To be able to deal with problems of this kind, one has to extend the tables presented in the Lotus spreadsheet. For example, the spreadsheet DP3R5C.WKS in Fig. 6 allows up to three states in the initial and final stages. To solve the stagecoach problem one would now have to re-
Dynamic programming on an electronic spreadsheet
209
number the states, but, of course, the optimal solution would still be the same. The routes which are not allowed are assigned a large cost, such as 9999999, which appears as asterisks in the spreadsheet. A slightly different version of the stagecoach problem with three final states is solved in Fig. 7 which has a lower total minimum cost than the previous problem. The optimal routes are found as (i) [2] ~
[5] ~
[7] ~
[11] ~
[13],
(iS) [2] ~
[6] ~
[7] --~ [11] --~ [13],
with the optimal cost of 8.50. Since almost every discrete stage, discrete state dynamic programming problem can be represented as a network (Dreyfus and Law[6[) it would be useful to have a larger spreadsheet which could accommodate more stages and states than the previous ones. In the next section we discuss one such problem in the oil transport technology area which has eight stages and a maximum of six states in each stage.
This spreadsheet (DP3R5C,iKS) solves a DP problem mith 3 rows and 5 columns (4 stages). Reference: Introduction to Operations Research, 3rd Edition, F.S. Hillier & G.d. Lxebersan, Chapter 7. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
:to[4] [1] . . . . . . . . . . . . ****H :to[5] ...... ***H* :Lot6] ...... *HH*
:to[7) [4] . . . . . . . . . . . . 7.00 :to[8] . . . . . . 4.00 :to[9[ . . . . . . 6,00
:to[10] [7] . . . . . . . . . . . . 1,00 :to[11] ...... **--** ~t0[12] . . . . . . 4.00
:to[13] [10] . . . . . . . . . . . . a**e}e :to[14[ . . . . . . 3,00 :totIG] . . . . . . ****ee
~to[4] . . . . . . 2100 :to[5[ [2] ............ 4.00 :to[6]
:to[7] . . . . . . 3600 ~to[8] [5] ............ 2.00 Ito[9]
4.00
~to[lO] . . . . . . 6100 :to[It[ [G] ............ ~*~ :to[12] . . . . . . 3.00
:tot13] . . . . . . ]]e~[e :to[14] [II] ............ eeeeee Ito[15l . . . . . . **eH*
:to[7] . . . . . . 4.00 ~to[fl] . . . . . . 1,00 Its[g[ [6] . . . . . . . . . . . . 5.00
:to[lO] . . . . . . S,O0 ~to[11] . . . . . . *~*~e :to[t2) [g] . . . . . . . . . . . . 3.00
:to[l~] . . . . . . ***eee 1to[14] . . . . . . 4.00 Its[t5[ [12] . . . . . . . . . . . . ******
......
3.00
~to[4] . . . . . . **~*** :to[5[ ...... aHHe :to[6] [3] . . . . . . . . . . . . *ee*H
......
[STAGE 1)
(STAGE 2)
(STAGE 3)
lEND [13] . . . . . . . . . . . .
0.00
:END [14] ............ 0.00
IEND [15] . . . . . . . . . . . .
0.00
(STA6E 4)
====================================================================================================================• = ===========
COST : COST : COST COST : COST FROM COST I FROM COST : FROM COST FROR COST I FROM COST STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. [1] STATE END DEC. I [41 STATE END DEC. [7] STATE END DEC. I [10] STATE END DEC. ~ [13] STATE END DEC. :aeeHes, ~esssseem :sstHss*
4 sH,es~eeH,: 7.00 5 6eeeeteesH~( 4.00 6 sesHssssHt: 6.00
7 11.00 7 I.O0 O 11.00 8 :eeeHe 9 12.00 t t * t H : 4.00
10 4,00 10 :~saeee I] Hees~asssse: 3.00 12 O.00 ~tssot~tsse~s
13 HeateeSeH*: 0.00 14 3.00 14 : 15 H s ~ t e H s t H :
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
13 0.00
13
~. . . . . . . . . . . . . . . . . . . . . . . .
COST COST COST COST I COST FRGH COST FROH COST FROM COST FROM COST I FRON COST STATE TO TO OPT, ~STATE TO TO OPT. :STATE TO TO OPT. ~STATE TO TO OPT. ~STATE TO TO OPT. , [2) STATE END DEC. : [5] STATE END DEC. [B] STATE END DEC. : [11] STATE END DEC, I 114] STATE END DEC. 2.00 4,00 3.00
4 13.00 teest~ 3.00 5 11.00 5 2.00 6 11.00 6 4.00
7 7.00 7 6.00 8 9.00 Dttt**:t~s~ee 9 10.00 ***e**: 3,00
10 9,00 t H H i : t e s t e t 11 * t e t * H t ~ s , s : t t t s e ~ 12 7.00 12 :****e*
13 Hssssetlesel 0.00 14 *~esse~ees: 15 ****eses****= =
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
]4
0.00
14
I . . . . . . . . . . . . . . . . . . . . . . . .
: COST COST COST COST : COST FROH COST FROH COST FRON COST FROH COST I FROH COST GTME TO TO OPT. :STATE TO TO OPT. fSTATE TO TO OPT. :STATE TO TO OPT. :STATE TO TO OPT. [3] STATE END DEC. [6] STATE END DEC. [9] STATE END DEC. I 112] STATE END DEC. : 115] STATE END DEC.
:*es***ee :,ee*Hme :~*e*****
4 ,ee**ss~=H* 5 *e*HHe~eH 6 *H*****~*ee:
4.00 1,00 5.00
7 8.00 7 3.00 B 8.00 8 :*HHe 9 11.00 **H**~ 3,00
10 6,00 10 :*sHe* 11 e~meee~Hs~e: 4,00 12 7.00 **H~e:H~e**
13 esH*geeseee: 0.00 14 4,00 14 : 15 H , ~ , ~ . e ~ * * :
================================================================================================================================
NJnieue Cost to the Final Stage is:
II.00
F i g . 6. S t a g e c o a c h p r o b l e m s o l v e d b y m o r e g e n e r a l m o d e l .
15 0.00
15
210
M . PARLAR
This spreadsheet (DP3RSC.WKS) solves a DP problem with 3 rows and 5 columns (4 stages). Reference: Introduction to Operations Research, 3rd Edition, F.S. Hillier & G.J. Lieberman, Chapter 7. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
:to[41 [1] . . . . . . . . . . . . ****** :to[5] . . . . . . ****** :toE6] . . . . . .
:to[71 [4] . . . . . . . . . . . . 7,00 Ito[8] . . . . . . 4,00 :toE9]
eee***
. . . . . .
Ito[7l
~to[4] . . . . . . 2,00 Ito[5] [2] . . . . . . . . . . . . 4,00 :tot6] ......
6.00
:foliO] [7] . . . . . . . . . . . . 1.00 Ito[lll . . . . . . 0.50 :to[12] . . . . . . 4.00
......
Ita[iO] 3.00
......
Ito[8] [5] . . . . . . . . . . . .
2.00
[9] . . . . . . . . . . . .
......
9.00
[11] . . . . . . . . . . . .
:ENO 6.00
4.00
:toll5] ...... 8.00
:to]7] . . . . . . 4.00 Ito[8] . . . . . . 1.00 Ito[9] [6] . . . . . . . . . . . . 5.00
Ito[IO] . . . . . . 3.00 [to[Ill . . . . . . l.O0 Ito[12] [9] . . . . . . . . . . . . 3.00
:to[13] . . . . . . 4.00 [toll4] ...... 2.00 :toll5] [12] . . . . . . . . . . . . 4.00
......
(STAGE l)
(STAGE 2)
0.00
1.00
:tot14]
¢to[12] ...... 3.00
3'00
~END [13] . . . . . . . . . . . .
:to[13] 6.00
fro[Ill
Ito[9]
Ito[4] . . . . . . *e~me* Ito[5] . . . . . . t,e*** ~to[6] [3] . . . . . . . . . . . . e*****
:toll3] [10] . . . . . . . . . . . . 2,00 :to[14] . . . . . . 1.00 ~to[15] . . . . . . 3.00
(STAGE 3}
[14] . . . . . . . . . . . .
0.00
~END [15] . . . . . . . . . . . .
0.00
(STAGE 4)
=••••••=••=••=•===•••••••=•••••••=••==••••==•••••===•===•••=•••••••••=•••=••••••••••=•••=•••=••=•=••••••••••=••••••••••••••••••
COST ~ COST COST COST COST FROM COST : FROM COST FROM COST FROM COST FROM COST STATE TO TO 0PT. :STATE TO TO OPT. (STATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. [ l l STATE END DEC. [4] STATE EM0 DEC. ~ [7] STATE END DEC. ~ [10] STATE END 0EC. ~ [1~] STATE END 0EC. **eeeee* I******** :*eeeee,,
4 ********eee* 5 eeeeeHeee** 6 ,,*eeeHe,,*
..........................
7.00 4.00 6.00
7 8.50 * H H * [ 1.00 8 9.00 **eee*l 0.50 g 8.00 9 4.00
I0 2.00 e*ee*el 2.00 It 1.50 11 ~ 1.00 12 6.00 ***eee~ 3.00
, ................................................
13 2.00 *tteee: 0.00 14 1.00 14 1 15 3.00 e*****i
, ........................
13 0.00
13
J........................
COST COST COST COST : COST FROM COST FROM COST FROM COST FROM COST : FROM COST STATE TO TO OPT. :STATE TO TO OPT. :STATE TO TO OPT. ~STATE TO TO OPT. ISTATE TO TO OPT. [2] STATE END DEC, : [5] STATE END DEC. [B] STATE END DEC. I 111] STATE END DEC, ] [14] STATE END DEC, 2,00 4.00 3.00
4 10.00 ******I 3,00 5 8.50 5 2.00 b 8.50 6 4.00
7 4.50 7 b.O0 8 7.00 ***eH~ %00 q 6.00 eeeeeel 3.00
10 l.O0 eeeee*: 1,00 It 10.00 eeeee*l 6.00 12 5.00 12 8.00
13 I.O0 13 I 0.00 14 6.00 ***eeel 15 8.00 *e**ee=
14 0,00
14
i ............................................................................................................................
COST FROM STATE TO [3] STATE Ieee*eeH leeeeeH* Ileee*H*
COST COST FROM TO OPT, ISTATE TO END DEC. I [6] STATE I 4 H*H~ee*eeel 4.00 7 5 e H * * H t e H e ~ 1.00 8 6 *Heeeeeee*el 5.00 9
COST COST C05T COST FROM COST FROM COST FROM COST TO OPT. T0 OPT. ]STATE TO TO OPT. :STATE TO TO OPT. :STATE TO END DEC. [91 STATE END DEC. : [12] STATE END DEC. : [15] STATE END DEC,
5.50
7 3,00 6.00 He*HI 1.00 7.00 H*eHI 3.00
I0 4,00 *t*e,,~ 4.00 II 2.00 11 t 2.00 12 5.00 * * * H e l 4.00
13 4,00 e*ee*#l 0.00 14 2.00 14 ] 15 4.00 eHeee(
•===•••••=••••••••==••=•••==•••••••====••=••=••••=•••••==•••=••=•••••••=••••••••••••••==••=••••••••••••••••=•••••••••••••••••=•
Rinilue Cost to the Final Stage is:
8.50 F i g . 7. A n o t h e r
stagecoach problem with multiple final states.
)7
3
6
Oil storage
J<
sut~,,,,on,
>J Final
F i g . 8. O i l t r a n s p o r t n e t w o r k .
destinations
15 0.00
15
Dynamic programmingon an electronic spreadsheet
211
~===========~========~==========~=~====~==~=========~=~=========~=~=====~===~=~=~====~=========~====~=~=~=======~ :: COST I COST I COST : COST : COST :1 FROM COST I FROM COST : FROH COST : FROM COST : FROH COST ::STATE TO TO OPT. ISTATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT H ] STATE END DEC. I [7] STATE EHD DEC. I [13] STATE END DEC. [19] STATE END DEC. I [25] STATE END OEC. IT : I I II***s** 7 ****HH~te*Ie.**S 13 HH*H*S*StlS*HH 17 **e*eeeeeee* 5.00 25 24.00 25 Is*teH 31 ~***e**e**s~ IIe**t** 9 *t*t*t*e.*eIeet}. 14 ***~t****t*tI***H* 20 **teee****HI 7.00 26 25.00 HeteeIH*tH 32 a H t H * t e * * t ]I**H** 9 ************I,**Ht 15 ************:****** 21 ******#*****:****** 27 *e*****#****: 2.00 33 19.00 33 ::--,-[0 * . . . * . . : . a . * 16 t l l i i l i i t * l * : * t i i i t 22 * * , t * t l i l l e t : * t i i a * 28 t i t * * * t i l i t * i * t , t t 34 * t i i * * * t * * * * fleeces* 11HHeeeleeeeleeeH* 17 **~*~t***~**IHee*~ 23 He~Hee~ee:eHe~e 29 *HH*eetHeIeee*~* 35 **~********* ]:el}lit [2 t l l l t t t t t t t * l t t t * * t 18 t * t t t t t * t t t t I l l t t i t 24 ******#*****I****** 30 ************Iee**** 36 ************ II . . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I: C0ST I C0ST C0ST I COST COST l: FROH COST I FR0fl COST FROM COST I FROM COST FROH COST ::STATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [2] STATE ENO DEC. I [8] STATE END DEC. I [14] STATE END DEC. I [203 STATE END DEC. I [26] STATE END DEC. IT i I I I Illai*i* 7 *ttt**H~*etl~Ht~a 13 t t * ~ * * * t l 8.00 19 32.00 *~tet*l 6.00 25 25.00 ***e~*IHHH 31HH.~H** ale*t*** 0 H*a**e**i*e:*Heat 14 * t t * * * * , * * t l 6.00 20 27.00 20 I 6.00 26 24.00 **t***l****** 32 *t********** :lillet* q *********tiele***** 15 * * * t i i t i i i i i I H i l i * 21 tHieeiteieel 4.00 27 21.00 27 I 1.00 33 18.00 33 :I#***** 10 t*****#*****I****** 16 l**t********l#*#*** 22 **HH******:t***** 28 *********HeI 3.00 34 18.00 34 IIHIeH II H H i * H e * H I e H H * 17 *eH*ieHe**IHHie 23 *HeHeeeeI*IeHe*e 29 *HHHH*eeleHeee 35 **HHeeeH*I Is,e,e* 12 IIIIIIeeIleIlleIIel IR lllllIlllIII]IIIlli 24 IlllIll|llIIllIllil 30 IIllllllllll~llllll 36 llilIllllill~ ........................................................................................................................ I COST C0ST C0ST C08T COST I FROH COST FROM C0$T FROM COST FROM COST FROH COST ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TQ TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. I [3] STATE END DEC. I [9] STATE END DEC. I [15] STATE END 0EC. I [21] STATE EHD DEC. I [27] STATE END DEC. I I i I I IIIIIII 7 IIIIIIIIIIII~IIIIII 13 IllllIIIIIII~ 9,00 19 33.00 IIIIIIIIIIII# 25 IIIIIIIIIIII~IIiIIl 31111111#IIIIIl I**e*. 0 ************I****** 14 * * * * , * * * * * * I 5.00 20 26.00 20 3.00 26 21.00 26 I--**-32 *******~****I I****** 9 ************I,****t 15 ************I 5.00 21 26.00 21 6.00 27 23.00 ******: 6.00 33 23.00 ******: I****** 10 ************:****** 16 ************I****** 22 *******--*** %00 28 28.00 ****--I 2.00 34 17.00 34 I IeHe** 11 ************:****** 17 **********t*leeeee* 23 ,,***.******:*****e 29 .eeee*******I 8.00 35 24.00 ******: :filial 12 llllllllllllJlllil] 18 llIllllIIIll~IlllIl 24 IlIIIIIllIll~IIlllI 30 IIlllIillIIiIllllIl 36 IIIIIllIllIi] I ........................ I................................................ I................................................ IT C0ST I C0ST COST I C0ST COST II FROH COST I FROM COST FROH COST I FROM COST FROM COST IISTATE TO TO OPT. ISTATE TO TO OPT. ISTRTE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [4] STATE ENO DEC. : [lO) STATE END DEC. I [16] STATE END DEC. I [22] STATE END DEC. I [28] STATE END DEC. IT I I I I :~IIIill 7 IIIIIiIIIIIl:IIIIII [3 IIIIIIIIIIII:IIIIiI l~ IIIIIIIIIIIIIIIIIII 25 IIIIIIIIIIIIIIIIIII 311111111111111 ]IIIllII 0 IIIIIIIIIIIIIIIIIII 14 IIiIIiIlllIII 4.00 20 25.00 20 IIIIIII 26 IIIIIIIIIIIIIIIIIII 32 IIIIIIIIIIIII II****** ? *,*H,t*t*Ieee*, 15 *******--*~*I 6.00 21 27.00 *see*el 9.00 27 26.00 27 I****** 33 *ease*e.H,el IIH**** I0 ******--****I*--*** 16 ************ 8.00 22 34.00 **H**II0.00 28 2%00 ******I 4.00 34 1%00 34 I IIHeH* 11 t t t t i t t t t t H I t i i t t t 17 * H I e H H H e I H i i H 23 t t * * H I * * . * I I 0 . 0 0 29 27.00 tHeeil 9.00 35 2S.00 teHetI :~Ittttt 12 t t t t t t t t t i i I l t } t I t t IS t t t t I t i t t t t t { I t t t t t 24 t t i t t t I t I t t t : t # t t I t 30 t t t t t t t I t t t t 4,00 36 20.00 t l t t t t : :I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IT COST COST COST COST COST IT FROH COST FROM COST FROH COST FROM COST FROH COST IISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [5] STATE END DEC. I [ l l ] STATE END DEC. I [17] STATE END DEC. I [23] STATE END DEC. I [29] STATE END DEC. {:tttttI 7 ******JHIeilee*H* 13 *****teeie**l****** 19 ****ee**eee*leeeee* 25 *eeeeeee*eeeIeeeeH 31 ********H**I {{tttlIt 0 ttttttttiItt{tIttiI 14 t i I t t t t t t i I t l t t t t t t 20 t t I t t I t t t t I I { t t t I t t 26 t t t t t t t t t I t t l t t t t t t 32 t t I t t t t l I t I t l l{tttttt 9 tttttlittttt:tttttt 15 t t t t t t I t I l t t 6,00 21 27.00 t t t t l I = t t I t t t 27 t I t t I t t i i i t t : t I t t t t 33 t t t t l t t t t t t t : ::tttttt [0 t t t t t t t i t t t t = t t i t t t 16 t t t t t t t t t t t t 7,00 22 33.00 * . . H 4.00 28 23.00 t t i t i t } t t i t t t 34 t t t t t i t t t i i t l ::tttttt II t t t t t t t t t t t t { t t t t t t 17 t t t t t t t t t t t I 3.00 23 25.00 23 5.00 29 22,00 29 I 5.00 35 21.00 . t t * t l =:-----12 t t t t t t t t t t t t { t t t t t t 18 t t t t t t t t t t t t : t t t t t t 24 t t t t I t * t t t t t 6.00 30 26,00 ******: 1,00 36 17.00 36 {: . . . . . . . . . . . . . . . . . . . . . . . . :. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IT COST I COST COST COST COST :I FROH COST I FROH COST FROM COST FROH COST FROH COST IISTATE TO TO 0PT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. :I [61 STATE END 0EC. I [12] STATE EN0 0EC. : [18] STATE END DEC. I [24) STATE EN0 DEC. I [30] STATE EN0 0EC. IS I I I I ::**tee, 7 ttttttttttti:titttt 13 t t t t t i t t t t i i l t t t t t i 19 t t t t t t i t t t t t { t t t t t t 25 i t t t i t t t i t t t : t t i t t t 31 i t t t t t t t t t i t l :=tttttt 0 tttttttittti{ 6,00 14 33.00 t t i t t t ~ i t t t t t 20 t i i t t t t t t t t t ~ t t t t t t 26 l t t t t t i t i t t i ~ t t i t t t 32 t t t t t t i t t t t t {:tttttt 9 ttttttttttttl 4,00 15 30,00 15 ] t t t t t i 21 t t t t I t t t t t t t ~ t t t t t t 27 t t t t t t t t t t t t ~ t t t t t t 33 t t t t t t t t t t t t ::tttttt |0 t t t t t t t t t t t t l 7,00 [6 32.00 ------= 9,00 22 35.00 t t t t t t { t t t t t t 20 t t t t t t t t t t t t { t t t t t t 34 t t t t t t t t t t t t ={ittttt l l t t t t i t t t t t t t ~ 0,00 17 33.00 t t t t t t = R,00 23 30.00 t t t t t t = [.00 2q [0.00 27 { t t t t t t 35 t t t t t t t t t t t t ]lit}tit 12 t t t t t t t t t t t t = 3.00 18 30.00 18 I 9.00 24 27.00 24 I 3.00 30 23.00 **--**: 4.00 36 20.00 36 ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: IIHinimum Cost IMinimum Cost IHinisum Cost IMinimum Cost IHinisum Cost alto the Final Ito the Final Ito the Final Ito the Final Ito the Final tIStage is: H*tH IStaoe is: 30.00 :Stags is: 25.00 IStage is: 18.00 IStage is: 17.00
Fig. 9. Solution of oil transport problem (part I).
III. A N OIL T R A N S P O R T T E C H N O L O G Y P R O B L E M
This problem is discussed in (Phillips et al. [4]) and its network representation is given in Fig. 8. The spreadsheet we have prepared (DP6RIOC.WKS) can solve a problem with a maximum of nine stages and a maximum of six states in each stage. Numbering the states in the oil transportation problem according to the spreadsheet table convention and inputting the costs in the spreadsheet network (not shown), the optimal routes are found by consulting Figs. 9 and 10 as follows:
CAIE 10:3-E
(i) [12] ~
[15] ~
[20] ~
[27] ---, [34] ---* [41] ~
[47] ---, [52] --, [57],
(iS) [12] ~
[18] ~
[24] ~
[29] ---, [36] ---* [41] ~
[47] ---, [52] ~
[57],
212
M. PARLAR
(iii) [12] --> [15]
[33]
[40] --> [47] --> [52] --> [57],
(iv) [121 --> [151 --~ [20] --* [27] --, [34]
[41] --> [48] --> [52] ---> [57],
(v) [12]--> [18] --~ [24] --* [29] ---, [36]
[411 --> [48] ---> [52] ~
(vi) [12]--> [15]
[21] --* [26] ~
[21] --* [26] ~
[571,
[34] --~ [41] ----> [481 --> [52] ---> [57],
(vii) [12] --> [18] ---> [241--> [29]---> [36]--> [41]--> [47]--> [521--> [571. T h e m i n i m u m total cost is 30.00 as the b o t t o m part of Fig. 9 indicates.
••=•=•=•=•=•===•=••••====••=•=•=•====•••=•=•=•=•••====•••••••===••====••••==•=••==•====••••=• I COST I FROM
ISTATE TO I [31] STATE
COST TO END
: COST I FROM OPT. ISTATE T0 DEC. I [37] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC. I [43] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC, I [49] STATE
I l,m****
I 37 ****i*******l******
I 43 ***6********Is*****
I 49 ********s***l 7.00
I~HH~
38 } H * ~ } * H H } I H } O H
44 * * H } H t H H I ~ H H * 45 IIIIiIIIiIIIIIIIill
50 } H H H * I * I t l H I * * *
COST TO END
I COST : FROM OPT. ISTATE TO DEC. [55] STATE
55 7.00 55 58 H } t * * H * H t I
0.00
55
COST TO OPT. END DEC. 0.00
55
lliIIII 3~ IIIIIIIi]III[liIIiI 5[ IiiIiII}IiIIIIIiIII 57 III}lIIIiIIIl IIIIIII 40 II*IIIIIIIIIIIIIIII 46 IIII*IIIIIIIIIIIIII 52 IIII*II*IIIIIIIIIII 5R IIIIIIIIIII*I ~IIIIII 4l IIIIIIIIIIIIIIIIII* 47 IIIIIIIIIIIIIIIIIII 5~ IIIIIIIIIIII~IIIIII 5~ ;I~I*~*HH;I IIIIIII 42 IIIIIIIIIIIIIIIIIII 4S IIIIIIIIIIIIIIIIIII 54 IIIIIIIIIIII~IIIIII 60 IIIIIIIIIIIII I................................................ I........................ I........................ I........................
I COST I FROM ISTATE TO I [32l STATE I
COST COST FROM TO OPT. ISTATE TO EHO OEC. I [38] STATE
I COST COST I FROM TO OPT. ISTATE TO END 8EC. I [44] STATE
I
IIHH~ lJliIH I*HHI
37 *H~*HHHiIH**H 38 IIIHI~HH*I 6.00 39 *~HiiHHHI 7.00
IiIIIII IIIIIII Iiimiii
40 IIIIIIIIIIII[IIIIII 4[ IIIIIIIIIIIIIIIIIII 42 llI*llllllllllIIiI*
I
43 44 45 46
IH~*IHHI*I 5.00 16.00 HHtll 7.00 14.00 45 I~***H
IIIIIIIIIIIIIIIIIII 47 IIIIIIIIIIIIIIIIIII 48 IllIllllll*IIIllllI
I................................................ I COST COST I FROM COST FROM COST ISTATE TO TO OPT. ISTATE TO TO OPT. I [33] STATE END DEC. I [39] STATE ENO DEC. IiH*H 4.00 9.00 7.00
37 38 39 40
*HI*IHH**IH*;H 18.00 ****;*i 7.00 23.00 ******i 8.00 17.00 40 1%00
I COST COST I FROM TO OPT. ISTATE TO END DEC. I [50] STATE
43 44 45 46
I COST COST I FROM TO OPT. ISTATE TO END OEC. [56] STATE
COST TO OPT. END DEC.
I
49 50 51 52
12.00 **H**I 3.00 10.00 50 I 6.00 *HHIi~I*~*IH*~II
55 3.00 55 56 6.00 **HHI 57 Hii****i*H
IIIIIIIIIIII~IIIIII 53 IIIIIIIIIIII~IIIIII 54 IIllllllllIIIlllll*
50 IIIIIIIIIilI 5~ IIIIIIIIIIII 60 llllllllllIl
0.00
56
0.00
56
I........................................................................ i COST COST COST I FROM COST FROM COST FROM COST ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. [45] STATE ENO DEC. I [51] STATE ENO OEC. [57] STATE ENO OEC.
***;**m****~ 5.00 17.00 ******I 4.00 15.00 ******I 4.00 14.00 46 I,HI**
49 50 51 52 53
12.00 ;*i***I 5.00 7.00 50 I 6.00 9.00 ******I 7.00 H**iHH***I*H***
55 56 57 58 59
5.00 55 0.00 6.00 ******I 7.00 ***;**I H*H*HH**
57 0.00
57
II*IIII 411111111111*IllIiIII 47 IIIIIIIIIII*IIIIIII IIIIIIIIIIiI~IIIilI IIIIIIIIIIII II*IIIl 42 IIIIIIIIIIII]IIIIII 4R IIIIIIIIIIIIII*IIII 54 llIIIIIIIIIIIlIIIII 60 IIIIIIIIIIII I................................................ I........................................................................
I COST FROM ISTATE TO I [34] STATE I
1~IH** 8.00 i 6.00 t 6,00
COST COST FROM TO OPT. ISTATE TO END DEC. I [40] STATE
I COST COST I FROM TO OPT. ISTATE TO END OEC. I [46] STATE
COST COST FRO~ TO OPT. ISTATE TO EHO DEC. I [52] STATE
COST COOT FROM TO OPT. ISTATE TO END OEC. I [58] STATE
I
I
i
I
38 39 40 41
~H~HI~H*I*;HH 22.00 ******I 8.00 16.00 *HH*I 8.00 15.00 41 I 4.00
44 45 46 47
~HI*HI*H*I 3.00 15.00 ******I 2.00 13.00 H***II 2.00 10.00 47 IHIIII
50 6.00 I~*H~I 5.00 51 7.00 ******I 3.00 52 5.00 52 I 6.00 53 IlIIllI{{I*IIllIlI{
COST TO OPT, EHO DEC.
56 5.00 ~*lIH1 57 3.00 57 1 58 6.00 H*H*I 59 IlIIllmlI*Ill
IIIIIII 42 IIIIIlIIIIII]IIIIII 40 IIIIlIIIIIIIIIIIIII 54 IIIII{*IlIIIIIIIII* 60 IIIIIIIlIIIII I................................................................................................ I........................
I COST I FROM ISTATE TO I [35] STATE I
COST COST FROM TO OPT. ISTATE TO EHO OEC. I [41] STATE I
IHIH~ I*HH* I**HH I 7,00 I 7.00 I 6.00
37 38 39 40 41 42
*~*~*H~H*I~I*÷~ HH*HI*H*IH*H* HH~*H~H&I****I* 17.00 ***H;I 7,00 [&DO 41 I 3.00 18.00 HHHI 3.00
I,H***
38 Hm*~HH***IItH*~
COST TO END
COST FROM OPT. ISTATE TO OEC. I [47] STATE I
43 44 45 46 47 48
~I~HIHH**I*H~** *H***÷HH*IIHH~ *~***Hi**I 2.00 12.00 *i***~I 3.00 9.00 47 I 3.00 9.00 48 II*HH
COST TO EHO
COST FROM OPT. ISTATE TO DEC. I [53] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC. [59] STATE
COST TO END
OPT, OEC.
I
H**H*~II*~ 0.00 59 0.00 59 *I~HHHH 8.00 *H,HI 5.00 58 I 7.00 *IHnl H&**&~***HI I........................ ;........................ ;........................ '........................ I........................ I COST I COST I COST COST I COST I FROM COST I FROM COST I FROM COST FROH COST I FROM COST ISTATE TO TO OPT. IOTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. I [36] STATE END OEC. I [42] STATE END DEC. I [48] STATE EHO DEC. I [54] STATE ENO DEC. [60] STATE END OEC. 44 tt***~*~H**I*H***
49 50 51 52 53 54
~*~HHH~I~IH**~ *HH**HH*I*~H~* 7.00 ****HI 8.00 6.00 52 I 5.00 8.00 ~Hl;il 7.00 ~HHH*****I~HI**
50 **~*~t*t****I*HmH
55 56 57 56 59 h0
56 Ht**t****~*
I****H 40 * * * * * * H * H * I I * * * H 46 H t * * * * t * * * * I 3,00 52 6.00 52 I 2.00 58 2.00 58 I 7.00 41 16.00 41 I 6.00 47 12.00 47 I 6,00 53 II.00 t * * * * t l 6.00 59 6.00 **t***I I 9.00 42 21.00 t * H t * I 9.00 48 I5.00 *t****I 5.00 54 7.00 ******I 4.00 60 4.00 ****t*I ============================================================================================================================= IHinimuI Cost IMlnimum Cost IMznimum Cost IHinimui Cost IMiniium Cost Its the Final Its the Final Its the Final Its the Final Its the Final IStage is: 15.00 IStage is: %00 IStage is: 5.00 IStage is: 2,00 IStage is: 0.00
Fig. 10. Solution of oil transport problem (part I1).
Dynamic programming on an electronic spreadsheet
213
IV. SUMMARY AND CONCLUSIONS
We have presented a method whereby an electronic spreadsheet (Lotus 1-2-3) can be used to solve discrete stage-discrete state dynamic programs. Once the spreadsheet is prepared, the user only has to enter the data in the branches of a network representing the problem. The spreadsheet model automatically calculates the minimum cost and the optimal route(s). It must be re-emphasized that this method may be useful in teaching the elementary concepts of dynamic programming in introductory management science/operations research courses. No programming knowledge is required to set up the spreadsheet and this may simplify the preparation of the model. The size of the problem one can solve is limited by the disk space available to the user. The oil transport problem with six rows and ten columns required about 80K of disk space. Given the current storage capacities of hard disks one could probably solve a problem with 25 rows and 25 columns provided that the computer itself has sufficient random access memory (RAM). The versatility of electronic spreadsheets could also be advantageously exploited to solve discrete-time linear-quadratic optimal control problems. Since Zhis class of control problems has a closed-form solution, Lotus 1-2-3 with its graphics capability can be used to find the optimal state and control trajectories and immediately graph them. Acknowledgement--This research was supported by the Natural Sciences and Engineering Research Council of Canada under Grant No. A4608. REFERENCES 1. H. D. Toong & A. Gupta, Personal Computers. Scientific American 247, 86-107 (1982). 2. J. M. Jones, Decision Analysis with Personal Computers and Spreadsheet Software, paper presented at the 26th International TIMS Meeting, Copenhagen, June 1984. 3. F. S. Hillier & G. J. Lieberman, Introduction to Operations Research, 3rd Edn. Holden-Day, San Francisco (1980). 4. D. T. Phillips, A. Ravindran & J. J. Solberg, Operations Research. Wiley, New York (1976). 5. H. Wagner, Principles of Operations Research. Prentice-Hall, Englewood Cliffs, NJ (1969). 6. S. E. Dreyfus & A. M. Law, The Art and Theory of Dynamic Programming. Academic, New York (1977).
DYNAMIC
0360-8352/86 $3.00 + 0.00 PergamonJournals Lid
PROGRAMMING ON AN ELECTRONIC SPREADSHEET
MAHMUT PARLAR Faculty of Administration, University of Bew Brunswick, Fredericton, N.B. E3B 6A5, Canada (Received for publication 5 February 1986) Abstract--We develop a Lotus 1-2-3 spreadsheet which can solve the "stagecoach" problem by dynamic programming. More general network models which have multiple initial and/or final states can also be solved by the same approach and the optimal route(s) can be found from the information generated in the spreadsheet. Another example from oil transport technology with eight stages and a maximum of six states is solved by a larger spreadsheet model and the seven alternative routes are found.
I. INTRODUCTION
Although electronic spreadsheets such as Visicalc were originally designed to deal with accounting type problems (Toong and Gupta[1]), they are now being used to solve OR/ MS type decision problems. In one particular application (Jones[2]) uses the Lotus 12-3 spreadsheet to solve a decision analysis problem with posterior probabilities. He also presents a sensitivity analysis using the/Data Table commands of the program. We have used I-2-3 to find numerical solutions to ordinary differential equations and optimal control and state trajectories to the discrete-time linear-quadratic control problem. We have also used this program to solve discrete stage and discrete state dynamic programs which will be presented in this paper. We will first discuss a prototype "stagecoach" problem (Hillier and Lieberman[3]) and describe the spreadsheet model in detail. A sensitivity analysis will also be presented. A more general network model with multiple initial and final states will also be discussed and an oil transport technology problem mentioned in (Phillips e t al.[4]) will be solved using the more general spreadsheet. II. DYNAMIC PROGRAMMING AND THE "STAGECOACH" PROBLEM
Dynamic programming (DP) is a technique used to find the optimal solutions to timestaged decisions. DP has been applied to problems dealing with inventory management, production scheduling and employment smoothing, capital budgeting, replacement and maintenance among others. Since there is no unique mathematical formulation for DP problems, standard computer packages to solve these problems have not been developed. In this section, after describing a prototypical example in DP, we will present the development of a Lotus 1-2-3 spreadsheet which can solve special types of dynamic programs. A problem specially constructed to illustrate dynamic programming is the "stagecoach" problem (HiUier and Lieberman[3]). It concerns a mythical salesman who had to travel from the west coast (California) to the east coast (New York) in the 19th century. The salesman had to travel through unfriendly Indian territory and he had information on the life insurance premiums he would have to pay between any two states. Since there were several possible routes he could take with different insurance premiums, his problem was that of choosing the cheapest route between state [1] (California) and state [10] (New York). The individual premiums between any two states are provided at the tips of the branches as depicted in the upper portion of Fig. 1. The lower portion of this Lotus 1-2-3 spreadsheet (STGCOACH.WKS) contains the functional equations and their computations which will be described later in this section. 203
204
M . PARLAR
This spreadsheet (STGCOACH.WKS}solves the 'Stagecoach' problem by dynamic programming. Reference: Introduction to Operations Research, 3rd Edition F.S. Hillier & G.J. Lieberman Chapter 7. ................................................................................................................................
:to[2] . . . . . . 2.00
Ito[5] 12] ............ 7.00 I Ito[6] . . . . . . 4,00 I Ito[7] ...... 6.00
:to[8] [5] ............ 1.00
:to[q] ...... 4.00
[8] ...... )to[lO]
Ito[5] :to[5] [I] ............ 4.00
. . . . . . 3.00 Ito[6] [3] ............ 2.00 linT71 ...... 4.00
...... 3.00
Itn[8] ...... 6,00 [6] ...... Ito[9] ...... 3.00
IENO [IO] ....... O,O0 Ito[lO] ...... 4.00 I [g] ......
Ito[5] . . . . . . 4.00
:toT4] . . . . . . 3.00
1
Ito[81
Ito[6] ...... 1.00 I ]to[7]
...... 3.00 I
[4] . . . . . . . . . . . .
;tolg] (7] ............ 3.00
5.00
(STAGE 1) (STAGE 2) (STAGE 3) (STAGE 4) ................................................................................................................................ =================================================== COST FROM ISTATE
TO
TO
[21 STATE
7.00 4.00 6.00
COST FROM OPT. ISTATE
EN0 DEC.
TO
COST TO
OPT,
[5] STATE END DEC, : : : : : : : : : : : : : : : : : : : : : : : : : :
5 11,00 5 1.00 6 11.00 6 4.00 7 12.00 * * * H * I
I=. . . . ===================I. . . . . . . . . . . . . . . . . . . . . . . .
8 4.00 8 1 g B.00 ***eH~ COST I FROM COST : ~STATE TO TO OPT. I . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . [8] STATE END DEC.
: COST
COST : COST 3.00 10 3.00 I FROM COST FROM COST ; FROM COST :STATE TO TO 0PT, :STATE TO T0 0PT. ISTATE TO TO OPT, I Ell STATE ENO OEC. t [3l STATE END OEC, ) (b] STATE ENO DEC. - . . . . . . . . . . . . . . . . . . . . . . . I 2.00 I 4,00
2 13.00 aeeeHI 3.00 3 ll,00 3 2.00
5 7.00 5 I 6.00 6 % 0 0 * * H * * I 3.00
l0
8 9.00 t e e * H l 9 7.00 9 ~ COST
i 3.00 4 ll.O0 4 4.00 7 10.00 **HH) I I............ ============I ................................................
I FROM ISTATE TO [g] STATE
I
COST FROM COST STATE TO TO OPT. [tO] STATE ENO DEC. 0,00
COST TO END
=. . . . . . . . . . . . .
l0 0,00
10
OPT. l===== ..... === ............. t OEC. I
:(Sensitivity Analysis) Minimum cost to ENO [lO] from START [I]: ll.O0
COST FROM :STATE
COST 4.00 I0 4.00 COST FROM COST TO TO OPT. ISTATE TO TO OPT. [4] STATE END DEC. I [ 7 l STATE END DEC. : : : : : : : : : : : : : : : : : : : : : : : : : :
I 4.00 I.O0 5.00
5 o.oo
5:3.00
6 8.00 6 [ 3,00 7 It,00 * * * * * * I : I
10
COST FROM [I] TO [3]
I
o 6.oo
o:
MIN. COST TO OPT, END OEC. .....
~Bi:ik~'ii:~6' I.O0 2,00
I I
3.00 4.00 5.00 6.00 7,00
3 ll.O0 3 ll.00 l,HI II.00 IHl* II.00 eee**
B.00
II.00 H*~
===================================================
O.O0 %00 10.00
S<=NO~
g 7.00 ******~ I
3 3
q.00 II.00 Heae ................................................................................................................................
F i g . 1.
Stagecoach problem.
The user only has to enter the one-stage costs (insurance premiums in this example) at the tip of each branch which will be automatically copied to the proper location in the table at the bottom of the worksheet. For example, suppose that E l 6 is the cell address of the cost of going from state [1] to state [3], its content being $4.00. If B52 is the address of the same cost in the DP table, its content is defined as + E l 6 so that whenever the user changes the cost in the network, it would be automatically copied to B52. Other one-stage costs are copied in a similar manner. The DP table of the S T G C O A C H . W K S spreadsheet automatically computes the optimal route(s) and displays the minimum cost for the entire trip at the lower left portion. We see that there are alternative optima since the optimal decision in state [I] is to go either to state [3] or to state [4] in the next stage. It is not optimal to go to state [2] and hence this appears as a set of asterisks in the very left portion of the table.
205
Dynamic programming on an electronic spreadsheet
The three optimal routes are (i) [l] ---, [3] ~ [5] ~ [8] ---, [10], (ii) [1] ~ [4] ~ [5] --~ [8] ~ [10], (iii) [1] ~ [4] ---, [6] ~ [9] ~ [10]. The minimum cost of all these optimal routes is 11.00 and this is indicated at the lower left part of the table. Clearly, since the costs are automatically copied to the table from the network branches, any change in any of these costs will result in an automatic recalculation of the worksheet resulting in possibly different optimal routes. Although testing the effect of different values of costs on the optimal solution can be done manually, Lotus provides a sensitivity analysis whereby these effects can be observed automatically. Focusing our attention on the lower right section of Fig. 1, we see the results of using "/Data Table I " sequence of commands. This table indicates that varying, e.g. the one-step c o s t C13 of going from [1] to [3] between 1.00 and 9.00 results in different decisions and/or different minimum cost values. Currently, when this cost is 4.00, the optimal decision is to go to state [3] and the corresponding minimum total cost is I 1.00. When the cost c~3 goes up to 5.00 or higher the [I] ~ [3] route is no longer in the optimal solution, but the minimum cost is still 11.00. The graphing capability of Lotus is also utilized to produce the diagram in Fig. 2, which describes pictorially the sensitivity analysis results presented above. The construction of the tables follow the principle of optimality and the related functional equations (recursive relationship): n = 1,2,3,4,
f*(s) = min{cs~. + fn+l(Xn)}, Xn
where x, is the immediate destination on stage n, f*(s) is the minimum total cost of the best overall policy for the remaining stages given that we are in state s and have
STAGECOACH
PROBLEM
(Sensitivity Analysis) 11
0
0
0
0
10 9
8 7 6 5 4 3
0
0
O
2 1 o 1.oo
I
I
I
I
I
I
I
~.00
3.00
4.00
5.00
6.00
7.00
8.O0
UlN. COST TO END
Cost f r o m [1] to [3l o OPTIMAL DECISION
Fig. 2. Sensitivity analysis for stagecoach problem.
9.00
206
M. PARLAR 846: (F2) +046+~MIN($X$51)
35
M
N
0
P O
===================
READY
R
S
T
34 35 COST 36 TO TO OPT, 37 38 STATE END DEC. =. . . . . . ==. . . . . . . . . =. . . . . ' 39 40 8 4,00 8 9 8.00 ******~ COST 41 I FROM COST 42 43 ISTATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . .
~ [8] STATE
V
O
N
X Y Z
Z
I
ENO DEC,
45
46 l0 47 COST 48 TO TO OPT. 49 STATE END DEC. l . . . . . . . . . . . . . . . . . . . . . . . . 50 ] 51 8 9.00 ******~ 52 9 7.00 9 I COST
COST FROH COST STATE TO TO OPT. riO] STATE END DEC. 0.00
10 0o00
T46: $IF(+S46=~HIN(S46),R46,9999999)
tO
READY
H N O P O R S T U V il l Y Z 33 34 35 COST 36 37 TO TO OPT. 38 STATE END DEC. =. . . . . . . ===. . . . . . . . . . . ==' 39 8 4.00 8 40 9 8.00 ***e**l COST 41 FROR COST 42 ]STATE TO TO OPT. ' . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 [8] STATE END DEC. 44 . . . . . . . . . . . . . . . . . . 45 46 3,00 l0 3.00 [ ' ~ COST COST "-"" FROH COST 47 TO TO OPT. l STATE TO TO OPT. 48 49 STATE END DEC. - . . . . . . . . . . . . . . . . . . . . . . . : [10) STATE END DEC. I 50 8 9.00 **e**e~ ~ 0,00 10 0,00 lO 51 9 7.00 9 ~ COST 52 m. . . . . . . . . . . . . . . . . . . . . . . . . .
Fig. 3. Calculations for stage 4.
made the best decision, csx, is the one-step cost of going from state s to x , . Clearly, the objective is to find i f ( l ) and the corresponding policy. Figure 3 indicates the solution on a part of the spreadsheet when there is one stage to go. When in state [8] the cost to [10] is 3.00 + 0.00, or in the spreadsheet Q46 + @ M I N (X51). This corresponds to c8,,o + f~(lO),
f*(lO) = O.
Obviously in this case since the only alternative is to go to [I0], this is what the table indicates as the optimal decision in state [8]. When there are two stages to go and when we are, for example, in state [6] there are two possible routes to take, i.e. [8] and [9]. If we go to [9] from [6] the total cost is 3.00 + 4.00, or in the spreadsheet L52 + (d, MIN($S$57) which corresponds to C6.9 + f 4*9 (),
f*(9) = 4.00.
The table indicates that, if we are in [6], it is n o t optimal to choose [8] as the state to visit in the next stage. This is indicated by the asterisks under OPT. DEC. corresponding to state [8]. Of course, the total cost of going from [6] to [10] via [8] is calculated as + L51 + @MIN($S$46) where $46 is the cost of going from [8] to [10]. The lower portion of Fig. 4 describes the contents of cell 052: @IF( + N52 = @MIN($N$51..$N$52), M52,
Dynamic programming on an electronic spreadsheet
207
9999999). This means "if N52 (the total cost of traveling from [6] to [10]) is equal to the minimum of N51 and N52 then the state corresponding to N52 (i.e. M52 or state [8]) is the optimal state to visit next; otherwise block out the route leading to M52 by assigning 9999999 which produces asterisks." To summarize, when in state [6], the next state to visit would be [9], as shown in Fig. 4. By the same arguments, we observe that when there are three stages to go, and say, when we are in state [3] it is optimal to go to [5] only, with a corresponding total minimum cost of 3.00 + 4.00 which the spreadsheet calculates from G51 + @MIN($N$40..$N$41). This corresponds to c3.5 + f~(5),
f*(5) = 4.00.
The table at the bottom of Fig. 5 indicates how the spreadsheet finds the optimal state to visit in the next stage. The 1-2-3 statement J51:@IF(I51 = @MIN($1$51.. +$I$53), H51, 9999999) does the following: It finds the minimum value of all the COST TO END values corresponding to each state (i.e. [5], [6], and [7]) in the next stage. The optimal N52: (F2) +L52+QHIN($S$57)
READY
H I J K L H N O P Q R S T 40 5 ll.O0 5 ~ 1.00 8 4.00 8 ] 41 6 11.00 6 : 4.00 9 8.00 *÷÷***: COST 42 7 12.00 **eee÷l : FROR COST 43 ]STATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [8] STATE END DEC. 45 46 COST 3.00 10 3.00 10 47 COST FROR COST 48 TO TO OPT. :STATE TO TO OPT. 49 STATE END DEC, [6] STATE END DEC. - . . . . . . . . . . . . . . . . . . . . . . . 5O 51 5 7.00 5 ~ 8 .].,0..0..****÷*I 52 6 9.00 ******:("3.00").. 9JT~ 9 ] COST 53 7 lO.O0 ÷ * ÷ * * * I ~ " " " ' - ' " " - ' - - \ : FROH COST 54 ~ \ ~STATE TO TO OPT. 55 . . . . . . . . . . . . . . . . . . '..................... '~.c= [?] STATE END DEC. 56 57 COST 4.00 [0 (4.00} 10 5B COST FROH COST 59 TO TO OPT. :STATE TO TO OPT.
052: tlF(+H52=eHIN($H$51..$N$52)~H5219997799) H I J K L M N 0 P O R S T 40 5 11,00 5 = 1.00 8 4.00 8 i 41 6 ll,O0 6 ~ 4.00 ? 8.00 i**i**~ COST 42 7 12,00 ******l I FROM COST 43 ISTATE TO TO OPT. 44 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ~ [8) STATE END DEC. 45 46 COST I 5.00 tO 3.00 10 47 COST FROH COST 4B TO TO OPT. :STATE TO TO OPT. 49 STATE END DEC, [5] STATE END OEC, ] . . . . . . . . . . . . . . . . . . . . . . . . 50 51 5 7.00 5 6.00 8 %00 ******I 52 6 %00 ******~ 3.00 9 7.00 ~rt~l COST 53 7 lO.O0 *t****: FROH COST 54 :STATE TO TO OPT. 55 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [9) STATE END DEC, 56 57 COST 4.00 l0 4.00 10 58 COST FROH COST 59 TO TO OPT. =STATE TO TO OPT.
Fig. 4. Calculations for stage 3.
READY
208
M. PAP,CAR 151: (F2) +G51+IMIN($N$40..$N$41)
READY
C D E G H I J L M N O P 34 35 COST COST 36 FROM FROM COST 37 ',STATE TO TO OPT. :STATE TO TO OPT. 38 [2] STATE END DEC. [51 STATE END DEC. 3q 40 7.00 511.00 5 l.OO 865"~ Ri 41 4.00 6 II.00 6 4.00 94[I]~I~I)**z-***~ 42 6.00 712.00 .****',I1 ~ I 43 44 . . . . ======'-. . . . . ==~. . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . '. 45 46 47 ~OST :FRO, COST / :FROM COST '. 48 TO TO O~T. ~STATE TO TO/O,T. :STATE ~0 TO O~T.'. 49 STATE e,0 Dec. ! ~ Dec. i ~] STATE E,D oec. ! 50 51 2 ls oo * , * , , , I L ~ " ~ 5 i 600 o 9.00 52 3 11.00 3 : 27'00 6 -qTO~t*****l 3.00 ? 7.00 9 ', 53 4 11.00 4 I 4,00 7 10.00 **.z.**z.l
,,,,,,i
35h ~IF(+I51=~HIN($1551..$1553),H51,9999999) C D E O H I J L M 34 ;35 COST COST 36 FROM FROM 37 ISTATE TO TQ OPT. ~STATE TO 38 [2] STATE END DEC. [5] STATE 39 40 7.00 5 11.00 5 1.00 8 41 4.00 6 11.00 6 4.00 9 42 6,00 7 12.00 ******I 43 44 45 46 COST COST 47 COST FROM COST FROM 48 TO TO OPT. ~STATE TO TO OPT. ISTATE TO 4? STATE END DEC. : [33 STATE END DEC. I [&] STATE 50 51 2 13.00 ******I 3.00 5 7.00 F'S]: 6.00 8 52 3 11,00 3 I 2.00 6 9.00 ***';'('(I 3.00 9 53 4 11.00 4 I 4.00 7 I0.00 *He*el = = = = = = = = = = = = = = = = = =
READY N
0
COST TO OPT. END DEC. 4.00 8 8.00 ******I
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
COST TO OPT. END DEC. 9,00 ******I 7.00 9 =
Fig. 5. Calculations for stage 2.
decision is to go to that particular state for which the COST TO END value equals the minimum of all those COST TO END values. Otherwise, the program prints asterisks which block the routes to nonoptimal states. The final state calculations and the optimal routes can be seen in Fig. 1. As mentioned before, there are three alternative optima all of which can be easily extracted from the tables in Fig. 1. Although this approach may not necessarily simplify any of the calculations in dynamic programming, it may be useful from an educational point of view. For many students taking management science/operations research courses, dynamic programming has been the most difficult topic to absorb. The spreadsheet approach presented here may be instrumental in clarifying the often puzzling backward induction method common to many problems. Not all the dynamic programming problems have a single starting state and a single ending state. In fact, many problems such as those in equipment replacement, or inventory control (Wagner[5]), when cast in a dynamic programming framework, become stagecoach type problems with multiple initial or final states. To be able to deal with problems of this kind, one has to extend the tables presented in the Lotus spreadsheet. For example, the spreadsheet DP3R5C.WKS in Fig. 6 allows up to three states in the initial and final stages. To solve the stagecoach problem one would now have to re-
Dynamic programming on an electronic spreadsheet
209
number the states, but, of course, the optimal solution would still be the same. The routes which are not allowed are assigned a large cost, such as 9999999, which appears as asterisks in the spreadsheet. A slightly different version of the stagecoach problem with three final states is solved in Fig. 7 which has a lower total minimum cost than the previous problem. The optimal routes are found as (i) [2] ~
[5] ~
[7] ~
[11] ~
[13],
(iS) [2] ~
[6] ~
[7] --~ [11] --~ [13],
with the optimal cost of 8.50. Since almost every discrete stage, discrete state dynamic programming problem can be represented as a network (Dreyfus and Law[6[) it would be useful to have a larger spreadsheet which could accommodate more stages and states than the previous ones. In the next section we discuss one such problem in the oil transport technology area which has eight stages and a maximum of six states in each stage.
This spreadsheet (DP3R5C,iKS) solves a DP problem mith 3 rows and 5 columns (4 stages). Reference: Introduction to Operations Research, 3rd Edition, F.S. Hillier & G.d. Lxebersan, Chapter 7. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
:to[4] [1] . . . . . . . . . . . . ****H :to[5] ...... ***H* :Lot6] ...... *HH*
:to[7) [4] . . . . . . . . . . . . 7.00 :to[8] . . . . . . 4.00 :to[9[ . . . . . . 6,00
:to[10] [7] . . . . . . . . . . . . 1,00 :to[11] ...... **--** ~t0[12] . . . . . . 4.00
:to[13] [10] . . . . . . . . . . . . a**e}e :to[14[ . . . . . . 3,00 :totIG] . . . . . . ****ee
~to[4] . . . . . . 2100 :to[5[ [2] ............ 4.00 :to[6]
:to[7] . . . . . . 3600 ~to[8] [5] ............ 2.00 Ito[9]
4.00
~to[lO] . . . . . . 6100 :to[It[ [G] ............ ~*~ :to[12] . . . . . . 3.00
:tot13] . . . . . . ]]e~[e :to[14] [II] ............ eeeeee Ito[15l . . . . . . **eH*
:to[7] . . . . . . 4.00 ~to[fl] . . . . . . 1,00 Its[g[ [6] . . . . . . . . . . . . 5.00
:to[lO] . . . . . . S,O0 ~to[11] . . . . . . *~*~e :to[t2) [g] . . . . . . . . . . . . 3.00
:to[l~] . . . . . . ***eee 1to[14] . . . . . . 4.00 Its[t5[ [12] . . . . . . . . . . . . ******
......
3.00
~to[4] . . . . . . **~*** :to[5[ ...... aHHe :to[6] [3] . . . . . . . . . . . . *ee*H
......
[STAGE 1)
(STAGE 2)
(STAGE 3)
lEND [13] . . . . . . . . . . . .
0.00
:END [14] ............ 0.00
IEND [15] . . . . . . . . . . . .
0.00
(STA6E 4)
====================================================================================================================• = ===========
COST : COST : COST COST : COST FROM COST I FROM COST : FROM COST FROR COST I FROM COST STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. [1] STATE END DEC. I [41 STATE END DEC. [7] STATE END DEC. I [10] STATE END DEC. ~ [13] STATE END DEC. :aeeHes, ~esssseem :sstHss*
4 sH,es~eeH,: 7.00 5 6eeeeteesH~( 4.00 6 sesHssssHt: 6.00
7 11.00 7 I.O0 O 11.00 8 :eeeHe 9 12.00 t t * t H : 4.00
10 4,00 10 :~saeee I] Hees~asssse: 3.00 12 O.00 ~tssot~tsse~s
13 HeateeSeH*: 0.00 14 3.00 14 : 15 H s ~ t e H s t H :
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
13 0.00
13
~. . . . . . . . . . . . . . . . . . . . . . . .
COST COST COST COST I COST FRGH COST FROH COST FROM COST FROM COST I FRON COST STATE TO TO OPT, ~STATE TO TO OPT. :STATE TO TO OPT. ~STATE TO TO OPT. ~STATE TO TO OPT. , [2) STATE END DEC. : [5] STATE END DEC. [B] STATE END DEC. : [11] STATE END DEC, I 114] STATE END DEC. 2.00 4,00 3.00
4 13.00 teest~ 3.00 5 11.00 5 2.00 6 11.00 6 4.00
7 7.00 7 6.00 8 9.00 Dttt**:t~s~ee 9 10.00 ***e**: 3,00
10 9,00 t H H i : t e s t e t 11 * t e t * H t ~ s , s : t t t s e ~ 12 7.00 12 :****e*
13 Hssssetlesel 0.00 14 *~esse~ees: 15 ****eses****= =
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
]4
0.00
14
I . . . . . . . . . . . . . . . . . . . . . . . .
: COST COST COST COST : COST FROH COST FROH COST FRON COST FROH COST I FROH COST GTME TO TO OPT. :STATE TO TO OPT. fSTATE TO TO OPT. :STATE TO TO OPT. :STATE TO TO OPT. [3] STATE END DEC. [6] STATE END DEC. [9] STATE END DEC. I 112] STATE END DEC. : 115] STATE END DEC.
:*es***ee :,ee*Hme :~*e*****
4 ,ee**ss~=H* 5 *e*HHe~eH 6 *H*****~*ee:
4.00 1,00 5.00
7 8.00 7 3.00 B 8.00 8 :*HHe 9 11.00 **H**~ 3,00
10 6,00 10 :*sHe* 11 e~meee~Hs~e: 4,00 12 7.00 **H~e:H~e**
13 esH*geeseee: 0.00 14 4,00 14 : 15 H , ~ , ~ . e ~ * * :
================================================================================================================================
NJnieue Cost to the Final Stage is:
II.00
F i g . 6. S t a g e c o a c h p r o b l e m s o l v e d b y m o r e g e n e r a l m o d e l .
15 0.00
15
210
M . PARLAR
This spreadsheet (DP3RSC.WKS) solves a DP problem with 3 rows and 5 columns (4 stages). Reference: Introduction to Operations Research, 3rd Edition, F.S. Hillier & G.J. Lieberman, Chapter 7. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
:to[41 [1] . . . . . . . . . . . . ****** :to[5] . . . . . . ****** :toE6] . . . . . .
:to[71 [4] . . . . . . . . . . . . 7,00 Ito[8] . . . . . . 4,00 :toE9]
eee***
. . . . . .
Ito[7l
~to[4] . . . . . . 2,00 Ito[5] [2] . . . . . . . . . . . . 4,00 :tot6] ......
6.00
:foliO] [7] . . . . . . . . . . . . 1.00 Ito[lll . . . . . . 0.50 :to[12] . . . . . . 4.00
......
Ita[iO] 3.00
......
Ito[8] [5] . . . . . . . . . . . .
2.00
[9] . . . . . . . . . . . .
......
9.00
[11] . . . . . . . . . . . .
:ENO 6.00
4.00
:toll5] ...... 8.00
:to]7] . . . . . . 4.00 Ito[8] . . . . . . 1.00 Ito[9] [6] . . . . . . . . . . . . 5.00
Ito[IO] . . . . . . 3.00 [to[Ill . . . . . . l.O0 Ito[12] [9] . . . . . . . . . . . . 3.00
:to[13] . . . . . . 4.00 [toll4] ...... 2.00 :toll5] [12] . . . . . . . . . . . . 4.00
......
(STAGE l)
(STAGE 2)
0.00
1.00
:tot14]
¢to[12] ...... 3.00
3'00
~END [13] . . . . . . . . . . . .
:to[13] 6.00
fro[Ill
Ito[9]
Ito[4] . . . . . . *e~me* Ito[5] . . . . . . t,e*** ~to[6] [3] . . . . . . . . . . . . e*****
:toll3] [10] . . . . . . . . . . . . 2,00 :to[14] . . . . . . 1.00 ~to[15] . . . . . . 3.00
(STAGE 3}
[14] . . . . . . . . . . . .
0.00
~END [15] . . . . . . . . . . . .
0.00
(STAGE 4)
=••••••=••=••=•===•••••••=•••••••=••==••••==•••••===•===•••=•••••••••=•••=••••••••••=•••=•••=••=•=••••••••••=••••••••••••••••••
COST ~ COST COST COST COST FROM COST : FROM COST FROM COST FROM COST FROM COST STATE TO TO 0PT. :STATE TO TO OPT. (STATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. [ l l STATE END DEC. [4] STATE EM0 DEC. ~ [7] STATE END DEC. ~ [10] STATE END 0EC. ~ [1~] STATE END 0EC. **eeeee* I******** :*eeeee,,
4 ********eee* 5 eeeeeHeee** 6 ,,*eeeHe,,*
..........................
7.00 4.00 6.00
7 8.50 * H H * [ 1.00 8 9.00 **eee*l 0.50 g 8.00 9 4.00
I0 2.00 e*ee*el 2.00 It 1.50 11 ~ 1.00 12 6.00 ***eee~ 3.00
, ................................................
13 2.00 *tteee: 0.00 14 1.00 14 1 15 3.00 e*****i
, ........................
13 0.00
13
J........................
COST COST COST COST : COST FROM COST FROM COST FROM COST FROM COST : FROM COST STATE TO TO OPT. :STATE TO TO OPT. :STATE TO TO OPT. ~STATE TO TO OPT. ISTATE TO TO OPT. [2] STATE END DEC, : [5] STATE END DEC. [B] STATE END DEC. I 111] STATE END DEC, ] [14] STATE END DEC, 2,00 4.00 3.00
4 10.00 ******I 3,00 5 8.50 5 2.00 b 8.50 6 4.00
7 4.50 7 b.O0 8 7.00 ***eH~ %00 q 6.00 eeeeeel 3.00
10 l.O0 eeeee*: 1,00 It 10.00 eeeee*l 6.00 12 5.00 12 8.00
13 I.O0 13 I 0.00 14 6.00 ***eeel 15 8.00 *e**ee=
14 0,00
14
i ............................................................................................................................
COST FROM STATE TO [3] STATE Ieee*eeH leeeeeH* Ileee*H*
COST COST FROM TO OPT, ISTATE TO END DEC. I [6] STATE I 4 H*H~ee*eeel 4.00 7 5 e H * * H t e H e ~ 1.00 8 6 *Heeeeeee*el 5.00 9
COST COST C05T COST FROM COST FROM COST FROM COST TO OPT. T0 OPT. ]STATE TO TO OPT. :STATE TO TO OPT. :STATE TO END DEC. [91 STATE END DEC. : [12] STATE END DEC. : [15] STATE END DEC,
5.50
7 3,00 6.00 He*HI 1.00 7.00 H*eHI 3.00
I0 4,00 *t*e,,~ 4.00 II 2.00 11 t 2.00 12 5.00 * * * H e l 4.00
13 4,00 e*ee*#l 0.00 14 2.00 14 ] 15 4.00 eHeee(
•===•••••=••••••••==••=•••==•••••••====••=••=••••=•••••==•••=••=•••••••=••••••••••••••==••=••••••••••••••••=•••••••••••••••••=•
Rinilue Cost to the Final Stage is:
8.50 F i g . 7. A n o t h e r
stagecoach problem with multiple final states.
)7
3
6
Oil storage
J<
sut~,,,,on,
>J Final
F i g . 8. O i l t r a n s p o r t n e t w o r k .
destinations
15 0.00
15
Dynamic programmingon an electronic spreadsheet
211
~===========~========~==========~=~====~==~=========~=~=========~=~=====~===~=~=~====~=========~====~=~=~=======~ :: COST I COST I COST : COST : COST :1 FROM COST I FROM COST : FROH COST : FROM COST : FROH COST ::STATE TO TO OPT. ISTATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT H ] STATE END DEC. I [7] STATE EHD DEC. I [13] STATE END DEC. [19] STATE END DEC. I [25] STATE END OEC. IT : I I II***s** 7 ****HH~te*Ie.**S 13 HH*H*S*StlS*HH 17 **e*eeeeeee* 5.00 25 24.00 25 Is*teH 31 ~***e**e**s~ IIe**t** 9 *t*t*t*e.*eIeet}. 14 ***~t****t*tI***H* 20 **teee****HI 7.00 26 25.00 HeteeIH*tH 32 a H t H * t e * * t ]I**H** 9 ************I,**Ht 15 ************:****** 21 ******#*****:****** 27 *e*****#****: 2.00 33 19.00 33 ::--,-[0 * . . . * . . : . a . * 16 t l l i i l i i t * l * : * t i i i t 22 * * , t * t l i l l e t : * t i i a * 28 t i t * * * t i l i t * i * t , t t 34 * t i i * * * t * * * * fleeces* 11HHeeeleeeeleeeH* 17 **~*~t***~**IHee*~ 23 He~Hee~ee:eHe~e 29 *HH*eetHeIeee*~* 35 **~********* ]:el}lit [2 t l l l t t t t t t t * l t t t * * t 18 t * t t t t t * t t t t I l l t t i t 24 ******#*****I****** 30 ************Iee**** 36 ************ II . . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . I: C0ST I C0ST C0ST I COST COST l: FROH COST I FR0fl COST FROM COST I FROM COST FROH COST ::STATE TO TO OPT. :STATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [2] STATE ENO DEC. I [8] STATE END DEC. I [14] STATE END DEC. I [203 STATE END DEC. I [26] STATE END DEC. IT i I I I Illai*i* 7 *ttt**H~*etl~Ht~a 13 t t * ~ * * * t l 8.00 19 32.00 *~tet*l 6.00 25 25.00 ***e~*IHHH 31HH.~H** ale*t*** 0 H*a**e**i*e:*Heat 14 * t t * * * * , * * t l 6.00 20 27.00 20 I 6.00 26 24.00 **t***l****** 32 *t********** :lillet* q *********tiele***** 15 * * * t i i t i i i i i I H i l i * 21 tHieeiteieel 4.00 27 21.00 27 I 1.00 33 18.00 33 :I#***** 10 t*****#*****I****** 16 l**t********l#*#*** 22 **HH******:t***** 28 *********HeI 3.00 34 18.00 34 IIHIeH II H H i * H e * H I e H H * 17 *eH*ieHe**IHHie 23 *HeHeeeeI*IeHe*e 29 *HHHH*eeleHeee 35 **HHeeeH*I Is,e,e* 12 IIIIIIeeIleIlleIIel IR lllllIlllIII]IIIlli 24 IlllIll|llIIllIllil 30 IIllllllllll~llllll 36 llilIllllill~ ........................................................................................................................ I COST C0ST C0ST C08T COST I FROH COST FROM C0$T FROM COST FROM COST FROH COST ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TQ TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. I [3] STATE END DEC. I [9] STATE END DEC. I [15] STATE END 0EC. I [21] STATE EHD DEC. I [27] STATE END DEC. I I i I I IIIIIII 7 IIIIIIIIIIII~IIIIII 13 IllllIIIIIII~ 9,00 19 33.00 IIIIIIIIIIII# 25 IIIIIIIIIIII~IIiIIl 31111111#IIIIIl I**e*. 0 ************I****** 14 * * * * , * * * * * * I 5.00 20 26.00 20 3.00 26 21.00 26 I--**-32 *******~****I I****** 9 ************I,****t 15 ************I 5.00 21 26.00 21 6.00 27 23.00 ******: 6.00 33 23.00 ******: I****** 10 ************:****** 16 ************I****** 22 *******--*** %00 28 28.00 ****--I 2.00 34 17.00 34 I IeHe** 11 ************:****** 17 **********t*leeeee* 23 ,,***.******:*****e 29 .eeee*******I 8.00 35 24.00 ******: :filial 12 llllllllllllJlllil] 18 llIllllIIIll~IlllIl 24 IlIIIIIllIll~IIlllI 30 IIlllIillIIiIllllIl 36 IIIIIllIllIi] I ........................ I................................................ I................................................ IT C0ST I C0ST COST I C0ST COST II FROH COST I FROM COST FROH COST I FROM COST FROM COST IISTATE TO TO OPT. ISTATE TO TO OPT. ISTRTE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [4] STATE ENO DEC. : [lO) STATE END DEC. I [16] STATE END DEC. I [22] STATE END DEC. I [28] STATE END DEC. IT I I I I :~IIIill 7 IIIIIiIIIIIl:IIIIII [3 IIIIIIIIIIII:IIIIiI l~ IIIIIIIIIIIIIIIIIII 25 IIIIIIIIIIIIIIIIIII 311111111111111 ]IIIllII 0 IIIIIIIIIIIIIIIIIII 14 IIiIIiIlllIII 4.00 20 25.00 20 IIIIIII 26 IIIIIIIIIIIIIIIIIII 32 IIIIIIIIIIIII II****** ? *,*H,t*t*Ieee*, 15 *******--*~*I 6.00 21 27.00 *see*el 9.00 27 26.00 27 I****** 33 *ease*e.H,el IIH**** I0 ******--****I*--*** 16 ************ 8.00 22 34.00 **H**II0.00 28 2%00 ******I 4.00 34 1%00 34 I IIHeH* 11 t t t t i t t t t t H I t i i t t t 17 * H I e H H H e I H i i H 23 t t * * H I * * . * I I 0 . 0 0 29 27.00 tHeeil 9.00 35 2S.00 teHetI :~Ittttt 12 t t t t t t t t t i i I l t } t I t t IS t t t t I t i t t t t t { I t t t t t 24 t t i t t t I t I t t t : t # t t I t 30 t t t t t t t I t t t t 4,00 36 20.00 t l t t t t : :I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IT COST COST COST COST COST IT FROH COST FROM COST FROH COST FROM COST FROH COST IISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. IT [5] STATE END DEC. I [ l l ] STATE END DEC. I [17] STATE END DEC. I [23] STATE END DEC. I [29] STATE END DEC. {:tttttI 7 ******JHIeilee*H* 13 *****teeie**l****** 19 ****ee**eee*leeeee* 25 *eeeeeee*eeeIeeeeH 31 ********H**I {{tttlIt 0 ttttttttiItt{tIttiI 14 t i I t t t t t t i I t l t t t t t t 20 t t I t t I t t t t I I { t t t I t t 26 t t t t t t t t t I t t l t t t t t t 32 t t I t t t t l I t I t l l{tttttt 9 tttttlittttt:tttttt 15 t t t t t t I t I l t t 6,00 21 27.00 t t t t l I = t t I t t t 27 t I t t I t t i i i t t : t I t t t t 33 t t t t l t t t t t t t : ::tttttt [0 t t t t t t t i t t t t = t t i t t t 16 t t t t t t t t t t t t 7,00 22 33.00 * . . H 4.00 28 23.00 t t i t i t } t t i t t t 34 t t t t t i t t t i i t l ::tttttt II t t t t t t t t t t t t { t t t t t t 17 t t t t t t t t t t t I 3.00 23 25.00 23 5.00 29 22,00 29 I 5.00 35 21.00 . t t * t l =:-----12 t t t t t t t t t t t t { t t t t t t 18 t t t t t t t t t t t t : t t t t t t 24 t t t t I t * t t t t t 6.00 30 26,00 ******: 1,00 36 17.00 36 {: . . . . . . . . . . . . . . . . . . . . . . . . :. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . IT COST I COST COST COST COST :I FROH COST I FROH COST FROM COST FROH COST FROH COST IISTATE TO TO 0PT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. :I [61 STATE END 0EC. I [12] STATE EN0 0EC. : [18] STATE END DEC. I [24) STATE EN0 DEC. I [30] STATE EN0 0EC. IS I I I I ::**tee, 7 ttttttttttti:titttt 13 t t t t t i t t t t i i l t t t t t i 19 t t t t t t i t t t t t { t t t t t t 25 i t t t i t t t i t t t : t t i t t t 31 i t t t t t t t t t i t l :=tttttt 0 tttttttittti{ 6,00 14 33.00 t t i t t t ~ i t t t t t 20 t i i t t t t t t t t t ~ t t t t t t 26 l t t t t t i t i t t i ~ t t i t t t 32 t t t t t t i t t t t t {:tttttt 9 ttttttttttttl 4,00 15 30,00 15 ] t t t t t i 21 t t t t I t t t t t t t ~ t t t t t t 27 t t t t t t t t t t t t ~ t t t t t t 33 t t t t t t t t t t t t ::tttttt |0 t t t t t t t t t t t t l 7,00 [6 32.00 ------= 9,00 22 35.00 t t t t t t { t t t t t t 20 t t t t t t t t t t t t { t t t t t t 34 t t t t t t t t t t t t ={ittttt l l t t t t i t t t t t t t ~ 0,00 17 33.00 t t t t t t = R,00 23 30.00 t t t t t t = [.00 2q [0.00 27 { t t t t t t 35 t t t t t t t t t t t t ]lit}tit 12 t t t t t t t t t t t t = 3.00 18 30.00 18 I 9.00 24 27.00 24 I 3.00 30 23.00 **--**: 4.00 36 20.00 36 ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: IIHinimum Cost IMinimum Cost IHinisum Cost IMinimum Cost IHinisum Cost alto the Final Ito the Final Ito the Final Ito the Final Ito the Final tIStage is: H*tH IStaoe is: 30.00 :Stags is: 25.00 IStage is: 18.00 IStage is: 17.00
Fig. 9. Solution of oil transport problem (part I).
III. A N OIL T R A N S P O R T T E C H N O L O G Y P R O B L E M
This problem is discussed in (Phillips et al. [4]) and its network representation is given in Fig. 8. The spreadsheet we have prepared (DP6RIOC.WKS) can solve a problem with a maximum of nine stages and a maximum of six states in each stage. Numbering the states in the oil transportation problem according to the spreadsheet table convention and inputting the costs in the spreadsheet network (not shown), the optimal routes are found by consulting Figs. 9 and 10 as follows:
CAIE 10:3-E
(i) [12] ~
[15] ~
[20] ~
[27] ---, [34] ---* [41] ~
[47] ---, [52] --, [57],
(iS) [12] ~
[18] ~
[24] ~
[29] ---, [36] ---* [41] ~
[47] ---, [52] ~
[57],
212
M. PARLAR
(iii) [12] --> [15]
[33]
[40] --> [47] --> [52] --> [57],
(iv) [121 --> [151 --~ [20] --* [27] --, [34]
[41] --> [48] --> [52] ---> [57],
(v) [12]--> [18] --~ [24] --* [29] ---, [36]
[411 --> [48] ---> [52] ~
(vi) [12]--> [15]
[21] --* [26] ~
[21] --* [26] ~
[571,
[34] --~ [41] ----> [481 --> [52] ---> [57],
(vii) [12] --> [18] ---> [241--> [29]---> [36]--> [41]--> [47]--> [521--> [571. T h e m i n i m u m total cost is 30.00 as the b o t t o m part of Fig. 9 indicates.
••=•=•=•=•=•===•=••••====••=•=•=•====•••=•=•=•=•••====•••••••===••====••••==•=••==•====••••=• I COST I FROM
ISTATE TO I [31] STATE
COST TO END
: COST I FROM OPT. ISTATE T0 DEC. I [37] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC. I [43] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC, I [49] STATE
I l,m****
I 37 ****i*******l******
I 43 ***6********Is*****
I 49 ********s***l 7.00
I~HH~
38 } H * ~ } * H H } I H } O H
44 * * H } H t H H I ~ H H * 45 IIIIiIIIiIIIIIIIill
50 } H H H * I * I t l H I * * *
COST TO END
I COST : FROM OPT. ISTATE TO DEC. [55] STATE
55 7.00 55 58 H } t * * H * H t I
0.00
55
COST TO OPT. END DEC. 0.00
55
lliIIII 3~ IIIIIIIi]III[liIIiI 5[ IiiIiII}IiIIIIIiIII 57 III}lIIIiIIIl IIIIIII 40 II*IIIIIIIIIIIIIIII 46 IIII*IIIIIIIIIIIIII 52 IIII*II*IIIIIIIIIII 5R IIIIIIIIIII*I ~IIIIII 4l IIIIIIIIIIIIIIIIII* 47 IIIIIIIIIIIIIIIIIII 5~ IIIIIIIIIIII~IIIIII 5~ ;I~I*~*HH;I IIIIIII 42 IIIIIIIIIIIIIIIIIII 4S IIIIIIIIIIIIIIIIIII 54 IIIIIIIIIIII~IIIIII 60 IIIIIIIIIIIII I................................................ I........................ I........................ I........................
I COST I FROM ISTATE TO I [32l STATE I
COST COST FROM TO OPT. ISTATE TO EHO OEC. I [38] STATE
I COST COST I FROM TO OPT. ISTATE TO END 8EC. I [44] STATE
I
IIHH~ lJliIH I*HHI
37 *H~*HHHiIH**H 38 IIIHI~HH*I 6.00 39 *~HiiHHHI 7.00
IiIIIII IIIIIII Iiimiii
40 IIIIIIIIIIII[IIIIII 4[ IIIIIIIIIIIIIIIIIII 42 llI*llllllllllIIiI*
I
43 44 45 46
IH~*IHHI*I 5.00 16.00 HHtll 7.00 14.00 45 I~***H
IIIIIIIIIIIIIIIIIII 47 IIIIIIIIIIIIIIIIIII 48 IllIllllll*IIIllllI
I................................................ I COST COST I FROM COST FROM COST ISTATE TO TO OPT. ISTATE TO TO OPT. I [33] STATE END DEC. I [39] STATE ENO DEC. IiH*H 4.00 9.00 7.00
37 38 39 40
*HI*IHH**IH*;H 18.00 ****;*i 7.00 23.00 ******i 8.00 17.00 40 1%00
I COST COST I FROM TO OPT. ISTATE TO END DEC. I [50] STATE
43 44 45 46
I COST COST I FROM TO OPT. ISTATE TO END OEC. [56] STATE
COST TO OPT. END DEC.
I
49 50 51 52
12.00 **H**I 3.00 10.00 50 I 6.00 *HHIi~I*~*IH*~II
55 3.00 55 56 6.00 **HHI 57 Hii****i*H
IIIIIIIIIIII~IIIIII 53 IIIIIIIIIIII~IIIIII 54 IIllllllllIIIlllll*
50 IIIIIIIIIilI 5~ IIIIIIIIIIII 60 llllllllllIl
0.00
56
0.00
56
I........................................................................ i COST COST COST I FROM COST FROM COST FROM COST ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. [45] STATE ENO DEC. I [51] STATE ENO OEC. [57] STATE ENO OEC.
***;**m****~ 5.00 17.00 ******I 4.00 15.00 ******I 4.00 14.00 46 I,HI**
49 50 51 52 53
12.00 ;*i***I 5.00 7.00 50 I 6.00 9.00 ******I 7.00 H**iHH***I*H***
55 56 57 58 59
5.00 55 0.00 6.00 ******I 7.00 ***;**I H*H*HH**
57 0.00
57
II*IIII 411111111111*IllIiIII 47 IIIIIIIIIII*IIIIIII IIIIIIIIIIiI~IIIilI IIIIIIIIIIII II*IIIl 42 IIIIIIIIIIII]IIIIII 4R IIIIIIIIIIIIII*IIII 54 llIIIIIIIIIIIlIIIII 60 IIIIIIIIIIII I................................................ I........................................................................
I COST FROM ISTATE TO I [34] STATE I
1~IH** 8.00 i 6.00 t 6,00
COST COST FROM TO OPT. ISTATE TO END DEC. I [40] STATE
I COST COST I FROM TO OPT. ISTATE TO END OEC. I [46] STATE
COST COST FRO~ TO OPT. ISTATE TO EHO DEC. I [52] STATE
COST COOT FROM TO OPT. ISTATE TO END OEC. I [58] STATE
I
I
i
I
38 39 40 41
~H~HI~H*I*;HH 22.00 ******I 8.00 16.00 *HH*I 8.00 15.00 41 I 4.00
44 45 46 47
~HI*HI*H*I 3.00 15.00 ******I 2.00 13.00 H***II 2.00 10.00 47 IHIIII
50 6.00 I~*H~I 5.00 51 7.00 ******I 3.00 52 5.00 52 I 6.00 53 IlIIllI{{I*IIllIlI{
COST TO OPT, EHO DEC.
56 5.00 ~*lIH1 57 3.00 57 1 58 6.00 H*H*I 59 IlIIllmlI*Ill
IIIIIII 42 IIIIIlIIIIII]IIIIII 40 IIIIlIIIIIIIIIIIIII 54 IIIII{*IlIIIIIIIII* 60 IIIIIIIlIIIII I................................................................................................ I........................
I COST I FROM ISTATE TO I [35] STATE I
COST COST FROM TO OPT. ISTATE TO EHO OEC. I [41] STATE I
IHIH~ I*HH* I**HH I 7,00 I 7.00 I 6.00
37 38 39 40 41 42
*~*~*H~H*I~I*÷~ HH*HI*H*IH*H* HH~*H~H&I****I* 17.00 ***H;I 7,00 [&DO 41 I 3.00 18.00 HHHI 3.00
I,H***
38 Hm*~HH***IItH*~
COST TO END
COST FROM OPT. ISTATE TO OEC. I [47] STATE I
43 44 45 46 47 48
~I~HIHH**I*H~** *H***÷HH*IIHH~ *~***Hi**I 2.00 12.00 *i***~I 3.00 9.00 47 I 3.00 9.00 48 II*HH
COST TO EHO
COST FROM OPT. ISTATE TO DEC. I [53] STATE
I COST COST I FROM TO OPT. ISTATE TO END DEC. [59] STATE
COST TO END
OPT, OEC.
I
H**H*~II*~ 0.00 59 0.00 59 *I~HHHH 8.00 *H,HI 5.00 58 I 7.00 *IHnl H&**&~***HI I........................ ;........................ ;........................ '........................ I........................ I COST I COST I COST COST I COST I FROM COST I FROM COST I FROM COST FROH COST I FROM COST ISTATE TO TO OPT. IOTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. ISTATE TO TO OPT. I [36] STATE END OEC. I [42] STATE END DEC. I [48] STATE EHO DEC. I [54] STATE ENO DEC. [60] STATE END OEC. 44 tt***~*~H**I*H***
49 50 51 52 53 54
~*~HHH~I~IH**~ *HH**HH*I*~H~* 7.00 ****HI 8.00 6.00 52 I 5.00 8.00 ~Hl;il 7.00 ~HHH*****I~HI**
50 **~*~t*t****I*HmH
55 56 57 56 59 h0
56 Ht**t****~*
I****H 40 * * * * * * H * H * I I * * * H 46 H t * * * * t * * * * I 3,00 52 6.00 52 I 2.00 58 2.00 58 I 7.00 41 16.00 41 I 6.00 47 12.00 47 I 6,00 53 II.00 t * * * * t l 6.00 59 6.00 **t***I I 9.00 42 21.00 t * H t * I 9.00 48 I5.00 *t****I 5.00 54 7.00 ******I 4.00 60 4.00 ****t*I ============================================================================================================================= IHinimuI Cost IMlnimum Cost IMznimum Cost IHinimui Cost IMiniium Cost Its the Final Its the Final Its the Final Its the Final Its the Final IStage is: 15.00 IStage is: %00 IStage is: 5.00 IStage is: 2,00 IStage is: 0.00
Fig. 10. Solution of oil transport problem (part I1).
Dynamic programming on an electronic spreadsheet
213
IV. SUMMARY AND CONCLUSIONS
We have presented a method whereby an electronic spreadsheet (Lotus 1-2-3) can be used to solve discrete stage-discrete state dynamic programs. Once the spreadsheet is prepared, the user only has to enter the data in the branches of a network representing the problem. The spreadsheet model automatically calculates the minimum cost and the optimal route(s). It must be re-emphasized that this method may be useful in teaching the elementary concepts of dynamic programming in introductory management science/operations research courses. No programming knowledge is required to set up the spreadsheet and this may simplify the preparation of the model. The size of the problem one can solve is limited by the disk space available to the user. The oil transport problem with six rows and ten columns required about 80K of disk space. Given the current storage capacities of hard disks one could probably solve a problem with 25 rows and 25 columns provided that the computer itself has sufficient random access memory (RAM). The versatility of electronic spreadsheets could also be advantageously exploited to solve discrete-time linear-quadratic optimal control problems. Since Zhis class of control problems has a closed-form solution, Lotus 1-2-3 with its graphics capability can be used to find the optimal state and control trajectories and immediately graph them. Acknowledgement--This research was supported by the Natural Sciences and Engineering Research Council of Canada under Grant No. A4608. REFERENCES 1. H. D. Toong & A. Gupta, Personal Computers. Scientific American 247, 86-107 (1982). 2. J. M. Jones, Decision Analysis with Personal Computers and Spreadsheet Software, paper presented at the 26th International TIMS Meeting, Copenhagen, June 1984. 3. F. S. Hillier & G. J. Lieberman, Introduction to Operations Research, 3rd Edn. Holden-Day, San Francisco (1980). 4. D. T. Phillips, A. Ravindran & J. J. Solberg, Operations Research. Wiley, New York (1976). 5. H. Wagner, Principles of Operations Research. Prentice-Hall, Englewood Cliffs, NJ (1969). 6. S. E. Dreyfus & A. M. Law, The Art and Theory of Dynamic Programming. Academic, New York (1977).