Several years ago, I had a client where we needed to allocate payroll costs across all the jobs they worked on. They had an external time tracking system, so I created a template to just paste their information into a page on a spreadsheet and some other information. The result was a preformatted sheet to create journal entries in QuickBooks Online using Transaction Pro Importer.
Since then, several people have asked for the template, so I have finally decided to share it here. I’ll provide screenshots here on how it works and if you would like to download the template yourself, you can buy it here for $5. Note – I won’t provide any support for the template or any refunds. It’s $5 – either take a shot or not. I’ll try to share as much about it here so you can make an informed buying decision.
There are two input tabs – (1) Job & Employee data input and (2) The timesheet.
Job & Employee Data Input
As you can see, on this screen, you enter all the jobs, admin accounts, and employee information.
To start, you’ll want to enter the Pay Period, the date of your Journal Entry, and the offsetting payroll account. In my example, I would always enter the initial payroll entries to an “Undistributed Salaries” expense account and then I would verify that the account was zero at the end of the month, so that I would know that I had allocated all of the salaries.
For the jobs, you’ll want to enter the information exactly how you would find it in QuickBooks Online. If you have a subcustomer, you’ll want to enter it like customer:subcustomer. This will allow Transaction Pro Importer to match the fields properly on the import.
In my situation, not all of the time was allocated to jobs, so there were other accounts that we had to deal with, like booking the time to an Admin account or a PTO account. I have allowed space for that in this template. Either use the account title or account number in Column B here depending on your import settings in Transaction Pro Importer.
Finally, there is this area for employees. You’ll want to enter their names, their hourly rate, and the chart of account where you want to book their time. My example was a software engineering firm, so there we had to split time to different job titles – Data Analysts, Software Engineer, Engineering Manager, etc. Each of these titles was a different expense account. You can always enter the same account number for each employee if that is your situation.
You’ll note that I have provided room for 15 jobs, 4 “Other Accounts” for time, and 10 Employees. If you want room for more employees, you can always split up your time sheets and do this for each grouping of 10 employees.
This is the sheet where you enter in all the time per job. You’ll just want to enter data in the blue cells. Note that once you fill in the Employee and Job info on the first sheet, that will all flow into this sheet (and the rest of them). This is just the first example of the automation I have put into the sheet. If you buy the template, these numbers will already be preloaded in the sheet so that you can see how all the links flow through the rest of the spreadsheet.
Single Employee Journal Entry
Once you have entered all your data, you’ll see that all of the individual employee sheets have been populated. Here is what it will look like:
And, here is the explanation of each column/area. Remember, all of this data is pre-populated based on your data entry sheets, so there is no need to adjust anything except for the blue cells.
- Is the name of your journal entry. In this template, there is one journal entry for each employee. The “RefNumber” will be what you entered as the “Pay Period” and “Employee 1” in the job and employee data entry sheet. I like to have that as a reference on the journal entry so you can easily find what you want when reviewing transactions in QuickBooks Online.
- This is the “Journal Entry Date”.
- Account is the labor account that we are allocating all of the time to.
- The amount field takes the hourly rate multiplied by the number of hours that have been entered on the time sheet. The sum of the journal entry should be $0 because we are moving wages from Undistributed Salaries to those other accounts.
- The Entity is the job that we are allocating the costs to.
- I’ve prefilled the memo line to list the employee then the number of hours that is being accounted for.
- I’ve left the class field blue so that you can enter in your own classes here if you want to.
- Hours is the total of what was entered on the timesheet sheet.
- This is just a visual check for you. I am pulling in the rate and employee name that you entered on the first sheet.
This is your overall journal entry that will be imported into QuickBooks Online using Transaction Pro Importer. But, remember there is room for 10 employees, so there are 10 separate sheets for each employee. I have also combined all the data into one sheet that can be used for a mass import – the JE Upload Sheet. Here is a screenshot of what that will look like.
Summary and Check
Finally, since I am one of those Type A accountants, I have included a summary sheet so that you can check that everything is working correctly. So there is a place for you to enter the data from your payroll report to make sure you have picked up all the dollars that need to be allocated.
I also included a check to make sure all the formulas are working correctly between the timesheet sheet and each individual employee sheet.
With these two checks, you should be able to pinpoint any issues with the template if you run into any.
So, this is my very detailed explanation of my job costing template for QuickBooks Online using Transaction Pro Importer. I would advise that this be used by someone that is very familiar with how job costing works and familiar with Transaction Pro Importer.