Average Cost Price Function

[gemma-the-husky]

I'm a little confused by this as my understanding of AVCO is that the current AVCO is calculated from the cost and quantities of all deliveries of that item, depending on sales and purchase quantities. Doesn't that mean if there is a amendment to a delivery record this could affect the current AVCO?
This way of costing is new to our company so I'm sure we will soon figure out the best way of it working for us once I get this up and running.

We manufacture products so the deliveries are of the items which are used to construct the products. There aren't any other costs involved in the inventory cost, just the item cost as we purchase it from our supplier.
This is all new to me. I wasn't even aware of AVCO as a pricing model before my manager brought it to my attention for this project. I'm a willing learner so I'm keen to get this working for us. Professional help sounds like a sensible way to go, and partly why I've come to this forum to seek assistance, but my company seem to have faith in me to learn and implement this. Rightly or wrongly, time will tell...

Let's say your year end is 30th June

1. You could re-assess your AVCO by adding all intakes and despatches since 1st July 2021, to work out your AVCO each time, but that might be a big task to load on your servers.

2. You could just store your current AVCO as of the last intake of say 25th Feb 2022, and rework that the next time you get a new intake of material. Then use the new AVCO until the next intake. That would be a lot simpler and a lighter load on your servers.

Now if you do option 1, and your stock only represents, say 2 months usage, by going back to July to assess your AVCO you are really taking into account stock that you no longer have in assessing the AVCO which is demonstrably incorrect according to accounting principles - but if the prices aren't volatile it won't make much difference. But if, say you were talking about oil prices, then in a rising market, a rolling AVCO would understate stock value compared with a FIFO basis.

In general though, I would have thought that it doesn't really matter how you manage recalculating the AVCO from time to time, (as long as you process the change correctly) as each intake will sort of drip into the working average. That's why I shouldn't think it will make hardly any difference if you process intakes and outflows in a slightly different order. Furthermore, Given that AVCO is probably not acceptable under SSAP 9 (FRS 102) practices (I'm a little out of touch here though) , it's all a bit moot anyway. Your auditors may want you to calculate a year end adjustment to restate the AVCO stock value, if it's material. Maybe it's worth talking to your auditors first.

 

[The_Doc_Man]

strSQL = "SELECT * FROM Deliveries_Query WHERE Average_Cost_Price Is Not Null ORDER BY #" & Format(Goods_Delivery_Date, "dd\/mm\/yyyy") & "#"

There is potentially a bit of overkill here. IF Goods_Delivery_Date is actually a date field, you don't need to format it and then re-enclose with #. You can just ORDER BY Goods_Delivery_Date. IF, however, that is a TEXT field (and is well-behaved), you could ORDER BY CDATE( Goods_Delivery_Date) with equal effect. The only problem would be if there are a lot of clerical errors in the date inputs as well. Which would have the same effect REGARDLESS of the field's data type, since bad data is bad data however you cut it.

 

[ChrisMore]

Let's say your year end is 30th June

1. You could re-assess your AVCO by adding all intakes and despatches since 1st July 2021, to work out your AVCO each time, but that might be a big task to load on your servers.

2. You could just store your current AVCO as of the last intake of say 25th Feb 2022, and rework that the next time you get a new intake of material. Then use the new AVCO until the next intake. That would be a lot simpler and a lighter load on your servers.

Now if you do option 1, and your stock only represents, say 2 months usage, by going back to July to assess your AVCO you are really taking into account stock that you no longer have in assessing the AVCO which is demonstrably incorrect according to accounting principles - but if the prices aren't volatile it won't make much difference. But if, say you were talking about oil prices, then in a rising market, a rolling AVCO would understate stock value compared with a FIFO basis.

In general though, I would have thought that it doesn't really matter how you manage recalculating the AVCO from time to time, (as long as you process the change correctly) as each intake will sort of drip into the working average. That's why I shouldn't think it will make hardly any difference if you process intakes and outflows in a slightly different order. Furthermore, Given that AVCO is probably not acceptable under SSAP 9 (FRS 102) practices (I'm a little out of touch here though) , it's all a bit moot anyway. Your auditors may want you to calculate a year end adjustment to restate the AVCO stock value, if it's material. Maybe it's worth talking to your auditors first.

