STAT COE-Report-06-2017

Reliability Test Plans for Binary

Responses: Excel Tool Guide
Best Practice
Authored by: Sarah Burke, PhD

21 February 2017

The goal of the STAT T&E COE is to assist in developing rigorous, defensible test strategies to
more effectively quantify and characterize system performance and provide information that
reduces risk. This and other COE products are available at www.AFIT.edu/STAT.

STAT T&E Center of Excellence

2950 Hobson Way – Wright-Patterson AFB, OH 45433
STAT COE-Report-06-2017

Table of Contents

Executive Summary....................................................................................................................................... 2
Introduction .................................................................................................................................................. 2
Get Test Plan ................................................................................................................................................. 3
Compare Test Plans ...................................................................................................................................... 4
Conclusion ..................................................................................................................................................... 6
References .................................................................................................................................................... 6
Appendix ....................................................................................................................................................... 7
STAT COE-Report-06-2017

Executive Summary
This paper describes how to use the STAT COE tool in excel for planning and evaluating reliability tests
when the response is binary (pass/fail). The methods used here utilize acceptance sampling plans
commonly used in industry and are common in statistical software. This tool should be used in situations
when practitioners do not have access to statistical software packages.

Keywords: operating characteristic curve, acceptance sampling, binary response, test plan comparison

Introduction
This paper describes how to use the STAT COE tool in excel for planning and evaluating reliability tests
when the response is binary (pass/fail). This tool should be used in situations when practitioners do not
have access to statistical software packages. The methods used here utilize acceptance sampling plans
commonly used in industry and are common in statistical software. This tool should be used in place of
the binomial nomograph as explained by Harman (2013). The appendix of this paper explains the
calculations used to determine the test plans and to plot the resulting operating characteristic (OC)
curves. Refer to Truett (2013) for questions on using OC curves and their interpretation. To generate a
reliability test plan when the response is continuous, refer to Kensler (2014a, b). To compare test plans
for continuous responses, refer to Harman (2015a, b).

The assumptions regarding the use of these sampling plans are as follows: 1) the response/outcome is
binary (pass/fail, for example); 2) each run in the sample is independently executed and performed
under the same test conditions; 3) the percent defective in the population is constant; and 4) the sample
size 𝑛𝑛 is fixed.

The goal of this paper is to describe how to use the tool, but first recall these important definitions:

• Acceptable quality limit (AQL): the largest percent considered acceptable (the level at which we
would like to pass the system). This can be thought of as the objective for an acquisition system.
• Rejectable quality limit (RQL): the poorest level of quality that should be accepted (the level at
which we would like to fail the system). This can be thought of as the threshold for an
acquisition system.
• Producer’s risk: The probability of rejection/failing a good system (i.e. failing a system operating
at or above the AQL).
• Consumer’s risk: The probability of acceptance/passing a bad system (i.e. accepting a system
operating at or below the RQL).

This tool has two functions: generate a test plan given supplied inputs and compare test plans using OC
curves. Each of these functions are described in the following two sections.

Page 2
STAT COE-Report-06-2017

Get Test Plan

The first tab in the tool allows you to generate a test plan given inputs of the AQL, RQL, producer’s risk,
and consumer’s risk. Ideally, a system operating at the AQL should have a high probability of acceptance
and a system operating at the RQL should have a low probability of acceptance. The AQL and RQL should
be entered as a percent so that 0 < 𝑅𝑅𝑅𝑅𝑅𝑅 < 𝐴𝐴𝐴𝐴𝐴𝐴 < 100. For example, suppose we have a system with
an objective reliability of 97% and threshold reliability of 95%. We set the producer’s risk and
consumer’s risk at 0.05 and 0.10, respectively, meaning that there is a 5% chance of failing the test for a
system that operates at or above the AQL and a 10% chance of passing the test for a system that
operates at or below the RQL. These values are entered as shown in Figure 1.

Figure 1. Data entry to generate test plan

By clicking the button “Get Test Plan,” the resulting sampling plan, consisting of the sample size and
acceptance number, is displayed (Figure 2).

Figure 2. Test plan for supplied inputs

In addition, the resulting OC curve is displayed with the AQL and RQL overlaid on the same plot (Error!
Reference source not found.) as the green and red vertical lines, respectively. Note that the producer’s
and consumer’s risks meet the specified requirements.

Page 3
STAT COE-Report-06-2017

Figure 3. OC curve for resulting test plan

In addition to the OC curve, an interactive table is included in the tool that allows you to enter in a value
of 𝑝𝑝, the quality level entered as a probability (0 < 𝑝𝑝 < 1), to determine the probability of acceptance
(pass the test) or rejection (fail the test). This value 𝑝𝑝 is entered into any of the orange cells as shown in
Figure 4 and the resulting probabilities are shown in the blue cells. The first two rows of this table are
automatically updated to include the AQL and RQL as entered in Figure 1.

Figure 4. Interactive table to calculate probability pass or fail for supplied value of p

Compare Test Plans

The second tab in the tool allows you to compare up to three test plans by comparing their respective
OC curves. To generate the OC curves for the test plans of interest, enter in the sample size and
acceptance number in their respective boxes (Figure 5). You can enter in 3 or fewer sampling plans to
Page 4
STAT COE-Report-06-2017

plot their respective OC curves in the same plot for comparison. As an optional feature, you can enter in
the AQL and/or RQL to include on the plot as well. The AQL and RQL should be entered as a percent.
Consider the example previously where the objective and threshold reliability are 97% and 95%,
respectively. In Figure 5, the AQL is entered as 97% and the RQL is entered as 95%. If both the AQL and
RQL are entered, they should be entered such that:

0 < 𝑅𝑅𝑅𝑅𝑅𝑅 < 𝐴𝐴𝐴𝐴𝐴𝐴 < 100.

