Average Cost Price Function

[ChrisMore]

Hi all,

I'm hoping for some assistance to help me build a function which calculates the average cost price (ACP) of our stocked products. I have had a look online to see if anything already exists but couldn't find anything suitable. I have attached 3 examples taken from an Excel workbook to help explain what I require. I'll breakdown what the columns are and how they relate to my database:

• Stock ID is the item code and is entered into the Product_Code field in the Purchase_Orders_Items table (purchase) and Customer_Order_Items table (sale).
• Movement is the transaction of the item, whether through a purchase order or sales order. Purchase order movements are entered into the Quantity_Delivered field in the Purchase_Orders_Deliveries table (linked to the Purchase_Orders_Items table where the Product_Code is entered). Sales orders are entered into the Order_Quantity field in the Customer_Order_Items table.
• Price per item for purchases are entered into the Product_Price field in the Purchase_Orders_Items table. Price per item for sales is taken from the ACP of the previous purchase of that item.
• Value is calculated from Movement x Price per item.
• Inventory is a running sum of all transactions (+ for purchases and - for sales).
• Total stock value is a running sum of the Value column.
• ACP is calculated from Total stock value divided by Inventory.

The ACP for each item is taken from the ACP of the previous purchase order for that item so I'm sure transaction dates will be a factor to consider. The purchase transaction date is the Goods_Delivery_Date field in the Purchase_Orders_Deliveries table and the sales transaction date is the Production_Complete_Date field in the Shipments table (linked to the Customer_Order_Items table). I have attached a screenshot of the database relationships.

I hope to use this function in a query so when it is run it shows the item names with the current ACP value for each. It is also desirable to find the ACP as per a selected date to see historical prices.

I really appreciate any help you can provide.
Chris

 

[gemma-the-husky]

I just have to say that inventory is one of the hardest things to manage.

While managing inventory quantities is not so difficult, managing costs is very difficult.

With an average cost price, you need a starting cost price, then each time you receive goods, you need to adjust the average price to reflect the addition of the new intake, so you need to quantify the existing stock level, and then the new stock level following the intake. Sales are then charged out at the new average until the next intake.

So if you have 3000 at £10 each, the total cost is £30000
If you buy another 400 at £11 each you now have 3400 items and a total cost of 34400, so the average is now £10.12, which becomes your new charge out rate. You might want to store a history of the reworkings of the average on the stock intake record, or in a AVCO price calculation history record.

This is one of the simpler ideas, but you will need data tables that support this. I would STORE the current AVCO in the product table, so you only ever need to work out the new AVCO for each intake.

Note that an average cost method of valuing inventory may not be accepted by your auditors.
You mention running sums, but that's not really the best concept with a database.

 

[ChrisMore]

I just have to say that inventory is one of the hardest things to manage.

While managing inventory quantities is not so difficult, managing costs is very difficult.

With an average cost price, you need a starting cost price, then each time you receive goods, you need to adjust the average price to reflect the addition of the new intake, so you need to quantify the existing stock level, and then the new stock level following the intake. Sales are then charged out at the new average until the next intake.

So if you have 3000 at £10 each, the total cost is £30000
If you buy another 400 at £11 each you now have 3400 items and a total cost of 34400, so the average is now £10.12, which becomes your new charge out rate. You might want to store a history of the reworkings of the average on the stock intake record, or in a AVCO price calculation history record.

This is one of the simpler ideas, but you will need data tables that support this. I would STORE the current AVCO in the product table, so you only ever need to work out the new AVCO for each intake.

Note that an average cost method of valuing inventory may not be accepted by your auditors.
You mention running sums, but that's not really the best concept with a database.

Thanks, I definitely want to stay away from storing the AVCO. I have been instructed by management to introduce the average cost for each product in such a way that reduces the requirement of updating values manually. We have a history of our prices being inputted incorrectly and therefore giving us a false profit figure. The only price which can be inputted is the one which appears on the purchase order.

I am currently using a function which automatically calculates our current inventory for each product. It's the one created by Allen Browne http://allenbrowne.com/AppInventory.html. Ideally the AVCO function will automatically calculate the price in a similar fashion.

 

[gemma-the-husky]

I don't see how you can avoid storing the AVCO. That isn't something you want to evaluate on the fly. If you are going to calculate the AVCO each time, you may as well do it "properly" and evaluate the true FIFO cost.

