By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 434,959 Members | 2,432 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,959 IT Pros & Developers. It's quick & easy.

# Calculation on Weighted Average Cost

 100+ P: 134 I want to calculate the average price, I have a cost price table- tblcostPrice as below,Same part no can be purchase from different supplier at different cost. In my tblcostPrice I have 2 suppliers for the same part at different cost Part No - KN-12345 Product ID....SUPPLIER......Qty......Cost price A0123.........AAA CO.......1000..... 0.90 A0123.........AAA CO...... 5000..... 0.70 A0123.........AAA CO.......9000..... 0.55 A0123.........BBB CO.......1000..... 0.85 A0123.........BBB CO.......4000.... .0.65 A0123.........BBB CO.....12000..... 0.50 So During Invoicing, If I enter a qty of 8,000 for part no = KN-12345, It will take all supplier's cost price fall under the same Part nos and check the qty range from table - tblcostPrice.This is to enable me to calculate the profit using average cost price against the selling price. For manual calculation, 8K cost price fall under the qty range of 5K in Supplier-AAA, and 4K in Supplier-BBB It will retrieve the rows from the above table and compute - A0123.........AAA CO...... 5000..... 0.70 =3500 A0123.........BBB CO.......4000.... .0.65 =3250 __________________________________________________ ......................................9000........ ........ 6750 Average cost = 0.75 (6750/9000) But my Access knowledge is limited. Dec 28 '07 #1
