refer the attachments

Homework #5

MEM Program

Operations Excellence

Due: Start of Class 02/17/20

Worth 10 Points

Case Study Problem:

Using Excel, build a queuing model of the Rosemount Vortex Flowmeter plant (like the one we completed in class for Penny Fab Two). Please use the starter spreadsheet. If you need additional fields or want new assumptions, feel free to add them in. The queuing model should include the raw processing time, failures, set-ups, rework, variability, the impact of Magic/Magnetic, and anything else you think is important. You will need to spend some time thinking about how to best model the flow lab. However, the model does not have to include the impact of batch moves or the impact of technicians. The model should include, as output, the average WIP and average CT.

Please post a copy of your final spreadsheet. Please make sure your name (or initials) is in the name of the file and your name is in the spreadsheet.

In addition to the spreadsheet, you should include a brief write-up:

a) an explanation of the model and any assumptions you made

b) the strengths and weaknesses of the model

c) how realistic you think the model is

d) Insights and conclusions from the model as to how cycle time could be reduced. If you discovered something different than after your first reading of the case, it would be interesting to point that out.

Please be sure to check that the model behaves correctly to changes in the input and makes sense intuitively. Hint: Please make sure your calculations do not show negative cycle stock or WIP numbers. If initial calculations show this, make reasonable assumptions to avoid the problem.

2

>Rosemount

ata

reakdowns

achines

R

ff Process Time

V

Eff Var Eff SCV Eff Process time Eff Var Eff SCV Eff Process Time Eff Var Eff SCV Eff Process Time

Machine

.2

2

5

9%

0.0

1

0

0.1

1 1 0.26

0.26

5

0.02 0.28 0.30 0.28

.89

1

0.03 8

.0

0.01 5

0.17 99.6% 1 1 0.02

0.02

0.05 0.00

0.05 1.88

19.59

1

8

.0

0 0 5 40 0.17 0.17 99.6% 1 15 0.03 0.00

0.03

0.03

6

0.25 0.03 0.00 0.29 0.03 0.29

37.93

A 1

0.29 16

1 20

0.5

15 15 0.31

5

73

0.39

0.65 0.39 0.65

2.56

B 1 0.31

16 100% 9,999,999 0.08 0.04 1 20 0.5 0.5 98% 15 15 0.31 0.05

0.32

0.65

0.40 0.07 0.42 0.40 0.42

2.48

C 1

100% 9,999,999 0.06 0.03 1 20 0.5 0.5 98% 15 15 0.24

0.25

42

0.31

0.31

1.58 0.31 1.58

3.19

D 1 0.15 0.31 16 100% 9,999,999 0.05 0.03 1 20 0.5 0.5 98% 15 15 0.15 0.10

0.15

0.20

2.60 0.20 2.60

4.88

E 1

0.17 16 100% 9,999,999 0.07 0.03 1 20 0.5 0.5 98% 15 15 0.18 0.03

0.18

0.25

0.25 0.04 0.62 0.25 0.62

4.06

1 0.03 0.006 8

0 0 1 40 0.17 0.17 99.6% 15 1 0.03 0.00 0.04 0.03 0.0002 0.20 0.03

0.20 0.03 0.00 0.31 0.03 0.31

28.86

1 0.023 0.006 8 99% 100.0 0 0 1 40 0.17 0.17 99.6% 1 1 0.02 0.00 0.07 0.02 0.0002 0.28 0.02

0.28 0.02 0.00 0.29 0.02 0.29

42.87

1

0.006 8 99% 100.0 0 0 1

0.0001 0

1 1 0.02 0.00 0.16 0.02

0.16 0.02

0.16 0.02 0.00 0.17 0.02 0.17

66.01

1 0.015 0.006 8 100%

0 0 1 9999999 0.0001 0 100.0% 1 1 0.02 0.00 0.16 0.02 0.0000 0.16 0.02 0.00004 0.16 0.02 0.00 0.16 0.02 0.16

66.67

ion Data

Product

1 A M

1 1 0.01 48 9

0.8 0.15

