scottbrown

Hi there.

I am trying to set up a spreadsheet that monitors mpg for about 30 veichles. So that bonuses cn be paid to be best and training given to the worst.

I just cant seem to get my head round it.

The raw data will be in kms covered, litres used. Then the mpg.
I would like to be able to do this on a week by week bases.

Somebody please help its doing my nut in..

rssteve

you are taking the piss, right? what happens if one of your drivers gets the old banger than needs a good service and hence uses more fuel than the others. Or one of your best blokes might carry more tools with him or something, should he then be penalised because bobs got fuck all with him and drives slow all day, getting less production done, but getting a bonus.

Do you have a courier or haulage(sp) business or something?

scottbrown

LOL.
basically have been tasked my director to have a look at our fleets performance. All our drivers are on sililar job, trunking own goods to RDCs. All in same trucks. They are also in same truck everyday.

I just need to work out some kind of way of monitoring fuel usage.

any ideas?

Iain Mac

Easy enough to do and increasingly common in fleet.

Do you want to incentivise fuel consumption or shopping around for lowest cost fuel?

I can't show columns in here, so follow the logic below:
A = Day;
B = Litres Purchased;
C = Gallons conversion (=b2/4.546);
D = KM driven;
E = Miles conversion (=d2*0.62) is close enough;
F = MPG (=e2/c2)

Accept that on individual days there will be anomolies (like he bought no fuel but still did 100 miles that day, or filled up last thing at night for a big run the following morning)

At the end of the month, total the columns and that's you basis for bonus or otherwise (but watch out for a whole load of vans running on fumes in the last day of the month!)

scottbrown

That is exactely what I want, But I am shit with excel and cant get it all to work.

christianh3

In the top right hand corner of the excel application there will be a little menu item called "Help", trying using it

scottbrown

I dont have this. Mine says "ask on PF"

christianh3

@ me, LOL fair play, good answer

Iain Mac

If you are shit on Excel then your boss may have asked the wrong person to handle this - believe me, when there is cash at stake you have to be right first time, every time.

Assuming the columns headings are on Row 1, your first data input row will be Row 2. Type my formulas (but without the brackets) into columns C, E and F, then click on C2 and put your cursor on the bottom right hand corner where it will change. Click down the left button on the mouse, hold it down and drag down 30 rows (or 22 if you don't work weekends - that will copy the formula throughout these rows. Do the same for column E and for Column F.

The remaining columns, A, B, and D are for you to complete each day from fuel receipts and driver logs.

And just in case you don't know about autosum, when you are at the foot of a column or end of a row, go into the first empty cell and click on the thing in your toolbar that looks like a Greek E, then press enter and it will add up everything in that column or row, so long as there are no empty cells in the range.

Iain Mac

Another option is to use a fuel-card supplier and have them provide this data for you!

scottbrown

afternoon all.

Right been doing this again all afternoon.

Got the sheet all working, but want to add averages for each type of veichle as well as a fleet average.

Decided to do on a week by week bases as thats how the info appears on fuel card statement.

anybody help?

thanks

Iain Mac

To do this you will need to either include every vehicle on one big spreadsheet or put each van on a different worksheet within the same file. Even this will very quickly become large and cumbersome. Someone smarter than me will know how you create a data input sheet that then populates and saves the new info as part of a historic record - maybe in ACCESS.

If you are determined to proceed in Excel, you will need a way to identify which vehicles are the same by giving them a cell on the spreadsheet to say what they are, e.g.:
All your Transit 2.5 190 LWB get coded as, say, 1
All LDV Convoy 2.5 = 2
Leyland Daf 2.5 = 3
etc.

Then you are get into IF functions (=IF(A3=1,F22,0) where F22 will be the average MPG for that vehicles, etc and for that you need to get into the help screens, then a SUMMARY sheet where the results and averages for each individual van are pulled forward from the appropriate data file.