Inventory coding help

[CEH]

OK, I've looked everyplace for this... can't find anything explained. I'm working on an inventory DB. I need to add another feature. Ability to value using FIFO. The method I will follow is by having an auto number field for each item purchase, or "lot" of items purchased. Maybe "LotInID" or as I have it now... "OrderID" and "ReceivedDate"
My problem is I am not deep enough into VBA to code this one. My example would be like this.... I purchase 5 widgets in Lot #1 for $6.00 each. I purchase 5 more widgets in Lot#2 for $7.00 each. Now I sell 7 widgets. My remaining inventory should be 3 widgets @ $7.00 each.
Can anyone show me how this would work??
Thanks

 

[anchorp]

Any Moren Info?

Did you ever get this figured out? I am dealing with pretty much the same problem. Or if anyone else has please say so as well.

Thanks.

 

[MarkK]

I purchase 5 widgets in Lot #1 for $6.00 each. I purchase 5 more widgets in Lot#2 for $7.00 each. My remaining inventory should be 3 widgets @ $7.00 each.

No. Remaining inventory should be 3 widgets @ $6.50 each. I think you need to calculate a moving average every time you add stock to inventory.

Sum all your widgets and all your costs.
  Widgets:
    Sum the inventory widgets plus the purchased widgets = 
         5 + 5 = 10
  Costs:
    Sum the (last average * inventory widgets) _
        PLUS (purchase price per unit * purchased widget count) -or-
        (5 * $6) + (5 * $7) = 30 + 35 = 65
  New Average Cost / Widget
    65 / 10 = $6.5

 

[CEH]

No. Remaining inventory should be 3 widgets @ $6.50 each. I think you need to calculate a moving average every time you add stock to inventory.

I don't remember whem I posted this... but wanted to reply in case others look for information here.... The valuation you used is incorrect. I specified a FIFO inventory.... Under a FIFO valualtion my figures ARE correct.

 

[WayneRyan]

Curtis,

Need more details on your table structure.

Rough idea though ...

'
' Assume VERY SIMPLE table for inventory
'
' tblInventory
' ============
' ProductName
' ReceivedDate
' Quantity
' OnHand
' UnitCost
'
Dim rst As DAO.RecordSet
Dim intToShip As Integer
'
' Are there enough?
'
If Nz(DSum("[Quantity]", "tblInventory", "ProductName = '" & Me.ProductName & "'"), 0) < Me.QtyRequired Then
   MsgBox("Insufficient quantity")
   Exit Sub
End If
'
' There are enough, ship them
'
intToShip = Me.QtyRequired
Set rst = CurrentDb.OpenRecordSet("Select * " & _
                                  "From tblInventory " & _
                                  "Where ProductName = '" & Me.ProductName & "' " & _
                                  "Order By ReceivedDate Ascending")
'
' Process each lot, oldest first
'
While intToShip > 0
   If intToShip <= rst!OnHand Then
      MsgBox("Finished shipment" & intToShip & " units @ " & rst!UnitCost)
      rst.Edit
      rst!OnHand = rst!OnHand - intToShip
      rst.Update
      intToShip = 0
   Else
      MsgBox("Partial shipment" & rst!OnHand & " units @ " & rst!UnitCost)
      intToShip = intToShip - rst!OnHand  
      rst.Edit
      rst!OnHand = 0
      rst.Update  
   rst.MoveNext
   Wend

hth,
Wayne