Efficiëntere steekproeven voor audits met de Solver-tool van Excel

Kunstmatige intelligentie kan de tijd en moeite die auditors besteden aan het schatten van steekproeven aanzienlijk verminderen.

Gestratificeerde steekproeven met gemiddelde per eenheid zijn een belangrijk instrument voor controleurs. De populariteit van deze statistische procedure vloeit voort uit het unieke vermogen om betrouwbare betrouwbaarheidsintervallen te produceren wanneer populaties met lage foutenpercentages worden onderzocht. Andere steekproeftechnieken zoals verschil-, verhoudings- en regressieschattingen leveren onbetrouwbare betrouwbaarheidsintervallen op, tenzij veel fouten in de steekproef worden opgespoord. Gezien de lage foutenpercentages die de meeste boekhoudkundige populaties vertonen, is gestratificeerde gemiddelde-per-stuk steekproeftrekking de aangewezen techniek bij het schatten van monetaire waarden.

Ons onderzoek heeft een nieuwe methode aan het licht gebracht om de stratagrenzen te selecteren die de tijd en moeite die accountants besteden aan steekproefschattingen aanzienlijk kan verminderen. Uit onze bevindingen blijkt dat de methode voor het selecteren van de stratagrenzen de belangrijkste beslissing is die van invloed is op de efficiëntie van de steekproef. Bij tests waarbij conventionele methoden voor de selectie van stratagrenzen werden vergeleken met een nieuwe methode op basis van kunstmatige intelligentie (AI), bleek dat de selectie van stratagrenzen met behulp van AI een aanzienlijke verbetering van de steekproefefficiëntie opleverde. Hoewel de resultaten varieerden met de onderzochte populatie, leverde de selectie van strata via de nieuwe methode gemiddeld een vermindering op van 40% van de breedte van de betrouwbaarheidsintervallen bij een gegeven steekproefomvang. Dit komt neer op een vermindering van de steekproefomvang met meer dan 50% bij gelijke betrouwbaarheidsintervallen als bij conventionele methoden voor de selectie van strata.

Commercieel verkrijgbare auditsoftware zoals IDEA en ACL zijn beperkt tot conventionele selectiemethoden voor stratumgrenzen. Controleurs die de stratagrenzen via AI willen selecteren, moeten deze mogelijkheid elders zoeken. Gelukkig beschikt de Microsoft Excel Solver-tool over een AI-mogelijkheid waarmee complexe problemen kunnen worden opgelost die kunnen worden gebruikt om stratumgrenzen te bepalen.

Voorbeeld planning met Excel's Solver Tool

In dit artikel demonstreren wij een proces in twee stappen met behulp van de Excel Solver tool om (1) stratagegrenzen te selecteren en (2) de steekproefgrootte te bepalen die nodig is om aan de gewenste monetaire precisie- en betrouwbaarheidswaarden te voldoen. Een basisversie van de Solver wordt meegeleverd met Excel en kan worden gebruikt om verbeterde stratagrenzen te vinden voor steekproeftoepassingen met gemiddelde per eenheid. Bij gebruik van de oplosser specificeert de accountant een objectieve formulecel die moet worden geoptimaliseerd door de waarden van een of meer andere cellen te wijzigen. Optioneel kan de oplosser beperkingen en restricties op werkbladcelwaarden specificeren. Zodra de Solver is aangeroepen, voert hij een intelligente zoektocht uit naar een optimale oplossing. Het zoeken gaat door totdat een oplossing is gevonden die aan alle beperkingen en restricties voldoet of totdat de door de accountant opgegeven zoektijdlimiet is bereikt.

Het laden van de Excel Solver Tool

Hoewel een basisversie van de Solver wordt meegeleverd met Excel, wordt deze niet geactiveerd door de installatieprocedures van Excel. Het moet worden geladen als een Add-in via de volgende stappen:

