due in 6 hours

1.     You are an analyst for a large retail chain that you own. 

2.     Create a payroll log for 4 weeks containing the following information:

a.     Store location, Department, Last name, first name, gender, marital status, hours, hourly rate, gross pay, union membership, health insurance type, health insurance cost,  union dues, % 401K deduction, the 401K investment in $, overtime earned, social security tax, federal tax, state tax, and net pay.  Call the worksheet payroll log one

b.     The business rules are the following:

                                                                                                       i.            There are 8 employees

                                                                                                     ii.            There are only 3 store locations:  Albany, NYC and Buffalo

                                                                                                  iii.            Persons earns time and a half for all hours over 40

                                                                                                  iv.            Union members pay 2 %  union dues of their gross pay

                                                                                                     v.            There are three types of health insurance with different costs for single or family coverage.   Social security, federal tax, and state tax rates are 8%, 15%, and 5%. 

                                                                                                  vi.            Deduction information should be included in a table.

                                                                                                vii.            Net pay is Gross pay minus deductions.

                                                                                             viii.            I want to easily be able to tell if an employee earned overtime.

                                                                                                  ix.            Create a separate table that contains the maximum, minimum, average salaries, how many employees are in the union

3.     Makes up payroll information for each week.  For each week, only the hours and pay rate should change.

4.     The row and column titles must be visible when you scrolling through the worksheet and printing.

5.     Each payroll week must be on its own page.

6.     I need a summary for all 4 weeks. 

7.     I need a sum and average parole by gender

8.     I need a breakdown of health insurance costs by department and store.

9.     For each week, create a graph that compares salaries for female and male workers.

10.                        For each week, create a graph that shows the breakdown by health insurance types.

11.                        Create a copy of the summary worksheet  that contains the same information with the following changes:

a.      10% increase in tax rates

b.      20% increase in health insurance rates

c.      Create subtotals for union vs. non-union

d.     Create a separate table that contains the maximum, minimum, average salaries, how many employees are in the union, earned overtime

12.                        Create separate worksheets for monthly paychecks for each employee.  Each worksheet should be named the employees last name.  Each sheet should show all deductions and be formatted like a check. 

13.                        General requirements for the entire workbook

a.     All worksheets must be properly titled.

b.     All fields must be professionally formatted and professional.

c.      Information must be reused where possible

d.     Each paycheck must be printed and be properly formatted where all columns and heading appear on each page.

e.      Print out the summary workbook for each health insurance type sorted by location and department.

14.                        You must include the following in your spreadsheet

1.      

                                                       i.            Pivot table

                                                     ii.            Pivot charts

                                                  iii.            Proper use of relative and absolute addressing

                                                  iv.            Proper header/footer

                                                     v.            Printing usability

                                                  vi.            Viewing usability

                                                vii.            Lookup functions

                                             viii.            A macro for printing

                                                  ix.            Lookup tables

                                                     x.            Appropriate Cell Error Correction

                                                  xi.            Proper cell locking

                                                xii.            Attractive formatting

                                             xiii.            Properly labeled tabs

                                             xiv.            A coversheet with containing where your pivot tables, lookup functions, pivot charts

15.                        You must turn in the following

a.     Printouts of your worksheet

b.     Printouts of your paychecks

c.      Upload your file to Blackboard.