Share this Question
5 Replies

 Expert 100+ P: 254 I want to calculate the average price, I have a cost price table- tblcostPrice as below,Same part no can be purchase from different supplier at different cost. In my tblcostPrice I have 2 suppliers for the same part at different cost Part No - KN-12345 Product ID....SUPPLIER......Qty......Cost price A0123.........AAA CO.......1000..... 0.90 A0123.........AAA CO...... 5000..... 0.70 A0123.........AAA CO.......9000..... 0.55 A0123.........BBB CO.......1000..... 0.85 A0123.........BBB CO.......4000.... .0.65 A0123.........BBB CO.....12000..... 0.50 So During Invoicing, If I enter a qty of 8,000 for part no = KN-12345, It will take all supplier's cost price fall under the same Part nos and check the qty range from table - tblcostPrice.This is to enable me to calculate the profit using average cost price against the selling price. For manual calculation, 8K cost price fall under the qty range of 5K in Supplier-AAA, and 4K in Supplier-BBB It will retrieve the rows from the above table and compute - A0123.........AAA CO...... 5000..... 0.70 =3500 A0123.........BBB CO.......4000.... .0.65 =3250 __________________________________________________ ......................................9000........ ........ 6750 Average cost = 0.75 (6750/9000) But my Access knowledge is limited. Let's assume you have a frmInvoice with cboProdID (Combobox for selecting ProductID), txtQty (Textbox for entering Qty) and txtAvgCost (for displaying average cost). You pick the ProductID and you enter a Qty. There's 2 things that need to happen now. Your form needs to look up the ProductID, Qty, and Cost price for each supplier - it is actually the maximum qty that is less than the value you entered and the minumum price. And then it needs to get an average from that list. I created a query for part 1 - qryAvg1 You can create the form I mention here or change the references to your own form. Create a new query in design view. Don't select any tables or queries and close the dialog. Click on the "SQL" button on the toolbar and paste the following SQL and save the query as qryAvg1: SELECT Max(tblcostPrice.Qty) AS Qty, tblcostPrice.Supplier, Min(tblcostPrice.[Cost Price]) AS Price FROM tblcostPrice WHERE (((tblcostPrice.ProductID)=[Forms]![frmInvoice]![cboProdID]) AND ((tblcostPrice.Qty)<=[Forms]![frmInvoice]![txtQty])) GROUP BY tblcostPrice.Supplier; Part 2: On the AfterUpdate property of txtQty select [Event Procedure] from the drop list and then click on (...) next to it to open code window. Paste the following: Expand|Select|Wrap|Line Numbers   Me.txtAvgCost = DAvg("[Price]", "qryAvg1")     If you add the same line to the AfterUpdate property of the cboProdID it will enable you to change which product you are invoicing and not have to reselect a qty. Save the changes and open the form and enter some selections. Dec 28 '07 #2

 100+ P: 134 Let's assume you have a frmInvoice with cboProdID (Combobox for selecting ProductID), txtQty (Textbox for entering Qty) and txtAvgCost (for displaying average cost). You pick the ProductID and you enter a Qty. There's 2 things that need to happen now. Your form needs to look up the ProductID, Qty, and Cost price for each supplier - it is actually the maximum qty that is less than the value you entered and the minumum price. And then it needs to get an average from that list. I created a query for part 1 - qryAvg1 You can create the form I mention here or change the references to your own form. Create a new query in design view. Don't select any tables or queries and close the dialog. Click on the "SQL" button on the toolbar and paste the following SQL and save the query as qryAvg1: SELECT Max(tblcostPrice.Qty) AS Qty, tblcostPrice.Supplier, Min(tblcostPrice.[Cost Price]) AS Price FROM tblcostPrice WHERE (((tblcostPrice.ProductID)=[Forms]![frmInvoice]![cboProdID]) AND ((tblcostPrice.Qty)<=[Forms]![frmInvoice]![txtQty])) GROUP BY tblcostPrice.Supplier; Part 2: On the AfterUpdate property of txtQty select [Event Procedure] from the drop list and then click on (...) next to it to open code window. Paste the following: Expand|Select|Wrap|Line Numbers   Me.txtAvgCost = DAvg("[Price]", "qryAvg1")     If you add the same line to the AfterUpdate property of the cboProdID it will enable you to change which product you are invoicing and not have to reselect a qty. Save the changes and open the form and enter some selections. Thank you for your prompt reply, thank so much for the coding, otherwise I am still scratching my head. but when I try some calculation some result ok, some not ok, here an sample BBB SUPPLER...1000.....0.5511 ..........................5000.....0.4411 .........................12000....0.3311 CCC SUPPLIER..1000.....0.5513 .........................5000.....0.4413 .......................11000....0.3313 DDD SUPPLIER..1000.....0.5521 ..........................5000.....0.4421 ........................11000....0.3312 If I order 11,000, manual calculation is 5000.............0.4411.........2205.5 11000...........0.3313.........3644.3 11000.......... 0.3312.........3643.2 .....................................9493.00 average cost = 0.3516 (9493/27000) But base on your coding I get 0.3679 Dec 28 '07 #3

 Expert 100+ P: 254 Thank you for your prompt reply, thank so much for the coding, otherwise I am still scratching my head. but when I try some calculation some result ok, some not ok, here an sample BBB SUPPLER...1000.....0.5511 ..........................5000.....0.4411 .........................12000....0.3311 CCC SUPPLIER..1000.....0.5513 .........................5000.....0.4413 .......................11000....0.3313 DDD SUPPLIER..1000.....0.5521 ..........................5000.....0.4421 ........................11000....0.3312 If I order 11,000, manual calculation is 5000.............0.4411.........2205.5 11000...........0.3313.........3644.3 11000.......... 0.3312.........3643.2 .....................................9493.00 average cost = 0.3516 (9493/27000) But base on your coding I get 0.3679 You should be multiplying the .4411 from supplier BBB by the qty you are ordering (11000) not at his price break point (5000). If you do that you will come up with .367867 in Excel Dec 28 '07 #4

 100+ P: 134 You should be multiplying the .4411 from supplier BBB by the qty you are ordering (11000) not at his price break point (5000). If you do that you will come up with .367867 in Excel Oh I am sorry, now I understand how average cost work, thank you for enlighten me again. Dec 28 '07 #5

 100+ P: 134 You should be multiplying the .4411 from supplier BBB by the qty you are ordering (11000) not at his price break point (5000). If you do that you will come up with .367867 in Excel Step -1 To summarized what I have understand, If there is no stock purchase from supplier, I would compute the supplier average cost using the Supplier table - tblcostPrice as posted above. Step-2 If I buy stock from supplier, this mean I have issue PO to Supplier, I would then compute the supplier average cost using table - tblPORecd in my earlier post, this mean sum up the qty, and total up the amt = amt/total qty. The qty refer to qty receive not order right ?, (this mean if I order 5000 qty, but supplier deliver partial qty 3000). If I am not wrong, in the 2 scenario we discussed, it assume there is no stock. What if there is stock balance for the part nos that appear in Scenario 1 and 2 ? would the calculation be different ? Dec 29 '07 #6

### Post your reply

Sign in to post your reply or Sign up for a free account.