I didn't say do it manually. What I meant was when you receive new product, you have the qty and cost of that receipt, and you should have the qty and cost of the current inventory, so update the new AVCO by processing that intake.

(new AVCO) = (existing total cost + new total cost) / (existing inventory qty + new inventory qty)
(new AVCO) = (existing qty * current AVCO) + new total cost) / (existing inventory qty + new inventory qty)
ie - the bold figures should provide the data you want

How far are you going back to assess the average cost. If you go back too far, you are including intakes that have already all been sold, so assuming rising costs, you will always be understating absorbed costs with AVCO, and probably understating the value of inventory on hand.

If you used a standard cost system, you would store that Standard cost, and then evaluate variances. AVCO is pretty similar really, I would have thought.

All of this is why evaluating inventory QUANTITY is easy, but evaluating inventory COST is decidedly not easy.

You can always evaluate the cost of the stock on hand, by working back through the last intakes to arrive at the actual cost of the current stock on hand according to FIFO, and see how that compares with your average.

Anyway, it's your system. Good luck evaluating the costs.

 

[ChrisMore]

I don't see how you can avoid storing the AVCO. That isn't something you want to evaluate on the fly. If you are going to calculate the AVCO each time, you may as well do it "properly" and evaluate the true FIFO cost.

I didn't say do it manually. What I meant was when you receive new product, you have the qty and cost of that receipt, and you should have the qty and cost of the current inventory, so update the new AVCO by processing that intake.

(new AVCO) = (existing total cost + new total cost) / (existing inventory qty + new inventory qty)
(new AVCO) = (existing qty * current AVCO) + new total cost) / (existing inventory qty + new inventory qty)
ie - the bold figures should provide the data you want

How far are you going back to assess the average cost. If you go back too far, you are including intakes that have already all been sold, so assuming rising costs, you will always be understating absorbed costs with AVCO, and probably understating the value of inventory on hand.

If you used a standard cost system, you would store that Standard cost, and then evaluate variances. AVCO is pretty similar really, I would have thought.

All of this is why evaluating inventory QUANTITY is easy, but evaluating inventory COST is decidedly not easy.

You can always evaluate the cost of the stock on hand, by working back through the last intakes to arrive at the actual cost of the current stock on hand according to FIFO, and see how that compares with your average.

Anyway, it's your system. Good luck evaluating the costs.

Thanks very much for this info, I was definitely over complicating it thinking I needed a function. I have been able to use your calculation to get the AVCO via a calculation field in a query. It works great when a new purchase record is added as the last record in the query but not so well when the record slots in between other purchases (i.e. in the possible scenario that a purchase record needed updating as the goods received date was initially entered incorrectly). As you can see in the screenshot of the query below, the record with the price £7.53 in the 'Average_Cost_Price' field needs to be updated to £7.57 as per the ACP field.

The AVCO calculation involves three fields and two of them are calculation fields. The ACP field is your calculation (new AVCO) = (existing qty * current AVCO) + new total cost) / (existing inventory qty + new inventory qty). The calculation requires the current AVCO (I've named this field 'Previous ACP') meaning another field is required to store the AVCO. 'Previous ACP' uses the stored AVCO in the calculation. This means when a new record is added in between the other records, the ACP field calculation concertinas down as each stored AVCO is updated.

I haven't been successful in creating some code to automatically update the stored AVCO. I can't use an update query as each record needs to be updated one at a time in order for the ACP calculation to concertina down. The code below hasn't worked and I don't know if I'm on the wrong track with it. Note, the where condition in the code will change but is being used to test the existing data.

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"
Set rstDeliveries = dbs.OpenRecordset(strSQL, dbOpenDynaset)

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

The screenshot below shows the result when running the code. Obviously not what I am looking for. I don't understand how it has arrived at the prices in the Average_Cost_Price field.

Are you or anyone else able to point me in the right direction to get this code working.

Many thanks.

 

[gemma-the-husky]

Yes, well entering dates out of sequence and so on is hard to avoid.
Could you not just store an "effective date" and apply the records in the order they are entered, rather than try to rework everything for a clerical error? That's what I would want to do. If the entry order needs to be accurate, maybe you need to review the clerical process.

if a date is entered wrongly, and the AVCO is slightly incorrect for a day or too, you won't even notice it.

 

[gemma-the-husky]

I haven't been successful in creating some code to automatically update the stored AVCO. I can't use an update query as each record needs to be updated one at a time in order for the ACP calculation to concertina down. The code below hasn't worked and I don't know if I'm on the wrong track with it. Note, the where condition in the code will change but is being used to test the existing data.

I would think either -
a) you process the intakes records one at a time, so you don't need an update query. You need to just process the one new record OR
b) you update the AVCO once for each batch of intakes

