Finding Items with Incorrect Average Costs


This Solution provides a technique to help find Items that have an incorrect Average Cost. (5822)






Big Business uses Average Cost to value inventory. Any data entry error involving Quantity or Cost could result in an erroneous Average Cost. So, finding errant Average Costs can be helpful in finding problems such as:

• Incorrect units used in transactions, such as counting by box instead of each
• Selling Built Items into a negative Quantity On Hand
• Input errors on Item Adjustments
• Returns with negative Price instead of negative Quantity

Basically, you find a group of Items and Save To Disk to work with the data in a spreadsheet. Add a calculation to find the percentage difference between Cost and Average Cost, then sort by this percentage.

Cost is the last price paid; it is entered on new Purchase Orders and updated when you enter a new cost and accept the prompt to "Update the Cost on the Item Card." Average Cost is the average value of the inventory you have on hand. If you bought one unit for $1 and second for $2, your Cost is $2, and the Average Cost is $1.50 ($3 total divided by 2 units).

Here are the steps:
1. Find a group of Items using the Item QuickSearch (e.g. "Inventory Item Is Yes", "Total Cost Is Not Equal To 0.00", and so on).
2. Click Save To Disk and save to a convenient location
3. Launch a spreadsheet and open the exported file
4. Insert a column for the calculation. Click on the S column, go to Insert menu> Column.
5. Enter a calculation in S2, such as, =IF(Q2=0,"",ABS((R2-Q2)/Q2))
This calucation tests if Cost is zero, to avoid an error dividing by zero, and calculates Average Cost minus Cost and divides by Cost. Using the example above, this would be $1.50-$2.00/$2.00 or -.25, which ABS makes a positive 25%.
6. Fill Down the calculation column. Click on S2, grab the lower right corner, and drag down to the bottom, use Fill Down, or Copy/Paste.
7. Sort by the calculation column. Click in S1, then choose Sort from the Data menu.



Related:

Return to Help