If you haven't done so already, download the Billings and Job Cost Reconciliation ("R1") spreadsheet from the previous lesson!
As you'll see, columns C and D on the spreadsheet include general job information, job name/number and status. Job names/numbers should come directly from your internal job schedules, which can be directly pasted on the spreadsheet from system reported reports but you may need to do a little more digging to determine the status of the projects. Most clients I've seen have a separate spreadsheet that lists the status of jobs, which I would then incorporate into the reconciliation. We're interested in the status of the jobs because the information on this reconciliation will help us build the WIP schedule and the completed jobs schedules.
There are three designations for the status of jobs, 0- Open, 1- Closed, 2- Closed in the prior year.
0- Open: This category should include jobs that still have costs to be incurred. If the estimated cost of the job is $100,000 and $95,000 of costs have been incurred, the job is "open" or in progress. Another example would include a job that is complete but not fully billed. Some people may disagree and with this but I think it offers an accurate picture of the job.
1- Closed: As expected, this category includes jobs that are finished. All of the costs have been incurred, the punch list is completed and it's 100% billed. This doesn't mean that all of the money has been collected on the billings, just that it's all been billed and the contract has been satisfied. It's expected that there will be no more significant costs on these jobs.
2- Closed in the prior year: Picking up where we left off above, if a job was presented as closed in the prior year, you don't want to show that it was closed again this year. It can technically be closed only once. But! It is highly likely that you will incur costs or even send out a small bill after the job is closed so we'll want to designate as a "2" for tracking purposes. Hopefully, it's not too significant, "significant" being relative to your overall financials.
Now, how we input the following information depends on the software you're using. Since most of you out there use Quickbooks, I'll use Quickbooks as an example. If you have questions about your software reports, feel free to reach out to me!
So you'll want to run the P&L by Job Summary from 1/1/X1 through 12/31/X1 from Quickbooks to get started. This report should include all of your billings for the said time period which will be included under column E. However, we want billings from inception to date for the WIP calculation and reporting purposes, so you'll want to include all of the prior year billings under column I, which will calculate the total amount billed on the project under column J. Columns F & G are reserved for adjusting journal entries that may be outside of the billings subledger. Sound like technical accounting non-sense? Those columns may be more relevant if you're the outside CPA, so we can skip getting into further detail on here.
Reconciliation: Now that we have the billings down, we can move to the account balances from rows 46 through 48 under columns C through E that we need to reconcile to. Chances are you have a construction income or revenues account on your trial balance where all of your billings are recorded. The balance in this account should agree to the total billings discussed above. It's very likely that it may not agree exactly, but hopefully it's not off by much. You may have had some billings that weren't tied to a job in Quickbooks and therefore don't show up in the P&L by Job Summary, which is fine. Hopefully the difference calculated is minimal!
Note: The above example assumes that you have a separate account for over/under billings. If you have one account for billings and over/under amounts, the reconciliation process will look differently but the concept is still the same! I would identify the billings for the current year and identify what the prior period over/under effect so we keep them separate.
See you in the next lesson where we talk about reconciling job costs!