Method 1 – Stock Take by Batch Extract and upload into Adjustment.
This method of carrying out a stock take involves extracting data from the Batch List and conducting a count which is then entered into a spreadsheet before being imported back into Aerotrack to create an Adjustment.
Using this method it is possible to count larger stores because the tasks can be broken down and carried out by multiple people at once.
The process involves exporting the inventory from the Batch List, this can be done by Bin Location or by any search criteria that you require. The information that is extracted is then formatted into a user friendly spreadsheet for a count to be carried out. The formatting can remove many of the columns that are included as part of the standard extract from Aerotrack as there are only seven columns that are going to be imported back into the system.
The process requires some manipulation of MS Excel spread sheets and needs to be carried out carefully and methodically as errors can be made which would cause stock variances to occur. It is important to note that the MS Excel spreadsheet must be in the “.xls” format for this upload process to be successful.
We recommend that a person within your organisation with good spread sheeting skills manages the stock take process and has control over the individual files.
- From within the inventory system open the “Batch List” table.
- Using the “Find Batches” function search for the records that you wish to include in your stock take. TIP If your company uses multiple bases you can search for everything for that base. Alternatively you could search by Bin Location or use the first few characters of the bin to find all bins in an area. Example: I have bin locations numbered A-01 through to A-15 and B-01 through to B-12. The “A” represents the aisle and the number represents the shelf. Search for “A-” in the Bin Location field and you will return all bins beginning with “A-“.
- Once you have the records you wish to count in your found-set click the export icon to create an excel spreadsheet containing the results. The export icon will open the save window for the user to save the file to a local drive.TIP When exporting large amounts of records we recommend that these are broken down into smaller spread sheets for ease of counting and for ease of importing into adjustments. This allows multiple counts to be carried out simultaneously and will reduce the time taken to count your entire store.
- The spreadsheet that will be exported will have 17 fields included, they are listed in the first column of the table below. Open this spreadsheet in MS Excel and make the changes listed in the “ACTION” column. Seven of the existing fields can be used and one new field for the “count” will be created, the rest can be removed from the spreadsheet altogether.
- The spreadsheet you end up with should have eight columns. Make sure the column names are correct and re-order the columns so they are in the order shown below.TIP You can leave the original “quantity_onhand” column in the spreadsheet as this may assist during the counting process. It must be removed before the spreadsheet can be imported back into Aerotrack.
- The count can then be carried out in any order you require. Sort and divide the spreadsheet so multiple areas can be counted simultaneously. To save time and to avoid data transfer errors you could enter the counts directly into the spreadsheet via a laptop.
- There will be batches where the count will be zero, these can only be identified after the complete count has concluded. Batches with zero quantity must have a zero entered in the count field prior to being imported, this will ensure that the quantity is adjusted down accordingly.
- This spreadsheet will show all of the parts that Aerotrack has recorded as being in stock. If the system has nil quantity of a part number it will not be listed. It is important that you implement a process whereby you can record part numbers, GRN numbers and quantities that are not listed. These can be manually entered into the spreadsheet or into a separate adjustment.
- When the count is completed and the spreadsheets have been compiled the results can be imported to Aerotrack to adjust the stock holdings.
- Create a new adjustment from within the Inventory section by clicking on the green cross icon.
- Go to the “Stock Take Details” tab and click the import spreadsheet icon –
- This will open the import dialog, click OK to proceed and navigate to the location of your spreadsheet to be imported. Select the file and begin the import.
- When the import is complete the counts will be displayed in the table view. Each line will have have an expected value, which is the quantity that Aerotrack shows as being is stock currently, a count value and a difference status.
- Now that the count has been imported and compared to the existing stock on hand you can begin to see how the adjustment is going to affect your stock holding. Before the adjustment lines are created the user can go through this screen and recount items, we recommend targeting the high cost items that have a status of “Down”.
- When the count is confirmed the adjustment lines can be created, click on the “Create Adjustment Lines” button to send the line items with a status of “Up” or “Down” to the Line Detail tab panel.
- If there are no “Exceptions” this process will create the adjustment and show the total up an down dollar values at the bottom of the screen.
- EXCEPTIONS – An exception will be raised if there is a problem with one or more of the line items.Before attempting to create the line items again the user must resolve these exceptions. Click on the Print Report button to display the “Stock Take Exceptions Report”, this will assist to track down and rectify the issues.
- Once all the exceptions have been rectified click the “Create Adjustment Lines” button to create the adjustment and proceed with validation. If the validation feature is not in use the adjustment will have taken effect and should be locked to ensure it cannot be altered.