Sample selection and testing tool

Our sampling tool is a simple, reliable, and statistically robust way for users to create sample data for testing of transactions (either for substantive or controls testing or both). 

It works like this:

• On the lead schedules typically there is an option that asks whether transaction testing is required as part of the audit work:

• Selecting "Yes" opens a link to create a sample testing page. These usually allow multiple versions of the testing to be created (for more than one type of payment say):
• The default name of the page may be edited from the default to identify what kind of transactions are to be included:
• Select "Add" and then navigate to the new page (H2 in this case), and select the balances to be included in the testing.
• This can be individual balances, or a total generated in the TB as below:

• Select "sampling tool" from the options (the other options relate to our superseded sampling methods and have been retained in some jobs for backwards compatibility) 

• Then select the assertions being tested.
• Additional assertions may be added from the dropdown or deleted from the icon next to each assertion.

• Describe the method used and why:

• Next, select the tests to be applied to the sample.
• Many pages provide a selection of common tests but these may be removed or additional tests added from the selector.
• Additional tests may be added from the dropdown or manually added using "Other test."

• Select "Import Data" or alternatively if there are only a few items and you are using judgement you may wish to enter manually:

• • If using manual entry this bypasses the CMS tool but still makes a table with tests added. 
  • Table columns may be added or edited from the edit icon at the left.

Preparing the data for import:

• Make sure that if items are excluded from the sample set this is documented and that the reason for this is made clear (for example, rent transactions are excluded as they have been proved in total).
• Leave the column headings as these will carry into the table in AA (line 5 below) - the titles (lines 1-3 below) may be left as these will be skipped over:

• Supported file types are: XLS, XLSX and CSV
• If extracting data from Xero follow the video below noting:
  
  • Go to the account transactions screen.
  • Change Grouping/Summarising to 'None' (this puts all the selected accounts into one table with account description in a seperate column).
  • Select the correct date range.
  • Select the accounts you want to include (in this case we use 'Select all' then filter just down to expenses (assuming we are doing an expenses sample), then remove the accounts you have already proved in total or plan to test another way.
  • Change the columns to only include the ones you want imported into the testing table.
  • Update then export to Excel.

• The data must be in .csv, .xls. or .xlsx format. Include column headings.
• If not extracted from Xero using the method above, make sure that any sub-headings or totals are removed, and the table is sorted into data order.
• Save the file where you will be able to find it easily for import.

NOTE: the tool will not work if there is a mix of debits and credits as it breaks the cumulative calculation, so remove credits and test separately, or where they represent journals remove both the debit and corresponding credit to clean up the data.

Importing the data:

• On selecting "Import Data" the following will appear:

• Set the import type as Cumulative Sample (for other options see below).
• Select "Import Data" and link the data file for import.
• Specify the sampling interval:
  • This would normally be the performance materiality, however, this interval may be increased if reliance is placed on internal controls testing already done or analytical review.
  • So for instance if gross profit percentages are consistent and the entity is stable we may opt to reduce the amount of testing of sales by making the sampling interval PM x 2 say (1/2 of the number of transactions in the sample).
  • Alternatively, if we identify high risk in a particular population, we may opt to reduce the sampling interval to for instance half of PM so we will pick twice as many items. 
• If there is a column called "amount" this will be used for the cumulative sampling.
  • If not change the name of the "Sample Column" to correspond with the appropriate column - 'debit' for example
  • If data is uploaded and there is no match a dialog will appear asking which column you wish to use for the sample.
  • Negative values in the sample column will throw off the cumulative sample. If you have negative values, it might be worth using a different sample method or double checking the data

• Click "Add" to create the sample and the testing table. This creates the sample based on a random "seed" - a starting value between zero and the sampling interval:

• Follow the link to the testing table (H2-1.1) - the original sample is attached automatically, the result of the sampling is summarised and the tests are added to each item ready to complete. Statistics are added showing the proportion of the population included in the sample by value and by line items:

• Completed tests are shown.
• Sample items may be completed by selecting "pass all tests" or if "pending tests" is selected tests are answered one by one.
• A failed test is marked as a deviation and these are tallied on the parent page for further work.

• Documentation may be attached, additional comments added and cross referenced, plus journal entries, management points, disclosure adjustments, requests, follow ups, and review notes.

Assessment and Extrapolation of Errors:

• Once all tests are completed assess the results:
  • If deviations have been noted then respond with "yes" (or if they are trivial there is another option)
  • Insert the total tested, and the total errors found.
  • If the extrapolated errors are potentially material additional work should be carried out.
  • This is of course dependant on the nature of the errors and the auditors judgement.

Import all rows option:

Under the "Import Type" selection there is another option to select "All Rows".

• This is used where the whole data set is to be loaded (where all items are to be included or where sampling has already been done using another method).
• This will also use the column headings from the Excel import.

Random sample option:

Under "import type" select Random Sample from the dropdown:

• Upload the data file, and select how many samples you would like - say 20 in this case, then click the Add button:

• Clicking the link to the page displays the samples and the data criteria:
• This gives equal priority to each row, so low-value items have an equal chance of being selected as high-value.

NOTE: if the sampling tool looks different to the above, you may still be using the old tool inherited from older templates. In this case, go back to the top of the page where it asks 'State sample size and selection method and rationale for this decision', delete the response and instead choose 'sampling tool' from the dropdown.