1. Selecteer het tabblad Bestand in Excel om een lijst met menukeuzes in de meest linkse kolom op te roepen. Klik op de keuze Opties om het dialoogvenster Excel-opties op te roepen.
2. Selecteer in het dialoogvenster Excel-opties Add-ins in het linkerkader en controleer of Excel Add-ins wordt weergegeven in het vervolgkeuzemenu Beheer onderaan het hoofdgebied Add-ins. Klik op de knop Go om het dialoogvenster Add-ins op te roepen.
3. In het dialoogvenster Add-ins vinkt u het vakje Solver Add-in aan en klikt u op de knop OK.

Zodra de Solver is geladen, is de volgende stap het maken van een plaatsvervangende berekening die links geeft tussen de te optimaliseren cel van de objectieve formule, andere relevante cellen en de lijst van populatie-items ingedeeld in steekproefstrata. De links tussen de cellen moeten zo worden gemaakt dat hun inhoud spontaan kan veranderen naarmate het zoekproces van de Solver vordert. Aan deze eisen kan gemakkelijk worden voldaan met behulp van de ingebouwde formulefuncties van Excel.

Een plaatsvervangende berekening maken

Het screenshot "Initial Placeholder Calculation" toont onze eerste placeholderberekening. Als uitgangspunt maakt dit werkblad gebruik van stratagrenzen die zijn geselecteerd via de in de auditpraktijk gebruikelijke methode van de gelijke kwadratische wortel van de frequentie. Er worden drie strata gebruikt om de presentatie te vereenvoudigen, maar met de hier beschreven procedures kan elk aantal strata worden gebruikt.

De in deze schermafbeelding gebruikte populatie werd voor demonstratiedoeleinden als volgt samengesteld. Eerst werd een kolomvector van 2000 cellen gemaakt met de celinhoud genummerd van 1 tot 2000. Deze nummers dienden als referentienummers voor de populatie. Op ons werkblad begint deze kolomvector met cel B27 en eindigt met cel B2026.

Ten tweede werd voor elk bevolkingsitem een geregistreerde waarde gecreëerd door vermenigvuldiging van het referentienummer van het item en een vaste vermenigvuldigingsfactor. De vaste vermenigvuldigingsfactoren waren: $1,00 voor items #1 tot #1.000; $2,00 voor items #1001 tot #1.600; en $4,00 voor items #1.601 tot #2.000. Het resultaat was een populatie van 2.000 posten met een totale geregistreerde waarde van $4.941.900 en individuele geregistreerde waarden variërend van $1 tot $8.000. Op ons werkblad begint de lijst van geregistreerde waarden met cel C27 en eindigt met cel C2026. De belangrijkste kenmerken van de populatie zijn (1) omvang = 2.000 stuks; (2) totale waarde = $4.941.900; (3) standaardafwijking = $2.558; en (4) individuele waarden variëren van $1 tot $8.000.

Het bovenste deel van het werkblad (rijen 4-6) specificeert de gewenste monetaire nauwkeurigheid ($150.000), het gewenste betrouwbaarheidsniveau (95%) en een initiële waarde voor de geplande totale steekproefomvang (60). De oplosser heeft deze door de accountant opgegeven waarden nodig om de verwachte monetaire precisie (cel H19) te berekenen terwijl het zoeken naar optimale stratagrenzen vordert. De beginwaarde voor de geplande totale steekproefomvang is niet kritisch omdat de optimale stratagrenzen niet erg gevoelig zijn voor deze waarde. De waarde moet echter ten minste gelijk zijn aan het aantal steekproefstrata vermenigvuldigd met de door de accountant opgegeven minimale stratumsteekproefomvang. Wij gaan uit van een minimale stratumsteekproefomvang van n = 20, en stellen derhalve de aanvankelijke totale steekproefomvang vast op n = 60 (20 items per stratum × 3 strata). Nadat in stap één de passende stratagrenzen zijn bepaald, wordt de waarde van de gewenste monetaire precisie door de oplosser in stap twee gebruikt bij het zoeken naar een optimale totale steekproefomvang.

