Reference no: EM131482955

Assignment

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
Supervisor	$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.5%
Total weekly wages >	$1,000	1.0%

- Income Tax withheld

? The income tax table is as follows

Pre-Tax wages		Rate
Less than or equal to (≤)	$800	10.00%
Greater than (>)	$800	15.00%

3. Insurance deduction

Age		Rate
Less than (≤)	50	9.00%	of Pre-Tax wages
Above (>)	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:

1. Last Names
2. First Names
3. Position
4. Weekly hours worked 5. Overtime hours worked
6. Regular hours worked
7. Hourly Pay rate
8. Base Pay
9. Overtime pay
10. Total weekly wages

* Name this new spreadsheet as "Employee Wages".

* Necessary columns should be imported from "SHM Hotel Employees" spreadsheet. Other columns should be computed. No hard coding allowed. Use only functions or formulas.

* Overtime hours worked: Use IF() - if weekly hours worked is greater than 45, it result should be ‘(weekly hours worked - 45 hours)', otherwise zero.

Overtime	45
	0

* Regular hours worked = weekly hours worked - overtime hours worked
* Hourly pay rate: Use VLOOKUP(). The necessary reference table should be used from instructions tab.
* Base pay= Regular hours worked × hourly pay rate
* Overtime pay = Overtime hours worked × Hourly pay rate × 1.5 (do not hardcode.)
* Total weekly wages = Base pay + Overtime pay

2. "Payroll Benefits" will include:

1. Last Names
2. First Names
3. Position
4. Total weekly Wages 5. Social security (SSN) contribution
6. Retirement contribution
7. Total Benefits
8. 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.

* SSN contribution = Total weekly wages × 6% (do not hardcode.)
* Retirement contribution = Use IF(). Necessary reference table is provided in the instruction tab.
* Total benefits = SSN contribution + Retirement contribution
* Pre-Tax wages = Total weekly wages - Total benefits

3. "Income Tax and Insurance" will include:

1. Last Names
2. First Names
3. Age
4. Pre-tax wages 5. Income Tax Rate (%)
6. Income Tax withheld ($)
7. 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.

* Income Tax Rate (%) = Use IF(). Necessary reference table is provided in the instruction tab.
* Income Tax withheld ($) = Pre-Tax wages × Income Tax Rage (%)
* Insurance deduction ($) = Use IF().Necessary reference table is provided in the instruction tab.

4. "Payroll Summary" that will draw on the other spreadsheets to display and compute the following:

1. Last Names
2. First Names
3. Position
4. Weekly hours worked
5. Total weekly wages 6. Total Benefits
7. Pre-Tax income
8. Income tax withheld
9. Insurance deduction
10. 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.

* Net wages payable = Pre-Tax wages - Income Tax withheld - Insurance deduction

5. Create a separate 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. On the Payroll Summary table, highlight the rows for the employees who have total benefits less than $50.0.

Total benefits

$ 50.0

* add this condition cell on the "Payroll summary" and use it for references.

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

Attachment:- Lab_Data.rar