Menu
▾
▴
web-erp-developers
|
From: AndyThompson <ath...@mo...> - 2005-10-25 17:55:12
|
Thinking about delving into FIFO and average costing additions to webERP. This will serve as a root message to hopefully get a discussion started on what needs to be done to make this happen. Any controlled item is pretty straightforward since the cost is a one to one association. Should be easy enough to add a cost column to the stockserialitems table. We will need a purchase receipts master table to store cost in batches of items received as well as track relief of those receipts on sales/transfers etc to calculate the cost against. Very simplistic (and very incomplete) overview, but hopefully will fuel some discussion. I'm not sure of everything this will impact right now, especially in existing functionality. -andy |
|
From: AndyThompson <ath...@mo...> - 2005-10-31 17:13:11
|
Well my previous discussion seems to have fizzled out :) I am still interested in developing this but will need a little guidance from everyone (anyone?) concerned as to the best way to develop it. Starting with receiving and item transfers first off I guess. Phil pointed out that the stockmoves table tracks some of what I'm after currently. There is a price column and a standardcost column in that table which seems a bit ambiguous to me. And they are not updated consistently as far as I can tell. I guess the primary things I can see missing from that table is the ability to track a quantity sold from the move record and possibly a column to track when the record is entirely sold to be used to narrow the result when looking for a cost. Also figure out the existing price/standardcost updates that are missing and get them fixed up. I would almost prefer to add a generic "cost" column to the table and use that at least for the FIFO costing lookups to remove any ambiguity. -andy |
|
From: Phil D. <we...@pa...> - 2005-11-01 05:55:55
|
On Tue, 01 Nov 2005 06:12, AndyThompson wrote: > Well my previous discussion seems to have fizzled out :) I am still > interested in developing this but will need a little guidance from > everyone (anyone?) concerned as to the best way to develop it. > Its pretty rare that two folks want exactly the same thing at the same time! I always want good things for webERP though and if I can help guide or explain the existing functionality and encourage you to get stuck in then I'm keen to do so. > Starting with receiving and item transfers first off I guess. Phil > pointed out that the stockmoves table tracks some of what I'm after > currently. > > There is a price column and a standardcost column in that table which > seems a bit ambiguous to me. And they are not updated consistently as > far as I can tell. > standard cost is updated at the time the first lot of the item is received against the purchase order. The same standard cost is then used throughout all subsequent receipts and purchase price variances are posted based on this standard cost at the time the first receiving of stock took place. Subsequent changes to standard cost therefore have no effect on the purchase order. The price is the purchase cost of the item as descibed previously. What do you mean not updated consistently? The price is taken as the standard cost initially until such time as the actual FX cost price for the item is entered against the stock item at the time of purchase order entry - as converted at the rate of exchange used for entry of the purchase invoice. The purchase price in this field is not updated ... I don't think - I may be corrected on this point - at the time of the closing of a shipment - shipments of course cost in major freight and other costs into the items that came in on the shipment. > I guess the primary things I can see missing from that table is the > ability to track a quantity sold from the move record and possibly a > column to track when the record is entirely sold to be used to narrow > the result when looking for a cost. Well that's not necessary for a FIFO valuation since one assumes that the first stock in is the first stock out. One only requires to look at the last receipts of stock that total the current balance of stock. > Also figure out the existing > price/standardcost updates that are missing and get them fixed up. I'm not sure what other updates you're looking for here Andy? > I > would almost prefer to add a generic "cost" column to the table and use > that at least for the FIFO costing lookups to remove any ambiguity. > > -andy > > > ------------------------------------------------------- > This SF.Net email is sponsored by the JBoss Inc. > Get Certified Today * Register for a JBoss Training Course > Free Certification Exam for All Training Attendees Through End of 2005 > Visit http://www.jboss.com/services/certification for more information > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers -- Phil Daintree webERP Project Admin |
|
From: AndyThompson <ath...@mo...> - 2005-11-01 13:19:53
|
>> Starting with receiving and item transfers first off I guess. Phil >> pointed out that the stockmoves table tracks some of what I'm after >> currently. >> >> There is a price column and a standardcost column in that table which >> seems a bit ambiguous to me. And they are not updated consistently as >> far as I can tell. >> > > > standard cost is updated at the time the first lot of the item is received > against the purchase order. The same standard cost is then used throughout > all subsequent receipts and purchase price variances are posted based on this > standard cost at the time the first receiving of stock took place. Subsequent > changes to standard cost therefore have no effect on the purchase order. > > The price is the purchase cost of the item as descibed previously. What do you > mean not updated consistently? The price is taken as the standard cost Primarily what I'm referring to is that the cost does not flow with an inventory transfer. The price and standardcost columns remain zero for transfer records. Cost needs to follow an item thru the entirety of it's life in the system. For your costing calculations currently, do you use the material cost stored on the item record? When you do a cost adjustment, do you just change that value or is there something in the system to adjust a shipment cost? Standard costing is entirely new to me. > initially until such time as the actual FX cost price for the item is entered > against the stock item at the time of purchase order entry - as converted at > the rate of exchange used for entry of the purchase invoice. > > The purchase price in this field is not updated ... I don't think - I may be > corrected on this point - at the time of the closing of a shipment - > shipments of course cost in major freight and other costs into the items that > came in on the shipment. > >> I guess the primary things I can see missing from that table is the >> ability to track a quantity sold from the move record and possibly a >> column to track when the record is entirely sold to be used to narrow >> the result when looking for a cost. > > > Well that's not necessary for a FIFO valuation since one assumes that the > first stock in is the first stock out. One only requires to look at the last > receipts of stock that total the current balance of stock. > Can you explain your thinking a bit more here? I think you are saying to take the qoh for a current stock item and query back into your receipts to match up the last X receipts that make up that qoh and take the cost of the oldest matching receipt? That might be a more dynamic way to track the cost as opposed to updating discrete records at the time that stock is relieved. I'm not sure if one way is preferable over another. Having discrete records might be preferable for updating inventory cost records. Something else I thought of this morning is that there is a record in the stockmoves table for both incoming and outgoing stock. I am only concerned with incoming movements for cost calculations so there needs to be some way to differentiate the record types in that table. And that also started me thinking whether it would be better to add a new table instead of trying to piggy back on the existing table? I will defer to you on that. -andy |
|
From: Phil D. <we...@pa...> - 2005-11-03 09:17:37
|
> > > > The purchase price in this field is not updated ... I don't think - I may > > be corrected on this point - at the time of the closing of a shipment - > > shipments of course cost in major freight and other costs into the items > > that came in on the shipment. > > > >> I guess the primary things I can see missing from that table is the > >> ability to track a quantity sold from the move record and possibly a > >> column to track when the record is entirely sold to be used to narrow > >> the result when looking for a cost. > > Well the stock value really doesn't change with a stock transfer? Are you thinking of an Average cost type arrangement where the cost and quantity are maintained and on average a unit is worth the total of the cost divided by the total of the quantity on hand according to this table - I think this is how MYOB works. > > Well that's not necessary for a FIFO valuation since one assumes that the > > first stock in is the first stock out. One only requires to look at the > > last receipts of stock that total the current balance of stock. > > Can you explain your thinking a bit more here? I think you are saying > to take the qoh for a current stock item and query back into your > receipts to match up the last X receipts that make up that qoh and take > the cost of the oldest matching receipt? > No accumulate the costs of the receipts in order of the most recently received stock to the point where the quantity accumulated matches the quantity on hand. > That might be a more dynamic way to track the cost as opposed to > updating discrete records at the time that stock is relieved. I'm not > sure if one way is preferable over another. Having discrete records > might be preferable for updating inventory cost records. > > Something else I thought of this morning is that there is a record in > the stockmoves table for both incoming and outgoing stock. I am only > concerned with incoming movements for cost calculations so there needs > to be some way to differentiate the record types in that table. Indeed there is - checkout the systypes table typeid=25 is stock receipts - there is a foreign key to stockmoves > And > that also started me thinking whether it would be better to add a new > table instead of trying to piggy back on the existing table? I will > defer to you on that. > Personally I am a big fan of standard costing because: 1. Costs change on every shipment and it is not always appropriate to change the cost for decision making based on an airfreight shipment or based on a 40 footer. The true cost for making logical decisions is the based somewhere in the middle - bearing in mind proper order sizes. 2. I like to see the differences reported against the standard cost so we can see : (i) if the business is shipping economically (ii) the effect of exchange rate factors (iii) the effect of supplier price changes 3. It allows very speedy - same day reporting You should read up a bit on it - there is some info in the manual as to how it works -- Phil Daintree webERP Project Admin |
|
From: AndyThompson <ath...@mo...> - 2005-11-03 13:32:29
|
Phil Daintree wrote: >>> The purchase price in this field is not updated ... I don't think - I may >>> be corrected on this point - at the time of the closing of a shipment - >>> shipments of course cost in major freight and other costs into the items >>> that came in on the shipment. >>> >>>> I guess the primary things I can see missing from that table is the >>>> ability to track a quantity sold from the move record and possibly a >>>> column to track when the record is entirely sold to be used to narrow >>>> the result when looking for a cost. > > > Well the stock value really doesn't change with a stock transfer? > > Are you thinking of an Average cost type arrangement where the cost and > quantity are maintained and on average a unit is worth the total of the cost > divided by the total of the quantity on hand according to this table - I > think this is how MYOB works. > No, although an average cost report could be written pretty easily with the data. The stock value doesn't change with a transfer, but systems I'm familiar with keep the cost with the stock thru it's entire life in the system. If I transfer 10 items to warehouse B, I want to know the cost that I moved to warehouse B independent from the entire system. >>> Well that's not necessary for a FIFO valuation since one assumes that the >>> first stock in is the first stock out. One only requires to look at the >>> last receipts of stock that total the current balance of stock. >> Can you explain your thinking a bit more here? I think you are saying >> to take the qoh for a current stock item and query back into your >> receipts to match up the last X receipts that make up that qoh and take >> the cost of the oldest matching receipt? >> > > No accumulate the costs of the receipts in order of the most recently received > stock to the point where the quantity accumulated matches the quantity on > hand. I think we're saying the same thing here. >> That might be a more dynamic way to track the cost as opposed to >> updating discrete records at the time that stock is relieved. I'm not >> sure if one way is preferable over another. Having discrete records >> might be preferable for updating inventory cost records. >> >> Something else I thought of this morning is that there is a record in >> the stockmoves table for both incoming and outgoing stock. I am only >> concerned with incoming movements for cost calculations so there needs >> to be some way to differentiate the record types in that table. > > Indeed there is - checkout the systypes table typeid=25 is stock receipts - > there is a foreign key to stockmoves > If cost is tracked with every item movement thru the system, I need to differentiate the location transfers into a location as well. That does bring up a couple other questions, what is the negative record in the stockmoves table used for? Reporting? And why do you leave a record in the stockserialitems table for serialized inventory with a zero quantity? > Personally I am a big fan of standard costing because: > I've read up on it a bit. Seems like it would be ok for a smallish, slow moving inventory. Even with only a few thousand items, which might be small in some regards, standard costing seems like it would be tedious to maintain. Especially in a quickly changing market where supplier prices can be different from week to week. I will use the disclaimer that these are observations from someone who has never used standard costing. Regardless, I don't want this to become a discussion about the merits of one costing system over another. If I implement webERP, I need FIFO valuation so I will add it. I just want to decide the best way to do it with the idea to give it back to the project if it is deemed worthwhile. -andy |
|
From: Phil D. <we...@pa...> - 2005-11-04 09:33:27
|
> That does bring up a couple other questions, what is the negative record > in the stockmoves table used for? Reporting? Not sure what you mean here? > And why do you leave a > record in the stockserialitems table for serialized inventory with a > zero quantity? To show that the item once existed in stock. > > > Personally I am a big fan of standard costing because: > > I've read up on it a bit. Seems like it would be ok for a smallish, > slow moving inventory. Even with only a few thousand items, which might > be small in some regards, standard costing seems like it would be > tedious to maintain. Especially in a quickly changing market where > supplier prices can be different from week to week. I will use the > disclaimer that these are observations from someone who has never used > standard costing. > > Regardless, I don't want this to become a discussion about the merits of > one costing system over another. If I implement webERP, I need FIFO > valuation so I will add it. Or average cost? The valuation reporting is one aspect and perhaps the easier aspect to consider the real interest is in creating a completely consistent general ledger posting schema that works using the costing method chosen - we are talking about every stock journal in the system - they are all over the place and amongst the more complex aspects of the logic. Its not going to be that easy to do. > I just want to decide the best way to do it > with the idea to give it back to the project if it is deemed worthwhile. > > -andy > Certainly would be useful to many I suspect. All contributions most gratefully received ;-) Phil |
|
From: AndyThompson <ath...@mo...> - 2005-11-04 13:07:54
|
>> That does bring up a couple other questions, what is the negative record >> in the stockmoves table used for? Reporting? > > > Not sure what you mean here? > When I do a location transfer, there is a record for -X quantity from location A and another record for +X quantity to location B. > >> And why do you leave a >> record in the stockserialitems table for serialized inventory with a >> zero quantity? > > To show that the item once existed in stock. > >>> Personally I am a big fan of standard costing because: >> I've read up on it a bit. Seems like it would be ok for a smallish, >> slow moving inventory. Even with only a few thousand items, which might >> be small in some regards, standard costing seems like it would be >> tedious to maintain. Especially in a quickly changing market where >> supplier prices can be different from week to week. I will use the >> disclaimer that these are observations from someone who has never used >> standard costing. >> >> Regardless, I don't want this to become a discussion about the merits of >> one costing system over another. If I implement webERP, I need FIFO >> valuation so I will add it. > > Or average cost? An average costing method could be written pretty readily once the data for FIFO is there. > The valuation reporting is one aspect and perhaps the easier aspect to > consider the real interest is in creating a completely consistent general > ledger posting schema that works using the costing method chosen - we are > talking about every stock journal in the system - they are all over the place > and amongst the more complex aspects of the logic. Its not going to be that > easy to do. > Well that was one of the main reasons for this thread. Trying to track down every script that will be impacted by this. I've got the goodsreceived script working as I think it should for other costing methods. I started digging into the stocktransfers script and discovered that it doesn't affect GL in any way. Right now our G/L is broken down by location so I will have to see what impact this will have on the big picture as well. -andy |
|
From: Phil D. <we...@pa...> - 2005-11-05 20:50:05
|
On Sat, 05 Nov 2005 02:07, AndyThompson wrote: > >> That does bring up a couple other questions, what is the negative record > >> in the stockmoves table used for? Reporting? > > > > Not sure what you mean here? > > When I do a location transfer, there is a record for -X quantity from > location A and another record for +X quantity to location B. > So there is a complete record by location of every stock movement ... checkout the stock movement inquiry. If the stock is transferred to another location we need to record the stock being taken from one location and received into the other. Phil |
|
From: Phil D. <we...@pa...> - 2005-10-26 07:05:25
|
Hi Andy, Well, currently the actual cost of a delivery is recorded in the stock movement - at the time the item is received the standard cost goes on the stockmoves record, but when the invoice is entered the line is updated with the actual cost - or at least the supplier invoice cost as converted at the invoice exchange rate. If the invoice is paid at a different rate this difference is not picked up on in the stockmoves record. Also, freight costs associated with getting the item to its "present location and condition" is completely ignored - as it is in most systems. I am not sure if I remember if the stockmoves record is updated when a shipment is closed - I guess this could easily be accomodated. The valuation then can be determined on a first in first out basis by looking at the last receipts of stock and the value attached to the stockmove record for the receipt of the stock up to a maximum of the quantity left in stock now. So it is possible with the data we have now to create a report for the FIFO valuation of stock - assuming invoices are all entered against all goods received. Its quite miserable to contemplate how this could be made to tie up with the value of stock in the general ledger - the whole system is geared around standard costing. Your point about prefixes is a good one. I have done this although perhaps not rigorously, but most scripts to do with stock begin with the word Stock - purchase orders - PO. I like to think the naming of scripts does tend to give away the function of the script. I am not sure what other scripts we might put in the receiving directory? If we end up with as many directories as scripts we are not much better off - in fact we are worse off cos we continually have to change directories every script we work on. Phil On Wed, 26 Oct 2005 06:54, AndyThompson wrote: > Thinking about delving into FIFO and average costing additions to > webERP. This will serve as a root message to hopefully get a discussion > started on what needs to be done to make this happen. > > Any controlled item is pretty straightforward since the cost is a one to > one association. Should be easy enough to add a cost column to the > stockserialitems table. We will need a purchase receipts master table > to store cost in batches of items received as well as track relief of > those receipts on sales/transfers etc to calculate the cost against. > > Very simplistic (and very incomplete) overview, but hopefully will fuel > some discussion. I'm not sure of everything this will impact right now, > especially in existing functionality. > > -andy > > > ------------------------------------------------------- > This SF.Net email is sponsored by the JBoss Inc. > Get Certified Today * Register for a JBoss Training Course > Free Certification Exam for All Training Attendees Through End of 2005 > Visit http://www.jboss.com/services/certification for more information > _______________________________________________ > Web-erp-developers mailing list > Web...@li... > https://lists.sourceforge.net/lists/listinfo/web-erp-developers -- Phil Daintree webERP Project Admin |
|
From: AndyThompson <ath...@mo...> - 2005-10-26 13:28:04
|
> Hi Andy, > > Well, currently the actual cost of a delivery is recorded in the stock > movement - at the time the item is received the standard cost goes on the > stockmoves record, but when the invoice is entered the line is updated with > the actual cost - or at least the supplier invoice cost as converted at the > invoice exchange rate. If the invoice is paid at a different rate this > difference is not picked up on in the stockmoves record. Also, freight costs > associated with getting the item to its "present location and condition" is > completely ignored - as it is in most systems. I am not sure if I remember if > the stockmoves record is updated when a shipment is closed - I guess this > could easily be accomodated. > I've been doing some stock transfers looking at this table and it appears some things are missing. The price (cost) column is not updated when a transfer is done, nor is the standardcost column. I'm not sure how invoicing would refer to this table? Have to dig thru some of it and try to sort it out. Also, there has to be some way of tracking when a record in this table is sold (or transferred) in it's entirety, which I assume is what you were referring to. Do you have a list of all scripts and their functions? As I'm digging into this I'm just going to the modules and seeing what scripts are referenced, but that would be helpful. If not I'll start putting a document together as I go thru everything. > The valuation then can be determined on a first in first out basis by looking > at the last receipts of stock and the value attached to the stockmove record > for the receipt of the stock up to a maximum of the quantity left in stock > now. So it is possible with the data we have now to create a report for the > FIFO valuation of stock - assuming invoices are all entered against all goods > received. > > Its quite miserable to contemplate how this could be made to tie up with the > value of stock in the general ledger - the whole system is geared around > standard costing. > I guess the first thing to do will be to go thru anything that uses inventory in any manner and figure out where how it calculates the cost currently. Perhaps together we can come up with a list. > > Your point about prefixes is a good one. I have done this although perhaps not > rigorously, but most scripts to do with stock begin with the word Stock - > purchase orders - PO. I like to think the naming of scripts does tend to give > away the function of the script. > > I am not sure what other scripts we might put in the receiving directory? If > we end up with as many directories as scripts we are not much better off - in > fact we are worse off cos we continually have to change directories every > script we work on. > I don't think separate directories would serve any benefit if the scripts had a set prefix. I do agree with Steve that isolation of third party apps is a good idea though. -andy |
