Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Accounting Information Systems
Rule 3: Overtime must be between 0 and 10
Ch. 15: The Human Resources Management/Payroll Cycle
Accounting Information Systems
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).
Note: This is now another Data Validation feature. To test it, first change all validation
rules from Stop to Warning on the Error Alert screen. Then highlight the three columns
of data and select the “Circle Invalid Data” option to get the following results:
Ch. 15: The Human Resources Management/Payroll Cycle
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
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
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
Ch. 15: The Human Resources Management/Payroll Cycle
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.
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
“Lookup and Reference” and choose VLOOKUP. Then complete the window as follows:
Ch. 15: The Human Resources Management/Payroll Cycle
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.
The data validation for sales is:
The data validation for the bonus is:
Ch. 15: The Human Resources Management/Payroll Cycle
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
New VLOOKUP formula: =VLOOKUP(B5,'Problem 15-7'!$F$7:$G$13,2,TRUE)
Ch. 15: The Human Resources Management/Payroll Cycle
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.
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
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).
To check for inaccurate or incomplete time data as well as errors in processing.
c. Direct deposit of paychecks.
d. Validity checks on Social Security numbers of all new employees added to the
payroll master file.
To prevent the addition of fictitious employees to the payroll.
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.
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.
To protect the confidentiality of payroll information.
Ch. 15: The Human Resources Management/Payroll Cycle
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.
To ensure complete processing of all payroll transactions.
k. Periodic reports of all changes to payroll database sent to each department
manager.
l. Providing employees with earnings statements every pay period.
Accounting Information Systems
Accounting Information Systems
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.)
formula: =IF(ISNA(MATCH(A4,$E$4:$E$26,0)),"Timecard employee# does not exist in master data","")
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.)
Accounting Information Systems
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 Security Number with the two middle
digits of 00 is invalid.
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.
Formula: =IF(ISNA(MATCH(E4,$A$4:$A$25,0)),"No paycheck created for this employee","")
Ch. 15: The Human Resources Management/Payroll Cycle
Accounting Information Systems
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
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.
4. Some of the payroll/HR services a CPA can offer are as follows:
a. Payroll administration
b. Benefits administration
c. Retirement plan administration
