Spreadsheet questions
1. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On a new sixth tab, calculate the payroll information for the first week of January 2012 (ending 1/7/2012). Do not delete any data on the other five tabs. Calculate the gross regular pay (regular hours x pay rate), gross overtime pay (overtime hours x pay rate), gross pay (gross regular pay plus gross overtime pay), exempt withholdings (employees do not have to have taxes withheld over the three stock option plans but credit union contributions are not exempt), taxes (gross pay minus exempt withholdings times a flat tax of 10%), net pay (gross pay minus taxes and minus exempt/non-exempt withholdings).
2. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. Assume that all deductions for Credit Union and stock option plans comes out of net pay. On a new sixth tab, create a tax table that calculates taxes the following way:
on the vertical axis the payrate from $1.00 to $50.00
on the horizontal axis the number of hours worked from 20 to 40
in the data area, actual taxes are calculated as follows:
no taxes for the first $200
5% tax over earnings between $201 and $400
7.5% tax over earnings between $401 and $600
12.5% tax for earnings of $601 and more.
3. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On the Employees tab, add a column with the current length of employment (assume all listed employees still work for the company), annual salary (assume 40 hours a week and 51 work weeks a year), and tax-free part of the salary. Each W4 exemption counts for $1000 without taxation, and employees can opt to have additional withholdings on their W4. Generate email addresses by combining the user name with "@softwear.com". Create a summary table at the top with minimum/ average/ maximums for length of employment, pay rate, W4 exemptions, additional W4 withholdings, and annual salary.
4. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On a new sixth tab, make a table with the number of employees by location first and then by job title. Include an appropriate graph.
5. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. The company has fallen on hard time and needs to consider reducing hours. On a new sixth tab, compare three scenarios: current without restricting time cards, an elimination of overtime but not cutting regular hours, and eliminating overtime while reducing regular hours by four. Do this for the time card of 11/17/2012.
6. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On a new sixth tab, make a table that shows the average number of regular hours and overtime hours, by location and by classification (exempt vs. non-exempt). Payroll should be able to filter this report by any combination of payroll periods. Show the data for the first payrolls of 2006, 2007, 2008, and 2009.
7. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On the Time Sheets tab, compare the monthly totals for regular and overtime hours for all months since 2006.
8. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On the Employees tab, show only exempt employees in California, non-exempt employees in Texas, employees in Human Resources, and employees making less than $15 an hour. Do not delete any data, and show the total number of employees meeting this condition at the bottom of the table.
9. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. On the employees tab, show only the exempt employees. Do not delete any data. At the bottom of the table, show the correct minimum, average, and maximum payrate.
10. The spreadsheet has five tabs: Employee, Department, Time Sheets, Deductions, and Deduction Types. The company has fallen on hard times, and needs to close one of their three locations. Exempt employees will be transferred to one of the other two locations, and non-exempt employees will be laid off. Use a pivot table to calculate the savings per year, assuming that all non-exempt employees are paid for 2040 hours per year. You can add a column to the employee's sheet if you want. The pivot table should show total salary by location and department, with total salary / average salary/ number of employees, for only the location with the highest total savings.