Microsoft Office Tutorials and References
In Depth Information
Introduction
REQUEST FOR NEW WORKSHEET
Date Submi ed:
April 16, 2012
Submi ed By:
Samuel Snyder
Worksheet Title:
The Mobile Masses Store Biweekly Payroll Report
Needs:
An easy-to-read worksheet that summarizes the company’s biweekly payroll
(Figure 2-3). For each employee, the worksheet is to include the employee’s
name, hire date, dependents, hours worked, hourly pay rate, gross pay,
federal tax, state tax, net pay, and total tax percent. The worksheet also
should include totals and the average, highest value, and lowest value for
column of numbers speciﬁed below.
Source of Data:
The data supplied by Samuel includes the employee names, hire dates, hours
worked, and hourly pay rates. This data is shown in Table 2-1 on page EX 72.
Calculaons:
The following calculaons must be made for each of the employees:
1. Gross Pay = Hours Worked × Hourly Pay Rate
2. Federal Tax = 0.22 × (Gross Pay – Dependents * 24.32)
3. State Tax = 0.04 × Gross Pay
4. Net Pay = Gross Pay – (Federal Tax + State Tax)
5. Tax % = (Federal Tax + State Tax) / Gross Pay
6. Compute the totals for hours worked, gross pay, federal tax, state tax, and
net pay.
7. Compute the total tax percent.
8. Use the AVERAGE funcon t o determine the average for dependents, hours
worked, hourly pay rate, gross pay, federal tax, state tax, and net pay.
9. Use the MAX and MIN funcons t o determine the highest and lowest
values for dependents, hours worked, hourly pay rate, gross pay, federal tax,
state tax, net pay, and total tax percent.
Approvals
Approval Status: X
Approved
Rejected
Approved By:
Date:
April 23, 2012
Assigned To:
Figure 2–2
Overview
As you read this chapter, you will learn how to create the worksheet shown in
Figure 2 – 1 by performing these general tasks:
Enter formulas and apply functions in the worksheet
Add conditional formatting to the worksheet
Apply a theme to the worksheet