Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

QuickBooks and Job Costing - Tips and Tutorial

by
7th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

Recently we've had a lot of discussion on AccountingWEB about the best software package for job costing. QuickBooks got a lot of votes, but our IT Zone consultant editor David Carter found it difficult to get out a simple listing of all costs from all sources. After advice from readers, he's been able to write a custom report in QuickBooks that gives him at least some of the detail he's looking for

In the discussions of the various packages so far, few members have much time for Sage Job Costing ('about as useful as a chocolate fireguard,' says Neil Eglintine). TAS Books has some supporters but its Tag codes can only be applied to the invoice as a whole. So if you have a supplier invoice that relates to more than one job, TAS can't handle it.

It seems clear that if you need a good, cheap job costing package, then there's really only one game in town - QuickBooks.

Theoretically, QuickBooks should be ideal for job costing as it can capture so much detail at both expense and item level when you enter a purchase invoice. However, I have to confess that my first look at Job Costing in QuickBooks left me completely baffled as to what was going on. And if the great expert is baffled, what chance has anyone else got? So what's the problem?

QuickBooks is undoubtedly a brilliant package. However, I am not sure whether any of the people who wrote it has ever actually worked in an accounts office. It contains some wonderfully sophisticated features, but the bread and butter basics often seem to be neglected ' daybooks is a notorious example.

I suspect that when they had to incorporate Job Costing the QuickBooks designers read up a book on accounting and learnt all the theory about recharged costs adding up to cost of sales, unrecharged to work in progress, and so on. So Job Costing in QuickBooks is built around the principle that every item of cost is going to be recharged to the client through the sales invoices.

But whenever I've been involved in costing systems, nobody has given a hoot about itemised recharging through sales invoices. Instead the purpose of the exercise is to capture every item of cost and make sure that it gets allocated to the right job.

At the end of the day, users simply want to print off a report for a job listing each item of cost from whatever source ' purchase invoices, timesheets, stock issues etc ' with a total at the bottom. And even if they do recharge the costs in an itemised sales invoice, it's such a fiddly business that they often prefer to raise the invoice in Word or Excel rather than in their accounts package.

So it seems to me that a large part of QuickBooks Job Costing falls into the 'highly sophisticated but no-one ever actually uses it' category, which makes it a lot more complicated than it needs to be. Hopefully, this series will help you identify the 30% of features which are really important and avoid being confused by the 70% of features which aren't.

In this article we'll consider the question that started it all off ' how do you get a report out of QuickBooks that lists all the expenses incurred on a job?

Most of what follows is based upon responses from readers to that article How Good is QuickBooks at Job Costing?. Many thanks in particular to Chris Fitzgerald, Nicholas Myles and John Fletcher for pointing the way.

Getting started
1. Start by going into the QuickBooks menu: Reports'Jobs, Time and Mileage (I'm using QuickBooks 2004). There are two key reports in the list ' Profit and Loss by Job and Unbilled Costs by Job.

As John Fletcher points out, after you have posted all your entries, running the Profit and Loss by Job report will provide an excellent check, because the last column is "No Name" and you can check here that there aren't any postings unallocated to jobs. (Profit and Loss per Job ' make sure you set the Columns' at top right to Customer:Job)

Chris Fitzgerald comments: "It is unfortunate that there is no report specifically for what you want but why not use the Unbilled Costs by Job as a basis and use the excellent report adjusting features to save a new report with the billing status changed to All? This can be called a Project Detail Report and be available for printing any time in the future."

"I hope that this works for you and any other Sage users who have not yet worked out that QuickBooks is God's accounting system!!"

To follow Chris' advice and create this new report, follow the steps below. If you are not familiar with the report writer already, this brief tutorial will give you an opportunity to see why QuickBooks' users get so enthusiastic about it.

Tutorial: How to create a new Project Detail Report out of the Unbilled Costs by Job Report

1. From the menu: Reports'Jobs, Time and Mileage.

Start up the Unbilled Costs by Job report. The report displays unbilled costs for all jobs.

Click the Modify Report button at top left.

