Enter opening balances for tracked inventory items
Import opening balances for your tracked inventory items, after you've added your tracked inventory items in Xero. This helps Xero keep track of the quantity and value of the items you hold. If you've just added a tracked inventory item and want to use it on a sales invoice or receive money transaction, you'll need to enter an opening balance so that your inventory count doesn't fall below 0. Otherwise, opening balances are optional.
You can import opening balances for up to 4000 tracked inventory items. If you have more than 4000 items, you can add opening balances for the extra items using inventory adjustments. You may also want to consider using one of Xero's connected apps for inventory.
App marketplace (xero.com website)
Points to note about inventory item opening balances
- You can only add opening balances to tracked inventory items.
- You can add opening balances at any time after your start date. We recommend you add the opening balances immediately after you've set up your tracked inventory items.
- Opening balance is optional for tracked inventory items. If no opening balance is entered, Xero will start with an opening balance of 0.
- This page has instructions for importing opening balances. If you only have a small number of items, add opening balances using an inventory adjustment.
Prepare to add opening balances
Before you add your opening balances, follow these steps to get ready.
Complete the getting started steps for tracked inventory items
Make sure you've completed these preliminary steps.
Do an item count
We recommend you perform a physical inventory item count for your chosen opening balance date. This way you know your opening balances are correct.
Enter or import your tracked inventory items
Your tracked inventory items must be entered in Xero before you can add opening balances. You can either import many inventory items at once, or add them individually.
Prepare to download the opening balance template file
Before you download the opening balance template file, Xero needs some information for your organisation:
Go to Accounts, then select Inventory.
Click Import, then select Opening Balances.
Enter the date of your opening balances.
Take care when choosing the date for your tracked inventory opening balances. Your choice may vary depending on whether you're an existing Xero customer, or new to Xero.
Under Which account holds the value of your inventory?, select the correct option for your organisation. This is the account or accounts on your balance sheet where your inventory balance is currently recorded.
Choose the account that holds the opening value of your inventory
Click Download the template containing your balances.
Open and complete the opening balance template
Open the downloaded template file. This will open in a spreadsheet program such as Microsoft Excel. The file will contain all tracked items from your inventory list.
Enter or copy your opening balance details in the last 3 columns in the CSV file, before you save the file to your computer. Make sure you follow the:
- Rules for entering opening balances into the template
- Columns in the opening balance template explained
- Tips for making opening balance entry into the template easier
Rules for entering opening balances into the template
Make sure you follow these rules so Xero will import your file:
- Leave the first row in place as the header row - If you delete the header row, Xero will not import your file.
- All columns must stay in the same order as Xero's template file - If you're copying data from another system, make sure you use Xero's exact format.
- Do not change any data in the first 3 columns - This data is taken from your tracked inventory item records. If you change any of this data, you will get an error message when you try to import the file.
- Enter 0 for Quantity on Hand and Total Value on Hand if your opening balance for an item is 0 - Make sure you enter an account code in the Adjustment Account column.
Columns in the opening balance template explained
| Column | Explanation |
|---|---|
| Item Code | The code you've created for your inventory item. Do not change any value in this column. Do not change any value in this column. If you need to change the item code for your item, you must edit your item. |
| Item Name | The name you've given your inventory item. Do not change any value in this column. Do not change any value in this column. If you need to change the item name for your item, you must edit your item. |
| Inventory Asset Account | The chart of accounts code for the account which will hold the value of your tracked inventory item going forward. You assigned this account to your tracked inventory item when you created your item, or converted it to tracked from untracked. The account in this column is debited in the opening balances journal Xero posts. Choose the account that holds the opening value of your inventory Do not change any value in this column. If you need to change the inventory asset account for your item, you must edit your item. |
| Quantity on Hand | Enter the quantity of the item you have on hand. You can enter positive numbers up to 2 decimal places. |
| Total Value on Hand | Enter the total value of the items you have on hand. You can enter positive numbers up to 2 decimal places. |
| Adjustment Account | Enter the chart of accounts code for the account currently holding the value of your inventory item. If you use anything other than the code, you'll get a warning when you try to import the file and Xero won't import the field. Your Adjustment Account will be the same as your Inventory Asset Account if you're not changing the account that holds the value of your inventory item. If you are changing the account, the Adjustment Account and the Inventory Asset Account will be different. The account in this column is credited in the opening balances journal Xero posts. Choose the account that holds the opening value of your inventory |
Tips for making entry of optional fields in the opening balance template easier
Here are some tips to help you enter optional data in the template correctly either:
- Before you save the file to your computer, or
- If you get errors after you've imported your file
If you are using Excel to create your CSV file
- If you are using Excel to create your CSV file take care not to enter figures and symbols which Excel could automatically reformat as dates or numbers as this data may cause all or some of your import to fail.
Review and complete the opening balance import
After you have selected the CSV or TXT inventory item file to import into Xero, you can review what's being imported before completing the import process.
As long as you have followed the rules and tips, Xero will import the opening balances from your CSV file.
If you get an error message when you try to import, check out our troubleshooting tips:
Completing the opening balance file import
Select Complete Import to import your file. Once imported, go to Accounts, then select Inventory to see your items.