Payroll Lab

New skills needed:

  • Entering formulas
  • Using the If function

1. Create a spreadsheet to keep track of weekly payroll data. To do this, enter the data below, starting in cell A2.

Name

Pay Rate

Hours

Dependants

Marge Simpson

25.00

45

2

Herman Hollerith

14.95

30

0

Betty Rubble

11.50

29

1

Buffy Summers

17.75

40

5

Fred Flintstone

24.00

34

1

Augusta Aida

33.50

55

3

Jane Doe

12.75

20

4

Suzy Snowflake

18.00

40

5

Mister Bogus

19.50

40

8

Roger Rabbit

25.50

29

2

Herman Munster

35.00

44

4

2. In Cell A1 enter the title “CIS110 Fictional Payroll”. If you forgot to skip the first row when entering the data in step one, simply insert a new row at the top of the sheet.

3. Calculate the gross pay in column E with the following formula:
Gross pay = IF( Hours >40, 40 * Pay Rate + (Hours-40) * 1.5 * Pay Rate, Pay Rate * Hours)

4. Calculate the Federal tax with the following formula:
Fed. Tax = 20% * (Gross pay – Dependents * 0.35)

5. Calculate the State tax with the following formula:
State Tax = Gross pay * 3.5%

6. Calculate Net Pay by subtracting the sum of the state and Federal taxes from the Gross pay: Net pay = Gross pay – (Federal tax + State Tax)

Formatting:

7. Format the title in cell A1 with the Title Style. Merge and Center the title across row A through H. Put in a bottom double border under the title.

8. Apply 20% Accent 1 style to cells B3:H13. Use the “no border” border option to get rid of any borders you may have picked up during your copy and pasting of the data.

9. Format the headings in row 2 with Heading 2 style. Add a thick bottom border under the headings.

10. Format the names in column A with a bold, size 12 Calibri font.

11. Format the pay B3: B13, in Accounting style.

12. Format the number of hours and the Dependents as a number with no decimal places.

13. Format cells E3:H13 in Currency style.

14. Make columns A through H a width of 15.

15. Make row 1 a height of 45, row 2 a height of 25 and the rest of the rows a height of 35.

16. Center the cell contents in the spreadsheet both vertically and horizontally.

17. Left-Align the names in column A and check the text-wrap box for the names.

18. Apply Conditional Formatting to any Net pay less than $500.  Make the font bold and white on a red background.

19. Add your name to the footer.

20. Save the document, print it in landscape mode with fit to page and print gridlines selected, and put it in your portfolio.

Click here to see an example of the finished spreadsheet.

 

lmayer@elgin.edu