Custom Manufacturer solves Job Costing problem with custom application
Custom equipment manufacturer needed to be able to accurately cost their jobs.
Develop a custom job costing tool which interfaced to all their existing cost
- Visual Basic
- SQL Server
- Sun Accounts
Job Costing to their requirements.
This manufacturer's custom equipment runs to many thousands of parts and has a manufacturing cycle from
around one to three months. During that time, they will issue many hundreds of purchase orders and collect
thousands of hours from their engineering department and shop floor. This in turn will generate many thousands
of accounting transactions through their financial system.
With the scope to have several large manufacturing processes running simultaneously as well as a very busy
spare parts division, an accurate job costing solution was required. In addition, it had to interface to
all their existing systems/applications. As well as obviously wanting to avoid the print out/key in cycle,
even the manual transfer of data was to be avoided. The whole system had to run automatically and be
no more than a single day behind production. In other words, costs committed or incurred one day had to be
available for review in the job costing system the following day.
A custom application was developed using Microsoft SQL Server as the main data repository. Some of the systems, such
as the financial system, were already SQL Server based making the access and extract of data relatively straight forward.
Other applications were using Microsoft Access and JET databases.
Batch conversion scripts were written to upgrade nightly copies of the various Access 1.1 and 2.0 databases into Access 97
format. This allowed the tables to be added as SQL Linked Servers. SQL Server tools/query language could then be used
to import directly from the Access databases.
The overnight process imports around 1.1m data records in slightly over 5 minutes. During that process, the system
matches purchase order commitments with supplier invoices. Some of these documents or their content are then rendered
within the costing application to save the employees having to keep jumping back to the source application to view
the original data.
The job costing data is presented as a tree structure comprising projects, jobs and cost centres. Individual costs can be drilled
down into as well as there being a number of summary screens providing totals and cross tabs by cost centre, supplier etc.
The customer has a fully integrated job costing solution, based on their own exacting requirements. They do not have to
resort to manually extracting data and trying to analyse it in tools such as Excel. Costs are never more than one day
behind, allowing them to track costs as they accumulate.
The tool serves two primary purposes - real time monitoring of costs as they accumulate on current jobs and the ability
to mine cost data from historical jobs for comparison/assistance with estimating.
At the time of writing (mid 2009) this tool is being retired after ten years in service. Due to acquisition, the company is moving
to the corporate ERP system. However, we are now in the process of taking our job costing methodology and implementing
it on top of this system and our initial developments are now being used by other companies in the group.
Details can be found in the Macola ERP Extensions case study.