Ending the Spreadsheet
Spreadsheets are wonderful tools ... except when they're not. Combining data, formulas, and formatting all into one cell is fantastic ... except when it's not.
Multiple People Using the Same Spreadsheet
Spreadsheets are great tools for single users who can do everything themselves, and who have to deal with only a moderate amount of data. But what happens when status information about a job — which can be touched by any of about 45 people — needs to be collected and combined, every day, to track the job's progress? And what happens when over 100 jobs are in process at any point in time? And what happens when the most significant part of a job's total cost — employee time — is not collected and tracked to the job?
For situations requiring data collection, data import, status tracking, and summarization and analysis of time and cost from different sources and multiple locations, a database trumps a spreadsheet anytime. While a database requires more time to design and develop than a spreadsheet, today's application development tools have significantly shrunk this time difference — and today's reporting tools provide virtually the same formula and formatting capabilities as a spreadsheet.
Upgrade to a Database?
This month's case study describes how we quickly designed and developed two database applications, focusing on simplicity and ease of use, to eliminate a spreadsheet. While the time data entry entry screen looks like a spreadsheet, and the job summary report looks like a spreadsheet, storing this information in a database allowed our client — for the first time ever — to track actual time against the job estimate, and review actual progress against the planned milestones.
And our client's employees? For this task, they were more than happy to end their usage of a spreadsheet — may it rest in peace.
Todd L. Herman
Read more on the benefits of developing an application.
Case Study: Managing Job Costs and Timeline
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.