# Thread: Perform calculations on inventory using FIFO?

1. Registered User
Join Date
Mar 2007
Posts
2

## Unanswered: Perform calculations on inventory using FIFO?

I have been asked to design a few reports for an existing database for a gas station. One of the reports needs to calculate the cost of gas on hand always assuming First-In-First-Out (FIFO) inventory.

How do I perform calculations assuming FIFO?

The database has 4 main tables described below, if that will help you. What I don't know how to do is match a cost from the Cost table with retail price from the Retail table for each gallon sold in the Sold table.

Primay keys are marked in bold; foreign keys are marked by the same color as the primary key of the main table.

If anyone would have suggestions, I would appreciate it.

tblCost
• CostID
• Date
• Cost
tblPurchased
• PurID
• CostID
• Date
• Store
• GallonsPurchased

tblRetail
• RetailID
• Date
• Store
• RetailPrice
tblSold
• SoldID
• RetailID
• Date
• DateGallonsSold

2. Registered User
Join Date
Sep 2004
Location
Tampa, FL
Posts
520
I have been asked to design a few reports for an existing database for a gas station. One of the reports needs to calculate the cost of gas on hand always assuming First-In-First-Out (FIFO) inventory.
Allow me to ask for the sake of clarity: What you need to do is assuming you purchased 100 gallons of gas at \$1 a gallon. you sold 70 of those gallons when you purchased another 100 gallons at \$1.50 a gallon. you would need to conclude that you had 130 gallons of gas on hand. 30 gallons you paid 1\$ each for and 100 gallons of which you paid \$1.50 for. So you have \$180 worth of inventory correct?

What I don't know how to do is match a cost from the Cost table with retail price from the Retail table for each gallon sold in the Sold table.
This is where I am confused because you are asking a separate question here. Your retail cost is inconsequential to the cost of the current inventory.

3. Registered User
Join Date
Mar 2007
Posts
2
Yes, your first example is what I need to come up with. How would I accomplish that in Access 2003?

In my haste yesterday, I didn't explain myself thoroughly. I am working on two reports... one to calculate the cost of gas on hand (assuming FIFO like above), and the second to calculate the margin on all fuel sold (price - cost * gallons sold). Here's where I need to match a cost with a price.

Hopefully, this is is a little clearer now!

Thanks.

4. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Wish I had more time. This looks like fun. I'd probably develop a table with a DateRangeID and the begin and end date range. Then I'd replace the Date fields in each table with that DateRangeID. Saves from trying to do some kind of "between" join.

Technically, you're doing Average Costing, not FIFO.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•