Updating stock levels and reorder points in bulk
You can update stock quantities, values and Reorder Points for products and materials in bulk via XLS import.
The most typical use case is recording the results of stock takes (i.e. stock counts) in Katana.
You can also use the XLS import for updating the Value in Stock for products and materials in Katana. The calculated Average Cost for items will change accordingly.
Tip: If you wish to update only stock quantities and keep the same Average Cost for items in Katana, delete the "Value in Stock" column from the XLS file before importing. This will keep the existing Average Cost for items in Katana and change the "Value in stock" accordingly to reflect the new quantity.
To update stock levels in bulk:
1. Navigate to "Settings" -> "Import data" in Katana.
2. Find the "Update stock levels and reorder points" section and select "Download template".
3. A spreadsheet (XLSX format) including all your products and materials with current Katana "In stock" levels, "Value in Stock" and "Reorder Points" will be downloaded. This template includes a "Katana ID" column, which we will use for matching the imported items with items existing in Katana.
4. Make the preferred changes to the "Stock Quantity", "Value in Stock" and Reorder Point" columns.
- "Stock Quantity" equals the "In stock" quantity for the item in Katana.
- "Value in Stock" equals the respective column in Katana. The Average Cost for the item is calculated in Katana based in the "Value in Stock" and "In stock" quantity.
- Reorder Points, Stock Quantities and Values in the spreadsheet are shown per Location. If you have created more than one Location in Katana, each row in the file is per Location of each product.
- Only Reorder Points, Stock Quantities and Values are imported to Katana. Any changes to Item names, Variant codes, and Item variant columns will not be imported, those columns are included in the spreadsheet purely for better usability purposes.
5. Import the XLSX file back into Katana by selecting "Upload data" next to the "Update stock levels and reorder points" in the "Import data" section. You can change the file name before import if necessary (it does not have to match the name of the downloaded template file).
6. Reorder Points, "In stock" quantities and "Value in Stock" for items will be updated in Katana. Stock Adjustment(s) will be created (one Stock Adjustment per Location) to record the differences between the imported Stock Quantities and Values and your existing stock in Katana.
Tip: Creating .xlsx on Mac. Open the spreadsheet in Numbers and choose File -> Export to -> Excel... from the menu at the top of your screen.
Tip: Creating .xlsx in Google Sheets. Open the spreadsheet in Google Sheets and choose File -> Download as -> Microsoft Excel (.xlsx).
Which rules are applied to the import?
1. Imported items are matched to existing items in Katana by "Katana ID" included in the spreadsheet. This ID is not visible to the user inside Katana, it's a value purely used for matching the items.
2. Imported products with no "Katana ID" and "Location" match with Katana will be skipped during import.
3. Reorder Points, Stock Quantities and Value in Stock for items are overwritten per Location basis.
4. Empty cells are ignored during import. Existing data in Katana will not be overwritten with blank values.
5. If you only change the "Value in Stock" for an item, but do not change the Quantity, on the Stock Adjustment that is created in Katana for recording the differences, Katana will create 2 adjustment rows for the item: one with quantity adjustment -1 and the other with quantity adjustment +1. This is necessary for recording the value differences in Katana.
6. If you wish to update only stock quantities and keep the same Average Cost for items in Katana, delete the "Value in Stock" column from the XLS file before importing. This will keep the existing Average Cost for items in Katana and change the "Value in stock" accordingly to reflect the new quantity.