You do want to know your gross margin every month in 2016 – right? That’s a critical metric to watch as it defines your sales success and production efficiency: raise your rates and your margin goes up, increase labor productivity and margin goes up. Good stuff!
Now, IF you want the very most accurate gross margin % every month in 2016 you have to start with an accurate WIP (aka over/under billings) in December. That’s because jobs open in December flow into the New Year bringing with them accumulated costs and invoices to date as well as accumulated over/under billing amounts. NO MATTER WHAT ANYONE SAYS you must carry those jobs forward into the New Year to get an accurate gross margin going forward.
NOW – it’s not any harder to do an accurate December WIP than any other month of the year – it’s just MORE IMPORTANT.
SO –here’s how:
1. Set up a QB or Sage Memorized Report titled “WIP Dec 2015”
2. From Reports/Jobs-Times & Mileage/Job Estimates vs. Actuals Summary
a. Enter the END date 12/31/15 in the “TO” date cell
b. Customize and remove the “$Diff” columns in both Act. Cost and Act. Revenue
c. Customize and from the Header/Footer tab change the “Title” to read “WIP Dec 2015”
d. Customer and Filter for ONLY WIP jobs – if correct Job Status has been applied to your jobs and updated as the jobs flow through the system then choose “In Progress”
e. IMMEDIATELY you will see 2 very important things:
i. Are the Job Status markers correct for the jobs you expected to see?
ii. Is the QB information correct – in this example Bilbo Baggins’ job needs some clarification – in a BIG way.
3. Now – correct in QB and then translate the information to the RA Excel WIP Workbook on the December 15 tab
4. Proof the information between the two:
a. Est. Cost (in QB) = Current Job Cost Budget (column G)
b. Act. Cost (in QB) = Actual Cost to Date (Column H)
c. Est. Revenue (in QB) = Current Contract Price (column D)
d. Act. Revenue (in QB) = Total Invoiced to Date (column V)
5. When they match – column by column – you’ve finished the easy part of calculating your year end WIP (over/under billings)
6. Now you can begin the difficult part of the WIP calculation – the Estimated Cost to Complete
a. From Reports/Jobs-time & Mileage/Job Estimates vs Actuals DETAIL pull a report for each job where the Actual Costs to Date are significant – in this example you don’t need a Cost to Complete analysis on Bilbo Baggins’ house as there are so few costs.
b. Drop this report into Excel and makes notes on EVERY LINE ITEM – will you spend more to finish the job or have you saved this amount. From this report you can determine a BEST GUESS of how much the final project will cost.
Don’t just assume the numbers are right – this is so important at year end that it’s worth the time to analyze each job which has significant costs to date in detail. You’ll get better at this over time.
c. THEN make the journal entry in QB shown at the bottom of the Excel spreadsheet
BUT you’re NOT DONE YET – remember to REVERSE every WIP journal entry (click the “reverse” button and QB will do it automatically.
NOW you’re done with WIP for year end and set up for a more accurate gross profit calculation in 2016. Good work! Keep it up ….
Members, DOWNLOAD the new R.A. WIP Workbook for 2016 from the University website – this allows you to keep all monthly WIP in one workbook for the entire year AND see the all-important December year end 2015 WIP in the same place.