QuickBooks in Construction Accounting – Transfer Work in Process to Cost of Goods Sold

I want to thank two readers for asking about this issue. While reading Using QuickBooks in Construction Accounting they noticed that I failed to explain how to transfer the costs of construction to the profit and loss statement. In effect, how do you move these individual costs over to the costs of goods sold section of the profit and loss statement. Thanks John and Clarissa for the request.

Well, the first article related to using QuickBooks in construction accounting is pretty lengthy and covers the accounting perspective from the job costing viewpoint. The transfer of information is an accounting perspective and is designed to assist the owner of the construction company in analyzing profitability for the company for all combined projects. Thus, this article will cover some issues from the first article, then the financial accounting perspective and finally the step by step process to transfer the costs to the correct lines of information in the cost of goods sold section in the P&L statement.

Please understand, this is a long article to help you understand your accounting information. I tell the readers of my articles that I write to educate and assist in getting results from accounting, if you are looking for a quick solution, well, you will not find it here. This is the real world, and business activity is complex and it is important to get it right. There’s an old saying in technology: “Good data in means the maximum opportunity for good data out”, I don’t know who created the phrase, but I like the thought. Well, accounting software relies on the same principle, get the data into the system in a great format and you’ll have the best chance of getting good information out to help you make great decisions.

[do_widget id=black-studio-tinymce-6]

Review of Construction Accounting

The goal of construction accounting is generating a feedback loop for an owner or project manager to understand performance issues, possible estimating issues, or areas for improvement. The feedback loop is important in helping the owner or project manager improve their respective performance on the next job. Through continuous improvement, you will maximize profit for the company. That is what good business is all about.

In my first article, I explained that your goal is to organize the data into nine phases of construction. This is referred to as project accounting. The phases of construction are like steps in building the residential or commercial building. Below are my suggested nine phases of construction:

01     Project Management (paperwork)
02     Site Development and Landscaping
03     Foundation/Footer
04     Frame
05     Trades
06     Walls (insulation, sheetrock, trim, interior doors, painting)
07     Flooring
08     Cabinets (Kitchen cabinets, countertops, bathroom cabinets, appliances, tile work)
09     Extras (Decking, railings, sound system, exterior buildings, change-orders)

You compare the costs of each phase against your original estimate to identify issues in estimating or performance. This is achieved by coding each expense, whether materials, supplies, labor, subcontractors and other items to the respective phase using QuickBooks. In that article I explained how you use the item coding entry format to identify the job, the class of work involved, and the respective phase.

This is a form of cost accounting, sometimes referred to as project accounting. THIS IS NOT FINANCIAL ACCOUNTING. Cost accounting is designed to identify areas of improvement within the project(s). Financial accounting provides information in such a way to identify possible improvements for the company overall. When it comes to profitability, project accounting is the best tool, hands down! You will gain more insight into cost savings and revenue generation with project accounting than with financial accounting. Financial accounting cannot pinpoint in detail where you went wrong. Project accounting will identify to the detail where you are out of budget in construction.

OK, so we want to turn the project accounting information into financial information.

Financial Information

To appreciate your hard work means understanding the financial information presentation format of a profit and loss statement. This article explains how a profit and loss statement is formatted for a contractor Best Format of the Construction Profit and Loss Statement. In this article I convey the concept of direct costs involved in constructing a residential structure. This is the format illustrated from that article:

CAN-DO CONSTRUCTION
Profit and Loss Statement
Period Ending MM/DD/YYYY
Completed Contract Method or Percentage of Completion Method

Contract Revenues $ZZZ,ZZZ
Change Orders/Allowances/Addendums ZZ,ZZZ
Interest Earned on Contract Revenues ZZZ
Total Construction Revenues $ZZZ,ZZZ
Direct Costs:
Land $ZZ,ZZZ
Materials ZZZ,ZZZ
Subs ZZZ,ZZZ
Labor ZZ,ZZZ
Other ZZ,ZZZ
Sub-Total Direct Costs ZZZ,ZZZ
Direct Margin $ZZ,ZZZ

Notice how I group the costs. These groupings make sense; as a contractor you would want to know total land and material costs. In addition, how much are we spending on labor and subcontractors to do the work? How about all those auxiliary costs like permits, the port a potty, signage, even those pizzas and beer we buy for the guys for staying late one night to get a step completed. All those costs are grouped together into the five groups identified above.

