Why let the outside CPA have all of the fun? Download the Job Schedules spreadsheet so we can start.
One of the first things I do after rolling retained earnings is reconcile revenues and costs from the job schedules to the trial balance. Blah blah blah is all you may be hearing right now and that's perfect! That means we're going to learn something!
Whether you have QuickBooks, Foundation, Viewpoint, etc. you have system generated job schedules. Bridging the gap between those reports and the trial balance is key! Let's start the lesson according to the tabs on the spreadsheet.
GP Schedule:
This tab accumulates the current year revenues earned and contract costs from jobs in progress and completed jobs (on the subsequent tabs). These totals should match what you have on your income statement as total contract revenues and costs. Now you may be one of the lucky ones that has financial statements where these don't match. But if yours does match, you'll want to look at row 15 and notice two cells highlighted in blue, these are the amounts that represent contract revenues and contract costs on your income statement. There's are formulas below that will tell you if you're in the clear.
WIP:
Now you're big time. You have a work in progress (WIP) schedule! Check out the previous lessons on the WIP schedule on the home page lessons 1 through 4. For now let's ignore columns X through AI. Those columns serve a different purpose, which I'll cover in a separate lesson! You'll want to update the cells highlighted in blue.
Done filling it out already? Do your underbillings and overbillings agree to your balance sheet? They should. There could be multiple reasons why they don't but it's likely that the accounting software is running without a button and that's fine. Under and overbillings are usually adjusted to match your WIP schedule calculation using a journal entry. What is that entry???
Journal Entry: To adjust WIP
Balance Sheet: Underbillings
Balance Sheet: Overbillings
Income Statement: Over/Underbillings Revenue
The debits and credits will shake out as you calculate the difference between what your WIP schedule shows in comparison to what your balances are on your trial balance (balance sheet).
On to the next tab!
Completed Jobs:
Total revenues and total costs should represent the total contract price (billings to date) and costs from inception of the job, respectively. Now, under the prior year (PY) column you'll see revenues earned and cost of revenue, which represent the revenues earned and costs incurred from prior periods on the closed jobs. For example, if you're presenting calendar year end 2021 financials, you'll include revenues and costs through 12/31/20 in the prior year column. If you've been eating your Wheaties, you already have these amounts. They're on your 12/31/20 WIP schedule! Look under columns H and I on the WIP tab from the prior year!
Done!
Now what?
Go back to the first tab and see if it ties out! It's the end of the world if it doesn't! Just joking. All that means is that you're going to spend time troubleshooting and in the end you'll learn something new and great!
Questions? Reach out at info@thewipreport.com.