Inventory Pricing (Changes in prices)

ConfusedA

Registered User.
Local time
Today, 18:34
Joined
Jun 15, 2009
Messages
101
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

Purchased are stored in "lots" with the cost per item for that "lot" of items.
 
Last edited:
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.
 
Last edited:
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.
 

Users who are viewing this thread

Back
Top Bottom