*** Excel sheet required only for 3 parts using the following data. Urgently ***

For this assignment, you are going to use a MS Excel spreadsheet to simulate a series of sequential

processes in an industrial setting. The structure is similar to the one developed during our lecture in

bottleneck analysis.

We will work on the case of a company in the miniature manufacturing sector. More specifically, they

produce die-cast scale models of airplanes, ships, armoured cars, and other military artifacts for

collectors.

Die casting is a metal casting process in which molten metal is forced into a steel mould under high

pressure. The steel moulds, known as dies, are fabricated to produce castings with intricate shapes in

a manner that insures both accuracy and repeatability. In the toy industry, the castings are then

painted, welded (or glued), and finally polished to provide an accurate scale representation of the

original object.

The process consists of the following stages: order preparation, where the different materials (led,

zinc alloy, plastic, rubber, etc.) are prepared and sorted for processing. These materials are then taken

to the Casting workshop, where the different parts of the miniature are moulded. The finished pars

are then taken to the Painting station, and from there to the Assembly workshop, where the different

parts and components are put together, welded, and glued to build the scale models. The Finishing

station takes care of the inspection, polishing, deburring, washing, and finally varnishing of the

finished replicas. Finally, all the miniatures are taken to the Sorting and Boxing desk, where orders are

sorted together, and put in boxes ready for distribution.

In one hour, the Setup team can get ready materials for up to 250 units. As this is a very simple

procedure, the standard deviation 50 units per hour. The casting team can only process 100 units per

hour, but due to the nature of the process and the high temperatures required, it has a higher

variability, with a standard deviation of 25 units per hour. The capacity of the painting station is in

average 150 units, with a minimum of 50 (for more detailed miniatures) and a maximum of 250 (for

models demanding less detail). The assembly team also works relatively fast and are capable of

processing up to 200 units per hour, with a standard deviation of 20 units per hour. The finishing

station oversees the quality control of the company and, therefore, tend to be slightly slower than the

preceding units. They can process, in average, 100 units, with a standard deviation of 10. Finally, the

sorting and boxing team can process up to 300 units per hour, also with an almost negligible standard

deviation of 10 miniatures per hour.

We assume that all the indicated capacities, apart from the painting station, follow normal

distributions with the indicated mean and standard deviation. The processing times at the painting

station follows a uniform distribution in the indicated interval.

The factory works two uninterrupted 8-hour shifts per day. Breaks and other needs of the operators

are already considered in the productivity data.

Part A

You will use random numbers to run a simulation of the 6-station manufacturing process described

above, in the same way that you ran the simulation of the game described by Goldratt in chapter 14

of The Goal. You are expected to simulate a working day (16 hours) and to compute and observe the

average throughput at each station.

For each intermediate processing unit, other than Order Setup, you will have the following columns:

WIP Inventory, Productivity, and Throughput (WIP is the acronym for Work in Process). For the

Order Setup process you will only have Throughput. Notice that, when using the metaphor of the

boy scouts, the WIP Inventory column is equivalent to the bowl of the corresponding player in the

classroom case.

To simulate the outcome or Productivity using a normal distribution, use the command

=CEILING(NORM.INV(rand(),Mean,StDev),1). When using the input [rand()], the

command NORM.INV generates a random observation of a normal random variable with the

indicated mean and standard deviation. As we are interested in getting a whole (integer) number

instead of a real number (we are talking about production units), the command CEILING rounds the

number up to the closest integer. A uniform distribution can be simulated using

=RANDBETWEEN(Min,Max).

Your table will have 16 rows. At the bottom, compute the total throughput of each station. Two totals

are of particular importance, the total Throughput of the Setup station, which represents the Total

Use of Materials and the total Throughput of the Sorting unit, which represents the Total Output

of the system. Compute the average inventory for each station.

Repeat the experiment 250 times. Include in your table the following columns: Total Use of Materials,

the Total Throughput of the four intermediate stations, the Total Output, and the Total Waste,

defined as the difference between the Total Use of Materials and the Total Output. Record at the

bottom of the simulation table the average of all the columns. Your analysis will be based on these

results.

QA. Is there a station that accumulates significantly more inventory than the others? Which is the

easiest way to reduce this inventory, without incurring in any investment in equipment or personnel?

Deliverables for Part A

1.

One sheet in your MS Excel file, containing the table of one working day and the 250

simulations.

Part B

Adjust the Throughput of the Setup station, replacing the randomly generated number with a value

that you consider reasonable (within the interval provided). We will refer to this as the target

variable. Build a table for a working day as in Part A. For example, I would suggests using candidate

values of 1 to 3 standard deviations above and below the mean (i.e. 100, 150,,400)

Using the What-If function in Excel, build another table representing a working day (16 hours). Then

build a table for a Two-Way Simulation. Your table must have one column for the simulation number,

and one more column for each of the values you will give to the target variable.

Let us start with Total Production. First, build a Two-Way table for the 250 simulations. You will use

the What-If analysis tool but in this case, you will need to follow the following steps.