In het centrale gedeelte van het werkblad (rijen 12-21) worden de stratagrenzen gespecificeerd, wordt het resulterende aantal strata en de standaardafwijkingen van de strata gerapporteerd, worden de stratamonsters gespecificeerd op basis van een optimale toewijzing van de totale steekproef, worden de bijdragen van de strata aan de schattingsvariantie berekend en wordt de verwachte monetaire precisie berekend. Bovendien wordt de afwijking tussen de gewenste precisie en de verwachte precisie (cel H21) berekend. Om ervoor te zorgen dat de lezer dit werkblad kan reproduceren, bevat de onderstaande tabel "Celformules en -namen" een lijst van alle celformules en bijbehorende namen die aan de cellen zijn toegekend.

In het onderste gedeelte van ons werkblad (rijen 27-2026) staan de referentienummers van de populatie-items (kolom B), de geregistreerde waarden van de items (kolom C) en de cumulatieve populatiewaarden (kolom D), en worden de populatie-items ingedeeld in een van de drie strata-kolomvectoren (kolom E, F of G), op basis van de stratagrenswaarden die zijn gespecificeerd in het werkbladbereik C12:D14. Merk op dat de stratumlijsten allemaal kolomvectoren van dezelfde lengte zijn (2.000 cellen).

In elke stratumkolomvector bevatten sommige cellen numerieke waarden (voor stratumleden), en andere cellen tekstwaarden (voor stratum niet-leden). Deze indeling zorgt ervoor dat alleen geldige stratumleden worden meegenomen wanneer Excel het aantal stratumonderdelen en de standaardafwijking van hun geregistreerde waarden berekent. Merk ook op dat veel werkbladrijen verborgen zijn in het onderste deel van de schermafbeelding "Eerste berekening van de plaatshouder" vanwege de beperkte ruimte.

Aangezien de beslissingen over het stratumlidmaatschap spontaan moeten worden herzien terwijl de Solver zoekt, worden geneste IF-statements toegepast op de kolomvector met populatie-itemwaarden (kolom C). Deze geneste IF-statements vergelijken de geregistreerde itemwaarden (werkbladbereik C27:C2026) met de onderste en bovenste stratagrenswaarden (werkbladbereik C12:D14). Wanneer een IF-instructie een populatie-item identificeert als een stratumlid, plaatst de IF-instructie de geregistreerde waarde van het item in de lijst met waarden voor dat stratum, anders wordt de tekst "Uitgesloten" in de lijst voor dat stratum geplaatst. Voorbeelden van de IF-statements die worden gebruikt om het stratum te bepalen, worden gegeven in de onderstaande tabel "Nested IF Statements Used to Identify Strata Members".

De Solver Tool activeren en informatie invoeren

Om de Solver activeren, selecteert u het tabblad Gegevens en vervolgens de optie Solver (uiterst rechts). Wanneer het dialoogvenster Solverparameters verschijnt, moet de Solver worden verteld het verschil tussen de gewenste en de verwachte precisie te minimaliseren. Hiertoe voert u de celreferentie van de objectieve formule (H21) of de celnaam (Precision_Difference) in het invoergebied Set Objective in en selecteert u het keuzerondje Min (minimaliseren). Vertel de oplosser vervolgens dat hij de bovengrenzen van stratum 1 en stratum 2 moet variëren om de celwaarde van de objectieve formule (H21 of de naam Precision_Difference) te veranderen. Dit gebeurt door de celverwijzingen voor de bovengrenzen (D12 en D13) of de bijbehorende celnamen (Stratum_1_Upper_Boundary en Stratum_2_Upper_Boundary) in te voeren in het invoergebied By Changing Variable Cells.

