Question
can you help me please open an Excel spreadsheet and create two tabs at the bottom one for each payroll period. columns for the employee
can you help me please
open an Excel spreadsheet and create two tabs at the bottom one for each payroll period. columns for the employee name, filing status, pay rate, gross pay, mandatory voluntary deductions, vacation, and gross pay. Assume that the employees have no cumulative earnings prior to these payroll periods. created for hours worked, overtime, and Net Pay.
use Excel formulas including SUM, SUMIF, SUMIFS, and Subtraction. A formula should also be included to tie information between two worksheets.
.include conditional formatting with at least two rules applied. and use the formatting features of Excel to give the worksheet a professional appearance
information
1-Students must include the mandatory withholdings. Use 6.2 percent for Social Security and
1.45 percent for Medicare for each employee. For FUTA, use .08 of the first $7,000 of each employee. For SUTA, use 5.4% of the first $8,500 of each employee.All of 2- the employees are paid on a weekly basis on Friday.
3-This project will focus on payroll for the dates ending 01/15/20 and 01/24/2020.
4- For all voluntary benefits, there is a 50/50 cost sharing agreement.
| Employee Name | Hourly or Salary | Filing Status | Number of Allowances for Federal and State Withholding. | Pay rate/hour | Hourly worked For the first payroll period | Hourly worked For the second payroll period | Over time hours | earn hours of vacation each pay period
| |
| Applie, Suzie | Hourly | Single | 0 | 15 | 50 | 45 | 10 | 3 | |
| Banana, Tim | Hourly | Married | 1 | 25 | 70 | 60 |
| 3 | |
| Catalope, Cheri | Salary | Married | 3 | 20 | 60 | 70 |
| 3 | |
| Dill, Sadie | Salary | Head of Household | 3 | 35 | 70 | 65 |
| 3 | |
| Eggplant, Bill | Hourly | Single | 0 | 13 | 55 | 45 | 20 | 3 | |
| Fort, Kyle | Hourly | Married | 0 | 15 | 45 | 55 |
| 3 | |
| Gould, Matt | Salary | Married | 4 | 30 | 60 | 65 |
| 3 | |
| Hamm, Mason | Hourly | Single | 1 | 15 | 65 | 56 |
| 3 | |
| Igloo, Alice | Hourly | Married | 2 | 15 | 56 | 70 | 25 | 3 | |
| Jasper, Alex | Hourly | Single | 2 | 25 | 55 | 45 |
| 3 |
|
| Employee Name | Healthcare, | union | Dental | Vision | Dependednt Education Fund | Accident Insuranc | 401K |
| Applie, Suzie | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Banana, Tim | $10 | 0 | $5 | $5 | 0 | 0 | $20 |
| Catalope, Cheri | 0 | $5 | 10 | 6 | 0 | 0 | 12 |
| Dill, Sadie | 15 | 0 | 5 | 7 | 5 | 7 | 25 |
| Eggplant, Bill | 0 |
| 0 | 0 | 0 | 0 | 0 |
| Fort, Kyle | 0 | 0 | 0 | 0 | 0 | 0 | 15 |
| Gould, Matt | 20 | 0 | 9 | 8 | 0 | 0 | 15 |
| Hamm, Mason | 10 | 0 | 0 | 0 | 0 | 0 | 0 |
| Igloo, Alice | 0 | 0 | 0 | 0 | 0 | 0 | 15 |
| Jasper, Alex | 12 | 0 | 5 | 8 | 0 | 0 | 18 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started