Anyone in a trucking business? Cost tracking...

[araymc1965]

I have some industry-specific questions & am hoping to get a bit of help with creating a spreadsheet to capture what my boss has now tasked me with creating. Of course, I'll list what he is wanting and maybe it will cross over to other industries as well.

We operate a business where we go to towns in about 100 mile radius of our office. He wants me to create a tracking spreadsheet where we can place mileage, fuel cost, time, & driver hours in order to find out our costs to do each stop. He rambles off a bunch of things so I am basically trying to decipher what it is he really wants and I think this would best summarize my task.

If it is too much to ask for help, I apologize.

Thanks in advance,
Anita

 

[CA_Punit]

Hey Anita,

We can only help if there is problem with sample data.

Say for Instance

If we have Total Distance Travelled/Mileage, it will provide us total Fuel Consumption

We can have time from his duty hour books or log book

We can get Fuel Consumption *(Fuel cost/Litre) to find out the Fuel Cost which can be checked with the Payment made.

Taking daily Fuel Stock can provide us details of Actual stock with Calculated stock


Just an overview

 

[AhoyNC]

Anita,
The company I worked for runs a private truck fleet of both local and long haul.
I assume since you say you run out about 100 miles would mean you are basically just doing local deliveries.
What type of trucks are you running- box, tractor trailer, both.
How many units (trucks, trailers) do you run?

If you breakdown your cost/mile per each unit you can then get a cost per destination.
The normal cost you would want to consider:

• Fuel Cost
• Truck/Trailer lease or purchase payments
• Repair & Maintenance
• Truck Insurance
• Permits & Licenses
• Tires
• Tolls
• Driver Wages & Benefits (are drivers paid per hour, per mile, per trip etc.).

Setting up a spreadsheet to track the above cost would not be difficult, but it would depend on the number of units and types of trucks you are running.

 

[araymc1965]

Anita,
The company I worked for runs a private truck fleet of both local and long haul.

I assume since you say you run out about 100 miles would mean you are basically just doing local deliveries.
YES, we do basically local-- we haul roll-off boxes (giant dumpsters) to various job sites within about 100 miles

What type of trucks are you running- box, tractor trailer, both.
We run roll-off trucks--- not box or tractor trailer..LOL

How many units (trucks, trailers) do you run?
We have about 6 trucks.

If you breakdown your cost/mile per each unit you can then get a cost per destination.
I'm looking at one of the locations which is 48 miles round trip. Our trucks should get in the neighborhood of 4.87 mpg.

The normal cost you would want to consider:

• Fuel Cost Average diesel cost: $2.25 per gal
• Tolls None
• Driver Wages & Benefits (are drivers paid per hour, per mile, per trip etc.). Paid hourly plus a per box incentive

The below he is not specifying to be included in what he wants to track. I believe his main plan is to track the per job, per location in order to be able to know when we quote our pricing that we are not losing $$ because of the distance/time/fuel for those in the farthest distance.

• Truck/Trailer lease or purchase payments
• Repair & Maintenance
• Truck Insurance
• Permits & Licenses
• Tires

Setting up a spreadsheet to track the above cost would not be difficult, but it would depend on the number of units and types of trucks you are running.

 

[AhoyNC]

There are number of ways to do this, but maybe this example will give you some ideas. You could setup a Data sheet to capture the cost items and then have a summary tab.
It may be difficult to have the drives give you the starting and ending odometer reading for each trip, so you could just use an estimated mileage in the mileage column and delete the odometer columns. Also, add any other cost columns if needed.

The summary sheet could be setup to look at a date range for total cost per mile and/or by customer.

Hopefully this will give you a little help.

Data Sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1							4.87				2.25
2	Date	Truck#	Beg. Odometer	Ending Odometer	Customer	Miles	Est. Fuel mpg	Driver Hours	Driver Pay	Box Incentive	Est. Fuel $	Cost	$/Mile
3	6/12/2020	Unit1	45000	45048	Cust1	48	9.856	2.25	$34.43	$25.00	$22.18	$81.60	$1.70
4	6/12/2020	Unit2	56500	56530	Cust2	30	6.160	1.00	$15.30	$25.00	$13.86	$54.16	$1.81
5	6/13/2020	Unit3	33600	33700	Cust3	100	20.534	4.25	$65.03	$25.00	$46.20	$136.23	$1.36
6	6/13/2020	Unit1	45048	45077	Cust2	29	5.955	1.25	$19.13	$25.00	$13.40	$57.52	$1.98
7	6/15/2020	Unit6	66000	66080	Cust6	80	16.427	3.00	$45.90	$25.00	$36.96	$107.86	$1.35
Data

Cell Formulas
Range	Formula
F3:F7	F3	=D3-C3
G3:G7	G3	=F3/$G$1
K3:K7	K3	=G3*$K$1
L3:L7	L3	=SUM(I3:K3)
M3:M7	M3	=L3/F3
I3:I7	I3	=H3*15.3

Summary Sheet

Book1
	A	B	C	D	E
1	Summary
2		Beg. Date	End Date
3	Date Range	6/1/2020	6/30/2020
4
5		Total $/mile
6	Average	$1.52
7
8	Customer	Total Miles	Total Cost	$/Mile
9	Cust2	59	$111.68	$1.89
10
Summary

Cell Formulas
Range	Formula
B6	B6	=SUMIFS(Data!$L$3:$L$7,Data!$A$3:$A$7,">="&$B$3,Data!$A$3:$A$7,"<="&$C$3)/SUM(Data!$F$3:$F$7)
B9	B9	=SUMIFS(Data!$F$3:$F$7,Data!$E$3:$E$7,$A9,Data!$A$3:$A$7,">="&$B$3,Data!$A$3:$A$7,"<="&$C$3)
C9	C9	=SUMIFS(Data!$L$3:$L$7,Data!$E$3:$E$7,$A9,Data!$A$3:$A$7,">="&$B$3,Data!$A$3:$A$7,"<="&$C$3)
D9	D9	=C9/B9

 

[araymc1965]

This looks great! I'm off until Monday and will be excited to work with this example. I'm sure I will have questions & hope you won't mind my asking some.

Thank you so much for putting in the thought process to help. I try to jump through hoops to achieve what he asks of me...the boss, i mean.

Anita

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Please review the rules and update your thread with the relevant link(s). Thanks.

 

[araymc1965]

My apologies.

I've cross-posted my inquiry in a total of 2 forums.

Thanks

 

[Peter_SSs]

I've cross-posted my inquiry in a total of 2 forums.

Thanks. The rule also asks you to provide links to those cross-posts. That way, if helpers want, they can check if the issue has been solved elsewhere or whether they might have a better solution for you.

 

[araymc1965]

Thanks. The rule also asks you to provide links to those cross-posts. That way, if helpers want, they can check if the issue has been solved elsewhere or whether they might have a better solution for you.

Oh! I totally didn't follow that... I'll have to fix when I'm back on my pc.