Obtaining a Work in Progress figure from Sage Job Costings

All of the companies I work for on a regular basis use one or another of Sage’s products for accounting purposes, but only one uses Sage Job Costings. As its name suggests, this is a program into which all the company’s costs are input, be that supplier invoices, employee time, etc, and allocated to a particular job, each of which has a unique reference, in order to provide accurate costings on a per job basis. If this program is used fully, it can also be used to generate invoices based on the costings – but (and I imagine this is common) the company doesn’t use it this way. Invoices are actually produced in the normal sales ledger module of Sage Line 50, using reports produced from Sage Job Costings as a basis. This gives them more control over the final invoices.

The downside of this approach is that the Job Costings program itself doesn’t know when a particular job has been invoiced (or, more accurately, when particular costs associated with a job have been charged out) and therefore can’t easily provide a figure for Work in Progress – which is, in essence, the costs associated with jobs that haven’t yet been charged out to customers. An accurate figure for Work in Progress is obviously needed for accurate accounts.

There is also an oddity in the way the company dates its invoices which, although I don’t fully understand the reasons for this, I just have to accept as the way things are done. It’s relevant because it does make calculation of a Work in Progress (WIP) figure that much more difficult.

It turns out that they have a list of every job in a spreadsheet, along with the date it was invoiced where appropriate – so by using that it’s possible to go through the list of jobs in Sage Job Costings and select those ones that haven’t been invoiced by a particular date (taking care not to select those new jobs that hadn’t been booked in by that date) and then run a report showing the total costings up to the desired date for all the selected jobs; this would be the WIP figure at that date. However, this is a very long and tedious job which is prone to error. It would possibly be a task that is bearable once every year, but certainly not something that anyone would want to do on a monthly basis.

Looking in detail, I noticed that although the “Job Classification” was being set in Sage Job Costings to one of about half a dozen types of job, it wasn’t actually being used in any significant way. This meant that it was effectively “free” for use in another way – and I concluded that with a little thought it could be used to make the calculation of a Work in Progress figure much easier.

The solution I devised was to create a default classification that each new job will have – “9999 – Not Invoiced”. Then, when any given job has been invoiced, its classification should be set to reflect the month in which it was invoiced (according to the invoice date – not the date was physically raised). I created 18 months worth of job classifications to get the ball rolling:

“0910 – Invoiced Oct 09”, “0911 – Invoiced Nov 09”, “0912 – Invoiced Dec 09”, “1001 – Invoiced Jan 10”, “1002 – Invoiced Feb 10” and so on – i.e. the year and month as a four digit number, and a hyphen followed by the word “Invoiced” and the month/year it was invoiced. (And as an afterthought, I’ve since added one for jobs invoiced “up to” September 09). This naming convention means the job classifications are shown and used in chronological order (the software actually sorts the classifications alphanumerically – this is important when using these classifications to provide a WIP figure) and provides a more user friendly date at the end of the name.

The classifications are set up by going to the “Settings” menu and clicking on “Job Classifications:

Settings Menu - Job Classifications
Settings Menu - Job Classifications

On doing this, the user is presented with a small window containing a list of job classifications, and buttons to create a “New” classification, or to “Edit” or “Delete” an existing one (highlight it first in either case):

Creating a new job classification
Creating a new job classification

With this system, if job number 12345Z is invoiced in December 2009, its classification should be changed to “0912 – Invoiced Dec 09”. If it’s invoiced in February 2010, its classification should be changed to “1002 – Invoiced Feb 10”. Until it is invoiced and the classification is changed, it will have the classification “9999 – Not invoiced”.

Changing the classification on a job is a simple matter of double clicking on a job to open it, clicking on the “Analysis” tab, and in the section labelled “Classification” choosing the classification reflecting the month and year it was invoiced from the drop down menu:

Changing the job classification
Changing the job classification

With this system of job classifications in place, to produce a Work in Progress figure as at the end of any given month, the costings report has to be run with the following criteria (ranges):

The “Job Number” can be ignored and left at the default range unless there are any peculiar circumstances. This is because the date range (below) should ensure costings are only included up to the required date. Similarly the “Cost Category” can be left at the default range, in order to include every type of cost, and the same applies to the “Transaction Type” range.

The “Transaction Date” is the first important one. The “From” date should be left at its default, and the “To” date should be set to the date the Work in Progress figure is being calculated for. Setting this range means all costs will be included that fall between those two dates – but this includes the costs for jobs that have already been invoiced, which is where the job classifications I’ve devised come into play.

The “Job Classifications” range, if set correctly, will ensure that those costs that fall within the date range are narrowed down to only those on jobs which have not been invoiced by the date in question. This should be set to run “From” the job classification representing the “Next” month, “To” the classification representing jobs that haven’t been invoiced.

The reason the report is run “from” the classification representing those jobs invoiced in the “next” month, rather than just for the “Not Invoiced” classification is that because, as a general rule, not all costs will have been entered by the date in question, and this figure is often calculated retrospectively – calculated at a particular date some time after that date – but some jobs (whose costs need to be included in the WIP figure) may have been invoiced in the next month, before the calculation is done. This is also why it was important to show the classifications in chronological order, bearing in mind they are sorted alphanumerically.

For example, if the Work in Progress figure is needed for the end of October 2009, the costings report has to be run for all jobs whose classification is in the range from “0911 – Invoiced Nov 09” through to “9999 – Not Invoiced”, with the date range set from “01/01/1980” to “31/10/2009”:

Setting the criteria (ranges) for a costings report
Setting the criteria (ranges) for a costings report

In a few months time, to produce a Work in Progress figure as at 31st March 2010, the job classification range would be “1004 – Invoiced Apr 10” through to “9999 – Not Invoiced” and the transaction date range “01/01/1980” through to “31/03/2010”

The one obvious problem with this approach is that the company concerned is already established, as is their use of Sage Job Costings. This means they already have a large number of jobs on the system, so this approach won’t suddenly start working properly until and unless someone goes back over all the old jobs and sets their classifications to something suitable (which I am hoping is what will happen, and is the reason I retrospectively added a classification for jobs invoiced up to September 09), or until the jobs on the system under the old scheme are purely historical, having all been invoiced. Once either the reclassification is done or, if it’s left to time to sort out then after a few months, the results should speak for themselves; a reasonably accurate work in progress figure, produced quickly and easily.

Related posts