Now I’m going to pause here for a moment and explain something else. If you remember in project accounting as we were entering data, we ‘class’ the entry to one of the major divisions of revenue for the company. Some companies do new construction and remodeling/additions work. As you enter the data, the entry is classed to one of your divisions. This way, we can prepare a report both in project accounting and in financial accounting for classes of work. The above example would look something like this:

XYZ, Construction Inc.
Profit and Loss Statement
Completed Contract or % of Completion Method
Date

  New Home Additions Total
Contract Revenues $ZZZ,ZZZ $ZZ,ZZZ $ZZZ,ZZZ
Adj’s/Allow’s/CO’s (ZZ,ZZZ)    (Z,ZZZ)   (ZZ,ZZZ)
Adjusted Contract Revenues ZZZ,ZZZ ZZ,ZZZ ZZZ,ZZZ
Direct Costs of Construction:
            Land ZZ,ZZZ                           – ZZ,ZZZ
            Materials ZZZ,ZZZ ZZ,ZZZ ZZZ,ZZZ
            Subcontractors ZZ,ZZZ ZZ,ZZZ ZZZ,ZZZ
            Labor ZZ,ZZZ Z,ZZZ ZZ,ZZZ
            Other   ZZ,ZZZ                     Z,ZZZ    ZZ,ZZZ
            Direct Profit $ZZ,ZZZ                      $Z,ZZZ $ZZ,ZZZ

If you are interested in learning more about class accounting and the multitude of advantages involved, read Class Accounting in Construction.

You can see from the example above the clear advantage of separating the construction company into divisions. It makes it easy to see the financial results and performance value associated with classes of accounting. Imagine if the material costs per dollar of revenue for additions is significantly greater than new home construction? Why? You would then compare material costs in the respective projects to find out why. But I digress from my goal here of teaching how to transfer these costs.

Back to transferring costs. Now you can see the advantage of grouping the major costs in order to gain a better understanding of the financial performance of the company. Grouping of costs allows you to compare your performance with other contractors or within your divisions (classes) of work. If you happen to be interested in what are good standards of performance for a contractor related to the direct margin line, I have written an article What is a Reasonable Profit in Construction? explaining the percentage value for direct profit desired.

The raw information is stored on the balance sheet in an inventory section known as Work in Progress or Work in Process (it doesn’t matter which one you use), I commonly refer to this as WIP. Within this control account are subsidiary identifiers labeled as projects (these are not separate chart of account type accounts, but are restricted to a customer name only). In each project account, you should have only the data associated with that particular project. If you mix the information, it makes it nearly impossible to separate the information for use in financial accounting. Regularly look at the ledger for your project accounts and make sure that the data in there belongs to that particular project. Remember that at the entry point of data in QuickBooks, we identify the phase via the item code, next we insert the explanation of the item, then the dollar value, the project identification (this puts the information into that WIP sub account), and finally the class of work (New Home, Additions, etc.). Remember all three identifiers serve a purpose. The item code identifies the phase of work for project accounting, the customer identification number codes our project to the WIP account, and finally the class code allows us to review reports in both project accounting and in financial accounting based on classes of work.

Once last thing before we move onto transferring data, note how the initial entry accumulates the data into the WIP account on the balance sheet? We do this in accounting as a function of both the completed contract and percentage of completion method. It doesn’t matter which one you are using, the key is that the dollar value associated with your entries are accumulating in this account. The purpose for accumulating the dollar value here is to get an accurate reflection on the profit and loss statement for financial purposes based on either method. If you are using completed contract method, once the contract is finished, you transfer all the costs and contract revenues over to the profit and loss statement at one time. If you use percentage of completion method, you transfer the costs and the corresponding revenue earned monthly or quarterly depending on your frequency of reviewing financial information.

I encourage those larger contractors (those with more than $10 Million per year in revenue) to do this monthly under the percentage of completion method. The reasoning is to identify issues sooner and resolve them quickly prior to engaging in another project.

Think of that WIP project account as a bucket filling up with information throughout the month. At the end of the month, it doesn’t matter how full the bucket is, we are going to dump that bucket into the pool which is our financial information. Most likely, you are running several projects, so we dump each bucket one at time into the pool to fill it up with information. In effect, we are going to zero out the respective project-accounts in the WIP section of the balance sheet.