so you have an AVCO as at 28/2/22. You have 4 intakes on 1/3/22. You reassess the AVCO at the end of 1/3/22 based on qty on hand at 28/2/22 plus the effect of the 4 intakes dates 1/3/22 - maybe a daily update to rework all the AVCO's for new intakes.

You could either do this as a total per day, or iterate each new intake separately. I cant really think it's critical, so whichever is easier to do.


to do it once you have

form sum of new intakes
process the sum of new intakes (ie adjust the qty on hand and the AVCO)

to do each separately you have a recordset of new intakes then

open recordset of new intakes
while not end of new intakes
  process intake (ie adjust the qty on hand and the AVCO)
  move to next intake
wend

Surely one of those is sufficient for your purpose.

 

[Eugene-LS]

![Average_Cost_Price] = ACP

Where you got that ACP variable and it's value?

 

[ChrisMore]

Yes, well entering dates out of sequence and so on is hard to avoid.
Could you not just store an "effective date" and apply the records in the order they are entered, rather than try to rework everything for a clerical error? That's what I would want to do. If the entry order needs to be accurate, maybe you need to review the clerical process.

There is a chance this scenario never occurs but through testing this AVCO feature it seemed a possibility. Another possibility is if a purchase needs to be retrospectively entered, i.e. if a purchase delivery was not recorded on the day it arrived. Like I say, this might never happen but when my colleagues start using this feature I just know they will find a way to break it.

I feel like I'm close to getting this to work but can't quite get over the line.

 

[ChrisMore]

Where you got that ACP variable and it's value?

ACP is a field in the query, as shown in the screenshot of the query.

 

[Eugene-LS]

ACP is a field in the query, as shown in the screenshot of the query.

I meant where does this come from in your code?

 

[ChrisMore]

I meant where does this come from in your code?

strSQL = "SELECT * FROM Deliveries_Query

'Deliveries_Query' is the query in question

 

[Eugene-LS]

strSQL = "SELECT * FROM Deliveries_Query

Clearly, you still don't understand my question, unfortunately.
Do you clearly understand what the code you have shown does?
It seems not.

 

[ChrisMore]

Clearly, you still don't understand my question, unfortunately.
Do you clearly understand what the code you have shown does?
It seems not.

I am still learning code, so I have taken this from https://docs.microsoft.com/en-us/pr...007/bb243789(v=office.12)?redirectedfrom=MSDN and modified it. I don't fully understand it, hence why I said:

I haven't been successful in creating some code to automatically update the stored AVCO. I can't use an update query as each record needs to be updated one at a time in order for the ACP calculation to concertina down. The code below hasn't worked and I don't know if I'm on the wrong track with it.

and

The screenshot below shows the result when running the code. Obviously not what I am looking for. I don't understand how it has arrived at the prices in the Average_Cost_Price field.
View attachment 98775

Please can you reword the question

 

[Eugene-LS]

Please can you reword the question

Here you are!
... in your code I see setting string:

![Average_Cost_Price] = ACP

but ACP is not declared in shown code, so my question is:
What it is - variable or function? - and where is it from?
...
I'm not just asking, it seems to me that this is key to solving your problem

 

[LarryE]

You are correct that it takes an open recordset to loop down through each record and then calculate and enter your required ACP value. But your SQL statement does not sort the records by date. The screenshot does, but the SQL you are using to open the recordset does not. Use an ORDER BY statement in your SQL to sort by date and that should get you closer to what you need (assuming the same product is not received more than once a day).

 

[gemma-the-husky]

