Question

Lensmakers is an international company that produces eyeglasses. They make most of the prescription eyeglasses that they sell in their stores. They recently purchased new equipment that makes the production of their glasses much faster and are considering a new marketing campaign that promotes that they can make your glasses in 30 minutes or less. The process to produce eyeglasses requires 3 steps: Grinding, Edging/Tinting, and Framing. The distribution of time, in minutes, for each step is shown in the table below. Use this data to create a simulation model with 1,000 trials to answer the following questions.

PLEASE SHOW YOUR EXCEL 

Step	Minutes	Probability
Grinding	9	0.02
	10	0.03
	11	0.06
	12	0.12
	13	0.18
	14	0.35
	15	0.15
	16	0.07
	17	0.02
Edging/Tinting	7	0.04
	8	0.10
	9	0.25
	10	0.37
	11	0.12
	12	0.09
	13	0.03
Framing	4	0.01
	5	0.03
	6	0.32
	7	0.40
	8	0.20
	9	0.03
	10	0.01

a. From your simulation, what is the average total time to produce a pair of glasses?  

b. From your simulation, what is the probability that it will take more than 30 minutes to produce a pair of glasses?  

c. From your simulation, what is the average time that it takes for the Grinding step in the process?  

d. From your simulation, what is the probability that it will take more than 35 minutes to produce a pair of glasses?

Answer 1

Set up the excel as follows:

AutoSave Off Book1 - Excel O Search Sign in File Home Insert Page Layout Formulas Data Review View Help JMP Share Comments AutoSum DIT CT Calibri A 11 ab Wrap Text General TTY AP 5 X cut La Copy Format Painter Fill Paste BIU Insert Delete format Ideas H Αν EESE Merge & Center $ %, too Conditional format as Cell Formatting Table Styles Clear Sort & find & Filter Select Clipboard Font Alignment Number Styles Cells Editing Ideas A21 X fi A B с D E F G H J K L N M - O Р Q R S T U w 1 2 3 4 00 5 6 7 8 Step Grinding Edging/Tinting Framing Minutes Probability Minutes Probability Minutes Probability 9 0.02 7 0.04 4 0.01 10 0.03 0.1 5 0.03 11 0.06 9 0.25 6 0.32 12 0.12 10 0.37 7 13 0.18 11 0.12 8 0.2 11 0.35 12 0.09 9 0.03 15 0.15 13 0.03 10 0.01 16 1/ 0.02 Trials Step Grinding Edging/Tinting Framing 1 13 10 / 2 14 8 6 3 12 10 6 4 13 10 6 5 13 9 7 6 14 9 6 0.4 7 15 11 6 8 16 6 9 10 11 12 0.07 9 13 7 10 10 10 10 10 14 1 11 14 6 13 14 10 10 6 12 13 12 6 15 16 17 14 15 13 15 11 11 13 10 9 6 18 9 16 17 7 7 15 9 18 17 10 9 9 10 / 19 20 9 7 14 15 8 19 20 2-1 22 23 24 25 26 27 28 29 10 9 8 4 6 21 22 23 24 25 26 9 14 13 11 8 9 8 7 8 6 14 13 27 12 8 Sheel2 Sheet1 Đ Ready 1 + 100%

For generating the 1000 trials, go to

Data -> Data Analysis -> Random Number Generation and set up as follows:

Random Number Generation ? X Number of Variables: 1 OK Number of Random Numbers: 1000 Cancel Distribution: Discrete Help Parameters Value and Probability Input Range: $B$4:$C$121 1 Random Seed: 1 Output options O Qutput Range: New Worksheet Ply: O New Workbook

This will generate 1000 random trials for the Step grinding. Repeat the similar process for Edging and Framing selecting the appropriate values of probability.

Do the following calculations:

The formulas used are as follows:

AutoSave Off Book1 - Excel O Search Sign in File Home Insert Page Layout Formulas Data Review View Help JMP Share Comments Σ AutoSum Calibri v 11 ν Α' Α' ab Wrap Text General AT TTY DO 47 X Cut La Copy Format Painter Fill Paste BI U Ideas a. Av Insert Delete Format hili FB Merge & Center $ %, Conditional Format as Cell Formatting Table Styles Clear Sort & Find & Filter Select Clipboard Font Alignment Number Styles Cells Editing Ideas A991 fi A B с D E F G H K L M o Р Q R S T U 10 6 12 15 11 N 28 31 9 7 10 6 27 14 9 7 30 10 6 15 14 10 9 9 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 9 8 6 8 12 7 13 15 15 10 9 988 989 990 991 992 993 994 995 996 997 998 999 1000 Average Time Number of trails with total time more than 30 mins Number of trails with total time more than 35 mins Total Trials 31 32 25 28 30 31 31 31 30 30.2 7 9 10 14 14 13.5 9 7 7 6.9 9.9 461 1004 11 1000 1005 1006 1007 1008 1009 1010 1011 101 1013 1014 Sheet1 Đ Ready 0 5 1 + 100%

AutoSave Off A Book1 - Excel O Search Sign in File Home Insert Page Layout Formulas Data Review View Help JMP Share Comments fx Ө Calculate Now Define Name Use in Formula Name Manager 20 Create from Selection Es Trace Precedents Ve Show Formulas Sa Trace Dependents Error Checking FRemove Arrows Evaluate Formula Insert Function AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Irig Functions Function Library Watch Window Calculation Options Calculate Sheet Defined Names Formula Auditing Calculation F991 fx F G Н. - K L 988 10 6 990 991 992 993 9 12 15 11 989 990 10 991 14 9 994 992 10 15 14 9 10 9 993 994 995 996 M N =SUM(K990:M990) 7 =SUM(K991:M991) 6 =SUM(K992:M992) 7 =SUM(K993:M993) 6 =SUM(K994:M994) 9 =SUM(K995:M995) 6 EESUM(K996:M996) 8 =SUM(K997:M997) 7 =SUM(K998:M998) 7 =SUM(K999:M999) 7 =SUM(K1000:M1000) / =SUM(K1001:M1001) 7 =SUM(K1002:M1002) =AVERAGE(M3:M1002) =AVERAGE(N3:N1002) 8 995 996 997 998 999 1000 1001 1002 1003 12 13 15 10 9 15 9 997 998 999 1000 10 14 14 =AVERAGE(K3:K1002) 9 =AVERAGE(L3:L1002) Average Time Number of trails with total time more than 30 mins 1004 -COUNTIF($N$3:$N$1002,">30") Number of trails with total time more than 35 mins =COUNTIF($N$3:$N$1002,">35") 1000 Total Trials 1005 1006 1007 1008 1009 1010 1011 1012 1013 1011 Sheet1 + Ready I 100%

a) Average total time to produce a pair of glass = 30.2 minutes

b) P(total time is more than 30 mins) = 461/1000 = 0.461

c) Average time for grinding step = 13.5 mins

d) P(total time is more than 35 mins) = 11/1000 = 0.011

Please Note: You might end up with slihtly different values when you do it yourself. This is because we are using a random number genration process. This gives different results everytime someone runs it. But the values would be slightly off.