Thanks very much for your feedback on this subject, it has been very helpful . I'll speak with my managers regarding some of the key points you have raised.

 

[ChrisMore]

There is potentially a bit of overkill here. IF Goods_Delivery_Date is actually a date field, you don't need to format it and then re-enclose with #. You can just ORDER BY Goods_Delivery_Date. IF, however, that is a TEXT field (and is well-behaved), you could ORDER BY CDATE( Goods_Delivery_Date) with equal effect. The only problem would be if there are a lot of clerical errors in the date inputs as well. Which would have the same effect REGARDLESS of the field's data type, since bad data is bad data however you cut it.

Thanks, I added the format out of desperation to get the code to work. I have taken the format away but the code isn't doing what I want. It looks like the ORDER BY isn't working or there is something else incorrect.
As you can see in the screenshot below, Average_Cost_Price on the highlighted line should read £7.52 as per the ACP field but instead it reads £7.63 so the loop must be running out of order. Any suggestions?

 

[gemma-the-husky]

I haven't checked, but are you sure it's not working?

There isn't much difference between 7.52 and 7.63. Depending how you calculate the AVCO each time, you might well get this sort of effect. There's not really a right and wrong. It's more a matter of applying the same logic consistently. I don't quite follow the column headers in your table/query. You can't follow the changing quantity on hand in that example.

Try it on a smaller number of transactions to convince yourself it's working. Step through as you add new transactions, and test the calculations. If there's an error you should be able to see it appearing.

 

[ChrisMore]

I haven't checked, but are you sure it's not working?

There isn't much difference between 7.52 and 7.63. Depending how you calculate the AVCO each time, you might well get this sort of effect. There's not really a right and wrong. It's more a matter of applying the same logic consistently. I don't quite follow the column headers in your table/query. You can't follow the changing quantity on hand in that example.

Try it on a smaller number of transactions to convince yourself it's working. Step through as you add new transactions, and test the calculations. If there's an error you should be able to see it appearing.

There must be an issue somewhere. As shown in the below screenshot, I have taken away some of the data from the Average_Cost_Price column so the SQL only returns the rows where the Product_Price value is £7.52. This means the Average_Cost_Price value should be £7.52 because the average price of four records of £7.52 is £7.52. However, after running the code, the Average_Cost_Price values aren't £7.52.

The highlighted row in the screenshot shows the 'ACP' and 'Previous ACP' column values are £7.52 so the AVCO calculation is correct, the code is just not working so the Average_Cost_Price column value equals the ACP column value.

I've added the code below.

Dim dbs As DAO.Database
Dim rstDeliveries As DAO.Recordset
Dim strSQL As String

On Error GoTo ErrorHandler

Set dbs = CurrentDb

strSQL = "SELECT * FROM Deliveries_Query WHERE Average_Cost_Price Is Not Null ORDER BY Goods_Delivery_Date"
Set rstDeliveries = dbs.OpenRecordset(strSQL, dbOpenDynaset)


'If the recordset is empty, exit.
If rstDeliveries.EOF Then Exit Sub

With rstDeliveries
Do Until .EOF
.Edit
![Average_Cost_Price] = ACP
.Update
.MoveNext
Loop
End With

rstDeliveries.Close
dbs.Close

Set rstDeliveries = Nothing
Set dbs = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

 

[gemma-the-husky]

So

1. Do you store an average price to use for deliveries, or do you work it out each time?
2. When you add a new delivery, what code do you use to evaluate the average cost?
3. When you add a new intake, do you recalculate the average, or do you just store it for future use as above.

 

[ChrisMore]

So

1. Do you store an average price to use for deliveries, or do you work it out each time?
2. When you add a new delivery, what code do you use to evaluate the average cost?
3. When you add a new intake, do you recalculate the average, or do you just store it for future use as above.

I hope I am interpreting your questions correctly in my answers here.

1. The average price is both stored and calculated each time. It is stored in the Average_Cost_Price field and calculated using the 'Previous ACP' and 'ACP' calculated fields in the query. The Average_Cost_Price field is also used in the calculation because the 'Previous ACP' field uses the value in its calculation.

