Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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?
The Trace Precedents tool is found on the formulas tab. It may help visually identify
15-1
©2018 Pearson Education, Inc.
Accounting Information Systems
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:
All input validation rules
15.7 Excel Problem
Objective: Learn how to use the VLOOKUP function for payroll calculations.
a. Read the article “Make Excel a Little Smarter” by Lois S. Mahoney and Charles
Kelliher in the Journal of Accountancy (July 2003). You can find a copy at
www.aicpa.org.
b. Read the section titled “Data in Different Places” and create the spreadsheet
illustrated in Exhibit 6. Print a screen shot of your work, and save your
spreadsheet.
15-2
©2018 Pearson Education, Inc.
Accounting Information Systems
c. Create a formula that calculates total bonuses. Also create a cell entry that
indicates what that number represents. Print a screen shot of your work, and
save it.
In the Bonus column, click on the cell for the first Smith, choose the formulas tab, select
Then copy this formula down the column and you will get the following spreadsheet:
15-3
©2018 Pearson Education, Inc.
Accounting Information Systems
d. Add the following data validation controls to your spreadsheet, including
explanatory error messages. Save your work.
Sales must be positive.
Sales cannot exceed 125.
Amount of bonus must be nonnegative.
Amount of bonus cannot exceed 20% of unit sales.
15-4
©2018 Pearson Education, Inc.
Accounting Information Systems
e. Modify your worksheet by placing the sales data and resulting bonus on a
different worksheet from the bonus table. Name your table array, and modify
the VLOOKUP function accordingly. Then add another employee: Johnson, who
sold 115 units. Print a screen shot of your new worksheet showing the bonuses
for each employee, including Johnson. Save your work.
15-5
©2018 Pearson Education, Inc.
Accounting Information Systems
15.8 The local community feels that secondary school education is a necessity in our
society and that lack of education leads to a number of social problems. As a result,
the local school board has decided to take action to reverse the rising dropout rate.
The board has voted to provide funds to encourage students to remain in school and
earn their high school diplomas. The idea is to treat secondary education like a job
and pay students. The board, however, could not agree on the details for
implementing this new plan. Consequently, you have been hired to devise a system
to compensate students for staying in school and earning a diploma.
As you devise your compensation scheme, be sure it meets the following general
control objectives for the payroll cycle:
All personnel and payroll transactions are properly authorized.
All employees are assigned to do productive work, and they do it efficiently and
effectively.
All transactions are accurately recorded and processed.
Accurate records are maintained.
All disbursements are proper.
Write a proposal that addresses these five questions:
a. How should the students be compensated (e.g., for attendance, grades)?
b. How and by whom will the payments be authorized?
c. How will the payments be processed?
d. How should the payments be made (e.g., in cash or other means)?
e. When will the payments be made?
There is no one correct answer to this problem. Students should answer parts b, c, d and e
as if they were developing a payroll system, regardless of how they answer part a. The
following are some of the issues that need to be addressed:
Who will have custody over records relating to student activity?
15-6
©2018 Pearson Education, Inc.
Accounting Information Systems
15.9 What is the purpose of each of the following control procedures (i.e., what threats is
it designed to mitigate)?
a. Compare a listing of current and former employees to the payroll register.
b. Reconciliation of labor costs (based on job-time ticket data) with payroll (based
on time card data).
c. Direct deposit of paychecks.
d. Validity checks on Social Security numbers of all new employees added to the
payroll master file.
e. Cross-footing the payroll register.
f. Limit checks on hours worked for each time card.
To prevent overpaying employees.
g. Use of a fingerprint scanner in order for employees to record the time they
started and the time they quit working each day.
To ensure the validity of employee time and attendance data by preventing one
h. Encryption of payroll data both when it is electronically sent to a payroll service
bureau and while at rest in the HR/payroll database.
i. Establishing a separate payroll checking account and funding it as an imprest
account.
j. Comparison of hash totals of employee numbers created prior to transmitting
time-worked data to payroll provider with hash totals of employee numbers
created by payroll provider when preparing paychecks.
k. Periodic reports of all changes to payroll database sent to each department
manager.
15-7
©2018 Pearson Education, Inc.
Accounting Information Systems
l. Providing employees with earnings statements every pay period.
15.10 Excel Problem
Objective: Learn how to use text and array formulas to locate potential payroll
problems.
a. Download the spreadsheet for this problem from the course Web site.
b. In column I, under the label “Ghost Employee?” write a function that compares
the employee# in the timecards column to the employee# in the payroll master
data column and displays the message: “Timecard employee# does not exist in
master data” for any employee in the timecards columns who is not listed in the
payroll master data columns. The function should leave the cell blank if the
employee# in the timecards worksheet does exist in the payroll master file
worksheet. (Hint: Use the ISNA and MATCH functions.)
c. In column L, titled “Invalid SSN?” write a function to identify invalid Social
Security numbers. Assume that Social Security numbers that begin with the digit
9 or that have the digits 00 for the middle two numbers are invalid. Your
function should display a message that flags either of these two conditions or
which displays nothing otherwise. (Hint: there are text functions that examine
specific portions of a string, such as the left 3 characters, and there are also
functions that convert text to numeric values.)
15-8
©2018 Pearson Education, Inc.
Accounting Information Systems
Excel’s built-in text functions (MID and LEFT) are used here to parse social security
numbers. The function MID takes three arguments: the first one indicates the cell to
test (in this case, the social security numbers in column G); the second indicates the
position to begin with (in this case, the fifth character which is the one immediately
The entire nested IF function then works as follows:
1. Test if the middle two digits are zero. If they are, return a message that a Social
2. If the two middle digits are not zero, then the second IF test is performed, which
d. In column P, titled “Missing Paycheck?” write a function to check whether a
timecard exists for each employee in the master payroll data section of the
worksheet. The formula should return either the message “No paycheck created
for this employee” or display nothing.
The MATCH function checks to see if the employee number in the master payroll
If the employee number in the master payroll data (column E) does not exist in the
timecard data (column A) the MATCH function returns the value N/A. Therefore, the
The solution looks like this:
15-9
©2018 Pearson Education, Inc.
Accounting Information Systems
15.11 Answer all of the following multiple-choice problems:
1. Tokenization is a control that mitigates the risk of
a. Inaccurate or invalid master data
b. Unauthorized disclosure of sensitive data
c. Unauthorized changes to payroll master data
d. Inaccurate time and attendance data
e. Theft or fraudulent distribution of paychecks
2. Which of the following controls reduce the risk of issuing paychecks to a “phantom”
or “ghost” employee?
a. Restrict physical access to blank paychecks
b. Prenumber all payroll checks
c. Use an imprest account to clear payroll checks
d. All of the above
e. None of the above
3. To implement proper segregation of duties, who should have the ability to create
new records in the employee master file used for processing payroll?
a. A payroll clerk
b. Someone in HR
c. The new employee’s supervisor
d. Any of the three people listed above could perform this task
e. None of the three people listed above should perform this task
15-10
©2018 Pearson Education, Inc.
Accounting Information Systems
4. An application control that compares the amount of an employee’s raise to that
employee’s existing salary is called a(n):
a. Limit check
b. Range test
c. Reasonableness test
d. Check digit verification
e. Size check
5. The purpose of issuing earnings statements to employees is to mitigate the risk of
a. Unauthorized changes to payroll master data
b. Errors in processing payroll
c. Theft or fraudulent distribution of paychecks
d. Untimely payments
6. The use of biometrics as part of employee authentication is designed to primarily
reduce the risk of which threat?
a. Inaccurate updating of the master payroll file
b. Inaccurate time and attendance data
c. Failure to make required payroll tax payments
d. Errors in processing payroll
7. Which of the following control procedures is designed to reduce the risk of theft of
paychecks or fraudulent distribution of paychecks?
a. Restriction of access to blank payroll checks
b. Prenumbering and periodically accounting for all paychecks
c. Redepositing all unclaimed paychecks and investigating the reasons why the
paychecks were not claimed
d. All of the above
e. None of the above
8. Use of a separate checking account for payroll is designed to reduce the risk of the
threat of
a. Unauthorized changes to the payroll master file
15-11
©2018 Pearson Education, Inc.
Accounting Information Systems
b. Errors in processing payroll
c. Theft or fraudulent disbursement of paychecks
d. Failure to make required payments to government tax agencies
e. Loss or destruction of payroll data
SUGGESTED ANSWERS TO THE CASES
CASE 15-1 Research Report: HRM/Payroll Opportunities for CPAs
Payroll has traditionally been an accounting function and some CPAs have provided
payroll processing services to their clients. Today, CPAs are finding additional new
lucrative opportunities to provide not only payroll processing but also various HR
services. Write a brief report that compares the provision of payroll and HR services
by CPAs with that of national payroll providers. Perform the following research to
collect the data for your report:
1. Read the articles “Be an HR Resource for Your Clients,” by Michael Hayes and
“Hired Help: Finding the Right Consultant,” by Joanne Sammer, both of which
were published in the November, 2006 issue of the Journal of Accountancy.
2. Contact a local CPA firm that provides payroll and HR services and find out
what types of services they perform and what types of clients they serve.
Reports will of course vary from student to student; however, the following presents
some points that should appear in a student’s report:
1. CPA’s naturally have the necessary skills to provide payroll and human resource (HR)
services.
2. Although national payroll providers also provide the same services, CPA’s are in a
3. Even if a CPA does not offer payroll/HR services, they are in good position to help
4. Some of the payroll/HR services a CPA can offer are as follows:
a. Payroll administration
b. Benefits administration
15-12
©2018 Pearson Education, Inc.
Accounting Information Systems
15-13
©2018 Pearson Education, Inc.
