435,584 Members | 3,458 Online
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

# Calculate Average Cost

 100+ P: 134 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. Dec 24 '07 #1
7 Replies

 Expert 100+ P: 254 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. Dec 26 '07 #2

 100+ P: 134 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 . Dec 27 '07 #3

 Expert 100+ P: 254 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. Dec 27 '07 #4

 100+ P: 134 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 ? Dec 27 '07 #5

 Expert 100+ P: 254 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 Dec 27 '07 #6

 100+ P: 134 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. Dec 27 '07 #7

 100+ P: 134 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 ? Dec 29 '07 #8