Cin7 Core (DEAR)  for CFOs/Controllers:
Notes on Xero Accounting integration with Cin7 Core (Dear) Inventory

This is a GrowthPath knowledge-base article.

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

For QuickBooks Online, it is very similar. Xero’s Tracking Categories are similar to Classes. QBO has one class mapping, and a specific option to map a Location if the Location feature is active in the QBO instance.

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.

Official Dear Documentation

https://support.dearsystems.com/support/solutions/articles/1000118855-accounting-transactions-in-dear-inventory

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.
• There ALSO needs to be an internal Dear reconciliation too, between the Dear balance sheet and the stock subledger!
• 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”^[1] 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 (it is also disabled for QBO)

Dear has a full trial balance, but to the user of Xero/QBO, it is mostly irrelevant. That is, Dear has a GL backend, but it doesn’t yet compete well with the dedicated capabilities of Xero or QBO.

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.
The term perpetual is odd; it means the stock balance is always correct. The contrast is with periodic stock, where the stock value is measured once a month via stocktake.

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 insert physical stock movements into a prior date. For instance if you do a stock adjustment to reduce qty for 100 units dated last week, the inventory effect is now, not one week ago although the financial entry is one week ago.

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.

Note that with perpetual inventory systems which split fulfilment and invoicing, these timing differences are almost inevitable and need an accounting solution (month-end accruals) if they are material. Also, they are subject to “swings and roundabouts”’; in a given month, there may be sales which were not shipped in time at the end of month (revenue without cogs), but also at the start of the month, there are shipments which are linked to last month’s sale (so cogs without revenue).

The financial effective date of the shipment can be specified by the user processing the shipment. With manual attention, timing differences can be controlled by choosing an over-riding effective date.

The default shipping date (prefilled in the Shipment stage of a fulfilment) has two choices available in General Settings -> Sale Process Customisation: Today, or Invoice Date.

This can help if the fulfilment flow is invoice before shipment.

An advanced sale can have multiple invoices. When starting a fulfilment in an Advanced Sale, it can be optionally linked to an existing invoice. If this is not done, the invoice date won’t be pre-populated. A Simple Sale has only one invoice. However, in Dear the fulfilment stage can be flow before or after the invoice stage, and the invoice date can only be provided to the shipment if the invoice exists before the shipment. For B2C integrations, such as Shopify, the invoice will exist before the fulfilment.

Unfortunately it is hard to use Dear’s reporting to reliably report on this as it requires matching the cogs value of fulfilments and invoicing, which is not supported in any one report.

However, GrowthPath’s Dear Analytics module provides this reporting specifically for this requirement, as this module produces tables designed to fill in gaps in Dear’s reporting. It enables many advanced and powerful reporting capabilities in your preferred reporting & analytics tool; recommended is Zoho Analytics.

Documents/Transactions sent to Xero by Dear

Note: 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.

Below are events which do have financial consequences.

This is a “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 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? When does it not work?

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)

Note! Undo and Void: Undo in Dear does not undo in Xero.

• Undoing documents in Dear does nothing in Xero.
• Subsequent changes to a document and reauthorisation do not always update Xero. Sometimes there will be a warning when saving/reauthorising the change in Dear, but there may not be an error.
• Voiding a document in Dear voids it in Xero as long as no payment exists on the Xero document.

Once an authorised invoice has synced to Xero, undo in Dear does not affect the invoice in Xero.

This is a property of the Xero API. Once an invoice is authorised, it can only be voided, in Xero.

Voiding in Dear will void in Xero (assuming no payment is applied in Xero).

But Undoing does not revert the Xero invoice to Draft.

https://developer.xero.com/documentation/api/accounting/invoices#invoice-status-codes

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.

Transaction Timing and historical integrity

If the cost of inventory is changed after a shipment, for example a landed cost is entered after the receipt and shipment to a customer, Dear will send correcting journals to Xero.

The effective date of transactions is the invoice date or receipt date. This means in the case above, the date of cost correction will probably be the invoice of the service invoice. The COGS correction journal will have a different date to the COGS shipment journal.

