Spreadsheets 101 for True Beginners: How to Calculate Freight Rates

Alright, my spreadsheet-loving crew! It is time to put our spreadsheet to work and make it helpful to our business! And we are going to start with learning to calculate freight rates.

If you are new to the blog, and new to spreadsheets, I recommend going back to this post. If you already know just enough about spreadsheets to be dangerous, then keep reading!

Why Calculate Freight Rates?

Let’s face it- shipping monuments is different than shipping clothes, trinkets or tools. Our products require special handling and packaging because they are heavy, large and can encounter devastating damage during transit. Of course, this makes finding reasonable and reliable freight a little tricky.

Our monument industry has several great freight providers who know our industry inside and out. And, while they aim to keep freight as low as possible, there are countless variables that require they constantly adjust their rates.

Things such as fuel, fuel taxes, insurance, DOT compliance, employee retention, etc, all drive the prices we pay for freight.

And, if your business is anything like mine, you pay a lot in freight!

Keeping up with the freight rates your carriers are charging will allow you to make pricing your memorials a little less daunting. And, of course, we use a spreadsheet to do that.

Today I am going to walk you through how I create a spreadsheet that will allow me to calculate and compare freight rates. Because I want my spreadsheet to be useful for years to come, I set it up in a manner that allows me to change freight rates and calculate shipping costs quickly and easily.

So open up the practice spreadsheet we have been creating together, and let’s get started!

Add a New Sheet

To get started, we are going to add a new sheet to the workbook we have been creating. Adding a new sheet to your spreadsheet is easy. If you look at the bottom of your spreadsheet, you will see a little plus sign.

That plus sign’s entire purpose is to create new, blank spreadsheets. So click it.

After you click on it, you will be presented with a new, beautifully blank spreadsheet. But…this isn’t just ANY spreadsheet. It is a new spreadsheet in the same workbook we have been creating.

You will notice that the old spreadsheet’s name is “Sheet1” and the new sheet’s name is “Sheet2”. I don’t like those names because they are just too generic and tell me nothing about their contents. So we are going to rename them.

If you right click on Sheet1, several options will populate. You want to scroll up to “rename” and click it.

After you click “Rename”, your tab name will darken. That means it is ready to assume a new name!

Sheet1 is the sheet we have used to calculate weight and will build on to compare freight rates. So let’s call it “Weight n Freight”. Just type it in and hit the enter key.

Now go over to Sheet2 and follow the same steps to rename it. We will call it Freight Carriers.

Now, before we get into the nitty gritty, let’s move our tabs around. If you click “Freight Carriers” and HOLD your click, a little arrow will appear.

While holding your click, if you move your mouse to the left, you will notice the little arrow will position itself in front of “Weight n Freight”.

This little arrow is asking you if you want to move the “Freight Carriers” tab in front of the “Weight n Freight” tab. Agree with it and release your click. Your tabs should look like this.

We are now ready to get to work!

Carrier Pricing

We are going to start by making headings for our list. You will recall we made headings in our very first post. Refer back to it if you can’t recall how.

Remember: the purpose of this sheet is to list our carriers and associated information. The goal is for us to choose the best freight rates for our loads.

When I sit down to do this, I like to list every carrier I have used in the past six months. This allows me to compare my existing relationships and also explore new relationships.

So now is the time to pull out some recent freight invoices.

If you are digging through paper files for these freight invoices, I do have a better suggestion for you: save time by scanning your invoices into QuickBooks Online!

When you enter a payment into QuickBooks Online, simply scan the invoice into your PC and upload it with the payment. I added the image below to show you where to upload the invoice.

This makes finding invoices super quick and allows you to let go of all that paper.

ANYWAY, no matter how you organize your freight bills, pull out those invoices and let’s start sifting through the important information we will need.

If you are also exploring new carriers, you will need to call them for rate information.

Okay! Moving on!

The headings I am choosing to use are:

• Carrier Name
• Account Number (helpful if you are exploring new vs existing relationships)
• Date of latest invoice (if any)
• Weight from invoice
• Total weight shipped
• Handling Charges
• Sur Charges
• Total invoice amount
• Freight per Pound

It is important to note that the freight per pound calculation is automated by using a formula. We discussed formulas in this previous post and are using them here.

Of course your list may look different than mine, and that is okay! The important thing here is to make sure that you formulate your rate per pound and then copy and paste it into subsequent cells. This will allow for you to automatically calculate the rate per pound for each carrier whose information you enter.

Now For The Fun!!

Now that your list of carriers is complete, we are going to click on the tab labeled “Weight n Freight”.

In Column L, cell L1, you want to simply type in the plus sign. Then, once the plus sign is in the cell, and the cursor is blinking directly next to it, click the “Freight Carriers” tab and then click on the name of your first carrier and hit the enter key.

The name of Carrier 1 should automatically populate.

Now. You may wonder why we should automate the name of the carrier. We do this because the carrier could change names in the future. And, if they do, you will only need to change the name on the “Freight Carriers” tab and not any of the other tabs you create.

Genius, right?!

You will continue to repeat this until all carrier names are listed on your sheet.

Now…here comes the exciting part!

Automate Your Freight Rates Calculation

This is my favorite part of the entire process! Next we are going to tell the program to take our per pound freight rate and multiply it by each individual piece’s weight!

The end result will be beautiful freight estimates that are right at our finger tips.

To get started we are going to stay in the “Weight n Freight” tab and click in cell L2 underneath our first carrier’s name. By doing that, we are indicating that our data is related to Carrier 1 and it’s relationship to the product listed in row 2.

So click in L2 and then enter that trusty old plus sign. That plus sign is simply telling the program to get ready for our calculation.

Then, we are going to click in our tab labeled “Freight Carriers” and find Carrier 1’s per pound freight rate. In my spreadsheet, it is cell H2.

BUT. After you click in cell H2, you need to then click the * key.

That funky little star (the asterisk) is telling the system we want to multiply the figure in cell H2 by another figure. Directly after clicking the * key, click on the “Weight n Freight” tab and then click in cell K2 listing weight for product 1.

This formula is telling the system to multiply our weight in one tab by the freight rate in another. It will look like this.

Once your formula is complete, simply click enter to tell the system you are done, and you will see a nicely calculated freight price for your product.

Now…we want freight prices calculated for our remaining products. And doing that is super simple! But, before you begin, there is something you should know. I circled it in red below.

Variables

We want this spreadsheet to be completely formulated so you can calculate rate changes just as quickly as your carrier dishes them out.

To accomplish this automation, we will use dollar signs. Yep! Dollar signs!

If you click in cell L2, where you just calculated your freight for Carrier 1, you will notice the formula displays in the formula bar.

Move your mouse up to that formula bar and add a dollar sign on either side of the H, like below.

=+’Freight Carriers’!$H$2*’Weight n Freight’!K2

After you add the dollar signs, click enter. This tells the program that cell H2 will be the fixed variable in this formula.

Now all you do is click in cell L2 and copy the formula. Then you paste it in the remaining cells for Carrier 1 and you are done!

There you have it! Beautifully calculated freight rates for your products. Now you can repeat the process for the next carrier.

Once you repeat the process you will be able to compare prices for each product and carrier. This will help you price out monuments as well as identify the carrier(s) you would like to work with for each product.