The latest version of this document is here: DEAR for CFOs: Notes on Xero Accounting integration with Dear Inventory

 

DEAR for CFOs: Notes on Xero Accounting integration with Dear Inventory

This is a GrowthPath knowledge-base article. You may share this link as you wish.

This document is for CFO, controllers, bookkeepers and accountants who want to know how Xero works with Dear. 

Essential things to review

Do you want to use the powerful feature Tracking Categories for profit-centre accounting in Xero? See Tracking Categories

Do you need to add some accounts? See Required Accounts

Are you using account codes in Xero? They are needed for Dear. Any account in Xero without an account code will not be visible in Dear. 

High-level overview

• Dear acts like a traditional ERP. A traditional ERP is financially split into subledgers, such as Sales, Inventory, which post to the GL. The difference is that instead of posting to the GL, Dear ‘posts’ to Xero via the integration.. 
• Dear is the owner of the stock subledger.
• Xero is the source of truth for all other ledgers, even those where Dear provides most of the source transactions (such as AR).
• The steps you would go through in a traditional ERP are still valid in Dear. That is, good financial controller practice of reconciling ledgers each month is just as valid with Dear.
• The integration is partially two-way: under some circumstances, you can enter customer and supplier payments in Xero and have them import to Dear. 
• The integration works well, but has some important practical weaknesses, and you must keep on top of sync errors. The weaknesses relate mostly to documents changing in Dear after they have synced to Xero. 
• Dear is not a standard costing system. It is a FIFO system based on actual stock costs, which is like average costing in the long run, but actual cost differs from average costing for any given transaction. Costs on purchased stock come from FOB supplier pricing and can be supplemented by expenses absorbed into stock (“landed costing”). 
• However, average costs are used to estimate margins at some places, for example at order entry. The true margin of a sale can only be known when particular items are picked for the order, as this locks in the actual cost.
• The default costing, FIFO, is an algorithmic or “logical” actual cost: it assumes the oldest stock is the next to be shipped. To track real-world stock movements, use batch or serial number costing methods.
• We do not import transactional history into Dear from the prior system.
• It deals well with multiple currencies.

This means Dear has ownership of Inventory, Inventory Adjustments, COGS, the Purchase Accrual accounts and WIP process accounts associated with assembly & manufacturing.

For those accounts, the only transactions in Xero are those which originate from Dear, in proper usage. It is a mistake to manually journal to them.

Unfortunately, though, we do not have formal control accounts in the Dear/Xero combination. That is, we cannot stop someone journaling to the inventory account. 

If there is a need to make adjustments to any of those accounts, create a new Xero account (a contra or variances account)  and book manual adjustments to that. 

This discipline makes the reconciliation process much easier.

• Xero’s Tracked Inventory feature is not used when we have Dear.

Dear has a full trial balance, but to the user of Xero, it is mostly irrelevant. That is, Dear has a GL backend. But it is not very good and it should largely be ignored.

Perpetual Inventory

It goes almost without saying that Dear is a perpetual inventory system, like any modern ERP. Stock is capitalised on acquisition, and expensed when shipped. The value of stock can be increased by absorbing acquisition costs such as inbound freight, or costs when moving stock between locations; these are known as “landed costs”. If stock value is changed after it has been shipped, Dear will still generate additional COGS journals.

Transaction dates/effective dates

Dear has some limitations about stock movements with retrospective effect. Each transaction which affects stock value has a transaction date, which is aligned with Xero. So you can always control the financial effective date of movements. However, you can not always insert physical stock movements into a prior date. I will not explain this further here; it is hard to explain, but you will know it when you see it. 

COGS vs Revenue timing, Revenue Recognition Issues

As is typical with perpetual inventory systems, revenue is recognised with the invoice is authorised, and the associated COGS is generated when the shipment is authorised. This leaves us open to timing differences: a sale on the last day of the month without a shipment will be a 100% gross margin sale. 

Unfortunately it is hard to use Dear’s reporting to reliably report on this. However, GrowthPath’s Dear Analytics module provides this reporting, as well as many other advanced reporting options.

 

Documents/Transactions sent to Xero by Dear

There are many events in Dear which do not cause financial events in Xero.

For example, authorising a sale or PO does not cause any financial event in Xero. 

 

Dear sends the following documents to Xero.; that is, the list below are events which do have financial consequences.

This is the “maximal” list, if all features are activated, which is typical for a GrowthPath configuration.

Dear is the sole source of truth for stock value. 

Xero is the sole source of truth for all other subledgers.

Mapping of Dear to Xero

 

