Efficiently update stock levels and values in bulk through a spreadsheet importing. This process is ideal for reflecting changes after stocktakes, ensuring the system accurately represents physical inventory. Update stock quantities, values, or both, affecting the calculated average cost.
You can also follow this guide for adding initial stock or check out this guide for a more in-depth tutorial.
The most typical use case for using this bulk update is recording the results of stocktakes (i.e. stock counts) in Katana.
If you use batch tracking for your items (a Standard plan or higher feature), you can use the following approach for bulk updating stock levels and values for batches. Find more information about updating stock for batches in the batch tracking section below.
You can choose to either update stock levels by keeping the current Average cost for items or update both stock quantities and values (which may result in a different Average cost) with the template. Choose which method you'd prefer before confirming the import:
By default, Katana will only update stock quantities from the file and keep the current Average cost for items in Katana. In this case, you do not need to change the "Value in stock" column in the template. This is a typical use case for stock takes.
You can update quantities In Stock and Value in stock, may might result in a different Average cost for items. Average cost is a calculated field in Katana as Average cost = Value in Stock / Quantity In stock. When you upload the file into Katana, you can choose to import the Value in stock column from the file.
Update Value in stock only and keep the current quantity In stock. This will result in a changed Average cost for items.
To bulk update stock levels (without batch tracking)
Navigate to Settings screen > Data import in Katana.
Find the Update stock levels and stock values section and select Download data.
A spreadsheet (XLSX format) that includes all your products and materials with current Katana "In stock" levels and "Value in stock" will be downloaded. This template includes a Katana ID column, which is used to match imported items with those existing in Katana.
If you created multiple Locations in Katana, the template will include a separate row for each Location of each item. For example, if you have 3 Locations in Katana, there will be 3 rows for the same item in the file, which enables you to import stock levels for each Location.
Make the preferred changes to the Stock quantity and Value in Stock columns.
- "Stock quantity" refers to the "In stock" quantity of that item in Katana.
- "Value in stock" is the total value of the entire Stock quantity. The Average cost for the item is calculated in Katana as "Value in stock" divided by "In stock".
- Stock quantities and values are shown per Location. If you created more than one Location in Katana, each row in the file is per Location of each product.
- If you use batch tracking (a Standard or higher plan feature), the file will include your batch information. See more details below.
- Only Stock Quantities and Values are imported to Katana. Changes to Item names, Variant codes, Item variant columns, or Unit of Measure will not import. These columns are included in the spreadsheet purely for better usability purposes.
Import the XLSX file back into Katana by selecting Upload data next to the "Update stock levels and stock values" 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).
Before the import begins, you'll be asked whether if you want to use the "Value in stock" from the file or keep the Average cost for items in Katana. By default, Katana keeps Average cost, so if the quantity of an item changes, the total Value in stock for that item also changes, but the Average cost stays the same.
If the Value in stock column is empty in the file, the above question won't be asked and the In stock quantity will be updated by keeping the existing Average cost.
If the Average cost and In stock quantity for items are zero in Katana (for example, when entering the initial stock), Katana uses the Default purchase price for items as the Average cost (if you have the price defined in Katana).
If you choose to recalculate the Average cost using the new Value in stock, Katana will import the "Value in stock" (and your item's Average cost may change).
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).
Rules applied to imports
Imported items are matched to items in Katana by the Katana ID in the spreadsheet. This ID is not visible inside Katana and is purely used for matching items.
Imported products with no "Katana ID" and "Location" match with Katana will be skipped during import.
Stock Quantities and Value in Stock for items are overwritten on a per Location basis.
Empty cells are skipped during import. Existing data in Katana will not be overwritten with blank values.
If you only change the "Value in stock" for an item, but not the Quantity, on the Stock Adjustment, 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.
To update stock levels in bulk (with batch tracking)
If you are using batch tracking (a Standard or higher plan feature) in Katana, you can use the same approach as described above (all the same rules apply), but there are a few additional things to note.
If you enabled batch tracking for only some items in Katana, the exported file will include stock levels for all items.
For batch-trackable items, each row in the file is a specific batch at a specific Location with the batch number and expiration dates indicated in the respective columns.
For items that are not batch-trackable, each item in the file represents the total stock quantity of the item at a specific Location (no batch info).
The exported file will also include batches with 0 quantity and "Unbatched" batches (but not "Unbatched" batches with 0 quantity).
You can remove the entire quantity from any batch (including the "Unbatched" batch) by changing the Stock Quantity and Value in Stock to 0 in the file.
You can assign Value in Stock per batch in the file. However, Average cost in Katana is not batch-specific. After importing the batches with varying Average Costs for the same item variant, Katana will calculate the Average cost for the total quantity of the item variant. This cost is assigned to the entire stock for that variant.
You can only update stock quantities and values for existing batches with this template. If the item is batch-trackable, but the "Batch number" field in the file is empty of unknown (not matching any existing batches in Katana), then this row is skipped during import. You cannot import new batches with this file. Use the following tutorial for importing new batches.
Your feedback is invaluable. Let us know your thoughts on this article or anything in Katana you'd like to see improved: [email protected]