Estimating Fuel Expenses…

I have been asked how we figured out our potential fuel cost, so I will try my best to explain it here.

The first bit of data is based on the manufactures reported performance estimates of the RPM, Speed and GPH data. This data is not 100% accurate and can have a variance of +/- 5% based on weight, performance of the hull and current.

For these base calculations we will be using the mid range performance data for the boat we selected. We will create an area on the spreadsheet to hold this data.

A B
1 RPM 1500
2 GPH 8.5
3 Knots 8.4
4 Tank Size 432
5 Res. Tank 300
6 Fuel Cost $3.77

We get the nautical miles from chart calculations or plotter, in the is case we are using 100 nautical miles.

A B C D E F G H I J K
7 Nmile RPM Knots MPH GPH Range Hours 10% Res. Tanks Cost
8 100 1500 8.4 9.7 8.5 492 10 111 N .26 $419.64

Cell A8 data is entered manually, 100 Nmiles.

Cell B8 is pulled from the first data.

=B1

Cell C8 is pulled from the first data.

=B3

Cell D8 is the first calculation we perform to convert the knots to MPH . We do this to determine time over distance which is calculated using MPH. You will need to multiply the knots by 1.15078.

=(C8*1.5078)

Cell E8 is pulled from the first data.

=B2

In Cell F8 determining the range is a little more complicated as we created a formula to calculate if we are using a reserve tank.

=IF(I8=”y”,((D8/E8)*(B4+B5)),((D8/E8)*B4))

Cell G8 the calculation for total time is made by dividing the distance by the MPH:

=A8/D8

Cell H7 is formatted to percentage and the value is manually to what your comfort level is.

In Cell H8 we determine the total fuel needed plus a reserve percentage for the trip is done by taking the distance and multiplying by 1.15078 to convert nmiles to regular miles. This is done because we will also be using the mph value and is needed to make the calculation correctly. Then divide mph by gph, then divide the regular miles by that result. Sounds confusing but here is the first part of the formula:

((A8*1.15078)/(D8/E8))

The second part of the formula is the same as the first but you will multiply that total by the reserve amount you want to make sure you have.

(((A8*1.15078)/(D8/E8))*H7)

Finally, add the two together. So you formula in cell H8 will look like this:

=((A8*1.15078)/(D8/E8))+(((A8*1.15078)/(D8/E8))*H7)

Cell I8 is manually entered as a “Y” or “N”. This is used to calculate formulas based on the total amount of fuel you will be carrying.

Cell J8 determines the amount of fuel used out of the total fuel carried. Take the amount of fuel calculated plus the reserve and divide by the total fuel capacity. The formula looks to see if are using a reserve tank so we again use an ‘IF’ statement.

=IF(I=”Y”,H8/(B4+B5),H8/B4)

For this trip it is calculated that we will use .26 of the total tank capacity without reserve fuel.

Cell K8, the easy part, take the total amount of fuel you need to carry for the trip and multiply by the cost of fuel.

=H8*B6

Right now the formulas and spread sheet I am using is for testing against our projected budget but I will be making an easy to use spread sheet calculator soon that anyone can use simply by plugging in your base numbers.

Let me know if you have questions or suggestions.