1. Use any cell at the top of the worksheet to introduce the value of the target variable. Start

with, for example 100. Lets say this is B1.

2. Instead of the random number generator in the Throughput column of the Setup station,

insert a reference to the cell you used in Step 1 (in my case I would introduce =B1). The same

reference must be used in each of the 16 rows in that column.

3. In the simulation table include a reference to the Total Production value as indicated in orange

in the figure below (the reference may be different in your table).

4. Open the What-If-Analysis dialog window. In the row input cell enter the location of the fixed

value that we defined for the target variable (in my case B1). In the column input choose

any empty cell nearby the table.

5. Once you execute the command, you will have a table of 250 simulations for each possible

value of the "target variable". At the bottom of the table compute all the column averages.

Let us now analyse the waste. All the materials that are prepared at the Setup station that are not

used at the end of the day, go to waste. Build a new table, the best position is to the right of the last

one, leaving a couple of empty columns.

Repeat all the steps above, but now we will analyse the Total Waste. To do so, the formula that

appears in orange in the table above show not indicate the difference between the Total Use of

Materials (sum of Throughput of the Setup station), and the Total Output (sum of Throughput of the

Sorting station).

QB. Which is the best value for the target variable? What happens to the Total production and Total

Waste values as you increase the value of the target variable? Discuss the trade-off between

product and waste. Is it eliminating waste completely a feasible target? Will it be economically viable

for the company? What would you suggest to the manager?

Deliverables for Part B

1.

One sheet in your MS Excel file, containing the table of one working day, and the simulation

tables for the two cases described above.

Part C

Let us go back to our initial setting, the one described in Part A. Rather than reducing the capacity of

one station, it may be worth investing in increasing the production capacity of another, either by

expanding its capabilities, or by introducing some parallel processing.

To do this in a more formal way, we first must identify the systems bottleneck(s). This can be done by

finding the process or processes that cause more accumulation of WIP Inventory. If your resultsin Part

A are correct, these must be the Casting and the Finishing stations. Let us focus on the Casting

workshop first.

After some market research, our Operations team has found two possible improvements:

a) Introduce parallel processing by buying a new casting machine of the same characteristics of

the first one.

b) Increase the capacity of the current machine, with the disadvantage that this increases the

process variability. The new capacity would be and average of 250 units per hour, with a

standard deviation of 80 units.

QC1. Test these two alternatives independently based on the initial configuration. Compute Total

Output and Total Waste using the What-If tool as we did in Part A. You will build one set of tables for

each of the cases (a) and (b). Is there an alternative that clearly outperforms the other? Can you

identify a new bottleneck?

Let us now focus on the Finishing station. At the moment, the station has 10 operators, who can

produce in average 10 units per hour each, with a standard deviation of 3.16 per worked. The company

has the following possibilities:

a) Hire 6 more operators with similar characteristics to the current ones. This will give an average

production of 160 units, with standard deviation 12.7.

b) To semi-automatise some of the processes in the station, increasing the productivity of each

worker to 18 units per hour each with a standard deviation of 5 per worker. This will give a

normal distribution with mean 180 and standard deviation 15.8.

QC2. Test these two alternatives independently based on the initial configuration. Compute Total

Output and Total Waste using the What-If tool as we did in Part A. Is there an alternative that clearly

outperforms the other? Can you identify a new bottleneck?

Your results in the previous two questions must have revealed the Painting station as a potential

bottleneck. If we analyse the station, we will notice that its output shows large variations, indeed, the

standard deviation is 250 650 = 33.33. Therefore, we would like to find an alternative system that

reduces the variability in the stations output. Our operations team has found that using air-brushes

instead of the traditional painting techniques used in the factory, will change the distribution of the

painting process to a normal variable with mean 230 and variance 25.

QC3. Test this alternative based on the initial configuration. Compute Total Output and Total Waste

using the What-If tool as we did in Part A. What could be the effect of changing the painting technique?

Is it worth investing?

Once you have arrived at this point, you may have noticed that, irrespective of the other bottlenecks,

the slow casting process generates large amounts of waste and, therefore, it must be addressed. But

also, Painting and Sorting require some work. It does not seem efficient to address these bottlenecks

independently, they must be tackled together.

QC4. Analyse different combinations of the alternative processes for Casting, Painting and Finishing

that we have analysed in this section and identify the best alternative for the company. Would you

suggest also reducing the input from the Setup team? By how much? Is a fixed amount the best

alternative? Would you be able to figure out a better decision rule for the amount of materials that is

processed by the setup station every hour?

QC5. What is the overall effect of the changes introduced? What was the impact in productivity? What

in waste? Has a new bottleneck arisen? What can you conclude in general about bottlenecks and

process re-engineering?

Deliverables for Part C

1.

One sheet in your MS Excel file (containing the table for one working day, and the 250

replications simulation, for each of the five cases described in QC1 to QC3.

2.

As many sheets in your MS Excel file for the different alternatives that you tested in order to

find the best set combination of processes for Casting, Finishing, and Painting altogether.