- - . - - -- -. - -- - -
A SPREADSHEET SOLUTION TO DESIGN SINGLA
2.1 SPREADSIIEET SOLUTION TO GRAF ET AL'S METHOD
Der.ivalion of a Single Sampling Pla11 (SSP) by running C or FORTRAN programs can be
replaced will1 spreadsheel solulions. In this chapter the Graf e l al procedure is implemented
oil the Bxcel spreadsheet leading to a new method cnlled Modified Graf et al Metliod (MGM).
Two ollier methods viz., Peach-Lillauer,~ metliod and the AT1 based method are also
I I~ I I I I I I I I I 1 1 l 1 ~ I I~IIII-~II liit~cl~otts TUI AlcSilt a11r1 Statida~d Normal
i l i s l ~ ~ b t ~ l t ~ ~ ~ s C:I!I I~c IIIIIIIC~ 10 CV~IIII~IIC l l te oxprcsslons ill l l i c Graf e l a! p~ocedure discussed
ill cIIIII)~c:--I
'l ' l~is olle slcli rnrliiill:~ Lo IIC~C~.I~~~IIC t i a ~ i t l c, is ht~scd 011 Bitioniial cltstribution type OC
ft~ncliol~. Tlic approxtmatlon foni i~~ln proposcd by Grnf et xl given in (1.7) requires the
ovalu;~tio~~ o1ll1e rollowing terns
(11 = AIC sin (fi)
* 4, = Arc sill (m Z,, = percentile of orcler o from N(0,l)
* c 2 n.(sin'rp')-0.5 (acceptance number)
The Excel template Lo work wit11 these forniulae is sliown in figure-2.1.
gle Edt Ww mrt Fwm& 1~01s Rate M d o w Edp - 6 J X >>
Q . ' a': lo . u I &! S p J z ~ t & C a ; E E I . a - & - F4 . f x =(1R"(D4+D~(l-l/~F6*D4*05)))+(D6+D7)/(4~SQRT(F6))
A . ~. ~ B.-.. C D E P i E X C ~ E M P L A T ; W'EVALt! ATE G b F rf i i o b U L A E TO 2 DETERMINE n and c FOR SINGLE SAMPLING PLAN
I I I I I
Figure-2.1: Excel template for Grnf et nl's proedure
' I ' I I I ? I I ~ I I I ~ I ~ : I I C ~ I Y C S it 1p1;111 iv1111 11 - I ! 1 itl~d c = 14 for I I IC given ~ I ~ ~ L I I V:I!IICS. 'I'lic p!aii is
Iiirwcvcr not ;ulrri~ss~l~ll: its I I givcs a lligl~er [\-I tsk (0.522) tl~an tlie specified valuc (0.08).
111 tlie liillow~ng sccliorr ;I ncw plan is dcrivctl wilh ihc sarlic n arxl values o r c tnodilicd io
s ~ c h ;I wily 111;1t llic pliltl hiis I-rsks C I ~ S L ' I ~ 10 file s/~eciJiiotio~ls.
2.2 bIODlk'II':U (;RAl' UI' AL ME'IYIOD (MGM)
Let t11c values given by Oral' c l a1 method be no and co, which niay not always yield an
atlri~issible pliln. Tlie adn~issibilily orthc plan can be verified by evaluating the achieved risks
at AQL and RQL. Thc dilference betwee11 the target risk and achieved risk is a measure of
tlie deviation from the adnlissible ],Ian. By searching ill the neighborhood o lno and co, it is
possible to locate a plan with adtnissible risks, by using spreadsheet functions. By fixing the
value o l n as no we can clia~lge Lhe value of co until the admissibility conditions are satisfied as
closely as possible. This search usually requires a few trails and can be at~tomotized by using
solver in Excel. Considcr the following proposition.
Proposition-2.1: A measure of performance of the plan is the Root Squared Deviation (RSD)
defined ill t e r m of Holder's noml Lp = 1) C (g, -G,,IP 11 lh' , where g, and G, aro the Expected
and Observed values a l it" data point. Taking p = 2 we get
2 112 RSD = Il [(l-@)-Lno~(%, C O ) 12 + [ P - L R Q L ( ~ ~ , co) I II (2.3)
Propasitio~i-2.2: RSD is coilvex in c and hence a unique minimum exists at c = c,,,.
l'raofi By definition Total Squared Deviatio~i is given by
(TSD) = [(l-a)-L,$Q,,(nO, c, ) 1' 4- [ p-J2kQ~(n,, c,) 1' which is the sum of squared errors at
hQL aud RQL respectively. The first component is a decreasing function oCc and the second
unu IS ,III ~i~ric;[sitrg 1[1nctt,i11 ; I I I ~ il~cir si1111 is cotiitcx ill c. T l ~ c RSD Ilcing :I real valrlcd
iilllctlull 11I''l'Sl) IS ;llso coll\'cx
I ~ I c ~ I ~ c I I I C ]lrllllI:
'She k ~ r ~ ~ ~ i ~ l i i ~ . given by i j r ; ~ f ct ill C ~ I I I bc lnodifictl as follows.
Stop-I: Conipule [Ire vair~cs or11 and c using (2.1) & (2.2) of chapter-] and call thern no and
cl, respcctivcly.
Stcp-2: Calcr~late L(AQL) and L(RQL) [or this plan.
Slcp-3: IFl11e arlmissibilily condiiiol~s are satisfied, i t is [he optimum plan; else go to next
step.
SLep-4: Fixing the value af 11 a1 no decrease [he value o f c from co Lo co- l and evaluate KSD
Step-5: Repeal Lhe procedure unlil RSD reacl~es minimum for some value of c = c,,.
Step-6: The plan (n, c) with n = no and c = c,, is the required plan by the Modified GraPs
Method (MGM).
The following is the stepwise procedure for implementing MGM on the spreadsheet.
2.3 IMPLEMENTING MGM ON SPREADSHCET
MCM is implenlented in two stages. Stage.1 deals with derivation of no and co from Grnf et al
procedure. Stage-2 uses solver tool to reach optimum c iteratively.
Stngel: I tnplemel~ti~~g Graf et al's procedure
Step-1: Input thevnlum of AQL, RQL, a ant1 P in specific cells of the sheet.
Step-2: BIIIW the fo r~~~u lne in various cells as sliown below. Labels for the vnrinbles
may be given on tilo lefl olforniulae cells.
Stop-3: This coillpletes the deterinination of n and c as no and co.
Step-4: By changing the input values, the output gets automatically updated.
H5
Stage-2 : Search for near optimum n and c by using Solver
Tlie rollowing are Lhe steps of the optiinizatioi~ procedure. Iterations are pcrfonned with cl =
co flnd c,+, = c,-1 until the RSD(c,) 5 RSD(ci+l). The Salver {nodule in Excel uses this
condition to terninate the search.
Step-5: Enter llie forniulae as shown below.
= ROUND (FS,D)
Stepd: Copy the c value born H5 to A9
Step-7: Put cursor on cell A9 (Destination cell).
Step-8: Select Solver fi'om Tools option and fill in the details of the wizard, with
target cell A9 and changing cell also as A9.
Step-9: Indicate the constraint as D9 < D l 0 so that the iterations are run until the
difference (D9-DIO) becomes negative. Once this condition is reached, Solver
aulo~natically stops iterations and the final values at A9 will be copl.
The complete procedure can be saved as a template so that simulation can be performed to
assess the sensitivity of the procedure. The Excel template for obtaining thc near optimum
plan is given in figure-2.2.
@ @ l a Bht Ylew Insert F m a t lads Qata Wdow Help
I ' )& '%?-:@ r - 1 0 -jB I B '~B35@jf<8;$,m-&-&~ ,
Figure-2.1. Excel Template for implementing MGM
2.4 COMPUTATIONAL EXPERIENCE AND SENSITIVITY
The MGM, which can be easily implemented on the Excel sheel witl~oul rcfel.cncc l o tahlcs
ant1 program codes, has been expelimet~ted over a widc range of input parameters and tl~e
aptin~al solution for each case has been obtained. The bellaviour of RSD is also vc~ifrcd and
tile findings nre reported in table-2.l(a),(b) and (c).
Illuslration-2.1
Let AQL = 0.05 and a = 0.05. For different values of P and RQL the behavior of RSD with
~espect to c is show11 in tables-2.l(a)-(c). It can be observed that the plan given by MGM
attains both ndmissibility m d minimum RSD.
Hence near optimum plans shown in bold face in the tnbles9,lja)-(c) cnn be developed with
the help of the Spreadsheet Telnplate. The near optimum sulrltion satisfies the arlvertised
risks quid closely. The behaviour of RSD is shown in figure-2.3. The RSD can be
inte~yreted as Lhe total percentage error (deviation) in the plan when compared to the specified
risks.
We note that in plan-1 there was about 44.5% error with the original n and c, which
was brought down to 0.4% by decreasing c from 14 to 9 ,
In plan-4 when P is increased to 0.10 the percentage error has decreased from 44.5%
to 3.3% and c has decreased Fram 13 to 8.
Again in plan-7 with P as 0.12, the optimal c has decreased from I 1 to 8 with
percentage error decreasing from 31% to 0.5%.
On an average with the chosen parameter the search was terminated in a maximum of
5 iterations.
It is also observed that higller the gap between AQL and RQL, smaller will be the
number o f iterations required to find con(.
Values of RSD wit11 AQL = 0.05, a = 0.05 and 11 =0.08
Table-2.l(a): Sensitivity of the plan with P = 0.08
(Figutes in bold face indicate the minimum RSD)
c
14
13
I I I
1<QL=0.13 n = 111 C = 14
Plan-1 - 0.4446
0.3329
Nenr Optimum Plan (767) (111,9)
RQL=0.15 11 = 76 c = 1 1
Plan-2
(565)
RQL=0.17 n=56 c = 9
Plan.3
Values of RSD with AQL = 0.05, a = 0.05 and P =0.10
i Table-Z.l(b): Sensitivity of the plan wit11 P = 0.10
c
14
13
Near Optimum
Plan
(F~gures in bold face indicate the minimum RSD)
IIQL=0.13 n = 102 c = 1 3
Plao-4
0.4454
(lo2,a)
RQL=O.IS n = 7 0 C=10
Plmd
(706)
KQ1,=0.17 11 =52 c = 8
Pla11-6
(523)
Val~les of RSD with AQL = 0.05, a = 0.05 and p -0.12
(Figures in bold fnce indicate the rninimurnRSD)
-~
c
Near Optimum
Plan
-. RQL=0.13 n = 94
Table-Z.l(c): Sensitivity of the plan with P = 0.12
(943) -
---- c = l l
RQL=0.15 n=65
(45,6)
RQL=0.17 11~448
(48,s)
Plan-7
14
c = 1 0 c - 7
0 1 2 3 4 5 6 7 8 9101112131435
Value of c
Figure-2.3(b): Behaviour of error at AQL in plan-4
1.20 1
0 I 2 3 4 5 6 7 8 9 lOlllZ131415
Value of c - - - - - - -- - - - -
Figure-2.3(e): Behaviour of error at RQL in plan-4
0 1 2 3 4 5 6 7 8 9101112131415
- Value of c -~
2.5 COMPARISION WITH GUENTHEI<'S METHOD
The solution obtained by MGM and the one given by the Guentller's algoritlun, (which is
cons~dered as a standard procedure and that always ensures admissibility) are compared and
the resiilts are shawn in table-2.2.
Madifled Graf et nl
SLNO AUL ItQL a P
1 Tahle-2.2: Comparison of Guenther's and MGM plnns I
it can be secn that the MOM offers a more conservative plan with smaller c when con~pared
to that of Guenther's plan. The behavior of OC c u ~ e s of plans obtained from thcse methods
for specific inputs AQL=0.05, R'?L=0.13, a = 0.05 and P =0.1 is shown in figure-2.4. Thus
the MGM has given llear optimum plans and they are found to be close to Lhe exact soltdioll
given by Guenther.
7- -
-- --
I Figure-2.4: Cornparision of OC curves
1,llus it is found tlial MCM gives better plall (Iravlng slnaller n) tl~an CiL~mrtller's proccdurc
Binolnial d~strihutio~l. The eadier reseurchers have provided ( ) FOR~,I<AN programs lo
work with Gucnther's procedure while wc have developed a C-ploglem Tor ~ u c ~ t l , ~ ~ ' ~
procedure using both Binomial and Poisson cases.
In thc following section a spreadsheet procedure for the Peach-Littauer's algoritllm is
developed slid the results are camparecl with those obtained by Guentlier's algoritllm.
2.6: PEACH-LITTAUER'S ALGORITHM
The following are the steps of Peach-Littauer algorith~n,
Step-1: Givcn valoes ofAQL, RQL, cr and P Calculate
2 2 X 2 ( c t l ) : l - B and q2(c) = XZ(c+');a by takingc=O,
"'(C)' 2.RQi. 2.AQL
StepJ: Verify whether ql(c) 2 q2(c). If it is sntisficd, then n =smallest integer between
[q1(c),q2(c)]; else go to next step.
Step-3: Increase c by one and repeat step-1 nnd step-2. The optimal solutian corresponds
to then and c when the procedure terminates.
The above procedure can be implemented in Excel spreadsheet by ~~si:lg Solver whiclr is
given in figure-2.5.
. . - 0 X ' t , e x
~1 MIo-,@ i$ 10 . ; H z E ; = ~ @ Z @ : : ~ C ~ ! - . A , % . ):
Figure2.5. Excel Template for implementing Peach-Liltaoer's algorithm
~liustration 2.2
Suppose AQL = 0.05, ROL = u 13, fl = 0.05 and P =O.O8 thcn the Encci lcmplate Elves n = 123
and ; 10 whlch exactly malclles to the Guentlm's solution. The plans fol- differzni input palanlclcrs
shown in table-2.3.
Table-2.3: Comparison of Guenther's and Peach-Littauer's plans
It can be seen from the above table that the sampling plan by Guenther's algorithm with
S.NO
1
2
Poisso~l OC and the plan given by the Peach-Littaner are idenlical far different values the
input parameters. Pcach-Littauer solution is obtained by a spreadsheet template, which is easy
to implement than the Guenther's model to be run with C-program.
RQL
0.13
0.15
AQL
0.05
0.05
2.7 A SINGLE SAMPLING PLAN THAT MINIMIZES AT1
This type of plans can be derived far rectifying inspection with given values of fraction
defective (p) and the lot sizc (N). The plan should pass thraugh either the point (RQL,P) or
(AQl.,I-a). The general proccdure to work with this method is explained below
u
0.05
0.05
P
0.08
0.08
GUENTIIER (Paisson)
n
123
90
PEACH-ILITTAUER (Poisson)
c
I0
8
II
123
90
c
10
8
step-I: Find the value of Polsson parameter ~ i p ~ corresponding t o p with c = 0.
This car? be found by using the Excel functioil CHlNV() given in Apl~endi~- [ .
stcp-2: Find value of n using n = npU/RQL.
step-3: Find h = np for given 13.
Step-4: Calculate OC p,) value for h and c (-0) using POISSON [X, Mean, 1).
step-5: Using this p, colnpilte AT1 = n +(N-n)(l-P,)
step-6: Put c = c+l and repeat the above steps until AT1 attai~ls minimum. Sincc K ~ I is convex in c, a unique minimum exists.
This is an iterative procedure that can be implemented in the Excel template, which i s given in
Let us take RQL = 0.10, P = 0.08, p = 0.02 und N 1000. The abovc proccdure yields the
7
8
Y
10
Table-2.4: Parameters of SSP that minimizes AT1
1 1.7709
12.9947 --
1d.ZOGO -- 15.4066
148
163
178
193
2.96
3.26
3.56 - 3.86
0.9889
0.9936
0.9963
0.997R
0.0111
0.0064
0.0037
0.0022
157
168
181
195
ÿ he solver tool automatically stops at c = 5. To show Ulat AT1 is convex, we havo cva lu~ t~d
ATI for values beyond c = 5. The behaviour of AT1 is shown in figure-2.7.
. - D I E ] ~ ~
3SP WfiIc?i%INIMIZES AT1 USING 2 .. . 3 . . -. 4
5 . -
I I I I I I 3TEPS:HYOL:'II)ir; THO rEPnfL4TE
6 . .- 7 -- . 8.. a
10 .. I I I I I I I 17 STEP-I: CHANGEINPUT VALUESLIKEEETA, RQL, p AND N IF REQUIRED. 18 STEP.2: PUT CURSORON THECELL AS. / I I I . 19 STEP9: TOoWSOLVJmSOLV~hanwc can sscfmsltainDll,D12 m d D l . .
I - T4 ! nffkf i5h:eEr I
Beta- . R Q b
4499999
Figure-2.6: Excel tcmplate to design SSP that minimizes AT1
c 4 . 5
0.1 0.08
npbeta) 7.9936 9.2747
- N =
DM 1000
n 100 116
Lembda 2.00 2.32
I-pa 0.0527 0.0311
ATI / 147 / 141 /
I 11-11
Figure-2.7: Behaviour of Am
ol,scrvntia~~s
111 this chapter wc have dealt with some aspccts relating to the design of a single sampling
First we have shown (hat [he algorithm-bascd procedures call be alternatively
lmplemenled in Excel worksheet by utilizing the slatistical functions. It is observed that
iterative procedures leading to the design of single sa~iipling plan could be easily implemented
i n Excel spreadsheet with solver 1001. This would rcduce programming effort and helps in
simulation experiments.
In the following chapter some aspects of'Slrip Lot Sr~mplifrg Plaits are considered in tlre light
of tile sprendsheelproceclures.