Dear “remembers” the accounts used historically when reversing transactions. For example, if a SKU has a certain inventory account on Monday, then goods are shipped on Monday, then on Tuesday someone reconfigures Dear to use a different inventory account for the SKU, and then the goods are returned back to stock on Wednesday, the original account of Monday is used for the goods return. The would happen if the Shipment was voided or undone.

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 with Xero

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.

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 by default, but can be allocated with other methods, such as physical volume, qty or user choice.

FIFO Costing for Inventory Items

The event at which FIFO cost is determined is the pick. The accounting entry is the shipment, but the FIFO allocation of specific stock occurs at pick.

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 under batch or serial control are costed by batch (which is also the solution for warehouse-specific costing), and by serial number (individually). Product that don't have batch or serial control can be considered as costed by "anonymous" or "invisible" batch.

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. This moves all the received stock into a new "anonymous" batch if the SKU is under FIFO costing.

For more about landed costs see: Notes on Dear Product Costing and Landed Costs

The FIFO nature of stock will "follow" stock in a stock transfer, so warehouses can have different average costs.

Stock transfers don't change stock value on the balance sheet, although it is possible to make stock transfers that move stock into an "in transit" account during the transfer, in which case the value moves from the inventory account to a temporary account.

Costing of Non Inventory Items

Apart from Stock and Service Items, Cin7 has Non Inventory Items.

Non Inventory items have many master data elements in common with Stock items, and they appear in the "products" section of Sales documents, not in Additional Charges.

You can include Revenue and Cost accounts. Revenue accounts work as you would expect. However, even though you can fulfil Non Inventory items, they do not incur COGS at the point of shipment. This is because they are never capitalized; they are taken to their expense account when they are "received".

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 which can differ due to timing or mistakes

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.

Drop Ship

Drop ship books the product expense to the COGS account.

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.

Customer Deposits: The Dear Method

Applicable to normal sales and Jobs.

In Dear, a deposit is entered on the Quote stage, although it is referred to as a Customer Credit.

For the Job Module, this method encourages starting with a Quote.

A Customer Credit can also be created by “refunding” a credit note as a Customer Credit. So you could create a stand alone credit note and then refund it.

In Xero, it does not create a balance to the customer’s favour on the statement. Instead, it creates a fully paid item.
Dear tracks the liability internally and some basic reporting is available via the Customer Credits list (Sale -> Search -> Customer/Credits)

In the accounts, it does a DR to bank and CR to the nominated Customer Credits acct.

This transferable credit can be applied to pay an invoice. When that happens, the Customer Credits account is used as the payment account.

It is a Receive Money in Xero, against the customer contact. It is not an AR item on the statement.

The liability is not recognised on the balance sheet.

Under this approach, there is no natural way to raise even a proforma invoice unless you produce a template which uses the Quote but formats it to look like an invoice.

Customer Deposits: Proforma method

There is another method, using an invoice.

Under this method, we can use an Advanced Sale Invoice or a Service Sale Invoice. The order is added as usual; if using an Advanced Sale it can consist of SKU items and/or additional charges. A deposit invoice is raised, using a DEPOSIT item (an additional charge), and the DEPOSIT item is assigned the Customer Credit liability account as the Revenue account on the Product card.

The DEPOSIT item is not a special item, it is just a predefined (by you) Service Item with the Revenue account created as mentioned above.

Example:

This raises an AR entry on the customer statement as it is a normal invoice.

The payment is booked as usual and the deposit invoice is paid. At this point, the balance is sitting on the balance sheet as a liability which is correct, and the customer statement will show no amount owing. A statement showing activity would record that the deposit has been paid.

The second invoice in the example  is the final invoice. The deposit item is added again, but this time with a negative value (quantity in Dear is always positive, it is the monetary amount which is negative), which makes it a credit line on the second invoice. The amount due is then the balance owing, and when this invoice is posted, the liability account is reversed since Dear posts to the “revenue” accounts at this point.

Adapting Proforma method to the Jobs Module

