Now that you are getting accurate labor info, you need a place to put it.

This month’s installment in our series on building a cost accounting spreadsheet marks the midway point. But if you have applied your time since the last installment wisely, you are really in the homestretch.

That’s because now, everybody on the shop floor is keeping track of all the time they spend on every job; each minute of each hour, each hour of each day, every day of the week. And once you’ve succeeded in doing that, the rest is easy.

You achieved this goal thanks to the most basic principle in management, the one without which even the most inspiring, charismatic leader will eventually fail: Accountability.

Some say everything ever tried in managing people is theory at best, that there can be no universally accepted principles because hard-and-fast rules just don’t apply when you’re dealing with the limitless variables inherent in relations between humans. Hogwash.

Start holding people accountable, and you will get answers to ALL your management travails. They may not always be the answers you hope for, and they may lead you to issue warnings or to let people go. But if you truly want to take all the drama out of running a business, the ability to make your expectations clear and achievable – and the will to hold people accountable for meeting those expectations – are really the only skills you need.

Thanks to these skills (and the simple form included in the August installment), you are now getting reliable labor numbers from your people.

You are still doing spot checks to make sure they are compiling the numbers throughout each day, not fabricating them by guess and by gosh at day’s end. You will do these checks and collect the forms daily from here on out, from new hires and veteran employees alike, for as long as your company exists.

In July we began building our spreadsheet with the columns and rows needed for tracking materials costs. Look at the graphic and you’ll see that section is now shaded. The white area is this month’s project, and this is where we will soon put the labor information you are now gathering.

Begin, then, by labeling the “Employee” column (column “G” on your spreadsheet).

If you are structuring your spreadsheet the same as the one in the graphic, you will have 12 rows between the headings and the “totals” cells across the bottom, giving you room for 12 employees. Don’t worry about entering employee names just yet. Remember, we are creating a TEMPLATE that we will copy again and again for use on every job. We will get into the specifics of entering actual materials, names and data when we do a sample job in November, the last installment of the series.

The next column, which we will label “Regular Hours,” is where you will enter each employee’s hours worked on this particular job. The last box in this column will total the regular hours worked by all employees (as opposed to Overtime Hours). To get that number, you will need to enter this formula in that last cell: =SUM(H3:H14).
(Editor’s Note: Spreadsheet formulas cited throughout this series apply to Microsoft Excel. If you are using a different spreadsheet program, consult your user’s manual to ensure that you are entering the correct formulas.)

Next comes column “I,” which you will label “Hourly Rate.” No sum is needed here, and we will enter each employee’s pay rate on a per-job basis. So here on our template, we leave all the cells in this column blank.

Figuring Overtime

“OT Hours” is another column that will require a sum formula at the bottom. After you label it at the top, enter =SUM(J3:J14), just in that last cell (the one whose coordinates are row 15 and column J).

Right about now, those readers who are new to spreadsheets can begin to see the amazing utility these data collectors provide. And it’s funny, isn’t it — they are nowhere near as complicated as you probably thought.

Now let’s tackle column K, “OT Rate.” As you can see, there are lots of zeros down this column. Each cell with a zero also contains a formula. But you don’t need to manually enter that formula in each cell. I will give you a shortcut for that.

Start in the first cell under your heading, and enter this formula: =I3*1.5.

Let’s break that down: “I3” are the coordinates for the first cell in the “Hourly Rate” column. Excel uses the asterisk as a multiplication sign, and “1.5” is your overtime factor (also known as “time-and-a-half.”)

So, when applied to the hourly rate (which, as noted, we will enter on each individual job), this formula will automatically calculate your overtime cost for each employee.
Here’s that shortcut for entering the formula in each cell:

First, select the cell where you just typed the formula. Then RIGHT-click that cell and, from the menu that appears, choose “Copy.” Now LEFT-click the cell and, holding the left mouse button, move the mouse down the column. This will select each cell as you pass over it. Continue to the next-to-last cell in the column (cell K14), and when it is selected, release the button.

Now, with your mouse over any part of the selected area, RIGHT-click again, select “Paste” and you’re done. The formula has been copied to each cell in the column and the program has automatically inserted the correct coordinate in each cell’s formula.

You can use this same trick in the next column. After you label cell L2 “Employee Total,” enter this formula in cell L3: =(H3*I3)+(J3*K3). Then repeat the trick down this column, through cell L14.

Cell L15 is where, when our template is used on an actual job, we will get the total we have spent for labor at the end of any given day the job is in the shop. Just enter =SUM(L3:L14), and your template is ready to yield that data.

Of course, throughout the process of building this template, you want to remember to save your work. I saved my template as “costing template.” (Sometimes my creativity astounds even me.)

Just two more columns to go this month. The first is “Taxes,” and it covers the taxes YOU pay on your employees’ behalf. The one we most want to capture is our contribution to Social Security and Medicare, to which we contribute 7.65% on the wages we pay out. But if you make any other tax payments that are figured as a recurring percentage on wages paid, you want to add those in as well.

That percentage of 7.65 translates to a multiplier of .0765. So, if Social Security and Medicare are the only taxes you contribute, in cell M3 you will enter this formula: =L3*.0765.

Copy it again as described down column M, through row 14. Then, in cell M15, enter =SUM(M3:M14). On a working job, this cell will show your total costs for payroll taxes on the project.

Finally, we will get our total for the labor section of our spreadsheet, including the taxes just calculated, in cell N3. Enter the following formula there: =L15+M15. This adds the total taxes to the total raw labor numbers.

Next month we will figure overhead costs and how they are applied. In November, we will put our template through its paces by entering data from a sample job.

Anthony Noel has written for CWB since 1994. Send comments to him at [email protected]. A full archive of Management Strategies columns can be found here.