Microsoft Office Tutorials and References
In Depth Information
REQUEST FOR NEW WORKSHEET
Date Submi ed:
April 16, 2012
Submi ed By:
The Mobile Masses Store Biweekly Payroll Report
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.
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
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.
Approval Status: X
April 23, 2012
J. Quasney, Spreadsheet Specialist
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
Set margins, and add headers and footers to a worksheet
Work with the worksheet in Page Layout view
Change margins on the worksheet
Print a section of the worksheet