Calculation on Weighted Average Cost

Originally Posted by jamesnkk

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.

Originally Posted by jaxjagfan

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:

Originally Posted by jamesnkk

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

Originally Posted by jaxjagfan

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

Originally Posted by jaxjagfan

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