Subscribe to TIC

Advanced Excel: Know Your Costs

In my last Excel article, I introduced some basic features to show the power and flexibility of this awesome program. In this article, I will expand on what was previously covered and introduce some new features. This is pretty advanced stuff. But if you stick with it, I really believe that it can make your business life more efficient, which can translate into more dollars earned, and more time saved.

Some of you may notice, from the images in this article, that I upgraded to the latest version of Excel. Wow, what a difference. The “Ribbon” feature replaces the old drop down menus, making things much easier to find. The Help library is very user-friendly, and contains a huge amount of information.

For this article, I have put together a small spreadsheet (you can download a copy at the end of the article) that walks you through the process of determining your minimum hourly labor rate based on your personal and business expenses, and the number of hours you work per year. For you more established (older) guys this is old news. However, some of you younger guys might find this useful.

Many of the techniques I will use might seem nonsensical in this particular spreadsheet. I use them only to demonstrate certain features that you might find useful when designing and building your own spreadsheets.

In the following screenshot, you will see that I created a range of data showing some business expenses and their respective yearly costs. (Note: you can click any image to enlarge it.)

The expenses are totaled using the SUM command and are shown in orange. (If you’re already confused, see my previous article, An Introduction to Spreadsheets for some basics.) With Excel, you can convert a range of data into a Table. Converting to a Table gives you more power and flexibility with your data.

Simply highlight a range of data, select “Format as Table,” and choose one of the predefined styles; or, you can make your own style.

Once converted to a table, you have the ability to quickly sort the data and search through it, too. The one, small table above does not show the real power of this function. But imagine if you had a huge table of materials and costs to manipulate. Then the power of this program would becomes more obvious.

In the picture above, you can see the two tables I created, one for Business expenses, and the other for Personal expenses, with their respective totals. As I mention in the yellow note on the left (adding a note is a great way to help others use your spreadsheet), enter all your expenses as accurately as possible to get the maximum benefit out of this spreadsheet.

In the following picture, you can see my “Calcs” worksheet, which includes the totals from the “Expenses” worksheet. I use cell-highlighting to make the spreadsheet easier to use and understand. This can be useful if other people will use spreadsheets that you develop.

You can see that we have now calculated the “Unburdened labor rate,” based on the combined yearly expenses and the total number of hours worked per year.

The next step is to calculate the fully Burdened labor rate per hour worked. This is shown in the following image. This labor rate will obviously vary from person to person based on deductions and the amount of profit desired.

This spreadsheet is dynamic. If you change any of your expenses, or hours worked, all calculations will update automatically.

I also formatted the “And the answer is:” cell to turn red if the value in it is less than zero. Here’s how you do that (Note: This is for Excel for Windows. The Mac version is different): From the HOME ribbon menu, select Conditional Formatting > New Rule > Format only cells that contain. Enter the required data into the “Edit the Rule Description,” and select “OK”.

Now we have a usable fully burdened labor rate. This, by itself, is great information, but we can make our spreadsheet even more useful. This kind of data lends itself to “What if” analysis. In order to make that analysis easier to perform, I created a large data table, shown below.

This table uses the data from other pages (or sheets) within our spreadsheet, so it, too, will change when the input data changes. This table was quickly made using the “Fill” and “Copy” commands. This range of data covers 3,000 rows and 8 columns. To generate the column of “Hours worked per year,” I used the “Fill” command as shown below.

To use the “Fill” command, you simply select the starting and ending values, and either Row or Column sequence, and Excel fills in the values. In this case, I started with 1 hour and stopped at 3,000 hours. The balance of the table was created using the “Copy” command. In Excel you can copy down or across. Copy down is shown in the image below.

When the copy is completed, you have the option of copying the cell contents exactly, such as January, filling rows 10 through 21, or completing a series as shown above. Copying right is shown below, and works the same way.

Now remember that the Copy command uses relative cell references by default. That means when you copy down rows or across columns the cell addresses change accordingly. But what if you want to use the contents of only one cell as a part of a formula down many rows or across many columns? How do you tell Excel not to change that cell reference? This is accomplished by inserting the dollar sign ($) into the formula:

Look in the Formula bar, where it says: =Calcs!$D$12/A6. The $ sign in front of the D (column reference) and in front of the 12 (row reference) instruct Excel not to change the cell reference if copied down or across. In my example, I copied only down, so I could have placed the $ in front of the 12 (row reference) only, but out of habit I fix both row and column.