Dear Document	Xero Document	Note
Purchase Invoice	AP Bill	AP Invoices, when authorised in Dear. Trigger is invoice authorisation. Note: Purchase orders can be sent to Xero too but this has no financial effect, it is just for information.
Sales Invoice	Sale Invoice	AR Invoices, when authorised in Dear. Revenue/Receivables booking is triggered by invoice authorisation.
Credit Notes	Credit Notes
Shipment	MJ (Manual Journal)	Journals to change stock, when a shipment is authorised
PO Receipt	MJ	Journals to change stock, when a PO receipt is authorised (some special rules for partial receipts in the Simple Purchases module)
Stock adj	MJ	Stock adjustments and stock takes can affect stock too, via journals
Manufacturing steps	MJ	Manufacturing processes will consume stock for components, and add finished goods. Manufacturing processes also allow for overheads, which are absorbed into stock, the necessary journals are sent to Xero
Payments and Refunds on Invoices	Payment	Customer and supplier payments, deposits and refunds create entries in Xero. Payments under most circumstances can originate in Xero and be imported to Xero.
Paying with Dear Customer Credit	Payment	This is treated like a standard payment
Conversion of CN to Customer Credit	Payment of Xero CN	This step pays the Xero CN!
NOTHING!	Paying document with CN in Xero	Paying bills and invoices with CN in Xero does not sycn to Dear
PO Accruals	MJ	Dear generates accrual transactions for stock receipts before the supplier invoice is booked, and for when the invoice is authorised before the receipt has happened
Manufacturing accruals	MJ	Dear will move inventory into WIP or GIT in some manufacturing steps, and in some stock transfers (depending on user choice)
Landed costs/capitalised expenses	MJ	Landed costs: Dear allows non-stock invoices to be capitalised into stock. Both the supplier invoice with FOB or ex-works pricing is booked (the traditional source of product cost) and then service invoices (such as freight, duties and clearing costs) are booked in Dear, creating DR entries to expense accounts. Dear has an interface to apply service invoices to stock receipts. Dear updates its product costs, and creates a journal in Xero to CR the expense entry and DR stock.
Landed costs/capitalised expenses	MJ	Capitalisation of expenses (‘Landed costs’) can also apply to production orders and stock transfers

Dear can also send Purchase Orders to Xero when they are authorised in Dear. This has no financial effect, but gives extra visibility in Xero of upcoming financial commitments.

When does sync happen?

The Golden Rule

Once there is a payment applied on a Xero document, Xero will accept no modifications. This generates sync errors on the Dear side.

Bulk updates of Xero payments are possible via the Xero API, but there is no GUI way of doing it easily.

By default, the synchronisation is triggered manually. It typically takes one to two minutes.

It can be scheduled to run automatically. 

Documents are triggered for sync when

• the document is authorised
• an authorised document changes

BUT NOTE! Not all changes will successfully sync. Xero has rules

• Lock date (remove or change lock date)
• Payment on document locks the document (remove and undo payment)

Undoing documents in Dear does nothing in Xero. 

Voiding a document in Dear voids it in Xero as long as no payment exists on the Xero document.

 

Two-way Payment Sync

Dear has two-way payment sync.

Payments created in Dear are sent to Xero. Payments entered in Xero are pulled into Dear. 

For each payment, the interface remembers which side authored the payment, and it only considers changes made from the authoring side.

There are circumstances which stop successful synchronisation. GrowthPath has a knowledge base article on fixing sync problems, including those related to sync errors.

Lock-dates, Audit Trail and Business Control

Xero has two lock-dates: ordinary user and advisor users.

Dear has lock date features too. It accepts and deploys the ordinary user lock date in Xero, so both systems have the same lock-date. You can override the Xero lock date in Dear’s settings, but it will always refresh with the Xero lock date when you next do a Xero sync, so the effect of changing the lock date in Dear is only temporary.

Dear has an excellent audit trail, and it clearly shows the accounting entries created by each transaction. 

Dear has a much more refined user permission module than Xero, and Dear will not be the weak link for business control.

Accounting Transaction Reconciliation

Dear has a good reconciliation tool to verify transactions in Xero vs the Dear record of the transaction. Every month, stock and cost of sales should be reconciled.

https://dearsystems.freshdesk.com/support/solutions/articles/11000056657-xero-integration-advanced-settings-#DEARXEROReconciliationreport

 

Foreign currency

Dear and Xero work well with foreign currency. Xero maintains AR and AP in the customer or supplier currency, translating to base currency when a trial balance is run. Dear sends invoices and payments to Xero in the currency of the supplier, customer or payment method, it does not translate them to base currency. Therefore, Xero linked to Dear performs unrealised and realised gain/loss calculations the same as it does normally.

 

