Solved Update the unit price of inventory taken from the price of latest purchase (1 Viewer)

[hfsitumo2001]

I see that quantity of inventory can be updated from quantity of purchase order/stock in with the code like this:

With rst
.Edit
!Inv_Qty = !Inv_Qty + Nz(Me.Quantity, 0)
.Update
End With

How can I update or replace the unit price of the inventory by the price of the last purchase. Can I make it like this"

With rst
.Edit
!InvUnitPrice= Nz(Me.UnitPrice, 0)
.Update
End With

Thank you for any idea or if you have sample of database

 

[Galaxiom]

You shouldn't store the quantity. Calculate it on-the-fly as required from the purchases, sales and adjustments.

Similarly, you can read the cost from the last purchase.

 

[Galaxiom]

Calculating inventory cost and values on hand is a science in itself. There are several different ways of managing it. Average cost of the inventory on hand is one way. Another is First In First Out (FIFO) where the cost of the current sale is the cost of the oldest purchase.

No matter how it is done, it can be calculated from inventory movements using aggregate queries.

Auditors have differing attitudes about what is most accurate. FIFO tends to inflate the value of the inventory higher than averaging. Simply using the cost of the latest purchase messes with accounting because you may be selling old stock that didn't cost that price.

 

[hfsitumo2001]

For us, we just one last in first out, even cost is really not matter for us, because actually we are not the profit making, the sample is just I take from sales, but actually our houseking job is to keep tract of what is the quantity on hand currently, when to buy supplies. So actually my target how can I update the price in the inventory field from the supplies receipt, so if any body just confirm if my code below is working
With rst
.Edit
!InvUnitPrice= Nz(Me.UnitPrice, 0)
.Update
End With

Thank you

 

[gemma-the-husky]

Inventory cost is very complicated.

If you buy 20 at $1, and another 20 at $1.30, and then use or sell 30 of these items, you will get a different usage cost, and remaining inventory cost depending on the costing method you use. It's not as simple as just "a set cost value" unless you use an average or a standard costing method. If you use FIFO or LIFO it's just plain hard. (as @Galaxiom just said).

your usage might span more than 2 inward consignments. a second usage needs you to know the status of the last usage to establish the start point, or you need to work the whole thing out again in total. Managing quantity is quite hard, but it's a whole world of difference to track costs. If you use standard costing, you start getting into variance analysis, which is another difficult area.

Your code looks OK to execute an update, but this might mean you are valuing all stock at the current or last price, which is a different thing again. Would you want to update a cost to zero, though?

 

[hfsitumo2001]

Inventory cost is very complicated.

If you buy 20 at $1, and another 20 at $1.30, and then use or sell 30 of these items, you will get a different usage cost, and remaining inventory cost depending on the costing method you use. It's not as simple as just "a set cost value" unless you use an average or a standard costing method. If you use FIFO or LIFO it's just plain hard. (as @Galaxiom just said).

your usage might span more than 2 inward consignments. a second usage needs you to know the status of the last usage to establish the start point, or you need to work the whole thing out again in total. Managing quantity is quite hard, but it's a whole world of difference to track costs. If you use standard costing, you start getting into variance analysis, which is another difficult area.

Your code looks OK to execute an update, but this might mean you are valuing all stock at the current or last price, which is a different thing again. Would you want to update a cost to zero, though?

our code looks OK to execute an update, but this might mean you are valuing all stock at the current or last price, which is a different thing again. Would you want to update a cost to zero, though?
Yes the last purchase price will be the cost of inventory as well as the cost of the supplies that we will be used to delivering supplies to the user department. Like I said, we are not trading company, we are just the housekeeping department that serves all buildings of the school.

The important thing is to keep tract of the quantity on hand, and when to order supplies. So we already have Order Level, Target level and based on updated Quantity on hand, we will be reminded by the system, whether we are in low stock level, and Amount to order.

Can you send me your sample

 

[gemma-the-husky]

I haven't got an example, because I have never had to value inventory, and it's so difficult, it might be too expensive to do as a one-off. Maybe @Galaxiom has something.

If you aren't going to do it in a formal way you may as well not value your inventory at all. You may as well just store details of your incoming deliveries in date order, with quantity and price, and use the latest receipt when you want a price, or an average - see below. That would be far easier than storing prices in the inventory tables.

If you use the latest price. it just means that if the price is going up, you will overvalue your stock, and overcharge for issues, and if its falling, you may undervalue it, and undercharge.

Just to go back to your last post - if the price was missing and you store a price of zero, does that mean you would issue your inventory at a cost of zero? Maybe you would look for the last non-zero price, or even calculate the average cost over the last year's receipts. See, it's much easier if you do that, than if you try to calculate the precise cost of the items you are issuing, based on working out which receipt they relate to, by a particular costing method.

One way, the simple way, you just store the price history, and use it simply. The real way you have to be able to work out specifically which receipts represent your stock, according to your stock calculation method, and it's a bit harder, as you need to evaluate the quantity on hand each time before you can determine which incoming receipts the sales/issues relate to.

 

[jdraw]

Further to the info and questions from Galaxion and Dave, you may get some inventory valuation ideas by researching/Google/Bing.

 

[Pat Hartman]

I don't think using a "last in" valuation is standard accounting practice if that matters to you. If that is what you want, don't keep the cost on the inventory record. Keep the cost on the item record so it only occurs once

 

[TedSla]

Consider: Unlike FIFO, Last in, First out (LIFO) increases the "value" of earlier items in stock, thus creating a "paper" profit via increased inventory value. Most bean counters are opposed to that concept as they have to come up with the cash to pay the taxes while the product remains on the shelf.