Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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.
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.
The result should be this formula: =VLOOKUP(C7,$F$7:$G$13,2,TRUE)
Then copy this formula down the column and you will get the following spreadsheet:
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.
The data validation for sales is:
The data validation for the bonus is:
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.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?
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.
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.
i. Establishing a separate payroll checking account and funding it as an imprest
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.
l. Providing employees with earnings statements every pay period.
m. Check for paychecks with no social security or income tax withholding.
n. Check for multiple direct deposits to the same bank account in a single 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.)
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.
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.
