caret

Integrating with Xero Tracked Inventory

Set-up inventory accounts

Before you get started you will need to ensure the Xero organisation has the required accounts in its chart of accounts:

  • Inventory Asset Account – An asset account of type INVENTORY to track the value of stock on hand.
  • Inventory Adjustment Account – An account to track the value of inventory adjustments.
  • Cost of Goods Sold Account – An expense account to track the cost of goods sold.

You may want to have more than one inventory asset account if you want to distinguish between different stock lines on your balance sheet.

You can check this using the accounts endpoint and create new accounts if required.

Create the tracked inventory items in Xero

Once you know the Inventory Asset Account and Cost of Goods Sold Account you can create the inventory items in Xero (if they haven’t been created yet). See our documentation on the Items endpoint for details how to create and update tracked items.

Inventory Adjustments

Once the inventory items are created in Xero you may need to adjust the quantity and value of each item. You can do this using an inventory adjustment.

You may also need to do inventory adjustments periodically to increase/decrease quantity or revalue items (e.g. after a stocktake).

Increase Quantity on Hand

You can increase quantity on hand by creating a zero-total purchase (ACCPAY) invoice using the Invoices endpoint. For each inventory item you will need to create two line items; one to recognise the increase in the inventory asset (and increase the quantity on hand) and a second to recognise the the inventory adjustment. The inventory adjustment line will have a negative unit price so the two lines sum to zero.

Here’s an example. Imagine I’m adding 2 refrigerators at $1000 each. My inventory asset account is 630 and inventory adjustment account is 401.

The XML request to bring perform the adjustment would look something like:

<Invoice>
  <Type>ACCPAY</Type>
  <Contact>
    <Name>Inventory Adjustments</Name>
  </Contact>
  <Date>2015-10-01</Date>
  <DueDate>2015-10-01</DueDate>
  <LineAmountTypes>NoTax</LineAmountTypes>
  <Status>AUTHORISED</Status>
  <LineItems>
    <LineItem>
      <ItemCode>Fridge</ItemCode>
      <Description>Refrigerator</Description>
      <Quantity>2.0000</Quantity>
      <UnitAmount>1000.00</UnitAmount>
      <AccountCode>630</AccountCode>
    </LineItem>
    <LineItem>
      <Description>Inventory Adjustment</Description>
      <Quantity>2.0000</Quantity>
      <UnitAmount>-1000.00</UnitAmount>
      <AccountCode>401</AccountCode>
    </LineItem>
  </LineItems>
</Invoice>

Decrease Quantity on Hand

Decreasing quantity of hand is the reverse of an increase. The only difference is using a credit note of type to ACCPAYCREDIT (rather than an invoice). The following example would decrease the quantity on hand by 2.

<CreditNote>
  <Type>ACCPAYCREDIT</Type>
  <Contact>
    <Name>Inventory Adjustments</Name>
  </Contact>
  <Date>2015-10-01</Date>
  <DueDate>2015-10-01</DueDate>
  <LineAmountTypes>NoTax</LineAmountTypes>
  <Status>AUTHORISED</Status>
  <LineItems>
    <LineItem>
      <ItemCode>Fridge</ItemCode>
      <Description>Refrigerator</Description>
      <Quantity>2.0000</Quantity>
      <UnitAmount>1000.00</UnitAmount>
      <AccountCode>630</AccountCode>
    </LineItem>
    <LineItem>
      <Description>Inventory Adjustment</Description>
      <Quantity>2.0000</Quantity>
      <UnitAmount>-1000.00</UnitAmount>
      <AccountCode>401</AccountCode>
    </LineItem>
  </LineItems>
</CreditNote>

Inventory Revaluations

Revaluing inventory is a three step process that includes a decrease adjustment and an increase adjustment:

  • Perform a GET on the item to retrieve the quantity on hand and total cost pool. Divide the total cost pool by the quantity on hand to get the average cost for the item.
  • Perform a decrease adjustment to bring the quantity on hand down to zero. Use the quantity on hand as the quantity and the average cost of the item as the unit price.
  • Perform an increase adjustment to bring the quantity back to the previous level. Use the revalued average cost as the unit price for the item.

Tracked Inventory Opening Balances

