Case Studies

Ending the Spreadsheet

Todd Herman

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?

spreadsheet

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.

Sincerely,

Todd L. Herman

Todd L. Herman

Read more on the benefits of developing an application.


Case Study: Managing Job Costs and Timeline

Situation...

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.

Problem...

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.

Solution...

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.

Time Tracking Application

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.

Job Set Up Screen

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.

Time Tracking Screen - Left SideTime Tracking Screen - Right Side

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.

Job Summary Report

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.

Conclusion...

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.

Business Resources

Popular Case Studies

Applying Technology to Reduce Unbilled Charges

Accession Inventory graphSometimes, brute force gets a job done. Sometimes, a little finesse is all it takes. Other times, you need to combine the two to accomplish your goal!

Investing vs Spending

AvaCare-screen-shot-v4What's the difference "investing" and "spending"? From the perspective of current cash outlays, nothing is different - yet, taking the long view, there's a great deal of difference.

Deliver Major Cost Savings by Using Technology and Improving Process

Read how this fast growing company partnered with Todd Herman Associates, and found short-term and long-term cost savings through technology and process.

Making Reports Useful: A Case Study

Read how a CIO found a profitable innovation idea in the way the business delivers lab reports.

Activity Versus Achievement

Establish Accountability and Evalutate Results

The Wall of Knowledge

Clarifying Business Needs

Ending the Spreadsheet

Managing Job Costs and Timelines

Tip of the Iceberg

Delivering Meaningful Information to Executives

"Culture" is a Verb

Redefining "The Hottest Topic in Business Today"

Category:Leadership Relationships

Start transforming your business and yourself by signing up here:

captcha 

EIGHT TIME WINNER OF THE
APEX AWARD FOR WRITING EXCELLENCE!

2018a winner2017 winnerApex 2016 winner Apex 2015 winner Apex 2014 winner Apex 2013 winner Apex 2012 winner Apex 2011 winner

Business Book Reviews

Need more resources for growing your business? Get great business book suggestions from Todd.

A Picture Is Worth a Thousand Words - 2017 Summer Book Reviews

thebackofthenap Is a picture truly worth a thousand words? Todd's annual summer book reviews cover four books that have helped either hone thinking skills by using doodles and simple drawings, or spark ideas for improving doodles or other visuals.

 

Nothing Changes Until Somebody Feels Something

Whoever Tells The Best Story Wins(June 2016) Hugh MacLeod of Gapingvoid Art gave Todd inspiration for this summer's book reviews when he said "Attach emotion to logic and anything is possible" — A common thread in the four books Todd reviews.

 

Free Range Staff — 2015 Summer Book Reviews

Leadership and the One Minute Manager book coverTodd describes his idea of how to develop "Free Range Staff" — Employees who can take an idea, develop and run with it with little or no supervision — as he reviews four books he uses to teach and develop his staff.

 

Seeing Around Corners — And Four Books That Can Help

Book Cover of Data CrushTodd's review on books that can help you and your business see around corners better.

 

Interested in reading past case studies? Visit the Case Study Archive.

Todd Herman Associates

336.297.4200

620 Green Valley Road
Suite 104
Greensboro, NC 27408
/strong>

About Todd Herman Associates

Todd Herman Associates is a business technology consulting firm focused on non-routine technology issues such as replacing QuickBooks, getting two systems to "talk" to each other, shrinking process cycle time, and taming large volumes of data.