Calculate Average Cost

[jamesnkk]

Hi,
Although this question may not directly link to Access, but I think it a common question to those developer.so hope you could suggest a solution.

How do I get the average cost for Item sold, so I could calculate the Profit.-

Same Product item could bought from different suppliers, different suppliers have different cost price or buying price.

There is only 1 standard selling price, In order to calculate the profit, I would take selling price - Cost price. Since cost price are not fixed as we purchase from different suppliers at different price, So I though the solution is to use Average cost. So what is the best implementation, best practices or formula.

[jaxjagfan]

Hi,
Although this question may not directly link to Access, but I think it a common question to those developer.so hope you could suggest a solution.

How do I get the average cost for Item sold, so I could calculate the Profit.-

Same Product item could bought from different suppliers, different suppliers have different cost price or buying price.

There is only 1 standard selling price, In order to calculate the profit, I would take selling price - Cost price. Since cost price are not fixed as we purchase from different suppliers at different price, So I though the solution is to use Average cost. So what is the best implementation, best practices or formula.

Sum(Cost x Qty) = TotalCost
Sum(SellPrice x Qty) = TotalSales
TotalCost - TotalSales = TotalProfit (Less Expenses/Taxes)

Average(Cost x Qty) = AvgCostPerUnit
Average(SellPrice x Qty) = AvgSalesPerUnit (SellPrice is a constant)
AvgCostPerUnit - AvgSalesPerUnit = AvgProfitPerUnit

TotalProfit takes into consideration units still onhand (if any). It's best not to use Average when trying to calculate total profit. If you buy 20 units from supplier A then divide the expenses/taxes across the 20 units to get a True Cost Per Unit: (Cost + (Expense/Qty)) = TrueCostPerUnit.
Sum(TrueCostPerUnit x Qty) = TrueTotalCost

I work closely with Actuarials/Financial Analysts/Managers and have done a lot of different financial reporting. It all depends on what the end user wants to see.

I would need to know a little more on your data structures to more definitive.

[jamesnkk]

I have a Selling Price table call it TblSellPrice that consist of

Part No - KN-12345
QTY ............. SELL PRICE
1 ................. $1.20
5000 ............ $1.00

During invoice entry if I enter qty= 3,000, it will retrieve selling price @$1.20
This got no problem.

I have another Cost Price table call it TblCostPrice that consist of

Part No - KN-12345
Vendor ....................Qty......Cost price

ABC SUPPLIER.......1000..... 0.90
............................... 5000..... 0.70

BBB SUPPLIER......1000..... 0.85
...............................5000.... .0.65


But to capture the Average cost price for qty=3000 there are 2
scenario if I have make any purchase order from supplier, this mean stock received will store into another table - TblPORecd

Part no........Qty Recd......Cost price... Amt.......Supplier
KN-12345.....1000............ 0.90...........$900........abc supplier
KN-12345.....5000............ 0.65..........$3250....... bbb supplier

Quote me if I am wrong, I would take 4150/6000 to get average cost=$0.69
Since I only sell 3,000, so I think I might be wrong over here.

On the 2nd scenario, if there no stock purchase, no transaction goes into
TblPORecd, I would not be able to calculate my average cost using the above
step. I would then refer to the above Table - TblCostPrice

ABC SUPPLIER 1000 0.90 = $900
BBB SUPPLIER 1000 0.85 = $850

Average cost = 1750/2000 = $0.875.

Please advise .

[jaxjagfan]

I have a Selling Price table call it TblSellPrice that consist of

Part No - KN-12345
QTY ............. SELL PRICE
1 ................. $1.20
5000 ............ $1.00

During invoice entry if I enter qty= 3,000, it will retrieve selling price @$1.20
This got no problem.

I have another Cost Price table call it TblCostPrice that consist of

Part No - KN-12345
Vendor ....................Qty......Cost price

ABC SUPPLIER.......1000..... 0.90
............................... 5000..... 0.70

BBB SUPPLIER......1000..... 0.85
...............................5000.... .0.65


But to capture the Average cost price for qty=3000 there are 2
scenario if I have make any purchase order from supplier, this mean stock received will store into another table - TblPORecd

