Custom Manufacturer solves Job Costing problem with custom application

Business Problem

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.

Exmos Solution

Data Import

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.

Job Costing

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.

Business Benefit

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.