Chapter 7

You should start and save a new Tableau file for each Chapter.

Roger Company's files can be

downloaded from Connect in Excel format. For Chapter 7, you will import the Roger Company

accounts receivable

file. This contains the AR balance as of the end of 2018. See Chapter 4 for

instructions on how to import Excel files to Tableau. In this question, we will use Tableau to

highlight potential breakdowns in internal controls.

1.

Roger Company's internal controls require that the same employee not act in more than

one of the following roles: AR clerk, Cash Receipts Clerk, and GL Accounting clerk. To

identify sales for which these segregation of duties controls were not followed:

Add Invoice Total to the Rows line.

Add AR Clerk, Cash Receipts Clerk, and GL Accounting to the Columns line.

Save worksheet (tab) as Segregation of Duties.

In this bar chart, the initials along the top row identify the AR clerk, the next line

identifies the Cash Receipts clerk, and the row along the bottom of the bar chart identifies

the GL accounting clerk. From this, we learn that the employees with initials HMK and

NCS act as AR clerks; that employees FKL, HMK, MB, and SG act as cash receipt

clerks; and CT, HMK, and MB act as GL accounting clerks. That one person fulfills

multiple roles at some point is acceptable, but controls prohibit the same person fulfilling

multiple roles

on the same transaction

.

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash

Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances.

However, there are $25,352 of current AR for which HMK acted as both AR Clerk and

GL Accounting clerk. This shows that employee HMK occasionally acts in multiple roles

on the same transaction.

a.

What is the dollar value of accounts receivable for which HMK acted as both

AR clerk and Cash Receipts Clerk?

b.

When SG is the Cash Receipts clerk, what is the dollar value of accounts

receivable for which HMK acted as both AR Clerk and GL Accounting

clerk?

2.

Roger Company also requires all sales to be authorized.

Using the transactions in the accounts receivable file, identify the total dollar

volume of AR that was

not

approved (hint, use the Filter pane). Save your

worksheet (tab) as Not Approved AR.

3.

Problems 1 and 2 above identified instances of ineffective operation of key controls in the

revenue cycle.

How would these identified control deficiencies affect the nature, timing, and

extent of substantive procedures for the revenue cycle?

10

4.

Based on results from Problems 1 and 2 above:

What role does materiality play in classifying the type of control deficiencies

identified in Problems 1 and 2 above?

5.

Revenue is recognized when shipped. Based on shipping dates in the

shipping

file, total

revenue for 2018 is $1,372,637. The audit team has set overall materiality at 1% of

revenue.

Would you classify the authorization control deficiency identified in Problem

2 above as a control deficiency, a significant deficiency, or a material

weakness?