This method is applicable to the Jobs module. In the Jobs Module, all invoices begin as quotes, and they then convert to a Service Invoice.

Applying Credits Notes as payments

A customer credit note can be applied as a payment to an invoice, similar to conventional Xero.

NOTE: Credits notes can only be applied on the Dear side. Credit notes applied on the Xero side will not update the payment on the invoice in Dear.

A customer credit can also be converted to a Customer Credit if required.

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

Xero/QBO Control Accounts and Reconciliation

Avoid manual journals into unofficial control accounts

A control account is an account controlled by the accounting backend to prevent manual journals. That is, it is linked to a subledger.

Unfortunately, we can not assign the Dear inventory, revenue or COGSs account to this status. So it is possible to manually journal into these accounts.

We strongly recommend against manually journaling into these unofficial control accounts. Keeping this discipline makes the Dear/Xero reconciliation much easier if we can state with confidence that every transaction in Xero originated from Dear. It makes missing transactions easier to find.

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

Notes on the Xero Conversion Go Live Account Balances

If you are converting to a new Xero instance, there may be testing transactions in Xero prior to the go-live. These don't count for anything because the conversion balances reset all the account balances.

There will be no matching transactions in Dear for these early transactions, even if the source is Dear because all transactions were wiped from Dear prior to going live.

In Xero, the opening balances ("conversion balances") will have specified a value for each account based on the closing trial balance of the legacy system, and that will be the balance that Xero has on the go live date. If an account has no entry in the conversion balance, Xero interprets that as a desired balance of 0.

For example, assume we go live on March 1. if there were two testing transactions causing a balance of $200 DRin account 1-1234 dated in Feb, and no other transactions, then the end of Feb balance in Xero would be $200 DR.

That is, until the conversion balance is loaded. If the MYOB balance in that account was DR $900 on Feb 28, Xero will see that there is a difference of $700 DR and therefore it posts $700 to the account to force the balance to be $900 DR.

Hence, the testing transactions are irrelevant.

The conversion balances need to be reposted if any transactions are entered to Xero which an effective date prior to go-live, so lock Xero to stop that from happening. If you want to entered historical trial balances, you can do that from the conversion balance screen but do these in chronological order, oldest first.

Reposting the conversion balances is not as simple as entering the account balances. The Save button must be invoked, and Xero will do two verifications before it actually posts. It makes sure that the AR and AP open document total as of the conversion date matches the trial balance values for those two control accounts.

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. This means that COGS use the tracking category of the order header, even if a different location is used.

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.

More detail on tax rule selection and account selection can be found under the relevant headings.

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

*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.

Gift Card Liability (requires Gift Cards to be enabled)

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.

Limitations imposed by Dear for certain types of accounts

Dear imposes some arbitrary restrictions. There doesn’t seem to be any strong reason for these restrictions, so they might change.

Product Master

• Revenue Accounts on Products: Must be a Revenue or Liability account.
• Expense Account on Products (where applicable): must be an Expense account

Stock Adjustment and Stocktake

• Expense account must be an Expense Account or Liability Account

Finished Goods (Assembly)

• WIP must be a Current Asset account
• Finished Goods account (should be inventory): Must be Current Asset or Expense Account

Credit Note (AR)

An additional charge line account can be any account.

Purchase Accruals (Goods In Transit)

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

This is optional, but you should always have it on.

These transactions can happen in either order.

Even if you do both the receipt and invoice in the same session in Dear, it will create this pair of transactions.

STOCK RECEIPT

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

DR Inventory

        CR Goods Received Not Invoiced [This is a liability account]

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.

The effective date for this transaction is the date of the stock receipt

INVOICE BOOKED

And the Invoice books:

DR Goods Invoiced Not Received (Commonly known as Goods In Transit From Supplier, an Asset)

         CR Accounts Payable

The inventory is valued at the invoice price (or revalued if this is the second step). 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.

Accruals: incomplete invoice/receipt pairs do not complete the accrual accounting

When an invoice is booked for goods, the accrual account Goods Invoice Not Received is Debited.

