How to Input Payroll Using Mac Numbers (or excel or google sheets) and ADP
These instructions are after you have exported the xls sheet from Maid Central. There are 3 softwares you need to use are : Maid Central, ADP, and Numbers (or google sheets/excel)
1. Open the sheet in Numbers (or google sheets or excel) and add a couple more rows to the bottom.
2. Click on the top right corner of the first column.
3. Click “Convert to Header Column”
4. Click back in the top right corner of the first column a second time.
5. Click “Freeze Header Columns”. This will allow us to scroll through the columns without losing the view of the first column.
6. Scroll over to the column titled “Clock Hours” and click on the column header to select the whole column.
7. Scroll over to the far right and click on the format button (looks like a paintbrush) and select “Cell”
8. Click on the “Fill Color” and select a color. This will make it easier to see the columns we need to enter into ADP.
9. Follow the same steps for the Tips Column,,,,
10. Gross Wages….
11. And Reimbursements
12. Once all of those columns are colored, we need to add a couple of extra columns. We will do that by click on the little bars next to the last column and add 2 more columns.
13. Title the first column “Cell Phone” and the second column “Mileage”
14. Add a the dollar amount for the cell phone reimbursement. In this example we are doing $5. Continue that amount down for each tech.
15. Next in the mileage column, we are going to enter a formula that will subtract the cell phone amount from the total reimbursement amount.
16. The formula is ‘=reimubursements – cell phone’ and click enter for it to calculate.
17. Continue that same formula for each tech.
18. Next we are going to add 2 columns next to the “Gross Wage” by clicking the arrow on the top right side of column
19. Click “Add Column After”
20. Do the same steps a second time to add one more column.
21. We are going to create another formula. We need to multiply the clock hours by their base hourly rate (in this example, the techs base hourly rate is $17). In the column directly next to Gross wages, start the formula by clicking “=”
22. Then scroll over and and stay in the same row and click the cell in the “clock hours”
The formula will read: = ‘clock hours’ * 17
and press enter.
23. In the column next to that, we are going to do one more formula. This is going to calculate their “bonus”
In that cell, we are going to enter a formula that will subtract the hourly wage + tips from the Gross Wage.
24. The formula will read:
= ‘Gross Wage’ – ‘Hourly Wage’ – ‘Tips’ and press enter.
25. Continue this for all the techs. You can easily copy the formulas down by pulling the yellow dot down to all of the techs.
26. Type “*.095 [[Enter]]”
27. The next formula we need to create is for the workers comp rate. This is the actual rate but will be able to give us an estimate to how much it will be. In this example, the workers comp rate is 9.5%
In the cell next to the Total Gross wages, we will enter the follow formula:
= (‘Gross Wage’-‘Tips’)*.95
28. Log In to ADP Run.
29. Choose “I am an administrator/owner….”
30. Click “Run your Payroll”
31. Click “Admin Sign in” for Run
32. Log in with User ID and password.
33. Sign In
34. Click the ‘Run Payroll’ from the homepage
35. Now we are just going to enter the amounts from the “colored” cells we did above in the correct columns.
Regular Hours = Clock Hours
Bonus Amount = Bonus
CC Tips Owed = Tips
Mileage Reimb = Mileage
Cell Amount = Cell Reimbursement
36. After we enter the Regular Hours, Bonus, and CC Tips, we are going to preview the payroll.
37. We will want to confirm the amount in ADP matches the Gross Wage amount in the spreadsheet.
38. In this cell we want to add the WC amount to the payroll preview. We can do that by typing the following formula:
= ‘ADP Payroll Total’ + ‘WC total’ press enter
39. Back in Maid Central, we need to find the revenue for the week of the payroll. We need to go to the Revenue and Production Planning report.
40. Change the date to the date range of the payroll
41. Click ‘Update’ to run the report.
42. Back in our spreadsheet, we will enter the total from the revenue and production planning report.
43. Next to that cell, we will need to do another formula to fin out what our percentage of our revenue went to payroll. This amount should be 55% or lower.
The formula is:
= (‘ADP Payroll total’ – ‘WC amount’)/ ‘Total Revenue from the week’
44. Click here
45. Back in ADP, we are going to click ‘Previous’
46. Fill the amounts from the spreadsheet for the mileage and cell amount.
47. And then ‘Preview Payroll’ one last time
48. Repeat the same steps from above where we are going to create a formula to add the total payroll to the WC amount
49. And finally one last formula to see what our total payroll, including reimbursements, are to the revenue. The formula is:
= (‘Total ADP amount’ – ‘Tips’) / ‘Total Revenue’
50. Finally, back in ADP, if everything looks correct, we will submit the payroll.
51. Scroll to the bottom and click “Approve”
52. You will click “Approve” a second time to confirm.
53. Once you get the Success! screen, you can click “I’m done”
54. Last steps are back in Maid Central, we need to close payroll.
We will do that by going back to the payroll summary and selecting the payroll dates and click “Update”
55. Click ‘Close Payroll’
56. It will ask you to confirm and click “Confirm”
You must close payroll as soon as you submit to ADP. Doing this will update all reports in Maid Central as well as stop any late tips from being added when we have already submitted it in ADP.