2. You are in the Display tab. In the Columns box at bottom left some items are ticked. Go through and tick these fields as well (the Item Description to Sales Price fields are only relevant if you analyse some of your purchase invoices as Items. If you only analyse them as Expenses, don't bother):

  • Num (the supplier's invoice number)
  • Item
  • Item Description
  • Qty
  • Unit
  • Sales Price

    Note: the Name field is very important as this is the field that holds the name of the job. However, you don't need to select it this time as it is printed automatically on this report.

    3. Now click on to the Filters tab at the top. In the Filter box at top left, find Billing Status. Tick it.

    Change the Billing Status from Unbilled to Any

    4. Now click on to the Header/Footer tab at the top.

    Change the Report Title (second from top) from Unbilled Costs by Job to Project Detail Report

    Click OK. You are returned to the report but the title is now Project Detail Report.

    5. You can widen or narrow the columns if you wish. Widen the Memo column by clicking on the diamond to the right of the column heading, hold down your mouse, drag it right, release.

    6. To retain this report format permanently, click on the Memorise button at the top. The memorise report screen appears with the name and asks if you want to save it a memorised report group. Ignore this and click OK.

    7. Close the report.

    8. From main menu, go to Reports'Memorise reports. There is our Project Detail Report in the list. Run the new report and see the results on the screen.

    How to print a report for one job
    The report displays costs for all jobs, but you only want to see one. You can tell QuickBooks to automatically offer the Modify Report screen at the beginning of a report, so that you can select the job you want.

    1. To activate this option, pull down the main menu option: Edit'Preferences.

    In the left hand column of icons click onto the Reports and Graphs icon.

    At bottom left, tick the 'Display Modify Report window automatically" box. OK

    2. Now try running the report again.

    This time the Modify Report screen appears first. Click onto the Filters tab at top.

    In the left Filter column, tick on Name.

    In the Name box, change All Names to the name of the job you want by picking it from the drop-down list.

    Click OK at bottom. Now the report shows just the one job.

    End of tutorial

    But more work is needed
    This Project Detail Report is a good start, but unfortunately it's not sufficient as it doesn't print costs from ALL sources other than bills. The full report needs to pull together costs from purchase invoices, time sheets, stock issues, mileage expenses, stock assemblies, nominal journals and anything else. Personally I would also like it to include sales invoices and purchase orders.

    To get this full report, we are going to have to think again. Anyone got any suggestions?

  • Replies (4)

    Please login or register to join the discussion.

    avatar
    By David Carter
    19th Oct 2004 22:49

    Hi Alex
    At last someone has provided me with the material for part 2!

    I'm at Softworld this week but will take a look at your material next week. Thanks once again.

    Thanks (0)
    avatar
    By ablakey
    14th Oct 2004 17:49

    QuickBooks can do most of what you are after
    In response to:

    "This Project Detail Report is a good start, but unfortunately it’s not sufficient as it doesn’t print costs from ALL sources other than bills".

    To get information from all the sources needed you can use a combination of a few reports:

    time sheets - Time by Job Detail

    mileage expenses - Mileage by Job Detail

    purchase invoices - Custom Project Detail Report
    stock issues - (if you mean stock adjustments) - Custom Project Detail Report
    nominal journals - Custom Project Detail Report
    sales invoices - Custom Project Detail Report
    purchase orders - Custom Project Detail Report

    stock assemblies - No (No costs associated with this transaction)
    and anything else - ?? - What else is there?

    The Custom Project Detail Report was created by using the Custom Transaction Detail Report as a base and then just modified and filtered it to suit.

    The user could take the 3 separate reports and drop them to one Excel Spreadsheet, using the Export to Excel functionality within QB. The spreadsheet contains formulas to add the 3 reports together and produce the necessaries and the spreadsheet format could be saved by the user so that it can be re-used again and again with fresh data from QB.

    I have made the spreadsheet and example data file available for download and review at:

    https://www.quickbase.com/db/98ndf8w9?a=dr&r=b&rl=b

    Saves having to explain in detail how the QB report was customised and what formula's were used in Excel.

    When you open up the data file please look at the memorised reports list, the group called Accounting Web, for the 3 QB reports needed

    Hope this helps.

    Thanks (0)
    avatar
    By kevinread
    16th Sep 2004 20:27

    Stock purchases.
    One thing always bothers me with job costing on QuickBooks. How do you deal with stock that you've bought in to keep in stock and then issue to jobs?

    Thanks (0)
    avatar
    By David Carter
    16th Sep 2004 21:18

    Adjust Quantities/Value in Hand?
    Kevin, are you using Suppliers - Stock Activities - Adjust Quantity/Value in Hand?

    This has fields for a GL Adjustment account and a Customer:Job name. It looks OK although it seems you have to scroll through the entire stock database on the screen to find the one item you want to issue.

    Thanks (0)