The accrual accounting is completed only when one of these things happens:

• The stock matching the invoice is fully received
• The PO is marked as “received” (meaning no more stock will be received)

ANY MISMATCHED INVOICE AND RECEIPT PAIR WILL NOT CLOSE ACCRUALS

This includes

• Receipts that do not fully match the quantities on the invoice (short received)
• Receipts that exceed an authorised invoice

Priority of Tax Rules when there are multiple tax rules (e.g. on customer, on product)

(Dear fetches its tax rules from Xero if the Xero integration is configured).

There are priority rules to determine what tax rule is used on the order line.

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. You can influence which tax rule is applied on the order by default settings at product and customer level.

The order of priority is as follows: higher items win over lower items

1. Order line (that is, a manual override during order entry always wins)
2. Product Tax Rule (leave the tax rule blank to use the value from level 3. A non-blank product rule overrides level 3)
3. Purchase/Sale process document header (this defaults to the Supplier or Customer value, but you can manually override it)
4. Supplier/Customer*.

That is, a product with a certain tax rule on an order where the customer has a certain rule means that the product rule wins.

Common Scenario: Leave the tax rules blank on the Product

You can leave blank the product sales or purchase tax rule, in which case the product does not participate in the sales tax rule priority. The customer or supplier setting will cascade down to the order line, unless you intervene on the order header.

For example, you have a product you sometimes import, and don’t pay GST on the supplier’s invoice, but sometimes you buy it from a local supplier, which does include GST on the supplier invoice. While you can always edit each PO line, you can get the defaults behaving correctly, which is better.

In this case, leave the tax rule blank on the product. Use ‘Tax Exempt’ on the import Supplier, use “GST” on the local supplier.

When you place a PO with the import supplier, the “Tax Exempt” rule of the Supplier (level 4) is copied onto the PO (level 3). Because the Purchase Tax rule is blank on the Product, rule 2 is skipped, and the PO line will default to Tax Exempt. You don’t need to edit this, so you do not invoke any changes at level 1.

In other words, in the scenario above, the product would not over-rule the supplier tax setting because the product tax rule is blank. Effectively, the tax rule of the supplier goes all the way to the order line.

*Note: you can set a default tax rule when making a new Customer, Product or Supplier in the Reference Book Default Field Settings. This is optional.

Inventory, Revenue and COGS Account Priority Rules

Dear chooses the revenue and COGS account when an invoice is issued (revenue) and when the pick is authorised.

API Orders

An order created by API always accepts the accounts provided. No account selection logic is available to the API. The API user must provide accounts. Therefore, an API user must emulate Dear’s account selection logic for consistent behaviour.

Shopify and other integrations

If the Integration tile does not specify an Optional revenue account, the accounts are assigned the same as a sale entered in the web UI.

If an integration (e.g. Shopify) specifies an “Optional” revenue account, this is used over all other rules.

Sales created in Dear Web UI

A revenue account entered manually on the invoice always wins. You can not specify COGS manually on the shipment.

An account provided on the Product Master will be selected if present, for revenue, cogs, expense and inventory accounts.

However these values can be left blank, in which case the selection logic continues. This applies to SKU and Service Items (Additional Charges).

The order header has a default Revenue account. This value is inherited from the Customer master, if the Customer master has a default revenue account, or from the system defaults in General Settings. The order header Revenue account is used as the default on the invoice lines if there is no Product Master Override.

COGS and inventory accounts will fall back to the global defaults in General Settings if no value is provided by the product master. There is no customer setting for these accounts.

Historical fidelity of inventory accounts

While it is possible to change the inventory account at any time, according to the priority logic discussed above, Dear always remembers the account associated with a FIFO transaction. That is, when stock is picked, accounts are assigned at that point, and these accounts stick with that FIFO hard allocation, including when that stock is returned or if the transaction is undone.

This means that,for example:

On Monday, the inventory account is 1-1000, and an order is shipped.

On Tuesday, the inventory account is changed to 1-2000

In 100 years time, the order sent on Monday is returned.

When it is returned, the DR to inventory will use 1-1000.

