 |
 |
 |
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.