We have now made a simple and reliable way for users to create sample data for substantive testing of transactions.
In newer (e.g. LCE) templates, it is designed to replace the old-style sampling and testing tools, including our Excel-based tool. In the older templates, the new style is optional in order to maintain backward compatibility where work is already done.
It works like this:
- As before, on the lead schedules typically there is an option that asks whether transactions testing is required as part of the audit work:
- Selecting "Yes" opens a link to create a sample testing page - as before, these usually allow multiple versions 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:
- Then, after selecting the assertions being tested, describe the method used and why, and choose an appropriate sampling interval (this was split into a series of questions including selecting a confidence level in the old style):
- Next, (as before) select the tests to be applied to the sample. Select "Other Test" to add test items not included in the standard options:
- Then, prepare the "raw" data to be added. If extracting data from Xero follow the video below:
- The data must be in .csv, .xls. or .xlsx format. Include column headings. All data will be imported so remove any unneeded columns, remove totals, and any other information not to be input. If combining a number of ledger accounts say you may wish to add a column that contains the account code as below. If combining accounts sort the whole thing into date order:
- Select "Import Data" and link the data file for import. Specify the sampling interval. 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 (if data is uploaded and there is no match a dialog will appear asking which column you wish to use for the sample):
- 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.4) - 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 population included in the sample by value and by line items:
- Completed tests are shown. Documentation may be attached or additional comments added:
- Once all tests are completed assess the results (as before):
- There is an option above (1.4) called "Manual Entry" - this opens the "old" style tables where items to be tested are entered manually line by line, or imported from an Excel or CSV file. The tests selected are still used. The difference is that the table columns are pre-defined, and the import must follow the selected columns.
- Under the "Import Type" selection (see 1.4 above) there is another option to select "All Rows". This is for 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).
- We are planning to add other types of sampling in due course so that there will be other options available, for example, selecting all items over a certain amount (materiality say).
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 a high value.
- If higher value items are selected, filter the population before sampling to exclude say all items below trivial level, or below a factor of performance materiality say.
- Make sure that if items are excluded from the sample set that this is documented and that the reason for this is made clear.
- Supported file types are: XLS, XLSX and CSV