I am trying to build a database that includes inventory information. What I am trying to do is have a way of storing a base (yearly or quarterly) inventory and then every time a job uses material it will be recorded and a search of inventory can produce inventory used and/or current remaining inventory.

Setting up a basic inventory change via forms is not difficult, but my problem is I need to be able to account for changes in material prices.

For example if there are 20lbs of salt stored and it is worth approximately ($15/lb)
and then 50lbs of salt is bought at ($17/lb) then i have 20x15 and 50x17 or $1150lbs of salt.
If 30lbs of salt are then used I would just take an average price per salt value (IE 30 x $16.43) off of the value of the inventory.

How can I create a method of changing a price value associated with the salt so that I could get an average based on the current price in the inventory and the new price added?

costing is really difficult - you ought to talk to your accountants about this - if it isnt your own business you want a detailed job spec from your finance dept

you can have among others - fifo, lifo, avco, standard costing

some of these are not recognised for accounting purposes, so even if you use them you will have to restate your profits for tax purposes at the end of the year - whichever you choose it is extremely complex to manpulate in any system -

in summary - this is a real complicated project

Thanks for the advice Dave, I'll have to set up a meeting with my supervisor and see how he'd like to proceed. I wasn't even thinking of it in terms of final accounting measures so your bringing this to my attention was very important.

well if you are using (cost) prices, you are striking a profit - and theres no point whatsoever bothering with this, if to doesn't correspond to the profit you report in your accounts.

dealing with quantities is not so hard, but dealing with prices is!

with regard to quantities, the main thing is - DONT try to store the quantitiy on hand - store the movements, and calculate the quantity on hand from the movements.

I discussed this with my boss and he said he would like me to record quantities/prices based on the FIFO method... I have no idea how to go about doing this. My plan was to store quantity values with price values, but if there are more than one price value, how am I to set up a table to reflect this? And how can I have access know which 'type'(based on price) of each item is to be removed first?

Inventory control is one of the most difficult things you can attempt to program. It does not mater what programing platform or database, it is difficult.

It is not impossible to do. Hrere is a good model to get you started:

Inventory Control: Quantity on Hand (http://allenbrowne.com/AppInventory.html)

Purchased are stored in "lots" with the cost per item for that "lot" of items.

FIFO means first in first out - its a pretty standard mechanism

basically you "sell" the oldest items first - ie the ones purchased at the oldest price - so the stock is always the ones most recently bought (in logcial terms)

eg

buy 100 at £5
buy 150 at £6
use 80 (at £5, the oldest - FIFO))
use 50 (20 at £5 the balance of the oldest, 30 at £6 - the next oldest)

handling stock prices in this way is REAL HARD - but handling in any way is hard, as you can imagine.


-------
please note - If you arent EXPERT at access, you will find this virtually impossible to implement

I'm actually pretty new with this, but I'm a quick learner and I'm not eager to let my boss down, I'm going to read up on how to do this some more and will probably have some more questions later in the week as I'm going to avoid this part for a little while to get everything else in working order.

Thank you for the warning, I do anticipate problems but hope to be able to work it out, I have also passed on this information to my boss, so he is aware that this is probably above my ability, but as I said I'd like to give it a shot and maybe I can come up understanding a lot more than I had coming in (maybe not enough to succeed but at least more than I know now). So thanks all for the tips/advice and I expect to continue posting on this topic in a few days.
-A

good luck

but this is one app i would be very reluctant to build myself

unless there is a real reason to do this yourself, it would be MUCH cheaper buying a commercial app with the functions you need

I worked on a system (created by someone else) that included a system of averaged costing to take into account fluctuating purchase costs and new stock being bought in at new costs while existing stock was still in the system.

Basically, the averaged cost was calculated as follows:

NewAveragedCost= ((ExistingAveragedCost * ExistingStock) + (NewCost * NewStock)) / (ExistingStock + NewStock)

So for example, if you have your initial 100 widgets in stock that you bought for a dollar each, then you buy another 75 widgets, but at $1.10, you get:

NewAveragedCost= (($1.00 * 100) + ($1.10 * 75)) / (100 + 75)
= 1.0428571428571428571428571428571...

You have to round to $1.04, or 1.043 if you're storing cost prices with three decimal places (which is not unusual).

Costing the whole stock now, you have 175*$1.043 = $182.525. (The real cost of the stock, as purchased, was actually £182.50)

Obviously this isn't perfect, but because it could be coherently described, the accountants and auditors found it acceptable.

Not as accurate as a FIFO system, but this was in a system where the cost prices were used primarily to calculate workable profit margins, the value of losses, etc. The actual cost of purchasing was still fully and precisely accounted for in the bought ledger.

I second Gemma's reservations about the difficulties of implementing a FIFO system. I can't quite get my head around a way it could be easily done without denormalising the current available stock.

I'm wondering, what if you separate out the inventory by date, so that each set of 'bought' inventory is tagged to a date, and then set up the system so that the 'oldest' date for that type of item is removed first? Obviously 'lot' would be separated in a table by date instead of just by type, but i was just wondering if there is a way to deal with overlapping orders (IE orders that'd take 2 lots into account). I discussed everything you all have said with my boss and he said that this part could wait, ideally he'd like to have the FIFO system but if it is deemed impossible in the next month or so we can set up the average system.