2. The expressions for the calculated fields in the query are:

Previous ACP: IIf(Nz(DLast("Average_Cost_Price","Deliveries_Query","Purchase_Orders_Deliveries_ID <> " & [Purchase_Orders_Deliveries_ID] & " And " & "Goods_Delivery_Date <=#" & Format([Goods_Delivery_Date],"mm/dd/yyyy") & "#" & " And " & "Code =" & [Code]),0)=0,[Product_Price],DMax("Average_Cost_Price","Deliveries_Query","Purchase_Orders_Deliveries_ID <> " & [Purchase_Orders_Deliveries_ID] & " And " & "Goods_Delivery_Date <=#" & Format([Goods_Delivery_Date],"mm/dd/yyyy") & "#" & " And " & "Code =" & [Code]))

ACP: Round((([On Hand Quantity]*[Previous ACP])+[Delivery Total Value])/([On Hand Quantity]+[Quantity_Delivered]),2)

3. When a new delivery is added, only the average for the new delivery is calculated and stored. The average for the previous delivery is already stored and not recalculated but is crucial to the calculation of the average price of the new delivery.

 

[gemma-the-husky]

Well, there's an issue. dlast() most likely does not return what you expect.
I wouldn't like to hazard a guess what it does return to be honest. Seriously, I have never ever used dlast()

I don't like using long nested expressions in queries. I would much rather use a function, as it's easier to test it.

 

[ChrisMore]

Well, there's an issue. dlast() most likely does not return what you expect.
I wouldn't like to hazard a guess what it does return to be honest. Seriously, I have never ever used dlast()

I don't like using long nested expressions in queries. I would much rather use a function, as it's easier to test it.

I initially tried DMax in the expression but DLast worked better. Not sure why it did but I stuck with it. The expression does exactly what I expect it to do to calculate the average cost but maybe it isn't working with the loop code.

Due to my lack of coding knowledge/experience, I automatically lean towards using long expressions (I predominately have an Excel background so I am used to using formulas). However, I see where you are coming from in using a function and hopefully that will work as expected.

I'm not 100% sure on how to convert the expression to a function but I'll give it a go.

 

[gemma-the-husky]

dlast()
perhaps someone else could comment on the precise value returned by dlast()

if you are sure that the expression works correctly, then I presume something else is going wrong, based on your experience that the values you are getting don't appear to be correct.

 

[ChrisMore]

dlast()
perhaps someone else could comment on the precise value returned by dlast()

if you are sure that the expression works correctly, then I presume something else is going wrong, based on your experience that the values you are getting don't appear to be correct.

I'm trying DMax in the new function in the hope that it will work correctly with the loop code. I've made a start with it but as this is my first function I'm a bit stuck. This is what I have so far:

Public Function PreviousACP() As Long

Dim lngACP As Long

lngACP = Nz(DMax("Average_Cost_Price", "Deliveries_Query", "Purchase_Orders_Deliveries_ID <> " & [Purchase_Orders_Deliveries_ID] _
& " And " & "Goods_Delivery_Date <=#" & Format([Goods_Delivery_Date], "mm/dd/yyyy") & "#" & " And " & "Code =" & [Code]), 0)

If lngACP = 0 Then

lngACP = [Product_Price]

End If

'If not 0, this function outputs value of lngACP DMax function

End Function

I'm sure it's a super basic function to create, but I don't have the coding knowledge to create my own functions from scratch on my own just yet. I am trying to replicate the long 'Previous ACP' query expression (as shown in post #28) with this function and there are two areas I'm not sure on:

