Weighted Average Calc

[Fijian]

Hi,

I've searched hours on the forum but with no luck. If there's anyone out there who can help me would be very much appreciated.

The calculation in Excel is very simple but I am unable to replicate in Access. Please don't confuse this with FIFO calculation as it is purely Weighted Average Cost method based on Buy unit prices i.e Sale unit prices are not required as it will sell at the Average Cost of the Buys upto the point of Sale.

i.e.
Buy 100 @2 = 200
Buy 100 @3 = 300
Total 200@ 2.50 =500
Sell 100 leftover will be
Total 100 @ 2.50

I am attaching an Excel S/sheet which shows the Calculation of Stock ABC and the Average Prices (which is what I am trying to achieve). I have also attached an Access Dbase with same details but without any calcs as whatever I have done I am unable to achieve my result.

Any help will be much appreciated.

Thanks

 

[The_Doc_Man]

Here is the situation where I don't know how you handle it, and I'll explain why it is important to define this... look at points I have marked.

Action   Amount  Cost

Buy       100       2
Buy       100       3
Subtot   200       2.5
Sell       100       2.5
Subtot   100       2.5          POINT 1
Buy       100       3
Subtot   200       2.75
Sell       100       2.75
Subtot   100       2.75         POINT 2

At the two points in question, the issue is that you have two ways to treat this problem. In each case, you sold ALL of the items at a given contributor price. At point 1 after the sale, you exhausted all of the items you bought at price 2. Should this price REALLY be 2.5... or 3?

Similar question at point 2. You have now sold all of the items bought at the earlier price.

So is this a true running total over all time - or only a running total (and average) over as far back as those items at that price were still in your inventory?

 

[gemma-the-husky]

fijian

i would have thought that an (bulk) acess query wouldn't help.

it seems to me that to do this, after every movement, both in and out, you have to reclculate the qty and cost balances, and recompute the average.

the sequence of operations would even be significant - ie in general purchase ---> sale would produce a different answer to sale ---> purchase.

this may not be a problem for you, although as an aside this method of computing stock balances seems to run contrary to acceptable methods under SSAP's (SSAP 9 in my day) (if there still is such a thing)

 

[Fijian]

Hi The_Doc_man,

The values you have shown are correct!

If the values at Point 1 were sold at 2, then it becomes FIFO (First In First Out) and that's why I mentioned not to get mixed with that scenario. This method is Weighted Average and thats why 2.5 at Point 1 and 2.75 at Point 2 is correct and that is what I need.

and

Hi Gemma,

My dbase is not a key entry dbase but it Analyzes bulk data from another system and needs independant calculation which I am trying to do in this dbase (mind you, this is just the simple part as there is Yield and Price related to it which I have taken care off) therefore I'll have to recalculate each individual item if I re-import data - which will be on weekly basis!

So reagrdless of any standard means of calculation, I need to calculate it in this way.

Thanks very much,

Looking forward to hear some favourable response!

Fijian

 

[gemma-the-husky]

then i think you need to open a recordset, and iterate it in order - as i say i think the sequence of inputs/withdrawals WILL actual affect the calculation, but presumably you have a preferred sequence, and calculate the running totals for each record.

it would be fairly straightforward job - the important thing is that the sequence you use needs to be a repeatable - you need a unique identifier of some sort

 

[Fijian]

Hi Gemma,

I do have unique ID for each transaction which comes from the source dbase which are attached to each transaction.

I am still not sure what to do?

Cheers

 

[neileg]

If it works in Excel, why do you want to use Access? As Gemma suggests, order is implicit in a spreadsheet like Excel, order in an Access table is irrellevant. You have to impose the order using a sequencing field of some sort.

 

[gemma-the-husky]

in access you would have to open a recordset, sorted in the order yuo want to process the records, then run pseudocode such as

read firstrecord
while not endoffile
        process the record, evaluating balance
        readnext record
endwhile

effectively you do this in excel by referring to the previous row in each row's calculations. obviously in excel if you then change the row order, it will mess up your calculations.

the same thing happens in access, so you need a consistent row order. But the underlying problem with access (not really a problem, just a paradigm) is that access does not intrinsically have an order, it just deals in SETS of data. Therefore any bulk query processing that needs an implicit order cannot work, and you have to find a way to get around it, which you can do by processing the recordset a row at a time. Recordset processing is slower than using a bulk query, but still usable.

 

[The_Doc_Man]

Fijian, the issue here - as so accurately described by gemma - is that a query works on a set of data records. And here, I am using a very precise word. The word was NOT "sequence" - it was "set" - because at least in theory, when you use SQL, the order in which something is done should not matter. The original view was that SQL was a black box that did the same thing to every potentially selected record, and it might as well have been done simultaneously as any other way.

You are looking at VBA and some really complex code, I think, because this is a "moving window" algorithm. You need to track more data than you actually showed. I'll try to explain what you need...

Imagine your entries of part number, quantity, cost, and a couple of more fields: DateOfPurchase, QuantityUnsold. The QTYUNSOLD field would be used similar in concept to this.

1. When you buy the goods in quantity at some cost, the record that describes the purchase says the QTYUNSOLD matches the amount you purchased. The date of purchase is obvious. The cost is whatever it was.

