Employee Activity
Factory
Workers
Supervisor
Payroll
Clerk
Chief
Accountant
Accounts
Payable
Arlington Industries manufactures and sells engine parts for large industrial
equipment. The company employs over 1,000 workers for three shifts, and most
employees work overtime when necessary. Figure 15-10 depicts the procedures
followed to process payroll. Additional information about payroll procedures
follows:
The HRM department determines the wage rates of all employees. The process
begins when a form authorizing the addition of a new employee to the payroll
master file is sent to the payroll coordinator for review and approval. Once the
information about the new employee is entered in the system, the computer
automatically calculates the overtime and shift differential rates for that
employee.
A local accounting firm provides Arlington with monthly payroll tax updates,
Receive &
Reconcile bank
statement
Monthly
Complete
Timecards
Weekly
Weekly
Review and
Approve
Timecards
Prepare
Paychecks
Sign
Paychecks
Weekly
Record
Payroll
Distribute
Paychecks
which are used to modify the tax rates.
Employees record time worked on time cards. Every Monday morning the
previous week’s time cards are collected from a bin next to the time clock, and
new time cards are left for employees to use. The payroll department manager
reviews the time cards to ensure that hours are correctly totaled; the system
automatically determines if overtime has been worked or a shift differential is
required.
The payroll department manager performs all the other activities depicted in
Figure 15-10
The system automatically assigns a sequential number to each payroll check. The
checks are stored in a box next to the printer for easy access. After the checks are
printed, the payroll department manager uses an automatic check-signing
machine to sign the checks. The signature plate is kept locked in a safe. After the
checks have been signed, the payroll manager distributes the paychecks to all
first-shift employees. Paychecks for the other two shifts are given to the shift
supervisor for distribution.
The payroll master file is backed up weekly, after payroll processing is finished.
(CMA Examination, adapted)
a. Identify and describe at least three weaknesses in Arlington Industries’ payroll
process.
The payroll processing system at Arlington Industries violates the principle of
b. Identify and describe at least two different areas in Arlington’s payroll
processing system where controls are satisfactory.
Excel Problem
Objective: Learn how to find and correct errors in complex spreadsheets used for
payroll.
a. Read the article “Ferret Out Spreadsheet Errors” by Mark G. Simkin, in the
Journal of Accountancy (February 2004). You can find a copy online by accessing
www.aicpa.org.
b. Download the worksheet referenced in the article.
c. Enter the following erroneous data in the worksheet you downloaded in step b:
Change hours worked for Adams to 400,
Change hours worked for Englert to 4, and
Change hours worked for Hartford to –40.
Create a chart like that shown in Exhibit 2 of the article. Which of the errors are
easily found by the chart? What are the strengths and limitations of creating such
charts to detect errors? Print out your chart and save your work.
Note: Disable data validation on the hours worked column in order to input erroneous
data.
d. Create the three data validation rules described in the article (Exhibits 4–7
illustrate how to create the first rule). Print out screen shots of how you create
each rule, and save your work. (Note: The article “Block That Spreadsheet
Error” by Theo Callahan, in the Journal of Accountancy (August 2002) provides
additional examples of data validation rules.)
Rule 1: Payrates must be between $6.75 and $14.00.
Rule 2: Hours worked must be between 0 and 40
Rule 3: Overtime must be between 0 and 10
e. Follow the instructions for using the formula auditing tool. Print out a screen
shot showing use of the tool to circle invalid data (yours should be similar to
Exhibit 9 in the article).
f. Follow the instructions to run the “trace precedents” audit tool. Print screen
shots that show the results, and save your work. How useful is this tool? What
are its limitations, if any?
g. Enter the following data for new employees (inserting new rows in proper order
to maintain alphabetical listing of employees):
Name = Able, payrate = 11.11, regular hours = 40, overtime hours = 5
Name = Easton, payrate = 10.00, regular hours = 40, overtime hours = 0
Name = Johnson, payrate = 12.00, regular hours = 35, overtime hours = 10
Which audit tests and validation rules change? Why? Print screen shots, and
save your work.
Several audit tests and validation rules changed because their parameters were
established with the unadjusted cell references. The following audit tests and validation
rules should be adjusted to include the new entries: