Exercise on Normal Distribution



73-305 Statistical Quality Control

Computer Lab 2

Economic Design of [pic]Control Chart using Excel Solver

Summer 2002

(A) Exercise on Probability Distribution

1. Binomial: find probability of c successes from n trials if P(success) = p

• P(c successes) = BINOMDIST(c,n,p,FALSE)

2. Binomial: find probability of c or fewer successes from n trials if P(success) = p

• P(c or fewer successes) = BINOMDIST(c,n,p,TRUE)

3. Poisson: find probability of c arrivals if mean number of arrivals = (

• P(c arrivals) = POISSON(c,(,FALSE)

4. Poisson: find probability of c or fewer arrivals if mean number of arrivals = (

• P(c or fewer arrivals) = POISSON (c,(,FALSE)

5. Normal: find area from normal distribution variate z

• Area on the left of z, ((z) = NORMSDIST(z)

6. Normal: find normal distribution variate z from area, ((z)

• z = NORMSINV(Area on the left of z)

7. Normal: find area from normal distribution variable x

• Area on the left of x = NORMDIST(x,(,(,TRUE)

8. Normal: find normal distribution variable x from area

• x = NORMINV(Area on the left of x,(,()

Question A

Find

1. probability(exactly 1 success) if p=0.4, n=3

2. probability(0 or 1 success) if p=0.4, n=3

3. probability(exactly 1 arrival) if (=2

4. probability(0 or 1 arrival) if (=2

5. area (under normal curve) on the left of z = 2.5

6. z corresponding to the area (under normal curve) on the left = 0.4

7. the area (under normal curve) on the left if x=600, (=300 and (=120

8. x if the area (under normal curve) on the left =0.4, (=300 and (=120

( Exercise on finding cost per period for an [pic] control chart

• Given input data [pic]and trial value of control chart parameters [pic]

• [pic]=2*NORMSDIST(-k)

• [pic]

= NORMSDIST(k-(*SQRT(n))-NORMSDIST(-k -(*SQRT(n))

• [pic], [pic],[pic]

• Sampling cost per cycle =[pic], Search cost per cycle =[pic], Operating cost per cycle = [pic]

• Total cost per period = [pic]

Question B

1 Using the data shown below, find the total cost per period if an [pic]control chart has

a. [pic]

b. [pic]

c. [pic]

2 For each trial value of [pic] in Question 1, find an optimal [pic]that minimizes total cost per period. Find the optimal[pic].

Excel Solver

1. Add-in Solver, if needed: Click on the “Tools” menu. If the “Solver” is not one of the items of the “Tools” menu, choose the “Add-Ins.” Click on the box adjacent to “Solver Add-In” (a check mark will appear) and click “OK.”

2. Define objective: From the “Tools” menu, choose “Solver.” You get the “Solver Parameters” window. Click on the little arrow adjacent to the box “Set Target Cell.” Click on the Cell containing total cost and click again on the arrow. Click on the circle adjacent to “Min.”

3. Decision variable: The cell address for the decision variable is entered in the box below “By Changing Cells:.” The decision variable is the cell containing k.

4. Non-negativity: From the “Solver Parameters” window click on “Options.” You get the “Solver Options” window. Click on a box adjacent to “Assume Non-Negativity.” Click on “OK.”

5. Solve: Finally, from the “Solver Parameters” window click on “Solve.” A “Solver Results” window pops up. Click on “OK.”

Supplement: Economic Design of [pic]Control Chart using Excel Solver

Input

|a1 |cost of sampling each unit |

|a2 |Expected cost of each search |

|a3 |per period cost of operating in an out-of-control state |

|( |Probability that the process shifts from an in-control state to an out-of-control state in one period |

|( |Average number of standard deviations by which the mean shifts whenever the process is out-of-control. In other words, the |

| |mean shifts from ( to (±(( whenever the process is out-of-control |

Output (Decision Variables)

Determine n and k, where n is the sample size and k provides the upper and lower control limits as

Objective

Minimize the average cost per period.

Relationships

Type I error, ( = [pic]

1. Type II error, ( = [pic]

2. ((z) is the cumulative standard normal distribution function and may be obtained from the Excel function NORMSDIST: [pic]= NORMSDIST(-k), [pic]=NORMSDIST[pic], [pic]=NORMSDIST[pic]

3. For a given pair of n and k the average per period cost is

Setting up the Spreadsheet

A spreadsheet is set up to compute the average per period cost from a given pair of n and k

Input:

Known input values are entered in each of the cells below:

• D4: a1

• D5: a2

• D6: a3

• D7: (

• D8: (

Output (Decision Variables):

For given values of n, the Excel solver will output optimal values of k in cells B10, B11, B12, … For example, for n=1, the optimal value of k is obtained from B10.

Relationships

Formulae are entered for relationships explained below:

1. Type I error, (

C10 =2*NORMSDIST(-B10) (copied to C11, C12, …)

2. Type II error, (

D10 = NORMSDIST(B10-$D$8*SQRT(A10))

-NORMSDIST(-B10-$D$8*SQRT(A10)) (copied to D11, D12,…)

3. The average per period cost

E10 =$D$4*A10+($D$5*(1-D10)*($D$7+C10*(1-$D$7))+$D$6*$D$7)/

(1-D10*(1-$D$7)) (copied to E11, E12, …)

In the following, I explain the use of Excel solver to get a minimum cost value of k for n=1. Similarly, other minimum cost values of k for other values of n may be obtained. Comparing the costs resulting from all the pairs of n and k, we can get the best pair of n and k that yields a minimum average cost per period.

The Excel Solver

1. Add-in Solver, if needed: Click on the “Tools” menu. If the “Solver” is not one of the items of the “Tools” menu, choose the “Add-Ins.” Click on the box adjacent to “Solver Add-In” (a check mark will appear) and click “OK.”

2. Define objective: From the “Tools” menu, choose “Solver.” The “Solver Parameters” Window pops up. Click on the arrow adjacent to the box “Set Target Cell.” Click on the Cell E10, that stores the average cost per peirod and click again on the arrow. Click on the circle adjacent to “Min.”

3. Decision variable: The cell addresses for the decision variables are entered in the box below “By Changing Cells:.” You may again use the little arrow to enter the decision variable $B$10.

4. Non-negativity: From the “Solver Parameters” window click on “Options.” The “Solver Options” window pops up. Click on a box adjacent to “Assume Non-Negativity.” Click on “OK.”

5. Solve: Finally, from the “Solver Parameters” window click on “Solve.” A “Solver Results” window pops up. Click on “OK.”

6. The Solution: See the changes to your spreadsheet. The cell for k (i.e., B10) is expected to contain the optimal solution and its cost in the cell for the average cost per period.

-----------------------

[pic]

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download