978-0133428537 Chapter 15 Solution Manual Part 3

Unlock access to all the studying documents.

View Full Document

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.