Figure 5. Data entry to compare test plans

Figure 6 shows the resulting OC curves for the plans entered in Figure 5, as well as the AQL and RQL.
Note that these test plans do not perform well for the given AQL and RQL.

Figure 6. OC curves for three sampling plans

In addition to the OC curves, the tool also displays the probability of acceptance (pass test) and rejection
(fail test) for the AQL and RQL values if they are entered (see Figure 7).

Page 5
STAT COE-Report-06-2017

Figure 7. Summary results for AQL and RQL values

Conclusion
The excel tool presented in this paper is available on the STAT COE website at www.afit.edu/STAT or by
email at COE@AFIT.edu. This tool can be used to determine test resources and to compare test plans.
The appendix describes the calculations used to generate the test plan and to create the resulting OC
curve.

References
Harman, M. (2013). Use of the Binomial Nomograph for Test and Evaluation Planning. Retrieved 14
February, 2017, from STAT in T&E Center of Excellence:
http://www.afit.edu/images/pics/file/2_%20Use%20of%20the%20Binomial%20Nomograph%20for
%20Test%20and%20Evaluation%20Planning.pdf

Harman, M. (2015a). Reliability test Planning Utility Tool. Retrieved 14 February, 2017, from STAT in T&E
Center of Excellence:
http://www.afit.edu/images/pics/file/Reliability%20Test%20Planning%20Utility%20Tool-
Best%20Practice.pdf

Harman, M. (2015b). STAT COE Reliability Test Planning Tool App. Retrieved 14 February, 2017, from
STAT in T&E Center of Excellence:
https://www.afit.edu/images/pics/file/STAT%20COE%20Reliability%20Test%20Planning%20Tool.xls

Kensler, J. (2014a). Reliability Test Planning for Mean Time Between Failures. Retrieved 14 February,
2017, from STAT in T&E Center of Excellence:
http://www.afit.edu/images/pics/file/Reliability%20Test%20Planning%20for%20Mean%20Time%20
Between%20Failures(2).pdf

Kensler, J. (2014b). MTBF Test Planner Tool. Retrieved 14 February, 2017, from STAT in T&E Center of
Excellence: https://www.afit.edu/images/pics/file/STAT%20Planning%20Tool.xls

Page 6
STAT COE-Report-06-2017

Truett, L. (2013). Using Operating Characteristic (OC) Curves to Balance Cost and Risk. Retrieved 14
February, 2017, from STAT in T&E Center of Excellence:
http://www.afit.edu/images/pics/file/Using%20OC%20curves%20to%20balance%20cost%20and%2
0risk(1).pdf

Appendix
The assumptions for a binomial random variable are: 1) a binary outcome; 2) a fixed sample size 𝑛𝑛; 3)
independently selected runs from the population; and 4) constant probability of success 𝑝𝑝. If these
assumptions hold for a test plan for a binary response, then the number of failures in a sample of size 𝑛𝑛
has a binomial distribution. The probability of 𝑖𝑖 failures is therefore defined as:
𝑛𝑛
𝑃𝑃(# 𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓𝑓 = 𝑖𝑖) = � � 𝑝𝑝𝑖𝑖 (1 − 𝑝𝑝)𝑛𝑛−𝑖𝑖
𝑖𝑖

A system is accepted if the number of failures 𝑑𝑑 is less than the acceptance number 𝑐𝑐. If the four
assumptions described above hold, test plans for binary responses are determined by solving for the
sample size 𝑛𝑛 and acceptance number 𝑐𝑐 through a system of two nonlinear equations:
𝑐𝑐
𝑛𝑛
1 − 𝛼𝛼 ≥ � � � 𝑝𝑝1𝑑𝑑 (1 − 𝑝𝑝1 )𝑛𝑛−𝑑𝑑
𝑑𝑑
𝑑𝑑=0
𝑐𝑐
𝑛𝑛
𝛽𝛽 ≤ � � � 𝑝𝑝2𝑑𝑑 (1 − 𝑝𝑝2 )𝑛𝑛−𝑑𝑑 ,
𝑑𝑑
𝑑𝑑=0

where 𝛼𝛼 is the probability of a type I error, 𝛽𝛽 is the probability of a type II error, 𝑝𝑝1 is the acceptable
quality level (expressed as proportion defective), 𝑝𝑝2 is the rejectable quality limit (expressed as
𝑛𝑛!
proportion defective), and �𝑛𝑛𝑑𝑑� = (𝑛𝑛−𝑑𝑑)!𝑑𝑑!. The probability of acceptance at the AQL should be at least as
large as 1 − 𝛼𝛼. The probability of acceptance at the RQL should be no larger than 𝛽𝛽, the desired
probability of acceptance at the RQL. We typically want this value to be small since 1 − 𝛽𝛽 is the desired
probability of rejection as the RQL. Because these equations are nonlinear and 𝑛𝑛 and 𝑐𝑐 must be integers,
these values are determined through a search algorithm. The resulting test plan is the smallest sample
size and acceptance number that satisfy these two equations.

The OC curve for a binary response utilizes the binomial distribution as well. The probability of
acceptance (passing the test) is found by calculating the probability of observing 𝑐𝑐 or fewer failures in
the test across values of 𝑝𝑝 from 0 to 1 where 𝑝𝑝 represents the probability of failure:
𝑐𝑐
𝑛𝑛
𝑃𝑃𝑎𝑎 = 𝑃𝑃(𝑑𝑑 ≤ 𝑐𝑐) = � � � 𝑝𝑝𝑑𝑑 (1 − 𝑝𝑝)𝑛𝑛−𝑑𝑑
𝑑𝑑
𝑑𝑑=0

Page 7