Now that I have refreshed our memory on why we use cost (project) accounting, it is time to transfer the data to the profit and loss statement into those cost groups in the cost of goods sold section. This is easier than you think and involves three steps. The first step is to export the project data into a spreadsheet, the second step is to classify those project costs into the groupings (cost accounts) customarily found on the profit and loss statement, and the final step involves creating the actual journal entry to transfer the information. The section below explains this step by step process in detail.

[do_widget id=text-59]

Transfer of Data

Step 1 – Transfer Data to a Spreadsheet

This is the most difficult step in the process because most folks have difficulty keeping the information simple. The key is to limit the data in the QuickBooks report to just three fields of data. You need the project name, the memo section (helps you to identify the respective cost of goods sold account) and the dollar amount. To do this, go to your reports section of QuickBooks and click on ‘Accountant and Taxes’ or ‘Accountant’ reports depending on which version you are using. Don’t freak out, this reporting area only uses terms customarily used by accountants and CPA’s. It does not mean you can’t use these reports. It is my favorite section of QuickBooks but only because I have a lot practice using the software and I’m comfortable getting information the way I like to see it formatted. Once I explain what to do, you find it easy to do too.

Anyway, within this section is a sub section called ‘Transaction Detail by Account’. This is what we are going to use because we stored all this data into a single account called Work in Process or Work in Progress (WIP). When you click on this reporting format, limit the data range to your financial accounting reporting period, most likely last month. A report will pop up having all the data for all the accounts on your books. Thus, we need to customize the report. First, let’s go up to modify the report and click on the FILTERS tab. Once there, highlight the ACCOUNT filter in in that second column choose multiple accounts at the very top. A pop-up comes out asking you to choose your account. If you store all the data in one Master WIP account (which you should have), choose that particular account.

In that second column in that pop-up, only the WIP should be checked. We only need to look at this one account. Next, we are going to choose another filter. We want to choose an active project, so click on NAME and choose multiple names like we did with the account filter. Again, the pop-up will occur and you select the project we desire to get information on for this project transfer. We now have three filters we are working with in this report, the date filter which is customized to last month, the account filter with WIP selected and the name filter with our project name selected. Now click on the DISPLAY tab.

This is where we really limit the data. In the columns box in the lower left section are a bunch of checkmarks for columns in the report. We only need to check four columns: 1) left margin, 2) Name (project name), 3) Memo, & 4) Amount. Click OK and look at the report. There should be three columns of information (the left margin column is invisible and acts as a formatting aid). At the top of the report is the data range for the data and the title ‘Transaction Detail by Account’. The real title is ‘Transaction Detail – Project Name’) and so we can change this easily by going to the Modify Report button at the top again, click on the HEADER/FOOTER tab and in the title block, change the title to ‘Transaction Detail – Project Name’ where Project Name is the name of the selected project for this data transfer.

I would also memorize this report so that next month you can use this particular report and just change the date range. This makes it easy to retrieve the report in the future. Save the report as an accountant’s report with the title you just gave.

Some formatting suggestions, on the report click on those diamonds between the columns and spread the columns to see all the data, especially all the data in the memo section. We want to see all this because it will help us to determine which grouping in the cost of goods sold section we want to use. You may also want to include in your filter section, the item code which will help you select which cost group to transfer the data to. Instead of 3 columns, you’ll have four. If you are one of those detailed driven types and remember well the information you have entered, then you may not need the item column telling you which phase because you already know. On the other hand, if you have difficulty choosing the grouping for your transfer and need more help, go ahead and add the item identifier to your report and you’ll have a fourth column of information to assist you in the next step.

With the report up on the screen, review the information. Does it look right? Did it capture all the data you have entered during the past month? If not, it is possible that some costs were coded to the wrong project. Go find those cost entries and code them to the correct project and rerun the report. Once you are satisfied you have all the information it is time to export the data.

Click the ‘Export’ button at the top of the QuickBooks report. I would create a new Excel workbook, but next month’s report, export the information to a new tab in this Excel workbook. Name the workbook, ‘Data Compilation by Month WIP – COGS – Project Name’ and label the excel tab (at the bottom right click and hit rename) for the respective month. Store the file in your accounting folder for the respective month in the respective year. This acts as your supporting documentation for your journal entry.

