| | You have just been hired as a payroll manager for SHM Hotel. Your task is to make a payroll statement that summarizes 1) the net wage payment of each department, and 2) overall payroll information.
The payroll statement needs to take the following into account: |
| | 1. Hourly pay rate: |
| | | Pay Rate | Hourly |
| | | Supervisors | $25.00 |
| | | Front office | $19.00 |
| | | Housekeeping | $18.00 |
| | | Concierge | $19.00 |
| | | Maintenance | $22.00 |
| | - Employees (excluding supervisors) are entitled to receive overtime payment equal to 1.5 times the pay rate for each hour exceeding 45 hour a week |
| | | Overtime rate | 1.5 |
| | 2. Withholdings |
| | - Social security and Retirement contribution |
| | * 6% of total salary in withheld as social security contribution |
| | | SSN Contribution | 6% |
| | * Retirement contribution is computed as follows: |
| | | Total weekly wages ≤ | $1,000 | 1.50% |
| | | Total weekly wages > | $1,000 | 1.00% |
| | - Income Tax withheld |
| | * The income tax table is as follows |
| | | Total weekly wages | | Rate |
| | | Less than or equal to (≤) | $800 | 10.00% |
| | | Greater than (>) | $800 | 15.00% |
| | 3. Insurance deduction |
| | | Age | Rate |
| | | ≤ 50 | 9.00% | of Pre-Tax wages |
| | | >50 | 11.00% | of Pre-Tax wages |
| | Assignment: |
| | You are provided with a spreadsheet that has the names and positions of the employees. You will design a model that include the five following spreadsheet tabs: |
| | 1. “Employee Wages” will include: |
| | | - Last Names | - Overtime hours worked |
| | | - First Names | - Regular hours worked |
| | | - Position | - Hourly Pay rate |
| | | - Weekly hours worked | - Base Pay |
| | | | - Overtime pay |
| | | | - Total weekly wages |
| | | Overtime | 45 |
| | | | 0 |
| | 2. “Payroll Benefits” will include: |
| | | - Last Names | - Social security contribution |
| | | - First Names | - Retirement contribution |
| | | - Position | - Total Benefits |
| | | - Total weekly Wages | - Pre-Tax wages |
| | | * Name this new spreadsheet as “Payroll Benefits”. |
| | | * Necessary columns should be imported from “SHM Hotel Employees” and “Employee Wages” spreadsheets. Other columns should be computed. No hard coding allowed. Use only functions or formulas. |
| | 3. “Income Tax and Insurance” will include: |
| | | - Last Names | - Pre-tax wages |
| | | - First Names | - Income Tax Rate |
| | | - Age | - Income Tax withheld |
| | | | - Insurance deduction |
| | | * Name this new spreadsheet as “Income Tax and Insurance”. |
| | | * Necessary columns should be imported from “SHM Hotel Employees”, “Employee Wages”, and “Payroll Benefits” spreadsheets. Other columns should be computed. No hard coding allowed. Use only functions or formulas. |
| | 4. “Payroll Summary” that will draw on the other spreadsheets to display and compute the following: |
| | | - Last Names | - Total Benefits |
| | | - First Names | - Pre-Tax income |
| | | - Position | - Income tax withheld |
| | | - Weekly hours worked | - Insurance deduction |
| | | - Total weekly wages | - Net wages payable |
| | | | (computed from the data imported into this spreadsheet) |
| | | * Name this new spreadsheet as “Income Tax and Insurance”. |
| | | * Necessary columns should be imported from “SHM Hotel Employees”, “Employee Wages”, “Payroll Benefits”, and “Income Tax and Insurance” spreadsheets. Other columns should be computed. No hard coding allowed. Use only functions or formulas. |
| | 5. Create following additional table on “Payroll Summary” spreadsheet. |
| | | - Compute the total number of employees |
| | | - Compute the number of employees who worked over 45 hours a week. |
| | | - Compute the total net wages per department. |
| | | * Use conditional functions based on the Payroll Summary table. |
| | | Number of Employees |
| | | # of employees worked overtime | >45 |
| | | Total net wages of each department |
| | | Supervisors | | $ |
| | | Front office | | $ |
| | | Housekeeping | | $ |
| | | Concierge | | $ |
| | | Maintenance | | $ |
| | 6. Highlight the rows for the employees who have total benefits less than $50.0. |
| | | Total benetifs | $ 50.0 | * add this condition cell on the "Payroll summary" and use it for reference. |
| | 7. Add a pivot table that shows the positions as rows and Total wages, Total benefits, income tax withheld and Net wages payable as columns. |
| | 8. Using the table you created in question 5, create a spreadsheet with a graph that compares total net wages per function (concierge, front office….). Make sure it has data label ($) and title (Net wages of each department). |
| | 9. Create a spreadsheet of “Table of Content” that link to each spreadsheet you generated (“Employee Wages”, “Payroll Benefits”, “Income Tax and Insurance”, “Payroll Summary”, “Pivot table”, “Chart”). |
| | Requirements |
| | Relevant data will be computed only once, if needed in another spreadsheet, you will link/import that data from the original spreadsheet (e.g.,“Total Weekly Wages” is computed in the “Employee Wages” Spread sheet and linked to the “Payroll Benefits” spreadsheet) |
| | Each Formula required by the assignment will be written so that it can be copied and applied to all the employees. This means that each formula should be written for the first employee and then simply copied to compute the data for the remaining 29 through the use of appropriate relative and absolute references. Inserting single-use formulas designed to work for a limited number of situations/positions will be penalized. |
| | As usual, avoid the use of hard-coded numbers in your formulas. |
| | Do not forget to apply relevant formatting where needed. |
| | Hints: To facilitate your work, copy the various tables (hourly and withholding or tax rates) to the appropriate spreadsheet. |
| | Functions to be used are: |
| | ₋ IF(), VLOOKUP(), COUNTIF(), SUMIF(), COUNTA() |
HWXJQ
lab_1.xlsx
