Page 119 - JoFA_Jan_Apr23

TECHNOLOGY

The Solver is not LOADING THE EXCEL SOLVER TOOL
Although a basic version of the Solver is included
activated by Excel’s with Excel, it is not activated by Excel’s installation
procedures. It must be loaded as an Add-in via the
following steps:
installation procedures 1. Select the File tab in Excel to bring up a list of
menu choices in the left-most column. Click the
Options selection to call up the Excel Options
and must be loaded as 2. In the Excel Options dialog box, select Add-ins
dialog box.

in the left frame and verify that Excel Add-ins
an Add-in. shows in the Manage dropdown box at the
bottom of the Add-ins main area. Click the Go
button to call up the Add-ins dialog box.
3. In the Add-ins dialog box, check the Solver
Commercially available audit software such Add-in box and click the OK button.
as IDEA and ACL are limited to conventional Once the Solver is loaded, the next step is to
stratum boundary selection methods. Auditors create a placeholder calculation providing links
wanting to select strata boundaries via AI must between the objective formula cell to be optimized,
look elsewhere for this capability. Fortunately, the other relevant cells, and the listing of population
Microsoft Excel Solver tool has an AI capability items classified into sampling strata. The links
that can be used to solve complex problems that can between cells must be constructed so their contents
be used to determine stratum boundaries. can change spontaneously as the Solver’s search
process proceeds. These requirements are easily
SAMPLE PLANNING WITH EXCEL’S SOLVER TOOL accomplished using Excel’s built-in formula
In this article, we demonstrate a two-step process functions.
using the Excel Solver tool to (1) select strata
boundaries and (2) determine the sample size CREATING A PLACEHOLDER CALCULATION
needed to meet desired monetary precision and The screenshot “Initial Placeholder Calculation”
confidence values. A basic version of the Solver presents our initial placeholder calculation. As
is included with Excel and can be used to find a starting point, this worksheet employs strata
improved strata boundaries for mean-per-unit boundaries selected via the equal-cumulative-
sampling applications. When using the Solver, square-root-of-frequency method commonly used
the auditor specifies an objective formula cell in audit practice. Three strata are used to simplify
to optimize by changing the values of one or the presentation, but any number of strata can be
more other cells. Optionally, the Solver permits employed with the procedures described herein.
specification of constraints and limitations on The population used in this screenshot was
worksheet cell values. Once invoked, the Solver created for demonstration purposes as follows.
executes an intelligent search for an optimum First, a column vector of 2,000 cells was created
solution. The search continues until a solution with the cell contents numbered from 1 to 2,000.
meeting all constraints and limitations is found or These numbers served as population item reference
the auditor-specified search time limit is reached. numbers. On our worksheet, this column vector

IN BRIEF a 50% reduction in sample size while ■ This article takes readers through each
yielding the same confidence intervals required step in using Solver to select
■ A new research project to investigate as conventional methods. stratum boundaries, applying the steps
the efficiency of stratified mean-per-unit ■ Excel’s Solver tool has an AI capability onto a sample population.
sampling found that a method based on that can be used to find improved
artificial intelligence (AI) translates into strata boundaries.
To comment on this article or to suggest an idea for another article, contact Courtney Vien at Courtney.Vien@aicpa-cima.com.

28 | Journal of Accountancy March 2023