Microsoft Office Tutorials and References
In Depth Information
Introduction
Figure 2 – 1
Recall that the rst step in creating an effective worksheet is to make sure you
understand what is required. The people who will use the worksheet usually provide
the requirements. The requirements document for the HyperMass Online Storage
Salary Report worksheet includes the following needs: source of data, summary of
calculations, and other facts about its development (Figure 2– 2).
Worksheet Title
HyperMass Online Storage Salary Report
Needs
An easy-to-read worksheet that summarizes the company’s salary report (Figure 2 – 3 on the next page).
For each employee, the worksheet is to include the employee’s name, dependents, hours worked, hourly
pay rate, gross pay, federal tax, state tax, total tax percent, net pay, and hire date. The worksheet also should
include totals and the highest value, lowest value, and average for the columns of numbers specied below.
Source of Data
The data supplied includes the employee names, dependents, hours worked, hourly pay rate, and
hire dates.
Calculations
The following calculations must be made for each of the employees:
1. Gross Pay = Hours Worked 3 Hourly Pay Rate
2. Federal Tax = 0.22 3 (Gross Pay 2 Dependents * 24.32)
3. State Tax = 0.04 3 Gross Pay
4. Tax % = (Federal Tax + State Tax) / Gross Pay
5. Net Pay = Gross Pay 2 (Federal Tax + State Tax)
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 MAX and MIN functions to determine the highest and lowest values for dependents, hours
worked, hourly pay rate, gross pay, federal tax, state tax, total tax percent, and net pay
9. Use the AVERAGE function to determine the average for hours worked, dependents, hourly pay rate,
gross pay, federal tax, state tax, and net pay
Figure 2 – 2
EX 67
Search JabSto ::

Custom Search