If you want to regularly create invoices for a customer in multiple currencies, you need multiple customers, although you can also change the customer currency before creating a sale. The Dear user interface does not support setting the currency when creating a sale.

Stock is valued in the base currency. 

Assuming you are using purchase accruals, Dear can receive stock before the supplier invoice is booked. Therefore, it uses the PO prices and exrate. At this point there is no AP. Later the invoice is booked. The invoice prices may be different, so an adjustment is made to stock so that the final valuation agrees with the invoice, according to the invoice exrate. At this point, the invoice ex-rate is locked in as far as stock goes. If the invoice is not booked, the value of stock is offset by the accrual account “Goods Received but not yet invoiced”, and there is no gain/loss accounting, since both these accounts are in base currency.

After the invoice is booked, we have an AP amount is in a foreign currency created at the exrate active in Dear at the time, which was also used to value the stock,

Gain/Loss accounts

The invoice is paid, once, or more than once, and whenever it is paid, and exchange rate is finalised. While Xero keeps track of foreign currency AP balances, every time you make a trial balance, the AP is converted to base currency. This means that the value of stock, the value of the payable and the value of the payment all need to be the same. Because the exrate changes, this is very unlikely to be true. A gain/loss account is used to bring them back into alignment. The part that can’t be revalued is the part that was converted into base currency at some point in the base. For purchasing, this step is the stock receipt. For sales, it is the revenue amount on the invoice. 

Gain/loss accounts make the accounts balance. 

Before there is a payment, the difference on any day is the difference between the value of the foreign currency document and the value of the associated “frozen” conversion to base currency. In reality, it is simply the difference between the historical exrate and the exrate when the trial balance is made. These are the two exrates that Xero needs to know for its calculations, and it gets them from Dear.

 This varying difference changes from day to day, depending on when you run the trial balance. It’s called an unrealised gain/loss/

At the same point, a payment is made or received. At this point, the AP or AR balance disappears. The exchange rate is no longer varying, and the gain or loss at that point becomes “realised”. 

Tax: you should seek advice from a tax accountant, but in general, realised gain/losses are part of your taxable income, and unrealised gains and losses are not. 

Consolidation of multiple Dears in different base currencies

GrowthPath’s Dear Analytics Connector takes transaction data from multiple Dear instances into one data warehouse, and it can convert to a reporting currency.

 

Revenue, COGS, Stock Valuation and Perpetual Inventory

Dear is a perpetual inventory system. Stock is an asset, and shipments generate cost of goods sold bookings. Revenue is booked when an invoice is authorised.

Dear values stock in the base currency. Supplier invoices are the main basis for stock valuation, and Dear has a good landed cost system. Landed costs are allocated weighted by value be default, but can be allocated with other methods, such as physical volume, qty or user choice.

Products can be costed in bulk: FIFO is used. The stock value is distinct per warehouse. 

Landed costs can be pushed onto stock receipts (from suppliers), onto production and assembly orders, and stock transfers between warehouses. 

Products be costed by batch (which is also the solution for warehouse-specific costing), and by serial number (individually).

It does not do standard costing (except to overhead costs added to a BOM)

FIFO is not exactly the same as average costing, although it is the same eventually. Dear does not do average costing, but it uses average cost for margin estimates when a sales order is being prepared. The actual cost is not known until particular stock is allocated to the order, when happens during fulfilment.

Stock can be revalued by writing it down and re-receiving it. 

Stock Reconciliation and Stock Value

Dear should be viewed as the source of truth for the stock ledger. That is, Dear substantiates the value of stock in the Xero Balance Sheet. 

It is possible for Dear to use multiple inventory accounts: they are assigned at the product level. If left blank, a product falls back to the default stock account. I will assume there is only one stock account in use, to keep these notes simpler. 

I also assume that Purchase Accruals mode is activated.

 

Dear usually has two different stock values at any time for technical reasons

Dear has a value it assigns to every item in stock. You can see this value in the Inventory Movement Summary report, which serves as the “As On” stock report, since you can run it for any date. Therefore, this report will generate a value for all stock on hand.

THere is also a value on Dear’s balance sheet, which can be different to the stock on hand valuation. The difference is due to temporary timing issues, and incomplete document processing. So the difference should be periodically reviewed and understood.

Not every item in stock is booked to the inventory account due to timing effects and some other technical reasons. Dear has a report in the Finance section to report on source documents which have caused differences between stock on hand value and balance sheet valuation.

The Dear Balance Sheet stock value should agree with Xero, not necessarily the value of stock according to the Inventory Movement Summary report.

