CIS 123: Excel payroll analysis

1. Create complex formulas
2. Use the VLOOKUP functions
3. Create a conditional format for a cell
4. Use the IF function
5. Use the AND function
6. Define names for cells and ranges of cells
7. Protect cells from being changed

Your task for this assignment is to calculate payroll numbers for an employee for one week. This payroll worksheet is not realistic. It assumes the individual will be paid exactly the same amount every week. Some shortcuts were made to simplify the project - such as limiting deduction and withholding options.

The purpose of this assignment is to use formulas and some features which are built into Excel. Although the workbook algorithm is not completely accurate, it does give reasonable rough estimates of what is earned and what is owed in taxes. One interesting feature of this particular workbook is that it ends up showing what the employee effectively earns per hour in take home pay and what the employer cost per hour is for the same employee (excluding other overhead costs such as training, general liability insurance, management costs, etc.).

This problem uses a number of advanced features of Excel and it is expected that there will be questions. Keep in mind that the CIS 123 website has a FAQ sheet for using Excel, there will be a demonstration of Excel in one of the optional class sessions, and there are many tutorials on how to use specific features in Microsoft Excel on the Web. This is particularly useful if you want a question answered quickly outside of class time and you don't have an Excel reference at hand.

There are sample pictures linked at the end of this document that show what most of the project should look like when you are finished. Keep in mind that many cells must be formulas and that typing in numbers to match a picture instead of using a formula does not fulfill the requirements. You probably want to have the sample Payroll worksheet and sample Withholding Tables worksheet viewable as you work through this project.

Your personalized starting document is available here.

Do NOT work closely together or copy or share work with other students. The penalties can be quite severe. You are welcome to ask all the questions you want and to seek help from the instructor or LSC tutors.

Each student will get their own Excel workbook as a starting document ready to be modified. Use the document sent to you since all of the documents are slightly different. If you turn in a document that is not the one sent to you, then it will be awarded 0 points.

Point values for each requirement are listed in parentheses at the start of each requirement.

Remember that you are encouraged to ask the instructor questions via Desire2Learn email and in class.

1. Sheet1/Payroll: where the payroll is calculated
2. Sheet2/Tables: contains withholding tables

1. (4) Define names for withholding tables, hourly rate, and hours.
   1. On the Payroll worksheet, define the name Hourly_Rate for the cell containing the employee's hourly rate (B5)
   2. On the Payroll worksheet, define the name Hours for the cell containing the number of hours worked (B6)
   3. On the Tables worksheet, define the name Single_rates for the table containing the single federal withholding data (A3:D9). The heading above the table should not be part of the defined name's range.
   4. On the Tables worksheet, define the name Married_rates for the table containing the married federal withholding data (A12:D18). The heading above the table should not be part of the defined name's range.
   5. Note: If you want to make life easier for yourself while typing in the formulas, you probably want to go ahead and define names for all the cells which will have values in them in columns B and E on the Payroll worksheet. This will make debugging easier also since the formulas will then contain names, such as "Gross Pay" rather than cryptic cell references, such as "B20",
2. The requirements that follow all refer to the Payroll worksheet.
3. (3) Set up conditional formatting for the cell containing the marital status (B4). The cell should automatically get a red background if its value is not "S" or "M". You might want to check out the Excel FAQ on the course website if you are having problems with this.
4. The constants section will remain as it is. The input section will be where we enter data to check out our model. The Calculations section is where you have to enter in formulas to make the model work.
5. Calculations section formulas
   1. (2) Regular hours (B16): if hours is greater than 40, then 40, else hours
   2. (2) Overtime hours (B17): if hours is greater than 40, then hours - 40, else 0
   3. (1) Regular pay (B18): regular hours * hourly rate
   4. (1) Overtime pay (B19): overtime hours * hourly rate * 1.5
   5. (1) Gross pay (B20): regular pay + overtime pay
   6. (1) Withholding allowance (B21): federal exemptions * weekly exemption
   7. (2) Employer Social Security (B22): if gross pay > max social security / 52, then social security employer pct * max social security / 52, else soc security employer pct * gross pay
   8. (2) Employee Social Security (B23): if gross pay > max social security / 52, then social security employee pct * max social security / 52, else soc security employee pct * gross pay
   9. (1) Employer Medicare (B24): medicare employer pct * gross pay
   10. (1) Employee Medicare (B25): medicare employee pct * gross pay
   11. (2) Medicare penalty (B26): if gross pay > medicare penalty cap / 52, then medicare penalty tax * (gross pay - medicare penalty cap / 52), else 0
   12. (1) Taxable amount (B27): gross pay - withholding allowance - addl pre tax deductions
   13. (3) Fed WH Base Amount (B29): if marital status is "S",
       then vlookup taxable amount as a range in the Single_rates table and return column 2,
       else vlookup taxable amount as a range in the Married_rates table and return column 2
   14. (3) Fed WH Base To Pay (B30): if marital status is "S",
       then vlookup taxable amount as a range in the Single_rates table and return column 3,
       else vlookup taxable amount as a range in the Married_rates table and return column 3
   15. (3) Fed WH Percent (B31): if marital status is "S",
       then vlookup taxable amount as a range in the Single_rates table and return column 4,
       else vlookup taxable amount as a range in the Married_rates table and return column 4
   16. (2) Federal withholding total (B32): Fed WH base to pay + (taxable Amount - Fed WH base amount) * Fed WH percent
   17. (3) Illinois tax (B33): Illinois tax rate * (gross pay - (Illinois line 1 allowances * 2100 + Illinois line 2 allowances * 1000) / 52)
   18. (1) Worker's Compensation (B34): gross pay * worker's comp insurance rate
   19. (3) Employee net pay (B36): gross pay - employee insurance - addl. pre tax deduction - post tax deductions - employee social security - employee medicare - medicare penalty - federal withholding total - Illinois tax
   20. (3) Employer total (B37): gross pay + employer insurance + employer social security + employer medicare + worker's compensation
   21. (1) Effective rate employee (B39): employee net pay / hours
   22. (1) Effective rate employer (B40): employer total / hours
6. (3) Protect ONLY the cells containing data and formulas in the Constants and Calculations sections (E4:E13, B16:B40). You might want to check out the Excel FAQ on the course website if you are having problems protecting only specific cells on a worksheet. Use the password: payroll. This step has to be done AFTER you get all of the formulas entered and working. You can test to see if this step is working by trying to change the contents of cells B4, B16, and E4. You should be able to change the value of cell B4, but not E4 or B16.

Here are some test cases you can use to test your model. Enter the values from a row of the table below into the Input Section and see if your values for the employee and employer effective hourly rates at the bottom of your Calculations section match the values in the table highlighted in blue.

Mar. Stat	Rate	Hours	Fed Exemp	Ill Line 1	Ill Line 2	Employee Ins.	Employer Ins.	Pre tax ded	Post tax ded	Employee eff rate	Employer eff rate
S	25.00	40.00	1	1	0	20.00	100.00	0	0	14.55	30.18
M	125.00	45.00	2	1	1	120.00	0	200.00	350.00	65.02	141.02
M	12.50	35.00	2	1	1	35.00	125.00	0	0	9.33	17.41

1. Sample 1 Payroll worksheet
2. Sample 2 Payroll worksheet
3. Sample 3 Payroll worksheet
4. Withholding tables worksheet
5. Excel FAQ sheet