Page 124 - JoFA_Jan_Apr23

Solver strata boundary search solution

listed in the By Changing Variable Cells input dialog box are completed (see the screenshot
area, specify a lower limit and an upper limit. “Solver Parameters Dialog Box for Strata Boundary
With two manipulated strata boundaries and two Search”), click the Solve button to begin the search
limits for each boundary, this requires a total of for strata boundaries.
four constraints. In our worksheet, since individual In our application, after about 10 seconds of
population items range in value from $1 to $8,000, searching, the Solver recommends a stratum 1
limits on strata boundaries are specified as (1) upper boundary of $1,526.52 and a stratum 2
$100 and $7,800 for the stratum 1 upper boundary upper boundary of $3,292.76 (see the screenshot
and (2) $200 and $7,900 for the stratum 2 upper “Solver Strata Boundary Search Solution”). A
boundary. These limits are a matter of judgment comparison of expected monetary precision for
but should provide a reasonably wide search field the equal-cumulative-square-root-of-frequency
for the Solver. If the search field is too narrow, method ($411,736.28 in the “Initial Placeholder
the Solver may be unable to locate an optimum Calculation” screenshot) and expected monetary
solution. Because all numeric values in a sampling precision for the Solver Search ($169,712.65 in
application must be non-negative, a check mark the “Solver Strata Boundary Search Solution”
should be entered in the Make Unconstrained screenshot) confirm the Solver’s recommended
Variables Non-Negative check box. strata boundaries yield better expected precision.
Finally, a solving method must be chosen from Given the initial total sample size of n = 60, neither
among the methods listed in the Select a Solving solution meets the auditor’s desired precision value
Method dropdown box. The methods available are of $150,000, but strata boundaries recommended
Simplex LP, GRG Nonlinear, and Evolutionary. by the Solver are much closer to the desired result
For the complex problem of selecting strata than those provided by the equal-cumulative-
boundaries, the Evolutionary method must be square-root-of-frequency method. As a final step
selected. Once entries in the Solver Parameters in this application, the Solver can be used to find

journalofaccountancy.com March 2023 | 33