COSTING: FIFO / LIFO / Weighted Costing

[FlavioT]

Hi!!
I'm having problems with costing!
I have 2 tables involved
1: dates and quantity sold
2: dates, price paid and quantity bought

So, I need to use FIFO (first in first out) to calculate the cost of the products on the 15 and last day of the month...

Anyone has already done it?? I cant figure in my head how would I do it!
It should be good that the user chooses the type of costing and then a Query would calculate it...
but how??

Thanks!

 

[JPM]

If I understand you correctly you are trying to calculate the actual cost of current inventory as of the 15th and end of the month.
I haven't done this myself either, but here is the logic as I see it:
In order to get the info you are looking for you would have to know what your current inventory is, i.e. 1,000 items.

Then you have to go to your purchasing records and figure out at what prices you bought your last 1,000 items.

Lets say you have the following records:

1-Apr 300 @ $15 4500
20-Mar 400 @ $12 4800
10-Mar 200 @ $14 2800
25-Feb 400 @ $10 1000

This would mean your current inventory on April 9 under FIFO is $13,100 right? (300*15 + 400*12 + 200*14 +100*10)
I hope this helps somewhat.
JPM

 

[FlavioT]

No... this would be the "pounded mean"...
the FIFO would be:
I bought:
10 itens at $10 on 1/1/04
20 itens at $15 on 1/2/04
30 itens at $20 on 1/3/04

So, if I sell 35 itens my cost using FIFO would be:
(10x10 + 20x15 + 5x20)/35

Using LIFO (Last in First Out)
(30x20 + 5x15)/35

And using the mean is what you told:
(10x10 + 20x15 + 30x20)/60

Anyone can figure a way by Queries for me to do it???

I can't!!!!!!!! PLEASE!

THANKS!!!!!
ray:

 

[JPM]

Ok, one more question for clarification. Are you saying you are looking for the FIFO CoGs for the current month?

JPM

 

[FlavioT]

I just want a tip on how to do it... but yes... I will be using FIFO...

Thats how the government wants to see the expenses...

I'd like some suggestions on how to work

It would be a thing like this
a(i) = unitys bought on day "i"
c(i) = cost of day (i)
b = unitys sold

If a(i) < b then a(i) = 0 b=b-a(i) cost = a(i)*c(i)
then this would loop to day a(i+1) and cost = cost + a(i+1)*c(i+1)
then the acctual cost would be: cost/b

But how to do this??? there must be a simple way........

 

[JPM]

I see that nobody else has posted any solution, so here is what I came up with. It's definitely not the cleanest solution, but maybe it gives you some ideas.

I created a spreadsheet that looks like this (sorry I haven't downloaded the tool that would allow me to post an actual spreadsheet):
A B C D E F G H I J
1
2 Purchases: Sales:
3 Date quantity item price Total cost Date quantity Input field:
4 4/1/2004 51 $10.00 $510.00 4/1/2004 62 4/10/2004
5 4/1/2004 102 $11.00 $1,122.00 4/2/2004 12
6 4/3/2004 153 $10.00 $1,530.00 4/3/2004 112
7 4/3/2004 204 $8.00 $1,632.00 4/4/2004 360
8 4/5/2004 255 $9.00 $2,295.00 4/5/2004 62
9 4/6/2004 306 $12.00 $3,672.00 4/6/2004 127
10 4/7/2004 357 $15.00 $5,355.00 4/7/2004 142
11 4/8/2004 408 $14.00 $5,712.00 4/8/2004 157
12 4/9/2004 459 $13.00 $5,967.00 4/9/2004 172
13 4/10/2004 501 $12.00 $6,012.00 4/10/2004 1500
14 4/11/2004 552 $11.00 $6,072.00 4/11/2004 202
15 4/12/2004 603 $10.00 $6,030.00 4/12/2004 217
16 4/13/2004 654 $9.00 $5,886.00 4/13/2004 232
17 4/14/2004 705 $8.00 $5,640.00 4/14/2004 247
18 4/15/2004 756 $11.00 $8,316.00 4/15/2004 262

Then I created a macro that would tell me the cost of an order based upon the input date in Cell J4:

Sub COGS()

' Macro to calculate Cost Of Goods Sold using FIFO

    Dim Q As Variant
    Dim Q2 As Variant
    Dim Q3 As Variant
    Dim P As Variant
    Dim P1 As Variant
    Dim P2 As Variant
    Dim ln1 As String
    Dim ln2 As String
    Dim ln3 As String
    Dim title As String
       
      
    Sheets("Purchases").Select
    
    Q = Application.WorksheetFunction.VLookup(Range("I3"), Range("F3:G17"), 2)
    Q2 = Application.WorksheetFunction.VLookup(Range("I3"), Range("A3:D17"), 2)
    
    If Q2 >= Q Then
    P = Application.WorksheetFunction.VLookup(Range("I3"), Range("A3:D17"), 3)
    Else
    
    Range("A3").Select
    X = Application.WorksheetFunction.Match(Range("I3"), Range("A3:A17"))
    ActiveCell.Offset(X - 1, 1).Select
    P = ActiveCell.Offset(0, 2).Value
Repeat:
    
    ActiveCell.Offset(-1, 0).Select
       
    Q3 = ActiveCell.Value
    Q2 = Q2 + Q3
    I = ActiveCell.Offset(0, 1).Value
    P1 = ActiveCell.Offset(0, 2).Value
    P = P + P1
    
    If Q2 > Q Then
    P = P - P1
    P1 = (Q - (Q2 - Q3)) * I
    P = P + P1
    End If
    If Q2 < Q Then GoTo Repeat 'repeat until Q2 >= Q
    
    End If
    P2 = P / Q
    
    ln1 = "The total cost for the order was: " & P
    ln2 = "The cost per item was: " & P2
    title = "COGS"
    MsgBox ln1 & vbLf & ln2 & vbLf, , title
    
End Sub

As I said not the best written code, but it works.

JPM

 

[FlavioT]

Thanks!!!
I'll try that!!! Just on the end of the month I'll export to exel and run this macro!

So, if he just wants to have an idea of the cost i'm using the average cost in access... Just because it's simple!

So, when my boss wants the REAL cost I can tell him!

thanks a lot!
Flavio