2. When you sell goods. you ALWAYS take the oldest goods first. If you have two lots on hand, sell the earlier lot first. When you exhaust the quantity in a record, the QTYUNSOLD drops to zero for that oldest record.

3. When you re-evaluate your price, it is based on the sum or average of (not original amount purchased, but...) QTYUNSOLD x Cost for that record.

The trick is the sale. If the sale forces you to exhaust the implied inventory from one record, you have to split the draw to encompass later stock records. That is, you must find the oldest record for that item and take as much inventory as is needed AND available. Then if you need more you go back and find the next-oldest record and exhaust that inventory amount. Keep going until the order is finished or you are out of inventory.

This is a case that violates the instincts of most Access designer types because you have to treat inventory differently than it normally would be treated. It is the "weighted" part that makes this unique - and ugly.

Because of that sliding window, you need something at least along these lines to accomplish what you describe. I'm going to stick my neck out here and suggest that you not attempt to recompute price during any intermediates of this process. Compute your price before you start and be done with it. Don't recompute prices again until all pending draws are complete.

This is, indeed, a FIFO algorithm.

that's why I mentioned not to get mixed with that scenario

If you DON'T then you are going to guarantee that inflation will eat your socks. Because as prices go up, if you DON'T use this FIFO approach the older, lower prices will drag down your weighted average. You MUST use a FIFO to eliminate obsolete prices or face loss of profit because your too-simple query cannot provide adequate pricing information.

Another way of saying this is that you can tell me to not throw you into the briar patch, but if that is where you need to go to do what you want to do, then what am I supposed to do? Leave you where you are, which is akin to NOWHERE, because you said "don't go there?" Can't do that and still give you reasonable advice on solving your problem.

 

[Fijian]

Thanks DocMan for your advise.

But I do not need to take a new methodology because the Avg Weighted is complex. I understand the full concept of FIFO and Inventory system but this is bit more complex. This is to do with Shares and the Accounting method required is Weighted Average which is the way I have defined in the Excel s/sheet.

So in simple terms, I need to calculate the way I have described and NEILEG I would have left it in Excel if it was a simple spreadsheet but I am dealing with Thousands of records which Excel won't be able to handle.

Otherwise, are we taking a defeat that this cannot be done in Access?

Anyone else out there who can help?

Thanks

 

[neileg]

Doc Man, just a heads up. Weighted average is considered a perfectly acceptable basis for stock valuation. In a rising price scenario, FIFO has the disadvantage that it charges the lowest price to your trading account, thereby increasing the profit simply because of price inflation in your stock. Weighted average is much less sensitive to this effect.

 

[gemma-the-husky]

fijian

the pseudo code i gave you will work

recordset hanlding is not a problem

just do an access query sorted by date order, and then iterate the rows in the recordset - in some ways you can get more control over the porcess, although it might take al ittle longer

afterwards, if it is convenient or possible you could calcluate/update the average as you insert each new record

 

[The_Doc_Man]

Thanks, neil. I'm not up on all of the various ways to consider stock pricing. Got out of that field a LONG time ago before more modern stuff came into being. I only assisted (a former employer's) purchasing staff back in the day when "just-in-time" scheduling was beginning to have practical meaning as opposed to mere theoretical consideration. (Of course, I mean "practical on computers.")

 

[JoeCruse]

Fijian,

I do a weighted avg for shipping mixed lots of materials, each lot having their own chemistry. To do this I set this little calculation in a Module:

Function WEIGHTEDAVG(X, N, T)
    ' Where X is concentration, N is no. of pallets per crushing lot,
    ' and T is total no. of pallets in the shipment
    ' Returns a weighted concentration value
    

    WEIGHTEDAVG = (N / T) * X
    
End Function

I have tables that hold lot analysis data and the lots assigned to each shipping order. I made a query that calculates total packages shipped from a lot for each order. I then run a query that includes the 2 related tables holding analysis data, the table holding lots assigned to each shipping order, and the query that runs total packages for each lot in the order. A calculated field in the query runs a SUM on the called WEIGHTEDAVG Function above.

This is pulled up in my order form by clicking a command button that pulls up a subform that has this last query as its record source. The underlying query looks at the orderID in my order form and runs the query and pops up to give me the results.

It took a lot of work to get it right, but it works well. If it would help you, I can attach a dummy mdb with the queries and stuff.

 

[Fijian]

Hi Joe,

Brilliant!

Please send me a dummy file, I will very much appreciate it.

Thanks

 

[JoeCruse]

Hey Fijian,

here is a dummy Access file zipped up with the basics of how I do weighted averages. It includes tables, queries, forms, and modules. Hopefully you'll find something useful in it. The 2 "XRF..." tables hold info/data that the weighted avg is calculated on. There is also some code in the modules and a form for using labels as a command "hyperlink", which I sometimes like better than command buttons.

Go to the Shipping Log form, which has one shipping record in it. You'll see that 2 "lots" are entered to fill the order in a subform, with the # of packages from each lot also entered.

This is in 2002-2003 format.

Good luck!

 

[Fijian]

Thanks Joe,

I had a look at the dbase you sent but it is not what I intend to do.

Is there anyone else with any ideas?


Thanks

 

[AccessDamian]

Question

Hi Fijian,

Which version is it?