Om efficiënt te kunnen zoeken, moet de oplosser onder- en bovengrenzen opgeven voor elke variabele die in het invoergebied By Changing Variable Cells is gespecificeerd. Om deze grenzen in te voeren, klikt u op de knop Toevoegen in het invoergebied Onderworpen aan de beperkingen om het dialoogvenster Beperking toevoegen op te roepen. Specificeer vervolgens voor elke variabele in het invoergebied Door variabele cellen te wijzigen een ondergrens en een bovengrens. Met twee gemanipuleerde stratagrenzen en twee grenzen voor elke grens zijn in totaal vier beperkingen nodig. Aangezien in ons werkblad de waarde van de afzonderlijke populatiebestanddelen varieert van $1 tot $8.000, worden de grenzen van de strata gespecificeerd als (1) $100 en $7.800 voor de bovengrens van stratum 1 en (2) $200 en $7.900 voor de bovengrens van stratum 2. Deze grenzen zijn een kwestie van inschatting, maar ze kunnen niet worden aangepast. Deze grenzen zijn een kwestie van beoordeling, maar zouden een redelijk breed zoekveld voor de oplosser moeten opleveren. Als het zoekveld te smal is, kan de oplosser mogelijk geen optimale oplossing vinden. Omdat alle numerieke waarden in een bemonsteringstoepassing niet-negatief moeten zijn, moet een vinkje worden gezet in het vakje Maak onbeperkte variabelen niet-negatief.

Ten slotte moet een oplosmethode worden gekozen uit de methoden die zijn opgesomd in het keuzemenu Selecteer een oplosmethode. De beschikbare methoden zijn Simplex LP, GRG Nonlineair en Evolutionair. Voor het complexe probleem van de selectie van stratagegrenzen moet de Evolutionaire methode worden gekozen. Zodra de invoer in het dialoogvenster Solver Parameters is voltooid (zie de schermafbeelding "Dialoogvenster Solver Parameters voor het zoeken naar stratagegrenzen" hieronder), klikt u op de knop Solve om het zoeken naar stratagegrenzen te beginnen.

In onze toepassing adviseert de Solver na ongeveer 10 seconden zoeken een stratum 1-bovengrens van $1.526,52 en een stratum 2-bovengrens van $3.292,76 (zie de schermafbeelding "Solver Strata Boundary Search Solution" hieronder).

Een vergelijking van de verwachte monetaire precisie voor de methode met gelijke cumulatieve wortel van de frequentie ($411.736,28 in het screenshot "Initial Placeholder Calculation") en de verwachte monetaire precisie voor de Solver Search ($169.712,65 in het screenshot "Solver Strata Boundary Search Solution") bevestigt dat de door de Solver aanbevolen stratagegrenzen een betere verwachte precisie opleveren. Gezien de oorspronkelijke totale steekproefomvang van n = 60, voldoet geen van beide oplossingen aan de door de accountant gewenste nauwkeurigheidswaarde van $ 150.000, maar de door de Solver aanbevolen stratagegrenzen liggen veel dichter bij het gewenste resultaat dan die van de methode van de gelijke-cumulatieve wortel van de frequentie. Als laatste stap in deze toepassing kan de Solver worden gebruikt om de totale steekproefomvang te vinden die nodig is om te voldoen aan de door de accountant gewenste monetaire nauwkeurigheidswaarde.

Herziening van de Solverinputs om de totale steekproefgrootte te zoeken

Zodra de stratagrenzen zijn bepaald, moet de in het dialoogvenster Solver-parameters ingevoerde informatie worden herzien ter ondersteuning van een zoektocht naar de totale steekproefomvang die nodig is om te voldoen aan de door de accountant gewenste nauwkeurigheidswaarde. Om de Solver op te roepen, selecteert u het tabblad Gegevens en de optie Solver (uiterst rechts). Wanneer het dialoogvenster Solverparameters verschijnt, laat u de waarde die in het invoergebied Set Objective (H21 of de naam Precision_ Difference) is opgegeven, ongewijzigd. Behoud ook de: (1) het keuzerondje Min; (2) het vinkje in het vakje Maak niet-beperkte variabelen niet-negatief; en (3) de Evolutionaire oplossingsmethode in het keuzemenu Selecteer een oplossingsmethode.

De oplosser moet echter worden verteld om de steekproefgrootte van de toepassing te variëren om het verschil tussen de gewenste en de verwachte monetaire precisie te wijzigen (cel H21 of de naam Precision_Difference). Dit wordt bereikt door de verwijzingen naar de stratagrenzen in het invoergebied By Changing Variable Cells te vervangen door de celverwijzing (E6) of de celnaam (Planned_Total_Sample_Size) voor de totale steekproefomvang van de toepassing. Tegelijkertijd moeten de onder- en bovengrenzen van de stratagrenzen worden verwijderd uit het invoergebied Subject to the Constraints door elke beperking te selecteren en de knop Delete te gebruiken.