Manufacturing Costs: Overheads in BOMs

Non-inventory items can be added to an assembly BOM in Dear. A typical example is an estimated labour cost. You may say that there is 0.5 hours of labour per assembly. 

You need to have a service item defined, which has an “expense” account associated. 

The assembly process will actually create a CR entry to this account. 

This is a “manufacturing variance” accounting entry, if you are familiar with traditional cost accounting. It is a type of contra account. 

The full accounting entries are:

At the time of auto-assembly or completion of a Finished Goods (manual assembly), inventory on hand is increased for the parent BOM (what you just assembled) and the components are taken out of stock. This has a $0 net effect on stock. But if you have a service item in the BOM, this will be included in the value of the assembled item. For instance, if you assemble one Tank, and it has $25 labour, the booking is

DR Inventory $25

    CR Service Item Expense Account $25

Accounting reporting effect: $25 favourable effect to profit, as expense is transferred to the balance sheet (inventory)

 

And then later, when the item is shipped:

CR Inventory  $25 more than otherwise*

    DR Cost of Goods Sold $25 more than otherwise

* “more than otherwise” means without adding the labour cost to the BOM

Accounting reporting effect: return of the labour cost to the P&L as a higher COGS

 

You have removed $25 from your expenses, and put it into stock. Your profit is higher. This is only a temporary effect because the $25 of capitalised labour will cause a higher cost of goods sold (COGS) when the item is shipped. In highly seasonal businesses which build up stock over months, the timing effect must be considered, as the months of producing stock builds will be highly profitable as labour costs “disappear” into the balance sheet.

The CR booking to the service item’s expense account is a “variance” account if you can compare this amount to an actual expense. For example, if you recorded your wage bill so that manufacturing labour cost went to an expense account separate for non-manufacturing wages,, then the CR entry should exactly equal the DR expense for manufacturing labour, in the perfect world where your estimates of labour per BOM assembly were entirely correct.  Any difference between the wage expense and the CR account indicates a “variance” which is an inaccuracy in the BOM estimates. It could mean that your estimate of labour overhead on the BOM is inaccurate, which means that your cost price of the item is inaccurate. This ability to compare estimated costs taken in to the cost price against actual real-world expenses is a valued feature of variance accounting. But you don’t have to use it. If you are happy with your estimates and can’t realistically make use of a variance figure, there is no need to do anything. 

 

Sales Invoices and Goods Movements

Dear is a perpetual inventory system so the shipment is a separate transaction to the sale. 

The invoice and the shipment can be on different dates: watch for this at month end. It is possible to have the COGS and the Revenue in different months. 

One order can have any number of shipments, and any number of invoices. There is not necessarily a one-to-one relationship between a shipment and an invoice. 

Shipment in Dear

Dr Cost of Goods Sold (there is account mapping logic to determine the account)

    Cr Dear Inventory 

Invoice in Dear

Dr Accounts Receivable 

    Cr Revenue

(with tax entries as well)

Settings for timing of revenue and COGS

The shipment date is most often set to default to the date the shipment is created. The alternative is to choose invoice date, but this only makes sense for flows where the invoice is created before dispatch (B2C for instance). 

 

The invoice date

The invoice date is a bit more confusing. Dear has a setting to determine when the invoice date is created if the user did not manually choose a date., and what that date is. The “when” is more important. You can choose it to default to when the order is authorised, or when the invoice is authorised. 

If your flow is Shipment then Invoice, I suggest

• Subscribe to the Automation module, and build a rule to create and authorise the invoice when the shipment is authorised.
• Get the invoice to use the current date.

So, in General Settings, choose “Fill the invoice date” = “When invoice authorised” and “Fill the invoice date” = ”With the current date”. 

Suggested settings for Ship-then-Invoice workflow

Chart of accounts: basic settings

• Xero makes account codes optional, but Dear will only recognise accounts which have an account code set.
• ‘Allow payments’ will allow Dear to use an account as a payment method, even if it is not a real bank

Opening balances for conversion

Not covered in this document. Items to pay attention to when converting are

• Inventory value & GIT stock
• Customer and supplier deposits/pre-payments allocated to open orders

Consider using tracking categories

This is one of the most powerful features in the Dear/Xero integration.

Xero has tracking categories, identical to what older systems call sub-accounts. You can activate one or two, therefore enabling up to three dimensions in the chart. Often these are used as a "division" and a "cost centre" (if both are used). 

When tracking categories are used, each accounting entry choose the account (of course), and now a value for each of the sub-accounts. 

So, a sale can now be classified into a revenue account ('Online Sales') and a division ('Export').

