Handling Inventory Costs That Change Over Time

[mubtuhogar]

Hi All,

On an inventory database, product costs change over the time. How do I maintain proper costs/prices on all invoices that do not get recalculated when new costs are setup for that same product.

Example,

On February 2010 we sold a TV Set for $300.00. Our cost for that TV Set on February was $200.00. How ever, today, the same TV Set now costs $225.00. When I add this new cost to the database it modifies all my previous transactions for that same TV Set and updates the cost to $225.00. How can I keep the previous transactions as they were entered with that previous cost of $200.00 and allow all future transactions to use the new cost of $225.00?

I am using Access 2007.

Thanks!

[RuralGuy]

Save the cost with your invoice.

[NoellaG]

or have a table with historical prices, with the "priceValidFrom" and "priceValidTill" fields

[evander]

What cost flow assumption are you using? FIFO, LIFO, weighted average, or perpetual average?

Similar to RuralGuy's suggestion, I usually place some code in the After Update event of either the inventory code or inventory quantity to fetch the appropriate price of an item given some date criterion.

As for Noella's suggestion, I would create an inequality join query.

In most of my projects, I have made a combination of these two approaches work to my satisfaction.

Good luck.

[NoellaG]

Hi Evander,

most used in warehouses (in Belgium warehouses anyway) is FIFO. Sometimes extra costs are added to the product (storage costs depending on the time, repairs ect... ). What I learned, if you deal with external customers, most of them have their own discussed price settings, and the commercial boys can make it pretty complicated. If it's an internal service very streight forward tarifs table is used.

grNG

[NTC]

I think RG's advice hits the issue most directly - though somewhat brief. Generically there are alot of cost elements that change in time - one example is the sales tax rate. One needs the 'transaction' table (in RG's advice the invoice) that records all the correct data of that transaction event.