Voor een efficiënt zoekproces moet de controleur boven- en ondergrenzen voor de totale steekproefomvang opgeven. Klik hiervoor op de knop Toevoegen om het dialoogvenster Beperkingen toevoegen te openen. Voeg vervolgens beperkingen toe voor een bovengrens en een ondergrens. In ons werkblad hebben we een bovengrens voor de steekproefgrootte van n = 1000 en een ondergrens van n = 60 opgegeven. Deze grenzen zijn een kwestie van beoordeling, maar moeten worden gespecificeerd zodat de oplosser een redelijk breed zoekveld heeft. Ten slotte moeten er nog twee beperkingen worden toegevoegd: (1) de variabele totale steekproefomvang (cel E6 of Planned_Total_Sample_Size) moet een geheel getal zijn, en (2) de oplosser moet worden opgedragen ervoor te zorgen dat de verwachte monetaire precisie (cel H19 of de naam Expected_Monetary_Precision) kleiner is dan of gelijk aan de gewenste monetaire precisie (cel E5 of de naam Desired_Monetary_Precision).

Zodra de gegevens in het dialoogvenster Solverparameters zijn herzien (zie de schermafbeelding "Dialoogvenster Solverparameters voor zoeken naar steekproefgrootte" hieronder), klikt u op de knop Oplossen om het zoeken naar de totale steekproefgrootte te beginnen.

In onze toepassing adviseert de Solver na ongeveer 90 seconden zoeken een totale steekproefomvang van n = 77, als volgt verdeeld: n = 33 voor stratum 1; n = 23 voor stratum 2; en n = 21 voor stratum 3 (zie het screenshot "Solver Sample Size Search Solution," hieronder). Merk op dat deze strata-steekproefgrootten voldoen aan onze eis van 20 steekproefpunten per stratum. Als niet aan de vereiste minimale steekproefgrootte per stratum wordt voldaan, kan het invoergebied Subject to the Constraints worden gewijzigd om een minimale steekproefgrootte in elk stratum te specificeren.

Het screenshot "Solver Sample Size Search Solution" geeft aan dat de door de Solver aanbevolen totale steekproefomvang naar verwachting een monetaire precisie van 149.114,83 dollar oplevert en dus voldoet aan de gewenste monetaire precisie van 150.000 dollar. Ter vergelijking, een totale steekproefomvang van n = 345 zou nodig zijn om de gewenste nauwkeurigheidsdoelstelling te halen als de methode van de gelijke-cumulatieve-kwadraat-wortel-van-frequentie zou worden gebruikt. In deze hypothetische toepassing verminderde het gebruik van de door de oplosser aanbevolen stratagrenzen de vereiste totale steekproefomvang dus met meer dan 75%. Voor veel populaties in de echte wereld verwachten wij dat de stratagrenzen die met behulp van AI zijn geselecteerd, de steekproefomvang met 50% of meer zullen verminderen. Gezien dit feit zouden alle auditors zich bewust moeten zijn van de mogelijkheden van de Solver. (Zie de zijbalk "Solver-opties" aan het eind van dit artikel, over de instellingen van de tool).

Gebruik maken van de Solver met niet-statische steekproeven

De in dit artikel gebruikte statistische formules zijn alleen van toepassing wanneer strata steekproeven via aselecte selectie worden genomen. De noodzaak om de controle-inspanning efficiënt toe te wijzen bestaat echter ongeacht of de accountant steekproeven willekeurig of via niet-statistische methoden selecteert. In omstandigheden waarin de accountant van plan is een populatie te stratificeren en steekproefitems te selecteren via niet-statistische procedures, zal het gebruik van de Excel Solver voor het selecteren van stratagegrenzen de efficiëntie van de steekproefcontrole waarschijnlijk verbeteren.