Need help with an equation. Trying to build a Excel Formula but I am stumped.

J

Joeynapp

New member
Joined
Sep 21, 2019
Messages
4
Hey guys new to the forum. I was asked by a friend of mine to build a excel formula for the equation I will show below. I have tried a couple ways but it is not computing upstairs I shouldn't really have trouble on this but I keep drawing a blank.

equation:

Bid Rate on 500 miles at an all-in of $2 a mile = $1000

Fuel Surcharge 20% (x $1000) = $200

Line Haul would be $800

BUT - the way the shipper will calculate it would be as follows:

FSC = Linehaul of $800 x 20% - so $160 to the truck for fuel

Add that to the $800 linehaul and you’re at $960. now your $40 short.

How would I figure out the percentage difference.

I feel this is a simple formula, but I am stumped.
 
Sorry, not familiar with the jargon. Is it obvious what bid rate is? Fuel surcharge? Line Haul?
 
No problem I know it’s confusing, probably how I’m explaining it.

Total bid rate would be $1000
Fuel surcharge would be 20%
Line haul would be- 833.34 (is the closest I could come up with.)
Miles would be 500
Goal is 2.00/ mile including fuel surcharge.
Total equaling $1000


My issue is that the shipper goes off the bid rate and adds the 20% of line haul rate to get the fuel surcharge (equaling my number $1000) So I am trying to figure out what equitation I would use to figure out the bid rate without having to do different variations to figure out the line haul. I am trying to create a formula for excel to punch the numbers in and make it simple. So basically total bid - the fuel surcharge % to get the line haul rate.

How I got the 833.34 was

I kept multiplying by .20 to get as close as I could within .00* of the total bid rate which was
833.34*.20 = 166.668
Then
833.34+ 166.668 = 1000.008 I would subtract the fraction of a cent to keep it 1000.00.

Sorry if I’m explaining it wrong.
 
Bid rate: is the total my friend is going to receive to haul freight on a specific lane. Lane is the specific pick up and destination of said freight (Chicago IL. To St. Louis MO) this rate is the goal rate with the line haul and fuel surcharge combined.

Line haul: is what the actual gross pay is just to haul that freight with out the cost of fuel. This is the rate the shipper uses to calculate the fuel surcharge

Fuel surcharge: is what the shipper will pay just for fuel. This is guaranteed paid to the carrier no matter what.

Carrier: is the trucking company that haul the goods from the manufacturer to their customer.

Shipper is the manufacturer of goods that must get to their customer.

How this shipper comes up with the fuel surcharge is they take the “line haul” rate and add 20% to equal the total paid to the carrier aka our bid rate.

How carriers come up with the total rate, what they want bid. Is by calculating their mileage and multiply it by a price per mile. So if I drive 500 miles and my goal pay is $2.00/ mile so my bid would be $1000.

What I am trying to accomplish is to

punch in my bid rate, my target. $1000

Punch in the percent (20%) that will be paid for my “fuel surcharge”

To come up with the “line haul rate”

Hope this clears it up.
 
Joeynapp said:
No problem I know it’s confusing, probably how I’m explaining it.

Total bid rate would be $1000
Fuel surcharge would be 20%
Line haul would be- 833.34 (is the closest I could come up with.)
Miles would be 500
Goal is 2.00/ mile including fuel surcharge.
Total equaling $1000
Click to expand...
The question was not, what are the values of bid rate, line haul, etc. It's, what in the world do they mean? Remember you're talking to math people, not to truckers. Talk to us as if we were third graders ...
 
I think I get it.

number of miles times goal rate = total charge.

bid rate + surcharge rate = goal rate.

Therefore if you have a goal rate of 2.00 per mile and your surcharge rate is 20%, your bid rate is

[MATH]2 \div \left (1 + \dfrac{20}{100} \right ) = 2 \div 1.2 \approx 1.6667.[/MATH]
Let's see how that works in prectice.

Goal rate = 2.00 times 500 miles = 1000 dollars.

Bid rate 1.6667 times 500 = 833.35.

20% of 833.35 = 166.67.

833.35 + 166.67 = 1000.02.
 
You must log in or register to reply here.
Top