FIFO Method

[jenna_max]

Hi,

I am trying to put together a spreadsheet that will show consumption of fuel and the price of what has been consumed. The fuel is topped up weekly, and then used by employees for various vehicles, I want to be able to put a dollar value to the fuel they are using. I have used an average in the past but want a more accurate figure and thought the FIFO method would work best. With this in mind, I'm not sure how to best set up my spreadsheet, at the moment I have the vehicle and Litres used. I also input the cost to top up the fuel, both total and per Litre cost. The Fuel tank is 10,000 Litres and is usually topped up anywhere between 5000-7000L at a time. I want to use the price per Litre to allocate a cost to consumption, which will change once the top up diesel has been used.

Any help would be appreciated.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[teylyn]

Hello,

Please take a look at the attached file.

The table is used to track tank top ups and fuel taken out by vehicles of the fleet.

Beginning with a full tank at a hypothetical 1.5 per litre, fuel taken out is costed at 1.5 / L

Then the tank is topped up with 1000 L at 1.8 per litre. The tank now contains some fuel at 1.5 per litre and some at 1.8 per litre. We can tell exactly how many liters per price, so we calculate the pre-topup fuel * 1.5 and add the 1000 L * 1.8. Divide this by the total liters now in the tank and you know the what a litre of fuel in the tank is now costing. This new price is applied to fuel taken out until the next top up.

The spreadsheet uses an Excel Table where formula get filled in to new rows automatically.

Let me know how you get on with that approach.

cheers, teylyn