> HG A\pMark Schreiner Ba==h\: 8X@"1Arial1Arial1Arial1Arial1EMR101EMBX101EMBX101EMR10"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* ""_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* ""_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* ""??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* ""??_);_(@_)0.00000.0 + ) , * (@ ( ( (@@ (@ ( @ (@ ( ( ! ! ! ` Front_endCMBack_end Port_comp Port_compKAUpper bound: 0.10ErrorsLevel of confidence 2001 Mark SchreinerUpper bound: 0.05Upper bound: 0.025Upper bound: 0.01Upper bound: 0.005Upper bound: 0.001Selected upper bound:Selected level of confidence:Index to # of errors:Index to confidence:n for upper bound=0.05:n for upper bound=0.01:n for upper bound=0.025n for upper bound=0.005:n for upper bound=0.001:N for upper boundRequired N:Audit Sampling for Microfinance Organizations0by Mark Schreiner, 2001, mark@microfinance.comInstructionsEntries\Save this spreadsheet with a new name. Keep the original, because this copy will be changed.HEnter the upper bound on the acceptable estimated proportion of "cases".@ Valid entries are 0.10, 0.05, 0.025, 0.01, 0.005, and 0.001.4 A "case" is an "unusual" or "erroneous" finding.? For example, if auditing for undisclosed rescheduled loans,? an undisclosed rescheduled loan is a "case" or "error".T The upper bound on the acceptable estimated proportion of "cases" is the maximumd share of the portfolio that could be "cases" and still not flag special action in the audit.Z For example, 0.01 means that, from an audit perspective, everything is fine as long asU the estimate of the proportion of "errors" or "cases" is less than 1 percent.9Enter the desired level of confidence on the upper bound.7 Valid entries are 90, 95, 97.5, 99, 99.5, and 99.9.8 The level of confidence are in units of percentages.W For example, a 95percent confidence level with a 0.05 upper bound implies that the[ auditor desires to know whether or not the proportion of "cases" or "errors" in theH portfolio is less than 0.05 with at least 95percent confidence.N Smaller upper bounds increase the required sample size, all else constant.] Higher desired levels of confidence increase the required sample size, all else constant.YGiven the acceptable upper bound and the desired level of confidence, proceed as follows:^This spreadsheet determines required sample size for sequential "needleinahaystack" audits.KIt does not manage the sampling and tracking of results from sampled items;= that is assumed to occur in the accompanying spreadsheet.Total sample size^ As always, the items are sampled in descending order, according to their sampling weights.] Or conclude that the estimated proportion is not less than the upper bound and stop.[ The estimated proportion is less than the upper bound with the desired confidence._ The estimated proportion is not less than the upper bound with the desired confidence.9 Give up at this point; do not review more items.`In some cases, the auditor will draw a sample of a certain size, with the sample size determinedg by considerations that are unrelated with the desire to sample for "needleinthehaystack" issues.^In these cases, this spreadsheet can compute the confidence level for the estimated proportion1 of the portfolio that is a "case" or "error"._In other words, rather than determined a required sample size and then sampling, one may have a_ sample of a given size (and have found a given number of errors) and wish to know with what] confidence it can be said that the etsimated proportion is less than a given upper bound.ETo use this alternative technique, enter the number of items sampled:.Enter the number of "cases" or "errors" found:eBelow are displayed the confidence levels that the proportion of the portfolio that is a "case" or an0 "error" is less than the given upper bound:Upper bound
ConfidenceBtw@A s
GB{CU EEY5F JG J n+bbJ JbL Jb+Y(Н0YY.Y^/Yb0Yb
1Yazp0f+bbf+6Y7Ybbp+S0bt00b;YIL
dMbP?_*+%J"Jb??ZU}$f}}$ JT0JbJbJ5JJJ JbJbJb
JJJJ^JbJb
JJJbJ0JJbJJJbYmJb
!"
!"
""
"
"
##
+
,

~
$@
!
(
"~
W@
#
$
%
&
'
)
*
.41. Draw D items.BP1. Draw 30 items.>@#ZL:O JT0!Jb"Jb#J5$J%J&J(Jb)Jb*Jb+J,JJ.J^/Jb1Jb
2J3J4Jb5J06J7Jb8J:J;Jb<Jm=Jb>J0?Jb
/f!*P If none of these first D& items are cases or errors, then stop:BPFC If none of these first 30 items are cases or errors, then stop:
"1f#!P If 6 or more of these first D! are cases or errores, then stop:BPFC If 6 or more of these first 30 are cases or errores, then stop:
$2u%_' If, however, 1 to 5 of these first D) items are cases, then proceed to Step 2.BPUR If, however, 1 to 5 of these first 30 items are cases, then proceed to Step 2.
&0E(D%/2. Draw D(D additional items.BP2. Draw 18 additional items.(H@1ZL)(D a^ As always, the items are sampled in descending order, according to their sampling weights.l*)V! If 1 or fewer of these first D(& items are cases or errors, then stop:BPLI If 1 or fewer of these first 48 items are cases or errors, then stop:+L@D"^[ The estimated proportion is less than the upper bound with the desired confidence.f,P If 6 or more of these first D(! are cases or errores, then stop:BPFC If 6 or more of these first 48 are cases or errores, then stop:=D$b_ The estimated proportion is not less than the upper bound with the desired confidence.u.,_' If, however, 2 to 5 of these first D() items are cases, then proceed to Step 3.BPUR If, however, 2 to 5 of these first 48 items are cases, then proceed to Step 3./8D&`] Or conclude that the estimated proportion is not less than the upper bound and stop.E13/3. Draw D1D( additional items.BP3. Draw 15 additional items.1O@:ZL2$.D)a^ As always, the items are sampled in descending order, according to their sampling weights.l35V! If 2 or fewer of these first D1& items are cases or errors, then stop:BPLI If 2 or fewer of these first 63 items are cases or errors, then stop:4+D+^[ The estimated proportion is less than the upper bound with the desired confidence.f52P If 6 or more of these first D1! are cases or errores, then stop:BPFC If 6 or more of these first 63 are cases or errores, then stop:6Db_ The estimated proportion is not less than the upper bound with the desired confidence.u7(_' If, however, 3 to 5 of these first D1) items are cases, then proceed to Step 4.BPUR If, however, 3 to 5 of these first 63 items are cases, then proceed to Step 4.87D/`] Or conclude that the estimated proportion is not less than the upper bound and stop.E:A/4. Draw D:D1 additional items.BP4. Draw 15 additional items.:S@CZL;/D2a^ As always, the items are sampled in descending order, according to their sampling weights.l<1V! If 3 or fewer of these first D:& items are cases or errors, then stop:BPLI If 3 or fewer of these first 78 items are cases or errors, then stop:=4D4^[ The estimated proportion is less than the upper bound with the desired confidence.f>;P If 6 or more of these first D:! are cases or errores, then stop:BPFC If 6 or more of these first 78 are cases or errores, then stop:?T6D6b_ The estimated proportion is not less than the upper bound with the desired confidence.>0@JT0AJbCJbDJ5EJFJGJHJbIJbJJbLJMJNJOJ^PJbQJb
RJUJVJbWJ0XJYJbZJ[J]Jb^Jmu@<_' If, however, 4 to 5 of these first D:) items are cases, then proceed to Step 5.BPUR If, however, 4 to 5 of these first 78 items are cases, then proceed to Step 5.A$>D8`] Or conclude that the estimated proportion is not less than the upper bound and stop.ECJ/5. Draw DCD: additional items.BP5. Draw 14 additional items.CW@LZLD?D;a^ As always, the items are sampled in descending order, according to their sampling weights.lE:V! If 4 or fewer of these first DC& items are cases or errors, then stop:BPLI If 4 or fewer of these first 92 items are cases or errors, then stop:FDD=^[ The estimated proportion is less than the upper bound with the desired confidence.fGFP If 6 or more of these first DC! are cases or errores, then stop:BPFC If 6 or more of these first 92 are cases or errores, then stop:HGD?b_ The estimated proportion is not less than the upper bound with the desired confidence.pI@EZ" If, however, 5 of these first DC) items are cases, then proceed to Step 6.BPPM If, however, 5 of these first 92 items are cases, then proceed to Step 6.JHDA`] Or conclude that the estimated proportion is not less than the upper bound and stop.ELQ/6. Draw DLDC additional items.BP6. Draw 14 additional items.LZ@NZLMLIDDa^ As always, the items are sampled in descending order, according to their sampling weights.lN<CV! If 5 or fewer of these first DL& items are cases or errors, then stop:BPMJ If 5 or fewer of these first 106 items are cases or errors, then stop:OMDF^[ The estimated proportion is less than the upper bound with the desired confidence.fPlOP If 6 or more of these first DL! are cases or errores, then stop:BPGD If 6 or more of these first 106 are cases or errores, then stop:QPPDHb_ The estimated proportion is not less than the upper bound with the desired confidence.
R3
U4
V5
W6
X7
Y8
Z9
[:
];~
]Z@
^<~
^@8h`JT0aYbbYbcJ5dJeJfJgJbhJbiJbjJkJlJmJ^nJboJb
pJqJrJbsJ0tJuJbvJwJxJbyJmzJb{J0Jb}Jb~JJ
`=
a>a
b?
b@~
c$4@5c%/FX@D^@D]@DcA,d%d$RQ?LDc{Gz?d%&X@cd)d%L^@L]@LA,de$
ףp=
?feem L{Gz?e%x{X@ddf$(\?gef%ƑX@edg$zGz?heg%`EX@fdh$233333?ieh%<~~X@gdi$Q?jei%Hm X@hdj$p=
ף?kej%`#X@idk$Q?lek%\楡X@jdl$&\(?mel%o;,_X@kdm$?dem%:W@ld%n$ףp=
?eDm{Gz?n%HSOV@mdo$yGz?pooxL{Gz?o%RZ8U@ndp$Q?qop%rR@odq$Q?roq%N@pdr$?sor%]cE@qds$wGz?tos%ȿ=OgW9@rdt$Q?uot%S֠$@sdu$rGz?vou%79 @tdv$iGz?nov%iJG?ud%w$*On?oDvMbP?w%f֧?vdx$Mb?yxxLMbP?x%Z?wdy$U&1?zxy%=#^=?xdz$~jtx?{xz%̶^u?yd{$WGzt?x{%`?zd$ةMbp?}x%P!ViB?{d}$~jth?~x}%?d~$Mb`?x~%h>}d$lMbP?wx%}&>~dDs
lGu[>>>>>>>>H[>>>>>>>H[>>>>>>JT0JbJbJ5JJJJbJb%$rH}M?xDC6?%Ƣy>d$B6J?LC6?%i>d$F?%ZW>d$@1U0*C?%fB>d$ڨMb@?%D)>d$@6:?%
>d$0U0*3?%05=d$>6*?%8=d$:6?%J=d H[>>>>>>>x@
AMQSX_k
dMbP?_*+%JM"NEC SuperScript GDILg
odXXXX22,C:\NECSSFW\DEFAULT.GDIC:\NECSSFW\PRINT.GDIC:\NECSSFW\PRINT.GDIC:\NECSSFW\OVERLAY.GDINEC SuperScript GDI"dXX??U}
}$ M JT0bYbY5YYYYb Yb
Yb
YYY^YbYb
YYYb0YYbYYYbYYYb
*V@W@`X@X@X@@08@>@B@G@J@Q@0?C@H@L@P@R@@W@0@K@O@@R@@U@@W@@\@0@P@S@V@@Y@[@``@0 @T@W@Y@@]@_@b@0
@@W@Z@@]@`@a@d@
*V@W@`X@X@X@@0G@N@R@@W@Z@`a@0?S@W@\@`@b@ g@0@Z@_@ b@ e@@g@ l@0@`@c@f@ i@k@`p@0@d@g@i@ m@o@r@0@@g@`j@@m@pp@q@t@
*V@W@`X@X@X@@0@W@^@b@ g@j@Pq@0?c@g@k@p@r@ w@0@j@o@r@u@0w@ @0@p@ps@u@ y@{@X@0@t@v@y@}@@@0@0w@Pz@0}@h@@@<<444444<444444<44444 T0!Yb"Yb#Y5$Y%Y&Y'Yb(Yb*b+Y,YY.Y^/Yb0Yb
1Y2Y4b5Y06Y7Yb8Y9Y:Yb;Y<Y?Jb
!
"*"V@W@`X@X@X@@0#l@r@w@@@@0$?Px@}@p@@@@@0%@@@@H@@@0&@@@@h@h@(@l@0'@@@@$@@ @0(@@p@<@@@@
*
+
,*,V@W@`X@X@X@@0@@@Ќ@@@0.?P@@l@@<@ܜ@0/@@@@H@@@00@@<@p@h@(@j@01@@@@"@@@02@@n@<@@@@
4
5
6*6V@W@`X@X@X@@07@h@Ҭ@@@@08?d@@ĵ@@@ @09@˴@@9@k@@@0:@@J@ @@q@@0;@:@@@@@@0<@@@@@@@
? ??LZ<<444444<444444<444444@ JT0B JbC JbE J5F JG JH JI JbJ JbL Jb
@
@W@?Z
B*B?@@@@
C/C?CD@%66B@C?CDCC?CDCC?CDCC?CDCC?JDC
E~
E$@5E>@L%
DB@DC@B5EH@L%
DB@DC@B5EO@L%
DB@DC@B5ES@L%
DB@DC@B5EW@L%
DB@DC@B5EZ@L%
DB@DC@B
F
~
F@5FN@E%DB@DC@B5FW@E%DB@DC@B5F_@E%DB@DC@B5Fc@E%DB@DC@B5Fg@E%DB@DC@B5F`j@E%DB@DC@B
G~
G@5G^@E%DB@DC@B5Gg@I%DB@DC@B5Go@G%DB@DC@B5Gps@G%DB@DC@B5Gv@G%DB@DC@B5GPz@G%DB@DC@B
H~
H?5Hr@G%#(DB@DC@B5H}@G%#(DB@DC@B5H@H%#(DB@DC@B5H@@H%#(DB@DC@B5H@H%#(DB@DC@B5Hp@H%#(DB@DC@B
I~
I?5I@H%2DB@DC@B5I@H%2DB@DC@B5I@I%2DB@DC@B5I<@I%2DB@DC@B5I@I%2DB@DC@B5In@I%2DB@DC@B
JJMbP?5Jh@F%7<DB@DC@B5J@F%7<DB@DC@B5J@F%7<DB@DC@B5JJ@F%7<DB@DC@B5J@F%7<DB@DC@B5J@F%7<DB@DC@B
L+L?CD?%EJB@+L>@I%EJDLB+LH@J%EJDLB+LO@J%EJDLB+LS@J%EJDLB+LW@J%EJDLB+LZ@J%EJDLB/<rrrrrv>F@YYY
Oh+'08@XpMark SchreinerMark SchreinerMicrosoft Excel@S*
՜.+,D՜.+,8 PX
#Washington University in St. Louisj
Front_end Back_endWorksheets 6>
_PID_GUIDAN{93E87B2430B811D5A542A8C915A2A561}
!"#$%&'()*+,./012345689:;<=>@ABCDEFRoot Entry FnWorkbookAlSummaryInformation(7DocumentSummaryInformation8?