By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 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
Share on Google+
5 Replies


jaxjagfan
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
  1.  
  2. Me.txtAvgCost = DAvg("[Price]", "qryAvg1")
  3.  
  4.  
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
  1.  
  2. Me.txtAvgCost = DAvg("[Price]", "qryAvg1")
  3.  
  4.  
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

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