Results 1 to 4 of 4
  1. #1
    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
    • Grade
    • Cost
    tblPurchased
    • PurID
    • CostID
    • Date
    • Store
    • GallonsPurchased

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

  2. #2
    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.
    Darasen

  3. #3
    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. #4
    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.
    Inspiration Through Fermentation

Posting Permissions

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