A common question our trainers and support experts get is “How do I remove all inventory counts and start fresh?” There are many reasons a company may want to do this.
- The Balance Sheet asset account for inventory is not reconciling to the inventory valuation.
- The counts are not accurate on a large scale.
- Inventory management has not been done in the system to maintain accuracy for the average cost, quantities on hand, etc.
When you need to start over with inventory quantities, it is recommended to reset all item quantities to zero, count your physical quantities on hand, and re-import the new quantities and values.
Resetting Counts to Zero
You may reset the counts to zero in bulk by doing an Inventory Qty Import. You may export the existing item quantities by using one of the two options:
- Inventory Valuation by Warehouse (Reports | Company | Items ) through the date you are wishing to reset. You may then export this report to MS Excel, removing the serialized items and changing the quantity be the reported quantity times -1.OR
- Running the Inventory Count By Warehouse CDV (Reports | Custom Data Views). You may then export this report to MS Excel, removing the serialized items and changing the quantity be the reported quantity times -1.
For either option, please make sure there is a cost for the item as this will affect the asset value and average cost. When formatting the import file, the columns needed for importing are listed below.
- Item: This is the name of the item.
- Warehouse: This is the warehouse the item count resides in.
- Cost: This is current average cost of the item. You will want this to be accurate as it will impact the value of inventory.
- Date: This is the date the count is conducted.
- Qty: This is the quantity counted. You will not have negative entries for the initial inventory import. For example, if the system states you have five (5) and you want the quantity to be zero (0), the Qty would be negative five (-5).
Import the exported file to reset the quantities to zero. Rerun the Inventory Valuation or Inventory Valuation by Warehouse to verify the counts have been reset to zero.
Perform a physical inventory count of the items in your warehouses, including trucks. Enter the counted values into the spreadsheet. Import in the new counts to set the starting quantities and value.
- Save the above file to your desired file share location.
- Open your live company file.
- Navigate to File | Company Utilities | Import.
- Select the workbook that was just saved to your file share directory. On the Import Wizard screen, select Inventory Qty and click Next >.
- Select the Inventory worksheet.
- If not checked, check the box that indicates your column headers are in the first row.
- Field headings should be mapped for those fields that are available to import.
- Click Next >.
- Check the box next to Clip Strings to Length.
- Click Next >.
- Check the box acknowledging importing of your data.
- Click Next >.
- Click the Import button.
- Once the import is complete, you may close the form.
- You may use the import option to perform routine inventory adjustments after you monthly, quarterly, or annual physical counts.
- In MS Excel, you can create formulas to calculate differences for imports.
- If you are unsure of how to import and would like to make sure you are gathering and formatting the information correctly, please email firstname.lastname@example.org and schedule some time to go over this process.