Now you will see the report on an excel spreadsheet. Looks pretty huh? Well, I want to add a new column to the data. Go to the end of your information and in the row (approximately #4) where all the column titles are located, add a new column and label it ‘COGS – Group’, click the centering function up to so your codes in this column will be centered up. You may want to format the report and bold the column title and adjust its width etc.

The hard part is over, now onto the easy stuff.

Step 2 – Classify the Data into the Respective Cost of Goods Sold Accounts

Now starts the brain function of all this information. By now you are smart enough to know that when you buy certain things you know it as one of the groupings within cost of goods sold. It could be R land, Materials, Subcontractors, Labor or Other. I use the letter R for real estate for the land grouping as you don’t want to use the letter L twice. The reasons I highlighted the first letters is that these are your classification letters for each row of data in your report.

Just code the respective entry with one of these letters in the COGS-Group column. This is the thinking part. Use your head. Sometimes a particular entry has both components built into the entry. The most common one is the subcontractors. If you’ll remember in my first article I suggested you get your subs to separate the materials and their labor elements on their invoices. This way, if you used your memo section correctly you will have two line items for that particular sub with one being the materials and the other his labor components. The memo section guides you and now you see the value of a good memo entry when loading the data. Remember from above, good data in = good data out?

Once you have coded all the rows for their respective grouping to the cost of goods sold section of the P & L Statement, it is time to sort the data. For those who know how to sort the data, sort it on the COGS-Group column. For those who don’t know how to use sort read this next paragraph and I’ll explain this easy to do function of Excel.

Excel has a built in function called ‘Sort’. If you don’t know how, go to the help function and its takes less than 7 minutes to read and understand the sort function. It takes about 40 seconds to do the actual sorting of data. If the sort didn’t work, you simply hit your reverse key at the top, that’s the arrow pointing counterclockwise, and it goes back to original report. Try it again. The first time I did this, it took me about five tries before I finally read the directions and figured it out (I’m a guy and didn’t read the directions at the start).

Once you have sorted the data into the respective Cost of Goods Sold groups, insert two blank rows between the major groups. Sum the value of each group in the first empty row after each group. Do you see what I’m trying to do here? That is right, by grouping the rows of data and summing that group, I now have a dollar value for that respective grouping for that particular project for last month. Now I am ready to create my journal entry. Don’t forget to save the report. Make sure that all the sub-totals total to the actual original dollar value when you started out with this report.

Step 3 – Create the Journal Entry to Make the Transfer

Now for the really easy step to transfer the information; click on Company at the top and click ‘Make a Journal Entry’. Date the entry for the last day of the prior month so the data shows up in the correct accounting period. Give the entry a name, I suggest ‘WIP – COGS: Project Name’ for the entry. Debit one row for each of the cost of goods sold groupings per our compiled data from the Excel spreadsheet and insert the dollar value as summed in that spreadsheet for that particular group. In the ‘Memo’ column, insert the project name so when you have any questions as to the sourcing of the entry when looking at the financial reports, it makes it easy to see the project source.

The total should sum up to the total amount of the report and this entry is coded as one dollar value as a credit to WIP. There is no need to give a name to the entry in the name column. Actually, don’t do it! The reason is that if you do give the project name in that column, it will mess up your project accounting reports. The entry name identifies for you the respective project.

I do suggest using the class column as this will separate the dollar value in the financial report (see above class accounting report) and help you to reconcile in the WIP account at project’s end. Remember, for the general journey entry, do not use the ‘Name’ column for this entry and do use the ‘Class’ column to identify the division of work.

Notice that there is no need for a phase code here. With financial accounting we are not interested in the phase codes, they are used with cost (project) accounting.

Do this for all your active projects one at time. Don’t forget to memorize the transaction reports for each project to use in the future. Save your Excel spreadsheets to the accounting directory within the year and the respective months. Good data in equals good data out.

YOU ARE DONE!

Summary – Transfer Construction in Process Values to the P&L

There is a difference between cost (project) accounting and financial accounting. When we initially enter data for our construction projects, we enter the data to serve cost accounting. We have to compile the data into a financial format by exporting the data into a spreadsheet and code the data for financial reporting purposes. Once coded and summed for financial purposes, a general journey entry is created and the dollar amount is transferred to the correct group accounts in the cost of goods sold section of the profit and loss statement. Now we can review both project reports and financial reports and understand where the revenues are generated and where the dollars go. Act on Knowledge.

[do_widget id=text-59]