Thanks very much for this info, I was definitely over complicating it thinking I needed a function. I have been able to use your calculation to get the AVCO via a calculation field in a query. It works great when a new purchase record is added as the last record in the query but not so well when the record slots in between other purchases (i.e. in the possible scenario that a purchase record needed updating as the goods received date was initially entered incorrectly). As you can see in the screenshot of the query below, the record with the price £7.53 in the 'Average_Cost_Price' field needs to be updated to £7.57 as per the ACP field.

The AVCO calculation involves three fields and two of them are calculation fields. The ACP field is your calculation (new AVCO) = (existing qty * current AVCO) + new total cost) / (existing inventory qty + new inventory qty). The calculation requires the current AVCO (I've named this field 'Previous ACP') meaning another field is required to store the AVCO. 'Previous ACP' uses the stored AVCO in the calculation. This means when a new record is added in between the other records, the ACP field calculation concertinas down as each stored AVCO is updated.

View attachment 98776
I haven't been successful in creating some code to automatically update the stored AVCO. I can't use an update query as each record needs to be updated one at a time in order for the ACP calculation to concertina down. The code below hasn't worked and I don't know if I'm on the wrong track with it. Note, the where condition in the code will change but is being used to test the existing data.

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"
Set rstDeliveries = dbs.OpenRecordset(strSQL, dbOpenDynaset)

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

The screenshot below shows the result when running the code. Obviously not what I am looking for. I don't understand how it has arrived at the prices in the Average_Cost_Price field.
View attachment 98775

Are you or anyone else able to point me in the right direction to get this code working.

Many thanks.

I don't really think you would want to rework the AVCO to set the cost on each delivery. If you are costing your deliveries once a day, or once a week, I would have thought you just use the current AVCO. From time to time you re-evaluate the AVCO. You could even cost the deliveries based on your despatches, or cost the ORDERS (rather than the deliveries) based on the AVCO at that time, None of it really matters so much. It's more a matter of establishing a policy that works.

If you then find that at the end of an accounting period you have absorbed too much cost or too little cost based on a FIFO evaluation of your final stick, you can make an adjustment to the profit.

Whichever way you go, you need to understand the pros and cons of each method. You do need to be fully conversant with how to code this, and you do need corporate agreement on how this will work. To repeat, managing the quantity is easy. Managing the costs is decidedly not easy.

Are you manufacturing. Do you just sell parts, or do you assemble or machine parts into larger structures. Do you need to absorb labour and other costs in assessing the inventory cost. Hopefully you are just selling parts, so it's easier but there's so much to consider. If this is all new to you, maybe your company needs to get some professional help on this, as it's not a trivial project.

 

[ChrisMore]

I don't really think you would want to rework the AVCO to set the cost on each delivery. If you are costing your deliveries once a day, or once a week, I would have thought you just use the current AVCO. From time to time you re-evaluate the AVCO. You could even cost the deliveries based on your despatches, or cost the ORDERS (rather than the deliveries) based on the AVCO at that time, None of it really matters so much. It's more a matter of establishing a policy that works.

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.

Are you manufacturing. Do you just sell parts, or do you assemble or machine parts into larger structures. Do you need to absorb labour and other costs in assessing the inventory cost. Hopefully you are just selling parts, so it's easier but there's so much to consider. If this is all new to you, maybe your company needs to get some professional help on this, as it's not a trivial project.

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...

 

[ChrisMore]

Here you are!
... in your code I see setting string:

![Average_Cost_Price] = ACP

but ACP is not declared in shown code, so my question is:
What it is - variable or function? - and where is it from?
...
I'm not just asking, it seems to me that this is key to solving your problem

I see what you are asking but I'm still not 100% sure how to word the answer.
I believe ACP is a variable.
As for your "Where is it from" question, the only way I know to answer this is that ACP is a calculated field in the Deliveries_Query query.
Sorry for making this difficult

 

[ChrisMore]

You are correct that it takes an open recordset to loop down through each record and then calculate and enter your required ACP value. But your SQL statement does not sort the records by date. The screenshot does, but the SQL you are using to open the recordset does not. Use an ORDER BY statement in your SQL to sort by date and that should get you closer to what you need (assuming the same product is not received more than once a day).

Thanks for your suggestion, I have added ORDER BY to the statement but I'm still not getting the desired results, maybe I haven't added it correctly.

My code is now:

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 #" & Format(Goods_Delivery_Date, "dd\/mm\/yyyy") & "#"
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

These are the results after the code is run: