THE SPECIALIST WITH A UNIVERSAL MINDANDREW VAZSONYI, Feature Editor, McLaren School of Business University of San FranciscoOperations Management, Goldratt and Queuesby Andrew Vazsonyi, McLaren School of Business, University of San Francisco Goldratt, in his book The Goal (over 2 million copies sold) has a fascinating description of how to simulate a production line. I have learned a great deal by studying his match-stick game. Here is the game. An Operations Management Game Five Boy Scouts, Andy, Ben, Chuck, David, and Evan, sit at a long table that has a big pile of match sticks. Al Rogo, the master of ceremonies, rolls a dice. Suppose he rolls 3. Al puts 3 match sticks into Andy's bowl and gives the die to Andy. Andy rolls the die. Suppose he rolls 4. He puts the 3 matches from his in- process inventory into Ben's bowl. Had he rolled 1, he would have put 1 match into Ben's bowl. He gives the die to Ben who rolls it. And so on. At the end, Evan rolls the die, and ships the finished product. They repeat the run 10 times, making up a round of the game. The players are dismayed to find that while they expected to ship 35 match sticks, they shipped only 20. To analyze the problem, I decided to put it on my computer. I do all my work with Excel, but this problem is not really suitable for spreadsheets. In the Stone Age, I used to program in different languages, and QBasic comes with DOS, so I programmed the problem in QBasic. (There is nothing new about the program, so I don't show it.) Using rounds of 20, a sample of 1,000 showed that, on the average, about 70% gets shipped, the rest stays in inventory. I wanted to find out how to remove the bottleneck, and to answer all sorts of WHAT-IF questions, but QBasic is an awkward language in which to do this. So I thought here is my chance to learn, and use Visual Basic, VBA, of Excel. Programming in VBA I copied the QBasic program into the Excel Module, but the program did not run. QBASIC PRINT statements don't work. You must tell VBA where to put the numbers in the worksheet. Suppose you want to put the value of the variable Inventory into cell C5, in the spreadsheet. This is the cell Row 5, Column 3, and so you write: Cells(3, 5) = Inventory. After I changed all the PRINT statements, the program worked and the results appeared in the spreadsheet. (There is nothing new about this, so I don't show it either.) To illustrate some of the results, Exhibit 1 shows the probability distribution of inventory. I used the ChartWizard of Excel. You need a reasonably large sample to get a decent curve. I used a sample of 100,000. Bear in mind that each round involves 120 rolls, and about 200 numbers, so we are talking about 20 million calculations. It took a little over 2 minutes on my Pentium 100. Fortunately, you get reasonable performance estimates (such as mean, variance, percentiles) by using samples of 1,000, each of which takes about 14 seconds. Answers to WHAT-IF Questions I found out many things I did not know before. When I saw the problem first, I said of course, the arrival rate is the same as the service rate, and so the system will blow up. I always thought there is something weird when the arrival rate reaches the service rate. This is nonsense. I changed the die generating inputs to have 4, 5, 6, 7, and 8 "faces" and calculated the in- process inventory. It was pretty much a straight line. The distribution of inventory between the players is something like this:
Andy 11% Andy has the most, Evan the least. If you increase Andy's capacity, the highest inventory shifts to Ben, but shipping will not improve. The bottleneck is Evan; if his capacity is increased, shipping will increase. I answered all sorts of WHAT-IF questions. WHAT-IF in-process inventory is limited? WHAT-IF overtime is introduced? How long will it take to process the inventory? In fact, I had to drastically revise my understanding of queueing theory for finite systems. The Strategy of VBA Programming Actually, this is very close to programming in any language. Iterations are done in the module, and the input/output interface fuses the VBA module with the spreadsheet. I look at the worksheet as a form, something like your 1040 tax form. I color the data to make things easy. Permanent information, such as the name and capacity of the players in the game, are in black. Variable input information is in yellow. VBA returns answers in red. Results of spreadsheet calculations are blue. Conclusions VBA may become the premier tool to build and solve mathematical models. VBA combines the best of two worlds: programming and spreadsheets. The principal problem is, of course, building the model. However, the student must learn the mechanics of the tool. Learning BASIC is 90% of the art, VBA 10%. The student learns model building by doing; I know of no better way to do this than by VBA. Ten years ago we used to teach BASIC to our students, but spreadsheets killed programming. The time has come to teach modeling by VBA. Unfortunately, there is no suitable textbook available. ***************************************************************** For copies of tables or figures mentioned in this article, contact the Managing Editor at hjacobs@gsu.edu. ***************************************************************** ***************************************************************** DR. ANDREW VAZSONYI is an internationally recognized author, researcher and educator. He is the author of over 70 technical articles, and 7 textbooks, in English, German, Spanish, French, Russian, Japanese and Hungarian. Dr. Vazsonyi received a Ph.D. from the University of Budapest. He is currently on the faculty of San Francisco University and has 20 years of teaching experience. Prior to becoming an educator, he served for 25 years in industrial positions. He is currently focusing on books and articles that apply Microsoft Excel and VBA to production and operations management.
Dr. Andrew Vazsonyi |