The Xero API does not directly support opening balances for tracked inventory items in Xero. There is, however, a workaround to get the correct opening balances, quantity and value of stock on hand into Xero. Please note - this process is only relevant when setting up new Xero organisations (i.e. for conversion partners). Importing inventory opening balances via the API happens in two steps:

  • Conversion balances - Importing the overall value of the inventory asset with the rest of the organisation’s chart of accounts and conversion balances.
  • Individual items - Importing the individual quantities and values of each inventory item.

Import Conversion Balances

Part of converting an organisation to Xero is importing its chart of accounts and conversion (opening) balances. Check out our conversions guide for details on how to do it via the API. Organisations that track the value of their inventory will have one or many inventory asset accounts. In Xero these accounts will have a type of INVENTORY however, for the purpose of the import, we will need to create a temporary account with type CURRENT. This is because there are restrictions posting to accounts with type INVENTORY via the API. Here is an extract of what the requests to the Setup endpoint might look like for an organisation that has $100,000 worth of inventory:

<Setup>
  <Accounts>
   ...
   <Account>
    <Code>630</Code>
    <Name>Inventory Asset Account</Name>
    <Type>INVENTORY</Type>
   </Account>
   <Account>
      <Code>631</Code>
      <Name>Inventory Asset Account Opening Balance</Name>
      <Type>CURRENT</Type>
   </Account>
   ...
  </Accounts>
</Setup>

<Setup>
  <ConversionDate>
      <Month>10</Month>
      <Year>2015</Year>
  </ConversionDate>
  <ConversionBalances>
     ….
      <ConversionBalance>
        <AccountCode>630</AccountCode>
        <Balance>00.00</Balance>
      </ConversionBalance>
      <ConversionBalance>
        <AccountCode>631</AccountCode>
        <Balance>100000.00</Balance>
      </ConversionBalance>
      ...
  </ConversionBalances>
</Setup>

Import Individual Items

After the overall value of the inventory is imported as a conversion balance, you can create a zero-total purchase (ACCPAY) invoice to import the quantity and value of individual items. The invoice will also transfer the overall inventory value from the temporary asset account to the actual inventory account. For each inventory item you will need to create two line items; one to increase the inventory asset (and increase the quantity on hand) and a second to reduce the value from the temporary account. The second line will have a negative unit price so the two lines sum to zero. To continue the above example imagine I only stock one inventory item - refrigerators - and I have 100 @ $1000 each. As above, my inventory account is 630 and my temporary asset account is 631. The XML request to bring in my opening balances would look something like:

<Invoice>
  <Type>ACCPAY</Type>
  <Contact>
    <Name>Inventory Opening Balances</Name>
  </Contact>
  <Date>2015-10-01</Date>
  <DueDate>2015-10-01</DueDate>
  <LineAmountTypes>NoTax</LineAmountTypes>
  <Status>AUTHORISED</Status>
  <LineItems>
    <LineItem>
      <ItemCode>Fridge</ItemCode>
      <Description>Refrigerator</Description>
      <Quantity>100.0000</Quantity>
      <UnitAmount>1000.00</UnitAmount>
      <AccountCode>630</AccountCode>
    </LineItem>
    <LineItem>
      <Description>Inventory Opening Balance</Description>
      <Quantity>100.0000</Quantity>
      <UnitAmount>-1000.00</UnitAmount>
      <AccountCode>631</AccountCode>
    </LineItem>
  </LineItems>
</Invoice>

The result is an purchase invoice that looks like this:

Opening Balance Invoice

For simplicity I’ve only done one inventory item but you can do multiple on the same invoice. And the inventory item has now been updated with quantity on hand, average cost and total value. The opening balance transaction shows in the recent transactions.

Refridgerator

The last thing to do is some tidy-up. The invoice has created a new contact called “Inventory Opening Balances”. We should archive this contact so it’s not used in future. You can read how to archive contacts in our Contacts documentation.

<Contact>
    <ContactStatus>ARCHIVED</ContactStatus>
    <Name>Inventory Opening Balances</Name>
</Contact>

We should also archive the “Inventory Asset Account Opening Balance” account because its balance will now be zero and is no longer required.

 <Account>
      <AccountID>e8da5599-cc0a-4347-93a9-53b24b0c28de</AccountID>
      <Status>ARCHIVED</Status>
</Account>