Tag Archives: Accounting

Cutting Audit Sampling Risk Down to Size: Automating the Sampling Process

Every Accountant professional at some point must take a sample. Sampling is defined as “the application of an audit procedure to less than 100 percent of the items within an account balance or class of transactions for the purpose of evaluating some characteristic of the balance or class” according to Audit Sampling guideline AU Section 350. Essentially sampling is the process of examining a small portion of a client’s records to make a judgment on the credibility of the whole. One of the risks associated with this process is selecting a sample which is not an accurate representative of the whole. Samples which are done manually may seem random but fail to meet this rigorous standard. Think about it, random means lack of pattern. As humans we tend to stick to patterns and routines. An auditor may pick all even number selections or every third in an effort at randomness. In this situation the likelihood that gross errors in a client’s books will be missed are exponentially increased. Sadly, many audits rely on manual selections from an Excel spreadsheet. For example, let’s say an audit is needed on accounts payable transactions. The typical audit would involve selecting a sample number of vouchers and back track to the supporting documentation such as invoices and purchase orders. Using the automated Excel technique highlighted below can minimize sampling risk and increase productivity.

 

Using Excel a tool can be developed to automatically apply red highlights random general entries. In order to have Excel automatically select random journal entries, we first need to organize our spreadsheet into two tables like the one below. The first table will contain our journal entries and the second will help us select random entries.

Master List

 

Add Conditional Formatting

 Journal List

1. Highlight the list of journal entry id numbers

Conditional Format Menu

  1. Under the Home menu click conditional formatting > Highlight Cells Rules > More Rules

 

Edit Format Rule Menu

 

  1. Select the formatting Rule type titled “Format only cells that contain”
  2. Change logical condition to “equal to”
  3. Enter J3 cell address in the last box
  4. Click Ok

 

Note: This will check to see if any of the journal ids in column B match journal ids in Column J

 

Edit Format Rules

  1. Click on Conditional Formatting the Manage Rules

 Edit Rule Manager

 

 

  1. Click on New Rule
  2. Follow steps 3 and 4 entering cells J4, J5, J6, J7 in the subsequent conditional format rules

Sample Generator 7

10. An inventory of all conditional format rules should look like the picture above

11. Click Ok

 

 

Add Random Calculation Formula

Sample Generator 812. Enter the following formula in cell A3 =RandBetween(1,10)

 

Note: The RandBetween formula is called a volatile function. This means the result constantly changes. This particular formula will give a random result between 1 and 10. Press F9 to see the result change.

Sample Generator 9

 

13. Copy the RandBetween function into the cells A4 through A12


Add Vlookup Formula

Sample Generator 10

14. In cell J2 type in =Vlookup(I3,$A:$B,2,false)

15. Copy this formula from J4 through J7

The Result

Sample Generator 12

16. Red highlights will be applied to the journal entries which appear with a selection number  between 1 – 5

17. Press F9 to select another random sample

Note: In some instances a cell in the Selected Journal Entry column may appear with #N/A. This means the selected sample number has not been found. Press F9 again to get another sample.

 

Bigger and Better

 

What if you’re working with more journal entries and need a larger sample. How can this approach be adjusted to accommodate? In order to apply this technique to a sample size of 25 we would need to make three adjustments to our spreadsheet:

1. Add more journal entries to the Journal Entry Master List

2. Revisit steps 2 – 8 for conditional formatting and enter cells J8 to J28. This will give the ability to select 20 more samples.

3. Change the RandBetween formula that appears in each cell in column A from RandBetween(1,10) to RandBetween(1,25).

4. Extend the helper table which contains the columns titled Selected Samples and Selected Journal Entry 20 more rows. The column titled Selected Samples should go from 1 to 25. Copy the vlookup formula down 20 rows.

 

This combination of tools is one of many techniques which can be used to simplify and reduce risks in the auditing process. To view a demonstration of this tool in action visit

youtu.be/zXqd-T_14fE.

3 Steps To Creating Dynamic Excel Reports

One of the benefits of Excel is that it saves time and increases productivity.  Quite often you can build something once and reap the benefits for years to come.  One way to increase productivity is to create dynamic reports.  Dynamic reports are built to give users the ability to change certain variables quickly.  This automation eliminates the need for data entry or constant redesign. Dynamic reports often employ the use of functions or other tools (ie Macros, VBA etc.) . Below is a three step process I typically take when creating dynamic reports for clients 1) Develop Template 2) Gather Data 3) Add Dynamic Techniques

Dynamic Reports With Excel

 

Develop Template

A template is simply a spreadsheet formatted in a way that presents information clearly and concisely.  This template is then reused on a periodic basis.   In order to develop a useable template, start by writing a list of data that you absolutely need.  After a brief list is created, go through a brainstorming  trial and error process of  template layouts.  Choose the layout which communicates your message most clearly.  It is a good practice to get outside criticism on the final format.

 Gather Data

After the template is created, the next step is to compile all the data points needed for the template. The final output desired, which is the template, guides the data that is needed.  The data points should be organized into a concise compact table on one spreadsheet.  In many cases after getting more information about your data such as the level detail and generation frequency “rework” may be needed to your template.  You may find out that some critical elements are unattainable or organized in different way.  These types of challenges may force a rework in the template format. Gathering data and developing a template is an iterative process.

Add Dynamic Elements

The final step would be to add dynamic elements.  The elements chosen are dictated by the format of your template or organization of your data.  Do you want to have the ability to filter through calendar years? Do want to be able to see the detail? These questions help determine the types of dynamic tools you would use.  Some of functions I use frequently for this are Sumif and Countif. These functions help to quickly populate reports without manual intervention.  A few other helpful tools are Visual Basic for Application (VBA) and Pivot Tables.

Time is precious.  Business owners and professionals are overworked and need to get things done quickly.  Implementing the outlined process will lead to time savings.  This new found time can be used in other productive ways to grow an organization.