Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: zz-x2580
Principles of Operations Chain Management
Understanding Bottlenecks
The Theory of Constraints, introduced and popularised by the book The Goal. A Process of Ongoing
Improvement by Eliyahu Goldratt and Jeff Cox, is a body of knowledge that deals with all the obstacles
that limit or constraint the organisation’s ability to achieve its goals.
Computer simulations allow managers to approximate real-world phenomena without going through
the expense of actually setting up and running the system. For this assignment, you are going to use
a MS Excel spreadsheet to simulate the match game described in Chapter 14 of the book The Goal. To
make sure you understand the problem we are simulating, before starting your work please read the
book chapter that has been provided together with this document.
Part A
You will use random numbers (dice rolls) to run the 5-person game described by Goldratt. You are
expected to run 125 replicas of the exercise and to compute and observe the average throughput at
each station (person).
Your spreadsheet should have 15 columns. Label the first one “Round Number”. Label the second
and third “Andy’s Roll” and “Andy’s Thrput”, respectively. Label the next three columns
“Ben’s Bowl”, “Ben’s Roll”, and “Ben’s Thrput”, respectively. Label columns 7-9 like
columns 4-6, with “Ben” replaced by “Chuck”. Label columns 10-12 the same way using “Dave” as
the player’s name; and finally columns 13-15 using “Evan”. Notice that we do not need a bowl column
for Andy, because his bowl is the whole box of matches, which is -to all practical extent- infinitely large.
The second step is to generate 125 rows of simulated numbers, one for each round. Andy’s throughput,
i.e. the amount that he passed on to the next player, will always equal Andy’s Roll because he can
draw from the whole box of matches. The throughput for the other players will equal the minimum
between the number of matches in their bowl in that round and their respective dice rolls. A player’s
“Bowl”, the amount of matches available for round X, will equal that player’s Bowl in round X−1, minus
the throughput that player in round X−1, plus the throughput from the previous player in round X. For
example, if in Round 3 Chuck’s bowl contains 7 matches and he rolls a 4, then Chuck’s Thrput
column for Round 3 will show the value 4 (the minimum between the matches in his bowl and his roll).
Next suppose that Ben’s Thrput in Round 4 is 2. In that case, Chuck’s Bowl for Round 4 will
equal 5 (because he started with 7 in Round 3, then he passed 4 to the next player, and finally he
received 2 more from Ben in Round 4).
Notice that the first row of your simulation will be slightly different. As there is no previous round, the
player’s bowls start empties. To keep things simple and avoid mistakes in the formulas you can simply
start your table with an extra row labelled “Initial Conditions”. All cells in this row should be
empty or have value zero.
You can use the MIN command in Excel to take the minimum of two numbers (specifically, the “bowl”
amount and the “roll” amount). For example, to take the minimum of the numbers in cells D2 and E2,
use the command =MIN(D2, E2).
Diego Ruiz-Hernandez, PhD. Senior Lecturer in Management Science.
To simulate the roll of a six-sided die, use the command =RANDBETWEEN(1,6). This will return a
pseudo-random integer number between 1 and 6. Notice that every time that you change a cell in the
spreadsheet, all random numbers will change. Don’t panic, this is a normal feature of Excel. Once you
have the spreadsheet set up and all the formulas in the row corresponding to Round 1 and Round 2
have been entered, copy the complete row corresponding to Round 2 downwards until you complete
125 cases.
At the bottom of each Thrput column in your spreadsheet, create a new row labelled “Averages”
where you will calculate the average throughput for each boy. You can do this by using the AVERAGE
command in Excel. Run the simulation 15 times using the technique that you learnt during Tutorial 1
of our module, recording the average throughput for each player for all 15 simulations in one table.
By doing this, you will be simulating 9,375 total dice rolls in just a couple of minutes. Compute the
average of each player’s column.
HINT You would have made a mistake in your spreadsheet if it contains negative numbers or if any of
your throughput columns contain a number greater than 6. Notice, however, that many the entries in
your bowl columns will most likely exceed 6.
Deliverables for Part A
1. One sheets in your MS Excel file, containing your 125 simulations together with the summary
table that contains the average throughput of each player for each of the 15 replicas of the experiment.
Please also compute the overall average of the exercise (the average of the column corresponding to
Evan’s average throughput).
2. In your written report, you should include the analyse the average throughput amounts for
each of your 15 simulations for each player, and the grand total of the exercise. Discuss any
conclusions you may derive from your results. Specifically, are the average throughput amounts
different among the five players? Do you observe a pattern? Do those averages change with different
simulation runs? How can you explain what you observe?
Part B
Next, let us play with the rules of the game a bit. In particular, the match game effectively illustrates
the effects of “dependent events” and “statistical fluctuations” together. We now explore what
happens when these effects are diminished.
1. First, let us try to introduce some level of independence among the players. One use of “buffer
inventory stock” is to decouple operations and eliminate dependencies among stations. Make a copy
of the simulation in Part A in your MS Excel file and call it Part B1. Make the following change: add
100 matches in the four cells that have the name “Bowl” for in the row Initial Conditions.
The rest of the game remains the same. Run the simulation 15 times. As before, record in a table the
average throughput amounts for each of your 15 simulations for each player. Compute also the overall
average over the 15 replicas.
2. Let us now try to reduce statistical fluctuations. Make a new copy of the results in part A and
name it Part B2. This time we will reduce the variability of our processes (i.e. the die rolls). To do
so, pretend that instead of rolling a die you are you flipping a coin. Heads means a throughput of 3
and tails means a throughput of 4. Specifically, change all of your roll columns from
Diego Ruiz-Hernandez, PhD. Senior Lecturer in Management Science.
Sheffield University Management School. Conduit Road. S10 1FL. Sheffield, UK
=RANDBETWEEN(1,6) to =RANDBETWEEN(3,4). Notice that the expected value of each roll is
the same (3.5), but the variance has decreased. Run the simulation 15 times and record in a table the
average throughput amounts for each of your 15 simulations for each player. Compute the aggregated
average for each player as before.
Deliverables for Part B
1. Two new sheets in your MS Excel file. One for each case in this part. As before, each sheet will
contain your 125 simulations and the summary table including the average throughput of each player
for each of the 15 replicas of the experiment.
2. In your report you should analyse the average throughput amounts for each of the 15
simulations for each player and for each of the two cases. How do the results in case B1 differ from
your simulations in Part A? What is the obvious disadvantage of implementing this approach? How do
the results in case B2 differ from your simulations in Part A? What can you say about the effect of
statistical fluctuations in a system? Also, write a paragraph describing any other conclusions that you
may derive from your analysis.
Part C
Let us introduce a bottleneck into the system and test Alex’s ideas about how the placement of the
bottleneck affects the total inventory in the system. For each of the scenarios described below, make
a new copy of the spreadsheet corresponding to Part A. To calculate total inventory, we need to add
the matches in all four of the bowls. Use the SUM command in Excel to add up all 125 rows in the four
columns that have “Bowl” in their heading. Then insert a formula at the bottom of your spreadsheet
that adds those four total bowl amounts together. This represents the total inventory in the system
over the course of the game. This replicates the fact that, in real manufacturing plants, companies pay
holding cost for every period -round- that they hold inventory. We are simply assuming that the cost
of a unit of inventory is one monetary unit. We will now introduce a bottleneck by having one player
rolling a 4-sided die instead of a 6-sided one, i.e., changing =RANDBETWEEN(1,6) to
=RANDBETWEEN(1,4) in one column. For each bottleneck placement (scenario) below, run the
simulation 15 times and record the total inventory each time. Then calculate the average inventory
for that scenario (averaged over the 15 simulations) together with the aggregated average of the
throughputs. Include all these amounts in one table.
Scenario 1: Andy is the bottleneck.
Scenario 2: Chuck is the bottleneck.
Scenario 3: Evan is the bottleneck.
Deliverables for Part C
1. Three new sheets in your MS Excel file. One for each scenario described in this part. As before,
the sheet will contain your 125 simulations together with the summary table including the average
throughput of each of the players, and total inventory for each of the 15 replicas of the experiment.
As usual, include also the overall averages of the exercise (throughputs and inventory).
Diego Ruiz-Hernandez, PhD. Senior Lecturer in Management Science.
Sheffield University Management School. Conduit Road. S10 1FL. Sheffield, UK
2. Your report should discuss what happens with the average throughput and inventory amounts
in each scenario. Compare your three scenarios. Where is the bottleneck more damaging? Why? Also,
write a paragraph describing any conclusions that you may derive from your analysis. Write a
reflection on the impact of bottlenecks in a production system.
Part D
In many industrial settings, parallel processing is introduced in order to speed up the process or to
eliminate bottlenecks. As a last exercise, let us explore the effects of introducing excess capacity into
the system. Make a copy of your spreadsheet of Scenario 2 in Part C. Suppose that one of the players
gets assistance from a new colleague, Robyn. In our setting, this is represented by letting the selected
player to roll two dice instead of one (the other four players roll as before).
Scenario 1: Give Andy the extra die (this will be a six-sided die). Run the simulation 15 times and record
the total inventory, as well as each of the player’s throughput. Record this in a table. Please notice
that you must give Andy two dice, a 12 sided dice will not represent parallel processing (could you
explain why?).
Scenario 2: Repeat the experiment giving Chuck an extra die (this will be a four sided die). Run the
simulation 15 times and record the total inventory, as well as each of the player’s throughput. Record
this in a table. Please notice that you must give the player two dice, an 8 sided dice will not represent
parallel processing.
Deliverables for Part D
1. Two new sheets in your MS Excel file. One for each scenario described in this part. As before,
the sheet will contain your 125 simulations and another the summary table including the average
throughput of each of the players, and total inventory for each of the 15 replicas of the experiment.
As usual, include also the overall averages of the exercise (throughputs and inventory).
2. In your written report, analyse the results of each scenario. Compare the results of the two
scenarios and suggest who should be given the extra die. Also, write a paragraph describing any
conclusions that you can derive. Write a reflection on the effect of parallel processing.
