Your hard work over the past few months is about to pay off — for the rest of your company’s life.

We started a journey four months ago. When it began, I promised that the result would permit you to know everything necessary to improve your company’s profitability. Based on your e-mails, most of you who started the journey are determined to complete it. I congratulate you, and can assure you that this month, your reward is at hand.

The cost accounting spreadsheet we started building back in July has taken you down roads, which, as a business owner, you probably knew you needed to travel, but were never quite sure how to navigate.

But now, even before we test drive our spreadsheet by “plugging in” a sample job, you can already see the importance of an organized approach to doing business. You have a new appreciation of the simple reality that, by carefully tracking every money-costing facet of every job you do — and they all cost money — you are collecting real-time information that not only might save your profitability on this job, but also increase it on the next one.

Along the way, you have also learned how to get crucial information from your employees. That means you know how to manage people better.

Nearly all of our spreadsheet is shaded (see “empty” graphic), meaning we are almost done. We have just three columns remaining, all of which you labeled last month: Amount Billed, Total Costs and — drum roll, please — Profit or (loss). Talk about summing things up, eh?

Cell S3 (just beneath “Amount Billed”) requires no formula, so move to T3 and key in this one: =O3+R3. Then, in cell U3, finish your template by entering the formula =S3-T3.
Save your template on your machine with a name that will remind you that it is just that; a template. And just as a backup, put a copy on a disk or flash drive. And speaking of drive, are you ready? Let’s take this thing out for a spin!

Blank Template

A Trial Run

First, with the sheet open, save a new copy for our sample ride. Under “File,” click “Save As” and rename the sheet “costing test.” Now look at the graphic with that name, and let’s walk through the entries on it.

I have imagined a simple p-lam counter project. For our purposes in testing our sheet, it doesn’t matter what the project looks like, so I have taken the simple approach. Even so, there are tips here you will want to follow through on as you do costing for every job, from the very simple to the highly complex.

First, notice that I have prefaced each material item with a quantity number. This practice not only allows you to write out the order in advance and recite it when phoning it in, it saves us a multiplier (read: space, and an extra formula) on our sheet. By simply entering the total for the materials on each line, regardless of quantity, we force ourselves to check that each pricing times the number of pieces purchased lines up with the amount billed when the invoice comes. If not, we have a problem, and we know our next step is to call the vendor.

The next few lines are self-explanatory, with one recommendation: Always work with the same inside rep at each vendor. Developing such relationships will garner you better pricing and service. Jumping from rep to rep only extends the time it takes for you to become “known” with vendor reps. A single point of contact ensures the most seamless, accurate transactions.

The “Notes” column may well be your first opportunity to format a cell. When you type in a note like the one shown, the text of that note doesn’t automatically jump to the next line until you ask it to. So if your note is too long, the cell will stretch and obscure the cell(s) next to it.

To solve this, right click on the cell in question, select “Format cells,” choose the “Alignment” tab, and check the box that says “wrap text automatically.” Pretty simple, huh?
A similar issue you may run into is how the spreadsheet interprets the number you type. If it “thinks” the number is a date, for example, an employee’s hourly rate of $17.86 will get garbled, to put it mildly. Use the right-click trick again and choose “Format cells,” but click the “Numbers” tab and from the Category list, choose “All.”

As you fill in the Employee, Regular Hours, Hourly Rate and OT Hours cells, the next group of cells fills in on its own, right through Cost of Overhead. All that’s left in order to see how you did is to fill in your selling price (“Amount Billed”) and look at the last cell.

That’s all there is to it!

In the midst of it all, creating this template seemed like a lot of work, didn’t it? But now that you see its power, I have to ask: Did you ever dream that figuring out your true costs could be so easy?

Anthony Noel has helped custom shops make more money for over 20 years, and has written for CWB since 1994. Send e-mail to [email protected].