1. the data type for the function and the declared lngACP (I tried Long and I'm getting a data type mismatch)
2. how to write the code so if lngACP is not 0 then the function outputs the value of the DMax statement

There are probably other areas I've missed/are currently incorrect. It would be great to be able to get some help and learn this to be efficient in creating my own functions. Thanks.

 

[gemma-the-husky]

I think if you use dmax, you will be finding the highest value of the AverageCostPrice within the records selected by the query, which most likely can't always be correct. If prices are falling for instance you won't want the highest price.

If you use dlast, I am not sure what you actually get. Do you get the last record inserted? But if that record was out of the true date sequence, then that probably that isn't the right record either. As I say, I never use dlast because I am uncertain which record it returns.

In some cases, I do make 2 lookups though - the first to find the record ID of the record with the highest date, and the second to read the data field that I really want (such as the price) from the record whose ID I just read.

Id there any way you can prepare a sample table of the stock movements of a given widget, showing items coming in, and their prices, and items moving out. and I will have a stab at the functions I would use to manage these prices.

 

[ChrisMore]

I think if you use dmax, you will be finding the highest value of the AverageCostPrice within the records selected by the query, which most likely can't always be correct. If prices are falling for instance you won't want the highest price.

If you use dlast, I am not sure what you actually get. Do you get the last record inserted? But if that record was out of the true date sequence, then that probably that isn't the right record either. As I say, I never use dlast because I am uncertain which record it returns.

In some cases, I do make 2 lookups though - the first to find the record ID of the record with the highest date, and the second to read the data field that I really want (such as the price) from the record whose ID I just read.

Id there any way you can prepare a sample table of the stock movements of a given widget, showing items coming in, and their prices, and items moving out. and I will have a stab at the functions I would use to manage these prices.

That's probably the reason DMax didn't work for me then and I had more success with DLast.

I have attached a test version of the database with the same data we have been focusing on in this thread.

I have included my version of the PreviousACP function, however, feel free to start it again if you prefer. The Deliveries_Query currently has my long expression in the 'Previous ACP' field so it's just a case of replacing this with the new function if you continue to use my setup.

I appreciate your help

 

[jdraw]

From m$oft https://support.microsoft.com/en-us...unctions-adb06169-bf49-4f80-bf04-1e4b5a48a8a7

​

 

[ChrisMore]

@gemma-the-husky have you had a chance to look at the sample database I attached? I appreciate any help you can provide with this

 

[gemma-the-husky]

@gemma-the-husky have you had a chance to look at the sample database I attached? I appreciate any help you can provide with this

I have some time now.
Can you just clarify your structure.

1. You have purchase orders, but no supplier table. For current purposes, we can assume the supplier is unimportant though.
2. You have an order-items table, but that isn't used in your query.
3. Then you have the order-deliveries table. So, I don't understand why you don't use the order-items table in the query. I also don't see what the prices in the order-deliveries table represent, as some of them are different to the prices in the order-items table.
4. You also do not have any stock issues recorded. (sales order items?) If you don't record your outgoings your inventory will just continue to increase. It may work arithmetically but it's not really correct, and you do need your process to take into account sales.
5. What is the price on the products table? What does that represent?

I would have thought as each purchase order delivery takes place you would re-evaluate the AVCO, and store that in the Products table. If products.cost is something else, you need a field products.avco. Then as you process each order intake, you do this (I would)

re-evaluate the quantity on hand (however you do that) and it's value using the current AVCO
evaluate the quantity and value for this intake
re-calculate the new AVCO, and update the value in the products table.
The history of AVCO changes could either be stored in the order-deliveries table, or as a subtable to the products table.

I would probably do this as part of the process to record the stock deliveries, although you could do it by iterating the order deliveries table, and successively updating all the items not already processed. I just can't see how you are doing this.

The logic would be similar, as below.

ie

record goods inward
call routine to re-evaluate AVCO

or

iterate uprocessed stock-deliveries
repeat for each record
    call routine to re-evaluate AVCO   
until all records checked

 

[ChrisMore]

I have some time now.
Can you just clarify your structure.

1. You have purchase orders, but no supplier table. For current purposes, we can assume the supplier is unimportant though.
2. You have an order-items table, but that isn't used in your query.
3. Then you have the order-deliveries table. So, I don't understand why you don't use the order-items table in the query. I also don't see what the prices in the order-deliveries table represent, as some of them are different to the prices in the order-items table.
4. You also do not have any stock issues recorded. (sales order items?) If you don't record your outgoings your inventory will just continue to increase. It may work arithmetically but it's not really correct, and you do need your process to take into account sales.
5. What is the price on the products table? What does that represent?

I would have thought as each purchase order delivery takes place you would re-evaluate the AVCO, and store that in the Products table. If products.cost is something else, you need a field products.avco. Then as you process each order intake, you do this (I would)

re-evaluate the quantity on hand (however you do that) and it's value using the current AVCO
evaluate the quantity and value for this intake
re-calculate the new AVCO, and update the value in the products table.
The history of AVCO changes could either be stored in the order-deliveries table, or as a subtable to the products table.

I would probably do this as part of the process to record the stock deliveries, although you could do it by iterating the order deliveries table, and successively updating all the items not already processed. I just can't see how you are doing this.

The logic would be similar, as below.

ie

record goods inward
call routine to re-evaluate AVCO

or

iterate uprocessed stock-deliveries
repeat for each record
    call routine to re-evaluate AVCO 
until all records checked

Thank you for having a look.

1. I removed the suppliers table because it wasn't relevant to the sample database.
2. I don't understand what you mean by this as the purchase order items table is in the query. Unless you mean customer order items, if so, I deemed it not necessary to the query because the quantity on hand function alone is enough to calculate the ACVO without the need for any fields in the customer order items table.
3. The price in the order deliveries table is the stored average cost price. The price in the purchase order items table is the buying price for the purchase order.
4. The customer order items table is used for sales. I removed the data because purchase data is enough to test whether the AVCO is working.
5. The price in the products table is the current buying price. It's the current pricing structure we have in place to understand our stock value. Hence why management want to introduce average cost as an addition/replacement to this structure.

When you say "call routine", do you mean through code or a manual routine? I tried to make a code routine as shown in my posts #26 and #32 and the query calculation fields. I think my set up (once the code works) uses your logic (or similar) but maybe I'm off the mark.

My logic is:
The intake is recorded in the deliveries table/deliveries query and using the deliveries query calculation field, previous ACP function (post #32) and loop code (post #26), the AVCO is re-evaluated and stored in the Average_Cost_Price field in the deliveries table.

 

[gemma-the-husky]

Thank you for having a look.

1. I removed the suppliers table because it wasn't relevant to the sample database.
2. I don't understand what you mean by this as the purchase order items table is in the query. Unless you mean customer order items, if so, I deemed it not necessary to the query because the quantity on hand function alone is enough to calculate the ACVO without the need for any fields in the customer order items table.
3. The price in the order deliveries table is the stored average cost price. The price in the purchase order items table is the buying price for the purchase order.
4. The customer order items table is used for sales. I removed the data because purchase data is enough to test whether the AVCO is working.
5. The price in the products table is the current buying price. It's the current pricing structure we have in place to understand our stock value. Hence why management want to introduce average cost as an addition/replacement to this structure.

When you say "call routine", do you mean through code or a manual routine? I tried to make a code routine as shown in my posts #26 and #32 and the query calculation fields. I think my set up (once the code works) uses your logic (or similar) but maybe I'm off the mark.

My logic is:
The intake is recorded in the deliveries table/deliveries query and using the deliveries query calculation field, previous ACP function (post #32) and loop code (post #26), the AVCO is re-evaluated and stored in the Average_Cost_Price field in the deliveries table.

1. OK
2/3. Ok. I misread it, and thought you have ignored the ordered-items. I would add a new field to the products table to store AVCO. I might also store the calculated AVCO in the order-delivery-items table to store that value to have a history of the changes. I might also/instead store the history in a subtable linked to the product table.
4. Ok.
5. OK.

Call routine. I would have a function/piece of code that accepts certain key parameters and returns the new AVCO. A "black box" routine that is thoroughly tested to return the correct value.

I will have a play!

 

[ChrisMore]

1. OK
2/3. Ok. I misread it, and thought you have ignored the ordered-items. I would add a new field to the products table to store AVCO. I might also store the calculated AVCO in the order-delivery-items table to store that value to have a history of the changes. I might also/instead store the history in a subtable linked to the product table.
4. Ok.
5. OK.

Call routine. I would have a function/piece of code that accepts certain key parameters and returns the new AVCO. A "black box" routine that is thoroughly tested to return the correct value.

I will have a play!

Thanks very much for your continued assistance

I'm happy to also store the ACVO in the products table, that makes sense. But can I ask why it's necessary to store the history in a products subtable? The plan was to store it in the deliveries table, which is linked to the purchase items table, which is linked to the products table.