Solution for calculating fuel cost, with different amounts purchased at different prices

[OKSteve]

Using Excel 2013 and Win 7. Trying to make a "simple" spreadsheet, to compare cost of leg of a trip. Stumped on how to calculate when at stop 2 or stop 3, we have purchased fuel at different prices. Need calculation to use all fuel purchased at original price first, then what was purchased at second leg next, and so forth. Don't always fill up, as many times, that is not ideal. Below is a walk-through of attached example file:


Starting with 79 gal that I paid $2.65 per gallon, for a transaction cost of $209.35. Then I depart, and first leg fly 385 mi. used 38.5 gal., which leaves 40.5 available space to refill. These people's fuel was high, so didn't purchase any, so leg cost is gal burned times cost per gal. Next leg, we go 135 mi., and are now down to 27 gal. So we buy 50 at $4.65/gal, and leg cost here will be same calculations as first, as still burning original fuel. But from here forward, I am lost on how to "use up all previous fuel cost, and start on next fuel cost, and next, etc. Any help will be greatly appreciated. I am aware second leg cost number is not accurate. I used same formula, as first leg, but on next set of cells, and that is when I became aware of error in my project. I also posted this at mrexcel.com a few days ago, but no solution from there yet. Thanks for any input.

 

[NARAYANK991]

Hi ,

I am not sure you are going about it the right way.

You have started off with 79 gallons of fuel , purchased at 2.65 per gallon.

It is clear that you cannot use any other fuel , at what ever cost you may have purchased it , till these 79 gallons of fuel have been consumed.

Thus , the total length of the first two legs ( 385 + 135 ) , which consumed just 52 gallons of fuel , must have been made using 52 gallons out of the 79 you started off with.

The next leg of 405 consumed 40.5 gallons , which means it must be made up of 27 gallons remaining of the 79 , and 13.5 gallons from the 50 gallons you added along the way.

The cost of this leg would then be :

27 * 2.65 + 13.5 * 4.65

which would equal 134.325

The cost of the first leg was 38.5 * 2.65 = 102.025

The cost of the second leg was 13.5 * 2.65 = 35.775

Adding the cost of the third leg gives us a total fuel consumption cost of :

272.125

Obviously , fuel we are left with is what we purchased along the way @4.65

How much fuel are we left with ? 79 + 50 - 38.5 - 13.5 - 40.5 = 36.5

The cost of this fuel is 36.5 * 4.65 = 169.725

The total amount spent in purchasing fuel is 79 * 2.65 + 50 * 4.65 = 441.85

If we subtract the fuel cost spent from this , we have 441.85 - 272.125 , which is 169.725 , which matches the cost we arrived at earlier.

If you confirm that all these calculations are correct , we can then derive the required formulae.

Narayan

 

[OKSteve]

Hello,
Thanks for looking at this. You are absolutely correct, and that is why I am requesting assistance. I am not trying to solve for a particular trip, I want to have a spreadsheet, where I can plan different routes, and compare costs. What I am needing assistance with, is a formula, to put in cell K4, (and following corresponding cells as in K5, K6, K7 etc. that will know which cost value, to use, and be able to use two cost values, in a situation where two different values apply, for the correct fuel that is being used. I guess it would be similar to first in, first out inventory, where the price changed significantly, every time a purchase was made. Perhaps an "if /then" type of thing, but that is beyond my excel knowledge. Thanks again, for looking.

 

[NARAYANK991]

Hi ,

I don't think it is so straightforward.

Can you specify the following ?

How many times can you refuel in any one leg ?

Narayan

 

[NARAYANK991]

Hi ,

Another issue to be settled is the number of refuels that can accumulate over many legs. To illustrate this problem , let us start with 50 gallons of fuel.

1. The first leg consumes 20 gallons leaving 30 ; you refuel 10 gallons.

2. The second leg consumes 20 gallons leaving 20 gallons , out of which 10 gallons are at the starting rate , and 10 are at the first refuel rate. Now you refuel 10 gallons.

3. The third leg consumes 70 gallons , which means it has taken the following :

• 10 gallons the starting rate
• 10 gallons at the first refuel rate
• 10 gallons at the second refuel rate
• 40 gallons at the third refuel rate

Obviously this can be extended to accommodate many more rates and quantities. So what is the limit to the accumulation ? Can we say that we will never have more than 2 rates in any leg ?

Narayan

 

[OKSteve]

A leg is one departure, one destination, and the fuel consumed between those. I think no more than 2 rates per leg is accurate most times, but possibly 3, as in your example.However, in your example,we would not have been able to go on the third leg, unless we purchased more than 10 gal. at the second refuel rate, as we would have exhausted all fuel, before reaching the third refuel station. Thanks again for looking at this.

 

[NARAYANK991]

Hi ,

Please upload a workbook with enough data in it , in as much complexity as possible , so that what ever solution is arrived at can be tested thoroughly.

Narayan