In the past few posts I’ve been talking about how to track the profitability of jobs when you’re running a service business where labor is the main item for sale. Basically, this involves comparing the number of hours you estimated for the job vs. the number of hours it actually took, and then assigning a dollar value to both numbers. This post will be about ways to analyze that data and get a better handle on your profitability. We’ll start with the data on the estimate or bid.
There are many million-dollar businesses where the bids are created with MS Word plus a calculator. But the absolute worst way to create an estimate is by using Microsoft Word or an MS Word look-alike. I know it seems efficient — you just look up a similar job, do a “save as,” and type in new line items and numbers. But this approach makes analysis very difficult.
A far better approach is to use Excel. With Excel, you have a record of the hours and rates in the bid that makes comparison with the actuals very easy. In the Excel-based approach, you type in item descriptions, hours, and rates to create the initial estimate. Those numbers are automatically transferred to two places: a customer-viewable bid (which could be a Word document based on a template), and a separate work sheet where you’ll do the job analysis. When the job is done, all you have to do is type in the actual numbers (hours worked) and let Excel calculate profit/loss. Here’s a very simple example:
It’s also easy to aggregate numbers from certain types of jobs or any given customer over a period of time.
This simple example shows a profit comparison exercise for different types of catering jobs: weddings, corporate receptions, etc. The average profit is the sum of the profits for all weddings in the analysis period divided by three. The profit margin is the average of the three weddings.
Now that we’re in the era of the Internet, collecting data about hours worked from your employees or subcontractors is also easy. If you Google “time tracking” you will find dozens of online solutions your employees can use to get their numbers to you. In terms of cost, most of them range from cheap to free. If you want, your workers can even report their hours via a mobile device such as an iPhone.
Remember that in Quickbooks, you can calculate your estimate, create a bid, collect employee hours, calculate profit or loss, and later on do aggregations that let you determine (if you’re a photographer, for example) whether you make more money shooting portraits or weddings.
My advice: Make a vow to get this done by the end of November, so you’ll have information for planning the new year.