Page 121 - JoFA_Jan_Apr23

TECHNOLOGY

Cell formulas and names monetary precision (cell H19) as the search for
optimum strata boundaries proceeds. The initial
Cell Cell formula (if any) Cell name (if any) value for planned total sample size is not critical
E4 (Cell is an input value, not a Desired_Monetary_Precision because optimum strata boundaries are not very
formula) sensitive to this value. However, at a minimum,
E5 (Cell is an input value, not a Desired_Confidence_Level the value should equal the number of sampling
formula) strata multiplied by the auditor-specified minimum
E6 (Cell is an input value, not a Planned_Total_Sample_Size stratum sample size. We presume a minimum
formula) stratum sample size of n = 20, and hence set the
C12 (Cell is an input value, not a Stratum_1_Lower_Boundary initial total sample size at n = 60 (20 items per
formula) stratum × 3 strata). After determining appropriate
C13 =D12+0.01 Stratum_2_Lower_Boundary strata boundaries in step one, the value of desired
monetary precision is used by the Solver in step two
C14 =D13+0.011 Stratum_3_Lower_Boundary
as the search for an optimum total sample size is
D12 (Cell is an input value, not a Stratum_1_Upper_Boundary conducted.
formula) The central portion of the worksheet (rows
D13 (Cell is an input value, not a Stratum_2_Upper_Boundary 12–21) specifies strata boundaries, reports the
formula) resulting number of strata items and strata standard
D14 (Cell is an input value, not a Stratum_3_Upper_Boundary deviations, specifies strata sample sizes based on
formula) optimal allocation of the total sample, computes
E12 =COUNTIF(Stratum_1_ Stratum_1_Population_Size strata contributions to the estimator variance,
Listing,">0") and projects expected monetary precision. In
E13 =COUNTIF(Stratum_2_ Stratum_2_Population_Size addition, the deviation between desired precision
Listing,">0") and expected precision (cell H21) is computed. To
E14 =COUNTIF(Stratum_3_ Stratum_3_Population_Size ensure the reader can reproduce this worksheet, the
Listing,">0") table “Cell Formulas and Names” provides a listing
E15 =E12+E13+E14 - of all cell formulas and related names assigned to
cells.
F12 =STDEV.P(Stratum_1_Listing) -
The lower portion of our worksheet (rows
F13 =STDEV.P(Stratum_2_Listing) - 27–2026) lists population item reference numbers
F14 =STDEV.P(Stratum_3_Listing) - (column B), item recorded values (column C), and
G12 =E5-G13-G14 Stratum_1_Sample_Size cumulative population values (column D), and
classifies population items into one of three strata
G13 =ROUND(E5*((E13*F13)/((E12*F Stratum_2_Sample_Size
12)+(E13*F13)+(E14*F14))),0) column vectors (column E, F, or G), based on the
strata boundary values specified in the worksheet
G14 =ROUND(E5*((E14*F14)/((E12*F Stratum_3_Sample_Size
12)+(E13*F13)+(E14*F14))),0) range C12:D14. Note that stratum listings are all
column vectors of the same length (2,000 cells).
G15 =G12+G13+G14
In each stratum column vector, some cells
H12 =(F12*F12/G12)* - contain numeric values (for stratum members),
((E12-G12)*E12) and other cells contain text values (for stratum
H13 =(F13*F13/G13)* - nonmembers). This arrangement ensures that only
((E13-G13)*E13) valid stratum members are included when Excel
H14 =(F14*F14/G14)* - computes the number of stratum items and the
((E14-G14)*E14) standard deviation of their recorded values. Also,
H15 =H12+H13+H14 - note that many worksheet rows are hidden in the
H17 =SQRT(H15) - lower portion of the screenshot “Initial Placeholder
Calculation” due to space limitations.
H18 =ABS(NORMINV((1-E6)/2,0,1)) -
Given the need to revise stratum membership
H19 =H17*H18 Expected_Monetary_ decisions spontaneously as the Solver’s search
Precision
proceeds, nested IF statements are applied to the
H21 =ABS(Desired_Monetary_ Precision_Difference column vector containing population item values
Precision-Expected_Monetary_ (column C). These nested IF statements compare
Precision)
item recorded values (worksheet range C27:C2026)

30 | Journal of Accountancy March 2023