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.
Sheffield University Management School. Conduit Road. S10 1FL. Sheffield, UK
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.

Your submission consists of two files: a MS Excel spreadsheet with the results of your simulation; and a MS Word file with your 1200-1500 words report.
ATTENTION: The excel file’s sheets must be read-only and password protected. This will avoid accidental changes during revision.
Electronic submission only through Blackboard. You are allowed to utilise the Turnitin Check before the assignment deadline which can be accessed via
the information room. This allows you to generate an originality report and use this to improve your referencing and citation skills.
Please note: Turnitin Check is NOT the final submission – you still need to submit your work to the Assignment Link on the module Blackboard
site, otherwise it will be marked as Not Submitted.
You should note that the time of submission is taken from once the document has been successfully uploaded and confirmed – this may take more than five
minutes during busy periods. Late penalties will be applied to any work submitted from 12.01pm on Friday 29th of April onwards. Details of how to
calculate a late penalty can be found in your programme Handbook. It is your responsibility to ensure the correct document/file has uploaded successfully.
When submitting you must:
1. Include a completed cover sheet (available from Blackboard (MOLE))
2. Use ‘StudentNumber-MGTXXX-I’ (e.g. 190011001-MGT253-I) as the document’s file name and also as the Assignment Title in Turnitin.
3. Use ‘StudentNumber-MGTXXX-IS’ (e.g. 190011001-MGT253-IS) as the excel file’s name.
Contribution to Final Mark for Module: 30%
Maximum Word Length: 1500
The word count is for the main body of the text and ignores the reference list and appendices. If you exceed the word length you will be penalised. For
details see the Management School Handbooks.
Please note that SUMS does not have a word count tolerance – it is a stated maximum as outlined above.
Requirements:
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.
In this work you will use a spreadsheet to conduct a simulation that replicates the experiment ran in Chapter 14 of the book by a group of boy scouts.
This work will be explained and partially developed during tutorial sessions 2 and 3 (weeks 27 [5] and 29 [7], and 28 [6] and 30 [8], depending on your
tutorial group) and must be finished and submitted as an individual work by the end of Week 34 (Friday 29
nd of April 2022).
The submission consists of the MS Excel file containing the simulation exercises, and a 1200-1500 words essay answering the questions indicated in the
statement of the problem.
The essay should also include:
A short description of the experiment within the context of the book chapter.
A thorough reflection about the impact of bottlenecks on a company’s processes. You should show that you clearly understand what a bottleneck is
in an industrial or service process, and how can a manager avoid and/or correct them. Your reflection should link what you observed in the
simulation experiment with real life situations in industrial and service settings.
The report must include some graphic support.
Further details of the exercise are provided in the document associated to this specification form.

Goldratt – The Goal Chapter 14 Individual Coursework Bottlenecks UG Coursework Specification Form MGT253

CITATION