For background on this particular project, please visit Ending the Spreadsheet.
This designer and producer of custom-designed products has about 100 jobs in process at any point in time, and uses QuickBooks Enterprise for its financial statements. Labor, both for design and production, comprises the great majority of the product's total cost.
While QuickBooks Enterprise was sufficient for financial reporting, it had no features to conveniently manage this type of business. While goods purchased for specific jobs were coded to the job in QuickBooks, such purchases accounted for only 20% to 25% of total cost. Our client had no easy, flexible mechanism to capture and track labor hours by job, thus lacking visibility of their largest cost. Finally, a Microsoft Excel spreadsheet was used to manage the jobs as they were processed, yet – because this was not an appropriate product for this use – getting accurate information to make decisions was a constant problem.
To address these issues, we:
- Identified a third-party tool to programatically extract data from QuickBooks on a scheduled basis.
- Developed a simple web-based time tracking application to collect time by employee, classified by customer job codes or internal company codes, drawing employee name and customer job codes from QuickBooks.
- Developed a job management application, drawing purchased materials from QuickBooks, obtaining labor hours and values from the time tracking application, and allowing web-based setup and tracking of key production steps and dates.
With these components in place, we then trained employees on using these tools to capture time by job and accurately track jobs through production. While this data was accumulating, we developed several analyses to manage job costs and timelines, including:
- Job Summary Report, showing key steps and their status (complete, incomplete, or not applicable).
- Labor Hours by Employee, permitting comparison of employee productivity with their peers.
- Job Profitability Report, showing estimated job margin at projected completion.
- Hot Jobs Report, showing jobs which are due in the next 2 weeks, are due within 4 weeks and at risk of slippage, or are projected to end up below desired gross margin percentage.
Above, a third-party utility allows us to extract information from QuickBooks and put it into the SQL Server databases underlying the Time Tracking Application and Job Data Warehouse. Drawing key information from QuickBooks ensures data consistency and forces process discipline – for example, a job cannot be setup in the Job Data Warehouse until it has been setup in QuickBooks.
Above, the Job Setup Screen allows authorized users to define overall job information, specify steps applicable to a certain job, and enter dates and other key information for the relevant steps.
Above, the Time Tracking Screen allows a user to pick the job code – either customer jobs or internal time codes – being reported, and then specify time for the relevant day. An audit trail is maintained of changes to each line item on the screen.
Above, the Job Data Warehouse pulls together the information extracted from QuickBooks, as well as the current contents of the Time Tracking Application – these account for about 95% of the end product's total cost. The data warehouse contains various analyses and reports, all with drill-down and drill-around functionality, and with the ability to export to various programs. Managers use the "Job Summary Report" to visually monitor the 100+ jobs in process, and identify those showing potential delivery date or cost issues.
Results & Benefits ...
Executives and managers now have accurate job status information no more than one day old, and visibility of the "Hot Jobs" requiring focused attention. Armed with this information, department managers can adapt to changed conditions, and account service representatives can keep customers updated on the status of their orders.
For the first time ever, executives and managers can determine which jobs are unprofitable and why this occurred, seek change orders from customers when warranted, plan the need for overtime work and temporary employees, and compare performance of specific employees to estimated labor hours.
Prior to this project, executives and managers had only historical financial information, without the ability to analyze reasons for unplanned results. Our work gave them the tools to plan and track specific costs by job, manage overtime costs by better using temporary labor, and identify employees requiring development to improve their performance.
For More Information ...
To discuss how technology usage and business process improvements could be applied to the issues facing your business, call us at 336.297.4200 to schedule a no-obligation consultation, or click here to contact us online.