Skip to content

Crack That WIP – 8 Dangerous Data Entry Errors That You May be Making

Crack That WIP – 8 Dangerous Data Entry Errors That You May be Making

During the recent Remodelers Summit in Minneapolis I gave two presentations on the finer points of WIP – or more specifically what I’ve noticed over years of studying the nuances of this incredibly vital and complicated tool. The majority of the information is relatively straightforward – but a few problems recur in many RA Member’s WIP.

And most people don’t even know their WIP is wrong! IF the WIP is wrong the journal entry is wrong, the profit/loss and the balance sheet are wrong. WRONG WRONG WRONG!

Here’s the test: complete the Margin Variability Chart (Download Here if you don’t have it) which compares gross profit margin month to month over the space of the fiscal year. Below is what it should look like: gross margin month to month within 2% points, plus or minus, from the budgeted yellow line.

However below is what it often looks like – the technical term is “all OVER the place!”

Your question might well be: why does it matter if your gross margin varies month to month if the year to date is close to the annual budget? In the case in the image above, it is – 29% average over 8 months.

It matters for two important reasons: significant fluctuations indicate a ‘systemic’ problem with the WIP process AND it makes your monthly budget reporting useless. Have you ever looked at the budget to actual report for a given month, known something was really off and put it aside perhaps never to use this important tool again?

[notification style=”success” font_size=”14px” closeable=”true”] Do you want to learn more about this? Judith is one of our key speakers at the Master Your Remodeling Business Workshop coming up in January! CLICK HERE for more information. [/notification]

So, let’s figure out why the WIP might be incorrect: after more than 30 years of using the WIP calculation and really digging into it the last 15, here are 8 errors in data entry that occur over and over again.

In order of most dangerous:

  1. Inaccurate cost to complete – this calculation sets up the over/under billing journal entry which flows to the profit/loss and balance sheet. If this number is wrong the journal entry is wrong as well as the financial statements.
    • Do a detailed line by line review of all larger jobs at 40/60/80% complete;
      • Before 40% you probably don’t know how the job will turn out; after 80% there’s probably little you can do to change the outcome.
      • Bring the job lead together with the job cost accountant so both know the costs included to the end of the month compared to % complete by site visit.
      • Enter internal change orders when you know you’ll either save money or spend more money on a line item but can’t bill the client.
  2. Actual labor rate not used in estimate – if the labor rate includes ‘hidden gross margin’ making the estimated labor rate higher than actual the % complete calculation for the job is incorrect making the earned revenue incorrect.
    • Complete the labor burden calculator ((on the RA website) for each field employee and compare to actual costs for that employee.
    • Update payroll calculations to bring the costs close (again within 2% +/-) on the job.
    • Review estimated vs. actual labor HOURS as well as dollars.
  3. Not tying WIP spreadsheet into accounting data – because the WIP journal entry changes the accounting reports it is essential that the data used to produce the WIP spreadsheet has a one to one correlation.
    • Set up a Memorized Report in your accounting for the jobs on WIP and enter the Actual Costs and Invoiced to Date on the Spreadsheet in exactly the same amounts.
    • Proof the TOTALS from the report to the spreadsheet. They should be identical.
  4. Incorrect prior year end WIP – if WIP is wrong at the end of the prior year the reversal into the New Year produces an incorrect starting line against which to calculate on-going monthly revenue.
    • Make SURE your WIP for year end is correct – if it’s not the entire new year will be incorrect to the degree of the year end reversal.
  5. Jobs costs or client invoices entered AFTER WIP is complete (or in progress) – if you follow the rules in #3 above you can always go back to any month to be sure amounts continue to be identical from the accounting report to the WIP spreadsheet.
    • Don’t enter, or allow anyone else to enter, any job related costs or income BACKWARDS to the month for which WIP has been completed.
    • Set up a closing date password when developing the Memorized Report to be used for the WIP spreadsheet and don’t allow anyone else to enter backwards without your knowledge.
  6. Change orders not correct – the original contract and original budget amounts on the WIP spreadsheet are straightforward: both come from the signed contract and never change over the course of the job. However the final/revised contract and budget are changed by Change Orders.
    • Enter change orders with to both the contract price and the budget.
    • Proof the gross margin for change orders to be sure it makes sense.
  7. Changed formula cells – many people use the same WIP worksheet over and over and sometimes enter ‘zero’ in a formula field or over-write the formula …. Thereby potentially causing significant errors in calculations.
    • Begin each year with a new WIP workbook for the year – download it from the RA website.
    • Enter the December WIP information from the prior year, update each tab for current months in the New Year.
    • Review the totals formulas monthly to be sure the formulas are correct.
  8. Keeping 100% complete jobs on the WIP – many RA members keep their completed jobs on WIP: this skews the ability to see the FINAL gross profit on WIP separate from the FINAL gross profit on completed jobs.
    • When a job is 100% complete it has been:
      • Income billed to the client 100% – regardless of when paid.
      • Expenses entered to the job 100% – regardless of when paid.
      • There is no over/under billing.
    • Leave it on for one more month after the following conditions have been met to be sure you’ve captured all costs.
    • Then move the completed job off the WIP to the Completed Jobs tab on the spreadsheet and post any subsequent expenses to Warranty.

NOW, you should have NO trouble smoothing out the gross profit line, month to month, your budget vs. actual reports for the company will be truly useful and YOU’LL BE HAPPY! Right?



Opt Out

I confirm that I am choosing to opt-out of the new Roundtables membership, henceforth referred to simply as Roundtables (“Roundtables”).  

I understand that my current membership will not change and will be henceforth referred to as RT Legacy, Roundtables Legacy, or simply Legacy (“Legacy”). I understand that by opting out I will not receive the additional benefits offered to Roundtables membership and will not receive the bonus PSCs.  I acknowledge that if I choose to upgrade at a later date, I will not receive any bonus PSCs above the standard issue.

By clicking below, you opt out and will be locked into the Legacy program for 2021. You will not receive an increase in dues, other than our standard inflationary annual dues increases. You will have the opportunity to opt in for the 2022 membership year. However, there will be no bonus given to future opt in cycles.

Please note that if you cancel your membership, you forfeit access to Legacy membership in the future. You may rejoin only as a Roundtables member.

Hey there!

Login To Come In

Member Support

Roundtables Application

Let's do this