help need with FIFO inventory calculation

[apprentice]

I am setting up a inventory database and i will like the cost of the items to be calculated using FIFO. Can anyone help me with some codes?

Sample tables:

PARTS TABLE:
part code
description
cost
quantity on hand


PART TYPES
part type id
party type

INVENTORY
invent id
location
reorder quantity

INVOICE
id
date
part code
part type
qty
cost
ext cost

NB
For every item i will like the previous cost to be charged before the new cost. eg. if 10 pens where entered at $2 and another 5pens were entered at $4 each and 1 need 11 pens, i want the first 10 to be charged at $2 each and one at $4.

i hope i explained it clearly.

 

[jdraw]

See the info and links here for more info.

Inventory and stock control are not trivial.
What is your experience level?

Good luck with your project.

FYI:
You should not use field names with embedded spaces. Stick to alphanumeric and "_" (underscore)

Date is a reserved word in Access.

I think party type is misspelled.

More info re FIFO and LIFO
http://en.wikipedia.org/wiki/FIFO_and_LIFO_accounting

 

[apprentice]

I do not have much experience in VBA programming and query calculations. the link was not helpful

 

[jdraw]

If the first link I provided was NOT helpful, perhaps you should describe in detail WHAT you are trying to accomplish, and WHAT part of FIFO is important enough to you that you put FIFO in the title of the post.

Please tell is more about your needs.

 

[gemma-the-husky]

I can only say that this is very difficult. Managing inventory quantities is relatively simple. Managing valuations is very difficult.

There are two different issues.

a) valuing your current stock - relatively simple. Given a static quantity, just consider the recent purchases in order.

b) costing your production/sales This is the difficult area, as if the cost of stock varies depending on the date it was purchased, then you are not issuing homogenous stock.


Personally, I would consider charging your stock either at current price, an average cost, or at a standard cost, and adjusting for variances between the actual price and the "deemed" price, when processing your purchase invoices. Rather than trying to manage a strict FIFO system.

If you also need to include raw materials into work in progress, and include labour and other overheads, then this even more complex.

This whole area is very very complex, and commercial solutions will be both very large, and very expensive.

I understand inventory, and I actually think you will find it very difficult to source a true FIFO based system.

 

[apprentice]

here is a similar database i am trying to build. its basically recording the issuance of gasoline from a bulk tank and keeping in mind the fluctuating cost per gallon over time.

table examples;

CUSTOMERS
id
name

SALES
id
meter_reading
quantity
price_per_gallon
Total

BULK TANK
id
P_Date
Avg_Price_Cost
Last_Price_Per_Gallon
Purchase_Quantity
Running_Quantity
Available_Quantity

PURCHASE INVOICE
id
PO_Number
P_Date
Purchase_Price_per_Gallon
Purchase_Quantity
Total Cost

The purchase order will be done manually. I will like help in ways i can update the bulk tank quantity whenever a purchase invoice is done. When ever diesel is added to the tank, if there is any other diesel remaining from the previous deposit i will like it to be issued at the old price if ever the new price per gallon increases or decreases. until the previous amount is zero then the new price can be considered. therefore if the tank limit is 50 gallons and it was refilled when it reached 10 gallons, i will like the remaining 10 gallon to be dispersed at the previous price before considering the new cost if there is.

i hope i am very clear.

 

[gemma-the-husky]

here is a similar database i am trying to build. its basically recording the issuance of gasoline from a bulk tank and keeping in mind the fluctuating cost per gallon over time.

table examples;

CUSTOMERS
id
name

SALES
id
meter_reading
quantity
price_per_gallon
Total

BULK TANK
id
P_Date
Avg_Price_Cost
Last_Price_Per_Gallon
Purchase_Quantity
Running_Quantity
Available_Quantity

PURCHASE INVOICE
id
PO_Number
P_Date
Purchase_Price_per_Gallon
Purchase_Quantity
Total Cost

The purchase order will be done manually. I will like help in ways i can update the bulk tank quantity whenever a purchase invoice is done. When ever diesel is added to the tank, if there is any other diesel remaining from the previous deposit i will like it to be issued at the old price if ever the new price per gallon increases or decreases. until the previous amount is zero then the new price can be considered. therefore if the tank limit is 50 gallons and it was refilled when it reached 10 gallons, i will like the remaining 10 gallon to be dispersed at the previous price before considering the new cost if there is.

i hope i am very clear.

yes, it's very clear, but it's very hard. It's easier in many ways to say

AVERAGE COST
the 10 gallons are worth £5.00 a gallon (so the stock is worth £50)
We buy another 50 at £5.50 a gallon (which costs £275)

so now we have 60 gallons, costing £325 in total, so the average cost is now £5.4166, and charge all future supplies at this new rate. This is actually easier to do than what you are trying to do, although it is not FIFO

or STANDARD COST
An other way is to have a standard cost, of say £5.00 a gallon, so you write off the cost at a fixed £5.00/gallon

When you buy 50 gallons at £5.50 you need to take 50 gallons into stock at the STANDARD cost of £5.00, and write off the extra cost (of £25) as a on-off variance (loss). Also easier than trying to do pure FIFO.


It's very complex, and the costing method used needs to be agreed at a senior level. Trying to manage consignments with true FIFO as you would like needs each consignment to be managed separately, which is murderously difficult.

Hope these thoughts help.