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.
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.
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.