This article requires beginner to intermediate knowledge of: IF, INDEX, MATCH, SUM, and VLOOKUP functions; how to calculate time duration in payroll, the Fill Handle, ways to AutoFill the formulas, custom cell formatting, and adjustment of background cell color features.
Download Practice Workbook
Download the Excel file here.
Problem Overview
The first dataset shows pay information for the month of November about twenty employees at a multi-state company. The month had 19 working days, and the company pays its employees hourly on a monthly basis. Overtime, paid time off (PTO), and regular working hours are provided.
Exercise 1 – Find the Gross Pay.
You want to calculate the gross pay. The working hours and the hourly pay are provided in the “Reference table” sheet. Additionally, the overtime pay is 1.25 times the regular pay and the payment for the paid time off is the same as the regular pay.
Solution: Use the VLOOKUP function to find pay rates. The hourly pay is different for different employees.
Exercise 2 – Calculate the Tax Deduction Amount.
The employees work in five different states. The state tax rate is different. Use the tax rates from the “Reference table” sheet to find the tax deduction amount.
Solution: There are four kinds of taxes in this exercise. Apply the state tax using the VLOOKUP function.
Exercise 3 – Find the Net Pay:
You want to calculate the net pay..
Solution: Use the INDEX MATCH formula, input the benefits & other deductions. Find the total deductions. Calculate the net pay.
Exercise 4 – Calculate Working Hours.
The dataset below, contains daily office entry time and exit time of “Ross Johnson” in December. Your task is to find worked time, late entry (in hour) if any, and overtime.
Solution: Employees must enter the office before 10 AM. More than 8 hours of work is considered overtime. Duration is in hour:minute format. Multiply it by 24 to get the hourly value.
The GIF below shows the formula to find the total worked hours.
Exercise 5 – Calculate the Monthly Pay of an Employee.
Calculate the actual pay of the employee in December.
Solution: If the value of worked time is more than 8 hours, consider overtime pay. If the employee enters the office after 10 AM, a penalty will be applied. A flat 12% tax is applicable to the gross pay. The actual pay = 0.88*daily. Add daily pay to find the total monthly pay.
The following image shows the solution to the first exercise.
Related Articles
- How to Draw Payroll Process Flowchart in Excel (with Easy Steps)
- How to Calculate Payroll Overtime with Formula in Excel
- Create Payroll Calculator in Excel (with Easy Steps)
- How to Make a Payroll Accrual Calculator in Excel
- Make a Payroll System in Microsoft Excel with Payslip
- How to Do Payroll Reconciliation in Excel (with Easy Steps)
