PREPARING A PAYROLL SUMMARY
Lesson Summary
Students will take information from time
cards and charts and input information on a payroll register in excel
and tabulate information.
Objective/SOL Correlation
The student will be able to complete
a payroll register using charts and time cards.
Expected Student Outcomes
The student will be able to total earnings,
deductions, and net pay for six employees and create a graph of gross
earnings.
Vocabulary
| Federal Withholding |
Gross Pay |
FICA |
Deductions |
| State Withholding |
Net Pay |
Overtime |
|
Materials Needed
Payroll
Register Chart from Excel
Time Cards
Federal Withholding Tax Table
Prerequisite Skills
Students should have some knowledge of
a time card, deductions are subtracted from gross pay to arrive at net
pay, be able to read a withholding tax table, and completed an introductory
activity on calculating payroll using a calculator.
Procedure
1. Open Preparing a Payroll Register from
the folder on the desk top. Save it in your home directory.
2. Create a footer with your name on the
left, Block 1 in the center, and the date on the right. (Go to
View, click header and footer, click custom footer)
3. Enter the place of your employment in
cell G3.
4. Highlight E6:E13 over toM6:M13, click
format, cell, number tab, currency.
5. Using the information from the Time
Cards Worksheet, type each employee’s Social Security Number, Regular
Hours, Over Time Hours, Gross Earnings, Hospital, and Other. (You
will use columns B, C, D, E, J, and K rows 6:11. Do not leave
any blank cells. Place a 0 in cells with no information or none.
6. To complete F6:F11, refer back to the
Time Card Worksheet to determine the number of exemptions claimed and
look up the gross earnings with the appropriate number of exemptions
for that employee on the Federal Income Tax Withholding Table.
Enter that amount in column F for each employee.
7. You will need to compute percentages
to complete State Withholding Tax, City Withholding Tax, and FICA.
Proceed with the following formulas:
(a). State Withholding Tax –Go to
cell G6 and type the formula =(.06*E6), press enter, then copy the formula
from G6 to G7:G11.(hint for copying: click back on cell G6, cross hairs
at anchor of cell, click and drag to G:11.)
(b). City Withholding Tax-- Go to
cell H6 and type the formula =(.02*E6), press enter, then copy the formula
from H6 to H7:H11.
(c). FICA – Go to cell I6 and type
the formula =(.075*E6), press enter, then go back and copy the formula
from I6 to I7:I11.
8. Go to cell L6, click and drag back to
F6 and click auto sum, then copy. (Hint: click on L6, cross hairs at
anchor, and drag down to L11.)
9. To determine net pay, go to cell M6
and enter the formula =(E6-L6), press enter, then copy to M11.
10. Using the autosum function compute
the following totals for all ranges E6:E11 through M6:M11. (Hint:
Click E13 and highlight to M13 continue to highlight up to M6, press
enter.)
11. Save your work.
12. Now let us make a graph. First
highlight, A6-A11 and then press the CTRL key and highlight E6-E11.
Go to Chart Wizard. Choose the first pie graph, click next, next,
click title tab and type Employees Gross Earnings, click Data Labels
tab and click on show label and percent, click next, click finish.
Move graph to next blank page and save. (It will not fit on your
chart.)
13. Print document.
14. While it is printing, let’s look at
the chart with the formulas instead of the numerical answers.
Go to Tools, options, View Tab, click formulas in the window options,
click ok. After you have viewed the formulas chart, return back
to the numerical chart. (Hint: go to tools, options, take
off formula, ok.)
15. Close your folder and turn in printed
material and handouts for a grade.
Evaluation
Students will complete the payroll register
with the given information. They will total columns,
determine percentage of payroll designated for taxes, and create
a graph of all employees and their gross earnings.
Karen Barnes
Oscar Smith High School
Chesapeake VA
