Xero Inventory Costing

Xero

Share

Ahmed Elsaka

Xero Inventory Costing

Xero uses average costing, which is similar to what is used on desktop products and is easy to manage, but doesn’t always fit what your tax accountant will want. In addition, Xero places several controls over quantities on hand as well as how you can work with inventory asset accounts, so that you can’t run into out-of-balance accounts or have to deal with odd numbers for cost of goods sold.

Xero Inventory Costing – Untracked Inventory

Untracked items are items where you are not tracking the quantity on hand. If we are talking about inventory (as opposed to service items, etc.), these would be items that have a low value, usually a high quantity, and that are not hard to acquire. Perhaps items that you purchase in bulk, such as small screws or washers. You don’t need to keep an exact count of them, you just periodically see how many you have on hand and order them as you need them. In other environments these would be called “non-inventory items.”

Even though you aren’t keeping track of the quantity you have on hand, it is important to understand how costs flow through Xero with these kinds of items.

Xero Inventory Costing – Untracked Inventory

To create an untracked item, you will leave the I track this item box unchecked. You will specify an account to work with for purchases and for sales.

Value flows through your accounts in a very simple manner:

  • When you purchase the item, Xero will debit the Purchases Account and credit Accounts Payable with the cost of the item from that transaction. The default cost used comes from the item record, but you can enter any value. Typically your Purchases Account will be an expense or cost of goods sold account, so the cost of a purchase will go directly to your profit & loss statement.
  • When you sell the item, Xero will debit Accounts Receivable and credit theSales Account with the sales price of the item.

Untracked item display

Xero Inventory Costing – Tracked Inventory

If you need to keep track of the quantity on hand of an item, you will create atracked inventory item. This changes how the costs flow through QuickBooks.

Xero Inventory Costing – Tracked Inventory

To create a tracked item, you will check the I track this item box. Now you havethree accounts that you can set up for the item: Inventory Asset, Cost of Goods Sold, and Sales.

The Inventory Asset Account is restricted to being an Inventory type of account, which is a sub-type of Current Assets. The Cost of Goods Sold Account typically is set to be a Direct Cost type of account, although Xero gives you the flexibility to choose others.

Values flow through your accounting system in a slightly more complicated way:

  • When you purchase the item, Xero will debit the Inventory Asset Account. The default cost will come from the unit purchase price in the item record. This places the item’s value into your balance sheet. Xero will calculate theaverage cost of the item based on the total value already on hand (if any) and the newly purchased cost of the items.
  • When you sell the item, multiple accounts are posted to:
    • The Cost of Goods Sold Account will be debited by the quantity of the sale multiplied by the average cost of the item. This value is credited to the Inventory Asset Account. This moves the cost, based on average cost, out of the balance sheet and into the profit and loss statement.
    • Accounts Receivable will be debited by the sales amount of the item, and the Sales Account will be credited.

Create a Tracked Item

Xero Inventory Costing – Untracked Inventory

Untracked items are items where you are not tracking the quantity on hand. If we are talking about inventory (as opposed to service items, etc.), these would be items that have a low value, usually a high quantity, and that are not hard to acquire. Perhaps items that you purchase in bulk, such as small screws or washers. You don’t need to keep an exact count of them, you just periodically see how many you have on hand and order them as you need them. In other environments these would be called “non-inventory items.”

Even though you aren’t keeping track of the quantity you have on hand, it is important to understand how costs flow through Xero with these kinds of items.

Xero Inventory Costing – Untracked Inventory

To create an untracked item, you will leave the I track this item box unchecked. You will specify an account to work with for purchases and for sales.

Value flows through your accounts in a very simple manner:

  • When you purchase the item, Xero will debit the Purchases Account and credit Accounts Payable with the cost of the item from that transaction. The default cost used comes from the item record, but you can enter any value. Typically your Purchases Account will be an expense or cost of goods sold account, so the cost of a purchase will go directly to your profit & loss statement.
  • When you sell the item, Xero will debit Accounts Receivable and credit theSales Account with the sales price of the item.

Untracked item display

Xero Inventory Costing – Tracked Inventory

If you need to keep track of the quantity on hand of an item, you will create atracked inventory item. This changes how the costs flow through QuickBooks.

Xero Inventory Costing – Tracked Inventory

To create a tracked item, you will check the I track this item box. Now you havethree accounts that you can set up for the item: Inventory Asset, Cost of Goods Sold, and Sales.

The Inventory Asset Account is restricted to being an Inventory type of account, which is a sub-type of Current Assets. The Cost of Goods Sold Account typically is set to be a Direct Cost type of account, although Xero gives you the flexibility to choose others.

