In this article, you will get five Excel payroll exercises. You should have beginner to intermediate knowledge of Excel to solve all the problems. Moreover, you should know the following: 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 to find the solutions to the problems. The problems can only be solved with Excel 2010 or later.
Download Practice Workbook
You can download the Excel file from the following link.
Problem Overview
There will be 5 exercises related to the payroll in the Excel file. The first three exercises will use the same dataset, and there will be another dataset for the last two problems. The first dataset shows pay information for the month of November for about twenty employees at a multi-state company. The month had 19 working days, and the company pays its employees monthly by the hour. Additionally, overtime, paid time off (PTO), and regular working hours are provided. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems worked out. Additionally, the relevant values for each exercise are provided in the “Reference Tables” sheet.
- Exercise 01 Find Gross Pay: Working hours for twenty employees are given. Your task is to calculate the gross pay. The hourly pay is provided in the “Reference table” sheet. Additionally, the overtime pay is 1.25 times of the regular pay and the payment for the paid time off is the same as the regular pay.
- Solution: You will need to use the VLOOKUP function to find the relevant pay rates. The hourly pay is different for many employees.
- Exercise 02 Calculate Tax Deduction Amount: The employees work in five different states. The state tax rate is different for each. Use the tax rates from the “Reference table” sheet to find the tax deduction amount in this exercise.
- Solution: There are four kinds of taxes in this exercise. Apply the state tax using the VLOOKUP function.
- Exercise 03 Find Net Pay: In this exercise, your task is to calculate the net pay of the employees.
- Solution: Firstly, using the INDEX MATCH formula, input the benefits & other deductions. Secondly, find the total deductions. Finally, calculate the net pay.
- Exercise 04 Calculate Working Hours: Daily office entry time and exit time for an employee called “Ross Johnson” is given for the month of December. Your task is to find the time worked, late entry (in hour) if any, and overtime.
- Solution: Employees should enter the office before 10 AM, else they will be late. More than 8 hours of work is considered as overtime. Moreover, as we’ve given the durations in hour:minute format, you will need to multiply it by 24 to get the hour value.
The animated image below shows the formula to find the total hour worked.
- Exercise 05 Calculate Monthly Pay for an Employee: In the final exercise, you will calculate the actual pay for the employee for the month of December.
- Solution: If the value of the time worked is more than 8 hours, then you need to consider the overtime pay. For example, if an employee worked 9 hours, then that employee will get 8 hours of regular pay and 1 hour of overtime pay. Additionally, if an employee enters the office after 10 AM, then a penalty will be applied. Finally, there is a flat 12% tax applicable from the gross pay. So, actual pay = 0.88*daily pay. Lastly, add all the daily pay to find the total monthly pay.
The following image shows the solution to the first problem.
Conclusion
Thank you for reading this article. By completing these payroll Excel exercises, we hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!
Related Articles
- How to Draw Payroll Process Flowchart in Excel (with Easy Steps)
- Make Payroll 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)
