2-training Cooperative Payroll Lesson

You are the clerical person for a company called "The Training Cooperative", which specializes in training other businesses in various computer programs. Because you have knowledge of Excel, your manager has asked you to prepare a weekly payroll report for the six trainers in the company.

Perform the following tasks to prepare this report: • Enter the worksheet title The Training Cooperative Payroll in cell A1. Make it larger than the rest of the text. • Enter these titles in your spreadsheet, across row 2: Employee, Rate, Hours, Dep. (Dependents), Gross Pay, Fed. Tax (Federal), State Tax, Net Pay • Enter the names of the employees, followed by Total • Here's the data for the six employees: Name Rate Hours Demi Collings $30.00 36 Ben Stiller $19.50 32 Harold Hall $25.50 32 Doris Kearnes $27.50 40 George Teller $32.50 40 Joan Seitzinger $19.50 30

Dependents 2 0 2 3 1 4

• Below are the formulas you'll use for Federal Tax and State Tax. You'll need to figure out formulas for Gross Pay and Net Pay yourself. How is pay calculated? Cell Reference

Cell Reference

o Federal Tax =20%*(Gross Pay – Dependents * 38.46) Cell Reference

o State Tax =3.2%*Gross Pay

• After you have finished totaling up the entire company's payroll, go to Autoformat and choose a nice-looking style to apply to your work. And remember, once you write the formula once correctly, you should use Autofill, so you don't have to rewrite it each time. • I also want you to look over the payroll and make a sensible graph out of some of the information. Include this on the same worksheet.

Write formulas to get these answers. Use the front page of this handout to help you.

Sample

The Training Cooperative Payroll Employee Demi Collings Ben Stiller Harold Hall Doris Kearnes George Teller Joan Seitzinger Total

$ $ $ $ $ $

Rate 30.00 19.50 25.50 27.50 32.50 19.50

Hours 36 32 32 40 40 30 210

Dep. 2 2 3 1 4

$ $ $ $ $ $ $

Gross Pay 1,080.00 624.00 816.00 1,100.00 1,300.00 585.00 5,505.00

$ $ $ $ $ $ $

Fed.Tax 200.62 124.80 147.82 196.92 252.31 86.23 1,008.70

State Tax $ 34.56 $ 19.97 $ 26.11 $ 35.20 $ 41.60 $ 18.72 $ 176.16

One possible chart might look like this.

Comparision of Net Pay $1,200.00 $1,000.00 $800.00 $600.00 $400.00 $200.00 $-

Net Pay Demi Collings Doris Kearnes

Ben Stiller George Teller

Harold Hall Joan Seitzinger

$ $ $ $ $ $ $

Net Pay 844.82 479.23 642.07 867.88 1,006.09 480.05 4,320.14

What do I mean by a sensible graph? First, stick with a column, bar, line, or pie chart, as the situation calls for. The other charts are probably not appropriate for the kinds of information you would be trying to graph. Here is a summary of the types of charts you may want to use. Look at the last page of the Excel handout which covers creating a chart or graph. Column Chart

Bar Chart

Line Chart

Pie Chart

Example of a good chart based on the Training Cooperative Payroll

Comparision of Net Pay $1,200.00 $1,000.00 $800.00 $600.00 $400.00 $200.00 $-

Net Pay Demi Collings Doris Kearnes

Ben Stiller George Teller

Harold Hall Joan Seitzinger

Example of a Poor Chart based on the Training Cooperative Payroll Demi Collings Ben Stiller Harold Hall Doris Kearnes

x

Ta x N et Pa y

e

.T a

St at

Pa y

Fe d

ro

ss

D

ep

.

George Teller

G

R at e H ou rs

$1,500.00 $1,200.00 $900.00 $600.00 $300.00 $-

Joan Seitzinger

Looks like kind of a mess, doesn't it? The program will let you make a chart comparing all this information, but it doesn't make any sense to do this. Just pick out one part or category and make a chart.