The following exercises give you exposure to some important statistical functions.Be careful when you enter decimal fractions. Depending on the formatting of the cell that the fraction is in, the Excel function may interpret it incorrectly. For example, entering 90 for 90% might be read by Excel as 90, not 0.90.

1 Use the binomial distribution function, BINOM.DIST(number_s,trials, probability_s,cumulative),to calculate the probabilities below.

[Note: probability_s is the population deviation rate]

Population Deviation Rate Sample Size # of Deviations in Sample Probability of ObtainingxDeviations Probability of

[p] [n] [x] [b(x|n,p)] ObtainingNo More

Than xDeviations

[Sb(i|n,p),for i=1 to x]

a 5% 30 0 ? ?

b 10% 60 2 ? ?

2 Suppose, in 1 a and 1 b, that the population deviation rate is the maximum tolerable deviation rate , MTDR.

Make about the population based on the number of deviations found in each case.

1a

1b

3 A. You have selected 50 transactions at random for a test of control.

Calculate the upper 95% confidence limit using the inverse beta function [BETA.INV(1-b,x+1,n-x) ].

Number of deviations in sample: 2

Upper Confidence Limit UCL: ?

B. Show that the UCL calculated in part A is correct by calculating the risk that the population deviation rate is greater than the calculated UCL. Use Excel's cumulative binomial function, BINOM.DIST(x,n,UCL,1) .

Risk, from BINOM.DIST(x,n,UCL,1) ? (The risk should be .05)

C. Suppose that the MTDR is 0.06 .

Calculate the risk, RMM, that the population deviation rate is greater than the MTDR.

Use the function BINOM.DIST(x, n, MTDR, TRUE)

RMM= ?