Audit sample methods 2507



  • Hi,
    does anyone know how to document random sample using MS Excel? According to audit best practices on documentation, we should save the random number generated for the sample in order to permit others to remake the sample. My knowledge of MS Excel says that Bill Gates forgot this statement and because of that auditors can’t use data analysis from Excel.
    About systematic sample… for use this method for control audit, we should use a random number for choosing the first item of the sample, in order to use statistical sample (otherwise we do not give all items the same chance of being selected). How can I use this method??
    Thanks for your support.



  • Hi,
    SOX and Statistical Sampling Documentation
    Good question…the SOx Act does not specify documentation requirements to provide a basis for statistical sampling procedures that are used to select audit samples for testing controls effectiveness.
    Documentation Requirements - Statistical Sampling
    When using Excel to identify a statistical sample for testing purposes and applying the random number generator worksheet function in Excel, it is helpful to save a printed copy of the sample items that are ‘selected’ by the system (Excel) for several reasons:

    1. The printed copy serves as a basis to document the number of and unique audit samples that were selected at a point in time;
    2. The documentation that is created from the printed Excel report can be used by the auditor as a basis to reperform audit procedures on the same sample items that were used by the Company to perform the controls assessment.
      Reperformance is one of several standard audit procedures that may be used by the auditor and when the results are consistent with the conclusion reached by Management, provide a strong indication that the work performed by management or others is competent, accurate, and reliable.
      Paperless Environment
      If you operate in a paperless environment, you can select Print to file after selecting File, then Print…from within Excel. The Print to file selection box appears on the Print Dialog box in Excel and it is my understanding that it creates a file that can be printed at any time with the same output (i.e., the samples selected will not change).
      Persons reviewing this post can correct the above if incorrect or you can confirm by researching print to file online in any search engine.
      Excel and Random Number Sampling
      Random Function - Excel
      You can use Excel to help you select an audit sample. Try the following formula:
      =ROUND(RAND()*100,0)
      It will return a value from 0 to 99 and each time you press F9, Excel will return with a different random number(s) in each cell containing this formula.
      Hope this helps,
      Milan


  • Hi Milan,
    Thanks for your reply… persons like you make this forum works. Sorry for my English.
    I’m working with AICPA methodology for audit sampling. As a ex big four employee, I need to find out some methodology for supporting my work now, as an internal auditor.
    Softwares like WinIDEA and ACL allow you to print the random number used and reperform the sampling any time you want because they permit you to choose the random number to use.
    My independent auditor is not allowing me to use Excel because they can not reperform the sampling procedures using original file and reaching the same sample (they want to reperform our sampling, not only use the same sample). Excel do not let you pick up an random number that you used before. So, in order to use random sampling I need to use IDEA or ACL, only.
    Thanks one more time for your attention.



  • Hi,
    Thank you for the nice feedback and good words.
    I am sorry to hear that the auditor is not flexible with regard to the tool that is used to perform random number statistical sampling procedures.
    It seems unreasonable given that any tool regardless of manufacturer, ACL, IDEA, SAS, etc., uses seeded numbers anyway. From a theoretical standpoint, it is not possible to produce a pure random number whatever tool that you choose. However, this discussion is well beyond the scope of this forum and would incite a pointless and endless debate.
    Sometimes, it is helpful if you develop/document your sampling methodology and present it to the auditor so that the auditor fully understands the process that is used to select the audit sample. This understanding might achieve auditor ‘buy-in’ since they opine on the SOX audit and the reliability of the work that is performed by others.
    Good luck,
    Milan



  • Hi Milan,
    Thanks for your reply… persons like you make this forum works. Sorry for my English.
    I’m working with AICPA methodology for audit sampling. As a ex big four employee, I need to find out some methodology for supporting my work now, as an internal auditor.
    Softwares like WinIDEA and ACL allow you to print the random number used and reperform the sampling any time you want because they permit you to choose the random number to use.
    My independent auditor is not allowing me to use Excel because they can not reperform the sampling procedures using original file and reaching the same sample (they want to reperform our sampling, not only use the same sample). Excel do not let you pick up an random number that you used before. So, in order to use random sampling I need to use IDEA or ACL, only.
    Thanks one more time for your attention.
    I just don’t get this :?
    If you have generated a random sample then no one should be able to generate the same sample anyway - otherwise it wasn’t random :roll:
    I have never come across an audit requirement to be able to regenerate the sample, what you often do see is a requirement to reperform the test. However, to do this I would expect you to document what the results of your sample were i.e. which invoice numbers were tested: This allows anyone else to then go and test the same items and validate you results.



  • Oh, and one more thing. The auditor is not in a position to allow or not allow this technique. It is for management to determine how it arrives at management’s assesment.
    Perhaps you need to tell your auditor to read AS5 to refresh their understanding of their (and management’s) accountabilities :evil:



  • My experience has been that external auditors from public accounting firms often tend to be extremely cautious and seem to stick to their internal firm audit programs and checklists without questioning their substance and basis in the auditing standards.
    I can understand the general notion that they want to try to understand what sampling technique you used, that you have the technical knowlege for random sampling and that you used a software that is known to be able to generate random numbers. I can also understand that they want you do document the sample that you picked so that they can reperform your testing of the entire sample or elements from the sample. However it should suffice that you tell them that you did it with Excel using a particular function and maybe show them the Excel sheet. However, any auditor should know that a random sample that uses the computer time as an input factor for the first random number cannot be redone because the time has changed in the meantime.
    In conclusion, I do not see any logical basis for their claim and you should challenge them and have them prove why they see a problem.



  • I really don’t know if I tend to agree with my public account firm because of my past as an external auditor or if Excel is a very flexible tool for sampling, witch allows any change you want without security. How can I prove that I haven’t changed the sample after random number has been generated, inducting my test?
    Even does software that use the computer time for generates the random number, if I reperform the sample using that random number I would be able to pick up the same sample (who uses Idea knows what I am saying). That’s the main use of audit documentation.
    I do think that all these questions are based on the principle that they don’t trust in our job’ but that’s their job. And, in order to achieve the certification, I believe we should walk together with external auditor, even using the same methodology. That’s the easiest way to make then use more of our work and do less test independently.



  • As stated by Denis in his post, management has ultimate authority to determine the sampling methodology used and to conclude on the operating effectiveness of the controls.
    However, the auditor reserves the right to place reliance on the conclusions from management when rendering the opinion on the ICFR (Internal Controls over Financial Reporting).
    Given this delicate balance between these audit objectives, I agree that it is preferable to work with the auditor so that they understand your sampling approach and can make an informed decision about reliance upon your work.
    In my opinion, taking a more confrontational approach, regardless of the appropriateness of your position, might agitate the auditor and lead to less reliance and/or further audit work.
    As for the issue of ‘trust’, this is commonly referred to as the duty to consider conclusions proposed by the client with ‘professional skepticism’. The auditor has the right and duty to use reasonable judgment, collect evidence required, and appropriately question or srutinize the use of non-standard auditing procedures, incorrect data and/or conclusions.
    Random sampling is an accepted sampling method and widely used by many companies. For this reason, I think that your challenge is simply getting the auditor comfortable with your sampling approach and if necessary, performing a walkthrough so that the auditor can make a decision with regard to reliance, objectivity, and competency of your work.



  • Distrust because the randomly selected sample items in an Excel sheet can be changed by the internal auditor (or other tester) after the testing, is way over the top. Professional scepticism does not mean to assume that all the testing has been forged. Even if you print out the Excel sheet and include the current date on the printout, anybody can fake such a printout and make it later with a manual date or by changing the Windows system time. I find this is a totally unacceptable level of mistrust by the external auditors. They should not be able to dictate you what software you use or to incur extra software licence costs for extra software. Try aruing with them in a calm and diplomatic manner and find out what their concerns are. If they are concerned about you falsifying the stuff, then point out that it could be falsified in different ways anyhow. There is only reasonable and not absolute assurance and this is not compatible with normal professional scepticism. After all this is not a forensic fraud audit.


Log in to reply