Once the formula was entered, I copied it down. The only thing changing in the modified formula during the copy is the divisor, which, in this case, is the contents of cell A6.

VLOOKUP

Using the same method, I completed my rather large data set. Now I can start performing “What-If” analysis. In the picture below you can see how the function VLOOKUP can be very useful.

In the formula bar you can see the formula: =VLOOKUP(E11,’Data Fields’!A6:F3005,6,FALSE).

The formula is comprised of the following parts: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The “lookup_value” in this case is the contents of cell E11 (currently 2000). The “table_array” is ‘Data Fields’!A6:F3005. The “col_index_num” is 6 and the “range-lookup” is FALSE.

So here is how it works. Type a number of hours worked per year into cell E11. The VLOOKUP function then returns the value on the same row in the “table_array,” but in the 6th column. In this case $42.39. The VLOOKUP function makes the “What-If” analysis much quicker.

What if I want to input a labor rate and find out how many hours I need to work to cover my business and personal expenses? Easy, but we have to add two more columns to our data range in the Data Fields sheet. The picture below shows how that was quickly performed.

I copied the “Burdened Labor Rate per Hour” part of the data range, and using the Paste Special command shown above, selected Paste Values. I did not want the formula, just the Value that formula created. Then I copied the “Hours worked per year” data range. I then converted that range of data to a Table. Once converted to a Table, I sorted the Burdened Labor Rate per Hour in ascending order. Now I can create another VLOOKUP function. This is shown in the picture below.

Now I can enter a labor rate and the new VLOOKUP formula I entered returns the number of hours needed to work from our data set.

Additionally, I added some simple calculations to show the labor hours broken down to weeks and months. A note about the VLOOKUP function: If you enter a value that is outside of the data range, the VLOOKUP function will return a value of #NA.

At this point in our spreadsheet development, you can start experimenting with different scenarios with time and labor rate. With a few keystrokes, you can determine the financial impact of moving your external work shop into your garage, working more or less hours, raising your hourly rate, or going into another line of work all together.

Charts

Let’s look at another feature in Excel. Charts can be very useful tools to help understand data. They can also be a great sales tool. Creating charts in Excel is very easy. Simply place your cursor where you want to place your chart, select Insert, choose the type of chart you want, and Excel will open a dialog box asking you to select the data you want included in your chart. These actions are shown in the pictures below.

. . .

Using your mouse, highlight the data, select OK, and the chart is created. Once created, you have complete control over all aspects of the chart. Double-clicking on the chart or axis brings up other dialog boxes, which allow you to change the font, color, scaling, data sets, and format, to name just a few. In the picture below you can see the simple line chart I created showing how the Burdened labor rate changes based on hours worked.

The following Pie chart shows the relative size between yearly Business expenses. Use your imagination, and let the powerful charting features of Excel help you better understand your data and sell your services to your customers. People are visual creatures—we love pretty pictures with lots of color.

Hopefully you found the Excel features I discussed, and the Labor rate calculations, useful. It never hurts to review your costs every few years. Things change, and you need to be able to modify your labor rates based on your market and business needs. You cannot make those changes without solid information. Just dropping your price by 15% to get the job may be required at times. But, if you do, you now have a tool that will help figure out the REAL cost in money and time on a yearly basis.

If you have any questions, feel free to ask them in the comments section below, and I’ll do my best to answer them.

If you’d like to download the spreadsheet used in this article, you can do so by clicking HERE.

Comments/Discussion