The division code can be used for expenses too; you may allocate freight, rent or marketing to "Export" when those invoices are booked. 

This means that you can run a P&L just for "Export".

So, they are powerful.

Dear can automatically map certain things to a tracking category. The two most common are

a) using the shipping warehouse as a tracking category (for geographic analysis)

b) using the sales channel as a tracking category (export vs b2b vs in-store retail vs b2c)

It can map other fields, including custom fields.

When using Location as the tracking category, the value on the Order Header is used, not the location of the actual pick. 

 

How does Dear choose which account to use?

Inventory accounts (Stock account, COGS account, value adjustments account, revenue account and expenses account for non-stock items) have global defaults.

They can be changed per SKU.

Users can specify particular accounts on invoices, stock adjustments, BOMs and basically everywhere an account is needed, overriding defaults selected by the system.

 

Dear requires some specific accounts

For official Dear notes:, which are copied into this section of the document are reading now:

https://dearsystems.freshdesk.com/support/solutions/articles/11000068527-xero-integration-basics

 

Required accounts: 

with suggested account numbers based on the standard Xero chart of accounts

Account	Account Type	System Account	Accepts Payments
Inventory Control [640]	Current Asset: See note below	None. Can not use the existing Xero Inventory account.	Doesn't accept payments
Inventory Discrepancy [305: put this in the margin as a Direct Cost]	Direct Cost	None	Doesn't accept payments
Cost of Goods Sold [310: this account may already be in Xero]	Direct Cost (DEAR standalone/Xero) Cost of Goods Sold (QBO)	Yes	Doesn't accept payments
Supplier Deposits/Prepayments* [660]	Current Asset	None	Accepts payments
Customer Credits [810]	Current Liability	None	Accepts payments

*may already be created by Xero.

Special notes: Inventory

NOTE: Xero pre-loads a default Inventory account for use with its “Tracked Inventory” feature; however, it does not have the correct settings for DEAR to function correctly. You will have to create a new one with the settings listed above. 

I recommend only one inventory account. Dear cannot use Xero's build in stock account, so a dedicated current asset account is required. It is possible to use multiple inventory accounts, since products can be linked to distinct inventory accounts, but it makes reconciliation more complicated, and Dear’s reports make it unnecessary to use the balance sheet to keep detail about stock holdings.

 

Optional Accounts

Inventory Accrual/Stock in Transit (requires Inventory Accrual to be enabled)

Inventory Accrual is HIGHLY RECOMMENDED because it makes stock reconciliation easier, and because it is accounting best-practice.

Account	Account Type	System Account	Accepts Payments
Inventory Accrual (Goods Received, Not Invoiced) [815]	Current Liability	None	Doesn't accept payments
Stock in Transit (Goods Invoiced, Not Received) [650]	Current Asset	None	Doesn't accept payments

 

Gift Card Liability (requires Gift Cards to be enabled)

Account	Account Type	System Account	Accepts Payments
Gift Card Liability [845]	Current Liability	None	Accepts payments

 

Payment Accounts

Dear allows payment entry in AP and AR. Payments are sent to Xero (if enabled).

When a payment is recording, the user chooses a “payment method”, which maps to an account in Dear. Typically, these are bank accounts, such as a traditional cheque account, or a PayPal or Stripe current asset account. Therefore, Dear detects payments methods by scanning for bank accounts, credit card accounts in Xero and “payment enabled” ordinary accounts. Account codes are needed

Additional payment methods are detected: accounts which have “payment enabled” are detected as a payment method too.

Purchase Accruals

In General Settings, purchase accrual accounting is set, as mentioned above.

Dear expects a PO to have stock receipt and an invoice. Assuming sensible names for the accrual accounts, the Stock Receipt books:

DR Inventory

    CR Goods Received Not Invoiced 

Valuation at this stage is based on the PO price, since there is no invoice yet. This initial valuation is based on the receipt date.

 

And the Invoice books:

DR Goods Received Not Invoiced

    CR Accounts Payable

The inventory is revalued at the invoice price. The date of the revaluation is the invoice date. 

 

In the Product Master page, you can view the transactions which lead to the inventory valuation for that product.

 

Tax Rules

Dear fetches its tax rules from Xero.

There are priority rules.

Tax Rules in DEAR can be assigned to customers, suppliers, products, the sale/purchase process and individual order lines to allow fine-grained control of your transaction process. Tax rules at the order line level override all other tax rules.

The order of priority is as follows:

• Order line 
• Product 
• Purchase/Sale process document header
• Supplier/Customer.

 

Payment terms and due dates

Dear pays no attention to Xero’s settings for terms. It calculates them, and sends to Xero a due date on the document.