If this account has been archived in Xero, a sync error will occur.

If this stock is subsequently shipped on a new order, that stock will do CR 1-2000

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.

Suggested Regular Reconciliation Activities

• Xero sync errors: Daily
• Dear internal stock reconciliation: Monthly (use of Financial Stock Differences Report)
• Dear to Xero stock reconciliation: Monthly

Dear Internal Stock reconciliation

Dear maintains two parallel views of stock value.

First, the most obvious is the stock records per SKU. At any moment, each SKU has a quantity on hand, and an average value. The value of stock is the sum of the values.

Secondly, Dear generates financial transactions when there is a stock movement.

These financial transactions result in a Dear balance sheet value. These financial transactions go into the Xero/QBO sync queue.

So it is the Dear balance sheet stock value which needs to be reconciled with Xero/QBO, not the inventory valuation founds from the product availability of the inventory movements summary report.

It is possible for Dear to have a different balance sheet value to the stock on hand valuation. Most of the time, this difference is an error and should be investigated.

Dear has a report called the Financial Transactions vs Stock on Hand difference report.

Each row is a “document”, that is, a processing event in Dear such as a stock adjustment, receipt or shipment.

Each document has two effects: it changes stock on hand, and it changes the account value of an inventory account. They should be the same. When they are different, they appear in this report.

An easy way to create a difference is to create a stock adjustment to reduce stock on hand for a SKU, and to provide the inventory account as the stock discrepancy account. Don;t do this: it is an error. Here is what happens:

When writing down stock, Dear follows FIFO, not average cost. If the FIFO cost of sku “widget” was $1 and you wrote down 1 unit, then there is $1 less in stock. Immediately, a SOH report would show $1 less in value.

In the accounts, Dear reduces the inventory account (CR) and DR the expense account you selected in the header of the stock adjustment.

But if you use the inventory account, then Dear is forced to create a booking like this:

DR Inventory $1

        CR Inventory $1

which is no change.

So according to stock on hand, we have lowered stock by $1, but in the balance sheet there is no change to inventory value.

This stock adjustment would appear in the Financial Transactions vs Stock on Hand difference report

Transaction Load and Consolidation

Load testing shows that Xero performs well under transaction loads well above Xero’s recommended limits.

The problem with transaction load is tax transaction load from invoice lines

See: https://www.growthpath.com.au/Business-IT/xero-stress-test

None-the-less, at some point consolidation of documents is required.

Dear can consolidate at the front end: on the sales channel. This is the same concept as an old retail sale register: daily sales and receipts are recorded as one journal. This type of consolidation is activated at the integration level, such as on the Shopify channel.

Dear can also consolidate the accounting entries it sends to Xero. This is managed on the Xero integration tile. Dear refers to this is “Consolidate transactions on export”.

The consolidation options are flexible. Different types of source documents have independent consolidation options. Avoid the products and services consolidation: there is not much point is recording this level of detail in Xero, and it adds to the internal load in Xero.

Consolidation is well documented by Dear under Xero Integration.

Managing consolidation is supported by a type of report which shows links between Dear documents (invoices, payments) and the Xero consolidated document.

Things to know before activating consolidation

Consolidation significant changes the workflow for consolidated document.

Once a transaction, e.g. an invoice, has been consolidated, it can no longer be voided. Specifically, this happens once the payment has been consolidated, but for online channels, the payment and the invoice are consolidated at the same time since invoices arrive with payments.

It can be credited, though.

Tracking Categories and COGS don’t work with consolidation

Tracking categories are not used for consolidated journals. This means there is no tracking category data for COGS if COGS exports are consolidated. You can avoid this by not consolidating COGS.

Restrictions by Channels

Entire source channels can be excluded from the sync process. Excluded invoices and COGS journals are marked by Core as Skipped.

Source Channels are: Dear, API, Shopify, etc

Excluding invoices means excluding the COGS too

Radical alternatives to consolidation

Dear invoices can be blocked from exporting all together, by channel.

You can do this while exporting COGS (consolidated makes sense).