15 Responses to “Advanced Excel: Know Your Costs”

  1. Daniel Schroth

    Spread sheets are a must in todays construction management. These are great templates but are there any available templates for bidding and comparing actual cost ?

    Reply
    • Chuck Kiser

      Morning Dan,

      I’m sure there are a great number of available templates that will do what you need. Finding the one the matches your exact need is the tricky part. Here is what I would do. Start a new workbook, enter a table of all the labor and material costs you want to track for each trade, prior to entering actual data Copy and Paste the blank table into two new sheets in the workbook. Name the three sheets Bid, Actual and Comparison. Enter the data into the Bid sheet, as the job progresses enter the actual costs into the Actual sheet. Create a simple formula in the Comparison worksheet that compares the data from the Bid and Actual sheet.
      It really is very easy and fast. And your workbook will be customized to your exact needs. The alternative is to spend money on any one of the many accounting packages available. Most people find those to be overkill.

      Reply
  2. martin lindwall

    Great information about Excel and seeing per job or yearly costs.
    But I didn’t see the Accountants code numbers with the Cost of Goods Sold (COGS) I.E. equip rental; material purchased for that job in the quote or marked up used on that job and used on that project; mileage and or travel too to job; subcontractors/independant contractors; etc.. I’ve always been told you must code each COGS for each job as a IRS deduction.

    Reply
    • Chuck Kiser

      Thanks Martin,
      I agree about using the correct codes for accounting purposes. See my response to Dan (above). With a new workbook, a modification of the one I used in my first article perhaps, you could breakout all the labor and materials into their appropriate categories. Use the calculated labor rate from this articles workbook as your starting labor cost and away you go.
      You could have all of this data in one “BIG” workbook but it might get to be hard to manage over time..

      Reply
  3. Bill Thomas

    Chuck
    Another fine article. As an “older guy”, I already do this. I could have used it as a younger guy. Like so many others, at an early time in my career, I didn’t realize all my costs and never added profit. I hope some of the younger guys starting out use this information you layed out so well.
    As you like spreadsheets – A customer of mine recently sent me a complete plan view done in Excel. When I see him next, I want to find out how he did it.

    Reply
    • Chuck Kiser

      Thanks Bill,

      I did not know what I did not know when I started out. I made the same mistakes as you. With the information available today there is no good reason for the young guys to repeat our mistakes.

      Reply
  4. John

    Great information on calculating your labour cost and how to basically run your business with the help of excel. I’ve been struggling for years trying to work out how much to charge etc. For me making stuff from wood is the easy part, I almost don’t have to think about it. It’s the business side of things that requires alot of effort.

    Reply
    • Chuck Kiser

      Thanks John,

      I see the building of things as Artistic in nature. While the business side of things is the Science part of the job. The whole right brain, left brain argument. People that have seen my work will attest I’m more of the Science side.

      Reply
  5. Don

    Chuck,
    Excellent article as usual. It has already been said that some of the older folks may already know this, but it should be noted in capital letters that this is an example. Figuring out what your labor rate should be is only the beginning of figuring out how much to charge for a given project. Some less experienced folks might think it is just about adding up all the sticks and bricks plus your labor and magically that is the selling price. If they ARE thinking that then they would be mistaken!

    Don

    Reply
    • Chuck Kiser

      Thanks Don,

      You are 100% correct and thanks for mentioning the labor rate is just the start of the pricing exercise. Generating accurate and competitive prices is difficult and time consuming when the costs for everything continue to change rapidly. Wouldn’t it be great to have a MSRP for all of our jobs?

      Reply
    • Chuck Kiser

      Thanks Greg,

      Hey did you figure out how to turn your laptop on yet?

      Kidding, Kidding……..

      Reply
  6. Sim Ayers

    “Reduce expenses or work more hours, if possible. ”

    Hours worked per year 3744

    I guess I need to start working 72 hours a week to break even in this economy.

    Sim

    Reply
    • Chuck Kiser

      I hear you Sim, The numbers that ‘poop’ out of the workbook are scary at best in this current work environment. You made me think about my example workbook. I made a mistake and your post highlighted it. I stated that the spreadsheet was dynamic in nature. So when the input data changes all the calculated data changes with it. It does in all but one case. Can anyone tell me where it does not? Anyone…..

      Reply
  7. David Luyendyk

    There are some tools that could supplement concepts in Chuck’s great article. While I’m not a carpenter, I do own a small business and have spent a good deal of time researching tools that make billing, tracking expenses and generally keeping track of my cash flow a lot easier. For my business I use http://www.xero.com. I’ve found it to be a great replacement for QuickBooks. For billing, take a look at http://www.freshbooks.com. Xero brings in my bank account balances every day automatically, which is a great time saver so I don’t have to manually enter transactions. Xero also integrates with FreshBooks, so anything you invoice through FreshBooks will appear in Xero and can be reconciled. If you have an accountant, you can provide him or her access to your Xero account, which they can access any time. I’ve found that to be very useful when I consult with my accountant. For my personal finances I use http://www.mint.com. It’s a great resource and lets me set up a budget and provides fantastic reporting. Just my two cents. Great article Chuck!

    Reply

Leave a Reply to Chuck Kiser

Click here to cancel reply.

Please note: Your first comment will be held for moderation/review by our staff before it appears. After you have one comment approved, all of your subsequent comments will appear immediately. Read our comment policy for more information.