Thanks a lot everyone for your thoughts!

When you take delivery of a stock item you will most likely have a GRN (Goods Received Note) and you will record the date of delivery. This may be full or part completed. You may also take delivery of items more than once a day, so bear that in mind. Also if you have shelf lifes on consumables you need to ensure correct stock rotation and EOQ (Economic Order Quantities) Delivery times, etc.

If you sort descending by delivery date or some other significant date you should be able to use the reducing balance approach.

David

This database is primarily for an inventory of road work (like job, employee's hours, and inventory used) I doubt that there will be multiple shipments of the same type of goods on the same day, most of the items worked with are salt/sand and diesel, I just need to be able to keep an approximate record of how much of these are used for each job. So I don't think I have to worry about anything going bad (maybe I'm wrong here) I personally don't deal with any of the shipping/recieving I've just been asked to make the program so that we can keep a general control on inventory and ideally have numbers that match up reasonably close to that of the accounting dept.

its not that it isnt doable

its just that it is extremely hard.

dealing with quantities is tricky in itself, but is reasonably straightforward, although its one application that you do need to know your way around access.

dealing with prices is much much harder

an average cost sytem as mike outlined means that each time you receive goods, you need to update the existing stock and compute a new average price. this average price can then be used for your cost when you strike a profit. now if this ISNT the way in which you are able to report profits (which needs to be agreed between your finance dept/accountants - and note that I believe AVCO is not acceptable to inland revenue, and not acceptable as an accounting practice (in UK at any rate)) then you need to find a way that is.

you could however do it using AVCO, and then do a true FIFO calculation to adjust your profits at the end of each accounting period - which may be acceptable

A standard costing system is similar, but probably much more complex

I am not sure at all how a true FIFO system would be implemented, without a lot of thought - it would be quite hard I am sure.

I am not sure at all how a true FIFO system would be implemented, without a lot of thought - it would be quite hard I am sure.I agree - I can't think of a way to do it just with queries.

I can see how to calculate the current stock based on opening stock plus or minus transactions.

I can see how to retrieve a list of GRNs with their costs.

But identifying which of these GRNs are responsible for the current stock items =hard. I would tackle it by iterating backwards through the GRNs, counting up quantities until I had accounted for the current stock, but...

The current stock is likely to comprise all of (say) the last three GRNs, plus a bit of the fourth-from-last - not at all impossible to calculate in VBA, but a bit involved. And that means it wouldn't be too bad for a one-item cost enquiry, but trying to constitute costs that way for a report on every stock item might perform a bit poorly.

The last two (completely unrelated) systems I had to maintain did it with an averaged cost, plus a 'last cost' (i.e. the last booked in cost). The averaged cost being useful to management, the last cost for the accountant.

I'm curious as to how a FIFO cost could be acceptable, actually - you buy 10 widgets at $10 each, then another 10 at $20 each - your stock cost $300 ($15 per widget on average), but if you do it on a FIFO basis, the cost is only $15 until you sell one. Because you sold one of the first, cheap ones, the cost per widget afterwards works out at ((9*10)+(10*20))/19 = $15.26 per widget - and continues to rise until you've sold the tenth one, when it is $20.

I'm curious as to how a FIFO cost could be acceptable, actually - you buy 10 widgets at $10 each, then another 10 at $20 each - your stock cost $300 ($15 per widget on average), but if you do it on a FIFO basis, the cost is only $15 until you sell one. Because you sold one of the first, cheap ones, the cost per widget afterwards works out at ((9*10)+(10*20))/19 = $15.26 per widget - and continues to rise until you've sold the tenth one, when it is $20.

FIFO is obviously awkward to implement in practice, but its a basic accounting principle - you use up the stuff you puchased, in the order in which you purchased it - therefore your current stock is ALWAYS valued at its most recent purchase price, and your costs should be based on the oldest price paid (FirstInFirstOut)

so in your example you buy 10 at $10, and another 10 at $20.

if you use 5, the cost of those 5 is $50 (5 x $10 - the oldest first), and your stock is worth $250 (5 at $10, and 10 at $20). You specifically do not recalculate an average cost under this method.

I do like the idea of using the average cost, as it is the most feasible in my mind. I haven't even got around to working on this part of my program yet, but I am glad that this topic has stirred up some intellectual consideration and I appreciate all your thoughts on how to do this.

Just a question regarding the use of the average price. If I am recording multiple inventory movements both in and out how do I manage to keep track of a changing average price on inward inventory without actually storing it's data in a table?

For Example... If I only had 1 item, and decided to calculate price by average and price was in terms of $/base unit. So Item1 has 20 base units in store at a price of $5/unit, and then there are 40 items purchased at $4 per unit, the average price should now be..$4.50/unit. Where could I store the first price unit put in and how would i be able to keep track of the price changes so that when a third batch is bought at a different price it averages: current + new price (per unit)/2

Thanks.
ConfusedA

to cacllulate a current average cost you WOULD have to maintain an current level of phyiscal stock and actual cost

as i say, before you go down this route, check with your accountants, as this method of calculating profit is generally NOT acceptable to Inland Rev (in UK) and you would have to restate your profits to a different basis - it is absolutely POINTLESS to try to use any accounting system that will not strike profits acceptable to the inland revenue.

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

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.

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

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.

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

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.

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.