Values flow through your accounting system in a slightly more complicated way:

  • When you purchase the item, Xero will debit the Inventory Asset Account. The default cost will come from the unit purchase price in the item record. This places the item’s value into your balance sheet. Xero will calculate theaverage cost of the item based on the total value already on hand (if any) and the newly purchased cost of the items.
  • When you sell the item, multiple accounts are posted to:
    • The Cost of Goods Sold Account will be debited by the quantity of the sale multiplied by the average cost of the item. This value is credited to the Inventory Asset Account. This moves the cost, based on average cost, out of the balance sheet and into the profit and loss statement.
    • Accounts Receivable will be debited by the sales amount of the item, and the Sales Account will be credited.

Create a Tracked Item

Xero uses the average cost method to value tracked inventory items. The calculation for this is:

Item value = (opening balance value + purchases value – cost of goods sold value) / quantity on hand

It is important to note that the date doesn’t come into play here. If you have a value of $637.50 on hand as of today, if you go back and date a new invoice at an earlier date, Xero will not change the value based on that date. That is different than some other accounting systems that are very date-sensitive. This is an important distinction.

Looking at the information for the item shown above, I purchased five of the item on March 1st. The total value at that time was $550.00, for an average cost of $110.00. I then purchased three more on March 3rd at a cost of $100.00 per unit. That adds $300.00 to the total value, for an average cost of $106.25. As I sell items (assuming no more purchases, and no inventory revaluation transactions), the Cost of Goods Sold Account will be debited by $106.25 per item sold. Even if I date the invoice to be March 2nd, before that second purchase, that currently calculated average cost of $106.25 will be used.

The purchases value includes the cost of items in:

  • Bills with the status of Awaiting Payment and Paid
  • Any Spend Money transactions

The cost of goods sold value includes the cost of items in:

  • Invoices with the status Awaiting Payment and Paid
  • Any Receive Money transactions

Quotes and Purchase Orders do not affect the quantity on hand.

It is also important to note that Xero prevents you from journaling to the inventory asset accounts, so you should not see these accounts go out of balance.

What About Negative Inventory Quantities?

Tracking costing of inventory items when you have a negative quantity (that is, you sold more than you received) can be a major hassle for a software system. What is the cost of goods sold of something that you sold that you didn’t have? What if you receive those items after you sold them? That is an issue that creates a lot of confusing entries in both QuickBooks Online and QuickBooks desktop products, both of which allow negative quantities on hand.

Well, in Xero this isn’t a problem, because Xero does not allow negative quantities on hand. If you don’t have it, you can’t sell it. This sounds great! Xero protects your Profit and Loss report from any sort of distortion that can occur when a system tries to work out the conundrum of a negative quantity of anything. It helps accountants because there is no need to try to figure out where odd numbers have come from. However, not allowing negative quantities can cause problems for some businesses, such as those that drop ship items.

Xero Inventory Tracking Notes

Here are a few additional tidbits about Tracked Items in Xero:

  • You cannot delete an item that has been used in an approved invoice or approved bill.
  • You cannot delete an item that has been used in a repeating invoice or repeating bill.
  • You cannot convert an item from untracked to tracked if the item is used in a repeating invoice or repeating bill. Note that you can remove the item from those kinds of transactions and then make the conversion.
  • You cannot convert an item from tracked to untracked if the item is used in any posting transaction.

Balancing Flexibility With Accountability

Accounting systems are a tool, and they shouldn’t get in the way of how you do business. On the other hand, you need to have accurate records and you don’t want to spend huge amounts of time trying to determine where certain figures in your accounting system came from.

I think that Xero inventory does a good job balancing these issues. They have protections on certain accounts that keep you from making mistakes that throw things out of balance. They don’t allow negative quantities, which prevents you from having to deal with the odd kinds of values you see in a Cost of Goods Sold account if you do happen to run into negative balances. This is all good, it protects data accuracy and makes it simpler to follow costs through the system.

On the other hand, many businesses just can’t deal with a restriction that prevents you from having a negative quantity on hand. This can get in the way of your workflow, particularly if you either drop-ship or you just don’t have enough manpower to keep your receiving records up to date ahead of sales. Yes, there are solutions to this. You can add people, you can change your workflow, you can use untracked items instead of tracked items, you can use draft invoices. Those solutions, however, aren’t always practical for a small business with limited resources.

That is why it is important to understand how a program like Xero works, so you know whether it can fit in your business



#Copied