Inventory Pricing (Changes in prices)

[ConfusedA]

If I were to do so via average cost (I know you're suggesting against it, but this program has become less about accounting and more or less just theoretical data) I figure I would need to record the 'current cost' at any time that stock is removed via a record that would include... amount of stock moved, etc. etc. and a field 'fldcost' which would have to be populated by another table holding the current cost value of that item. How could I possibly do this on a form: where the user could pick the item from a list, and without a need to display it on the form, be able to place the current cost(at a base unit level) into the record alongside the information gathered in the form?

Thanks for any thoughts.
ConfusedA

 

[gemma-the-husky]

what affects the average cost is new stock purchases

if you have a 1000 widgets at an avco of 1$ each, then this is fine - until you buy some more

if you buy another 500 at £1.20 each then your average changes for the whole stock

So at this point in time, you need to be able to compute your actual stock, tp rework the new average - so i would think you need to store the current average in your product table (maybe store a history of such changes in a linked history table)

So this is not simple. And over time, it is likely to drift off (eg perhaps your system forgets to include some of the movement elements) and therefore you need a "reconciliation process" to recalculate it, and start again.

an alternative to this is something called standard costing
this says we expect the standard cost of a widget to be 10$ (say), which never changes - and therefore you use 10$ as your cost when caclulateing profits.

But when you buy stuff, you find out the real cost was only 9$ (say). Therefore you have a favourable variance to account for. And there are hundreds of different variances. (In fact using avco probably gives rise to some variances also, I think) Even if you use standard costing, there still has to be a mechanism to adjust your trading results to a historic cost, to satisfy the IR/IRS

This is why you want to use a system that is acceptable to the IR/IRS - that way you dont get variances you ned to adjust for.

There are no easy ways with inventory. Managing quantity is tricky but fun. Managing prices and value is extraoridnarily difficult. Proper large scale inventory systems are exceedingly complex, and not to be attempted lighlty.

 

[ConfusedA]

I'm working with storing a history like you suggest, the problem I am having is that I need to keep a record of each outgoing transaction's 'current price' which is stored in a seperate table from the table of transaction histories. So like I have:

Tableprice:
Item Quantity Current Price(which changes based on additions)
TableAddition:
Item Quantity Added Price of New Quantity

TableOutgoing:
Item Quanity Removed etc. etc. Price at time of use (needs to be taken from tableprice)

My problem is that I can't figure out how to create the code for my report that allows me to record the current price into the field based off of the item picked.

I imagine this involves a complex set of 'if' statements and table relationships, but I am unsure of how to do so.

Should I just ask this in the coding section?

Thanks.
ConfusedA

 

[ConfusedA]

What I've done is created a text box which has a control source of the current price, now all I need is to be able to have a value inserted in to it based on the chosen 'type' value (in fldtype) that will correspond to it via tableprice. This is the part that I don't know how to do. In my mind it would be something in which the item picked on the form is searched through the list of available items, and if it is found(which it would be) then insert the price into the text box.

 

[ConfusedA]

Would a where clause work?
Like: Select fldprice from tblprice
Where
flditem(from tblprice) = <object for item>

 

[gemma-the-husky]

as i see it, an average cost will only work if it is live all the while - therefore if you sell some stuff, you need to look up the cost and use the average cost available at that time - if you have to wait for a later period to evaluate the average cost, this will be virtually impossible.

You DO NOT need to compute the average on a SALE - only on a purchase

ie if you have 1000 widgets in stock at an average cost of £1.80 each, any SALE will be at a cost of £1.80 each - so i would think, just store the average in the product file and look it up

avco = dlookup("averagecost","tblProducts","productid = " & myprod)

the average cost will change when you BUY some more - so if you buy another 300 at £1.50 each, and you had 1000 at £1.80 each, your total for the 1300 is £2250 (approx £1.73) - adjust your average and use that from now on -

I did say you didnt need to consider sales - but there are some unusual stock movements that you may need to consider - so a returned sale needs adding back into the stock quantity - and now you have to decide whether you add it back in at the current average cost .... or at the cost rate at which you originally sold it. - you could justify either treatment, I think - the latter will however affect the average

I can only repeat that any system attempting to attribute a cost against sales and thereby strike a theoretical profit is exceedingly complex.

 

[ConfusedA]

Thanks for the input Dave. All your thoughts have been very helpful on the matter, I should have probably explained this part earlier, but I'm not really interested in sales, our inventory is used within our group, but we need to keep records of how much is used and how much remains.

For the average cost I have a table able to be updated on the basis of an addition to the inventory, which allows for the average cost to be calculated. But because the average cost could potentially change between inventory uses I just want the current cost to be recorded on each record.

IE. If we have 300lbs of sand at $2/lb.
30lbs are used during the 1st week of january, then we would have a record like:
Week/Month/year/Job/Item Used/Amount Used/Cost per unit
1/Jan/2009/<insert job here>/Sand/30/$2.

So that at the end of the year when we want to see how much is used we would just query the item type and it would display a sum of amount used, and a sum of (amount used*cost per unit).

It's only the placing the cost value from the one table in with the right record that I'm having trouble with.

Say we have 15 items (each with their own average cost per unit)
I want to be able to pick out the proper associated cost from the table that lists them to match it with the table that will hold the transaction record.

But I am missing out on how to get the current cost to be displayed on the form when the item is picked.