1 A V

5 25 0.09 17 4

0.28 0.07

2 A M 6.668 5 25 0.01 12 4

0.20 0.07

3 A M 6.668 5 25 0.01

4

0.07

3 B M 6.668 1 1 0.01

8

4 B M

1 1 0.02

7

4 B M

1 1 0.01 22 7

0.37 0.12

4 B V

7 49 0.10 12 4

0.20 0.07

5 C M 16.67 1 1 0.02 54 6

0.10

5 C M 16.67 1 1 0.02 41 6

0.10

5 C V

7 49 0.15 10 3

0.17 0.05

5 C V

8 64 0.15 10 4

0.17 0.07

6 D M 10.002 1 1 0.01 40 6

0.10

6 D V

8 64 0.16 8 3

0.13 0.05

6 D V

7 49 0.13 7 3 1000.20 0.12 0.05

7 E M 10.002 1 1 0.01 23 6 290.06

0.10

7 E M 16.67 1 1 0.02 22 6

0.37 0.10

7 E V

3 9 0.07 5 3

0.08 0.05

1.00

13769.42

																																									Input				D	Natural	From					B	From Set Ups	From Re-work**	Summary
Operation												M	Raw Processing Time (hrs)	Natural Std Dev (hr)	Hrs/day	Yield	Jobs Between Rework	Set Up Time (hrs)	Set up SD (hrs)	Units b/n Setups	MTTF (hrs)	MTTR (hrs)	MTTR-							V					A	Avail	Batch In	Batch Out							E				Eff Var				Eff S					C		Eff Process time	Eff SCv	Effective Rate of																																																					1	Effective Workstation Rate (jobs/hr)
Welding											5															0								6		0.1											8					9					10																	0.0							3									0.01													4												0.	1									7					99.6%																							0.02	0.33		0.0	23	0.34						0.29	0.023		64						0.28		0.30	3.38					16
Hydro	0.019	96%				25	0.												15	40		0.17												0.00	2.			49	0.0010	2.75									0.05	0.00462	1.92		1.88		19.59
Sensoring	0.025					0.006	95%						20		0.06			0.0002						0.25			0.0001		37.93
									0.31						100%					9,999,999										0.07				0.04										0.5					98%			0.08		0.8		0.32		0.09	0.96			0.39	0.09867				0.65								0.10		2.56
0.23	0.53	0.0668			0.40	0.06850			0.42		2.	48
	0.24			0.37	18	0.14	2.32							0.15	2.47	0.15517			1.58										0.16		3.19
4.	41	0.1084	4.65							0.20	0.10909			2.60	0.11		4.88
		0.18	0.92	0.0365	1.		12	0.03755			0.62		4.06
Config	85%	6.7	0.00019		28.86
Test	0.00015		42.87
Inspection		0.015		9999999		100.0%		0.0000		0.00004		66.01
Packing	1,000,000.0		66.67
Figure 7: The Vortex Flow Lab	Product
Line	Scale	Avg. Weekly Demand (units)	Std Dev of Demand (units)	Var. of Demand (units2)	Long Run Avg. Mix	Process Time (min.)	Setup Time (min.)	Run Time per Week (min.)	Total Time per Scale (min.)	Process Time (hrs)	Set up (hrs)
			6.668	380.08	2070.41
70.014	1470.29
106.69
13	113.36	0.		22
35	286.72	2690.	54	0.58			0.13
			16.67	43	833.50	0.72			0.12
		10.002		290.06
80.016	1280.26
	1000.20	498	1.00	0.90
783.49	0.68
116.69	1516.97
120.024	1680.34
460.09	2817.23	0.67
123.358	1356.94
100.02
1210.24	0.38
466.76
56.678	453.42
Totals	790.16	19.18		13769.42

Functions

Name	Description	Parameters
BreakVar	Formula for breakdown variability	BreakVar(NatVar, A, MTTR, MTTR-VAR,T-O)
NonPreVare	Formula for set-up or recycle	NonPreVar(NatVar, StdDevSU, N, SUTime)