Part no........Qty Recd......Cost price... Amt.......Supplier
KN-12345.....1000............ 0.90...........$900........abc supplier
KN-12345.....5000............ 0.65..........$3250....... bbb supplier

Quote me if I am wrong, I would take 4150/6000 to get average cost=$0.69
Since I only sell 3,000, so I think I might be wrong over here.

On the 2nd scenario, if there no stock purchase, no transaction goes into
TblPORecd, I would not be able to calculate my average cost using the above
step. I would then refer to the above Table - TblCostPrice

ABC SUPPLIER 1000 0.90 = $900
BBB SUPPLIER 1000 0.85 = $850

Average cost = 1750/2000 = $0.875.

Please advise .

Yes, you are correct on the average. $0.69 would be your average "order" cost. Since only 3,000 ordered by customer ($1.20 ea.) then AvgProfit would be 1.20 - .69 = $.51 per unit. (AvgOrderProfit: 3000 x .51 = $1530)

TotalProfit would be 3600 (CustomerOrder) - 4150 (StockOrdered) = -550.

On the 2nd scenerio: .875 would be your average "supplier" cost. This would be a different reporting value than average order cost.

[jamesnkk]

Yes, you are correct on the average. $0.69 would be your average "order" cost. Since only 3,000 ordered by customer ($1.20 ea.) then AvgProfit would be 1.20 - .69 = $.51 per unit. (AvgOrderProfit: 3000 x .51 = $1530)

TotalProfit would be 3600 (CustomerOrder) - 4150 (StockOrdered) = -550.

On the 2nd scenerio: .875 would be your average "supplier" cost. This would be a different reporting value than average order cost.

Thank for your prompt reply and solution, Refer to scenerio-2 , is it true that
I can also take the lowest price from the 2 suppliers as the average cost ?

Sorry another question should I capture the average cost & order average price
during purchase order from customer, the reason is that customer may give me a PO, but it will not deliver immediately..Or capture only when issue invoice ?

[jaxjagfan]

Thank for your prompt reply and solution, Refer to 2 scenerio , is it true that
I can also take the lowest price from the 2 suppliers as the average cost ?

Sorry another question should I capture the average cost & order average price
during purchase order from customer, the reason is that customer may give me a PO, but it will not deliver immediately..Or only when issue invoice ?

To question 1:
No, then you are giving a "false" impression of costs being low. It may cause mgmt to lower selling price and eliminate profit. Do an average across all suppliers or an average of the highest 3 or average of lowest 3 (be cautious with the last one). It is better to go high than low in that scererio.

To question 2: (Income - Expense = Profit)
a. TotalInvoice, AvgCostPerUnit and other supplier related values are driven by Invoice data. Invoice = Expense.

b. TotalOrder, AvgOrderPrice, AvgSellPrice, and other customer driven values are driven by Order data. Order = Income

[jamesnkk]

To question 1:
No, then you are giving a "false" impression of costs being low. It may cause mgmt to lower selling price and eliminate profit. Do an average across all suppliers or an average of the highest 3 or average of lowest 3 (be cautious with the last one). It is better to go high than low in that scererio.

To question 2: (Income - Expense = Profit)
a. TotalInvoice, AvgCostPerUnit and other supplier related values are driven by Invoice data. Invoice = Expense.

b. TotalOrder, AvgOrderPrice, AvgSellPrice, and other customer driven values are driven by Order data. Order = Income

Thank you for enlighten me, have a nice day.

[jamesnkk]

To question 1:
No, then you are giving a "false" impression of costs being low. It may cause mgmt to lower selling price and eliminate profit. Do an average across all suppliers or an average of the highest 3 or average of lowest 3 (be cautious with the last one). It is better to go high than low in that scererio.

To question 2: (Income - Expense = Profit)
a. TotalInvoice, AvgCostPerUnit and other supplier related values are driven by Invoice data. Invoice = Expense.

b. TotalOrder, AvgOrderPrice, AvgSellPrice, and other customer driven values are driven by Order data. Order = Income

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 ?