Cin7 Core for CFOs: Notes on Xero Accounting Integration

The latest version of this document is here: Cin7 Core for CFOs: Notes on Xero Accounting Integration

Cin7 Core for CFOs: Notes on Xero Accounting Integration

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

This document is for CFOs, controllers, bookkeepers and accountants who want to know how Xero works with Cin7 Core.

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 Cin7 Core. Any account in Xero without an account code will not be visible in Cin7 Core.

High-level overview

• Cin7 Core 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, Cin7 Core posts to Xero via the integration.
• Cin7 Core is the owner of the stock subledger.
• Xero is the source of truth for all other ledgers, even those where Cin7 Core provides most of the source transactions (such as AR).
• The steps you would go through in a traditional ERP are still valid in Cin7 Core. That is, good financial controller practice of reconciling ledgers each month is just as valid with Cin7 Core.
• The integration is partially two-way: under some circumstances, you can enter customer and supplier payments in Xero and have them import to Cin7 Core.
• 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 Cin7 Core after they have synced to Xero.
• Cin7 Core 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 Cin7 Core from the prior system.
• It deals well with multiple currencies.

This means Cin7 Core 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 Cin7 Core, in proper usage. It is a mistake to manually journal to them.

Unfortunately, though, we do not have formal control accounts in the Cin7 Core/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 Cin7 Core.

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

Perpetual Inventory

It goes almost without saying that Cin7 Core 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, Cin7 Core will still generate additional COGS journals.

Transaction dates/effective dates

Cin7 Core 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 cannot 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 when 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 Cin7 Core’s reporting to reliably report on this. However, GrowthPath’s Cin7 Core Analytics module provides this reporting, as well as many other advanced reporting options.

Documents/Transactions sent to Xero by Cin7 Core

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

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

Cin7 Core sends the following documents to Xero; that is, the list below contains events which do have financial consequences.

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

Cin7 Core is the sole source of truth for stock value.

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

Mapping of Cin7 Core to Xero

Cin7 Core can also send Purchase Orders to Xero when they are authorised in Cin7 Core. 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 Cin7 Core 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 Cin7 Core does nothing in Xero.

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

Two-way Payment Sync

Cin7 Core has two-way payment sync.

Payments created in Cin7 Core are sent to Xero. Payments entered in Xero are pulled into Cin7 Core.

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.

Cin7 Core 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 Cin7 Core’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 Cin7 Core is only temporary.

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

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

Accounting Transaction Reconciliation

Cin7 Core has a good reconciliation tool to verify transactions in Xero vs the Cin7 Core 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

Cin7 Core 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. Cin7 Core 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 Cin7 Core 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, Cin7 Core 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 in a foreign currency created at the exrate active in Cin7 Core 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, an 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 past. 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 Cin7 Core.

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 Cin7 Core instances in different base currencies

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

Revenue, COGS, Stock Valuation and Perpetual Inventory

Cin7 Core 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.

Cin7 Core values stock in the base currency. Supplier invoices are the main basis for stock valuation, and Cin7 Core 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.

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 can 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 for overhead costs added to a BOM).

FIFO is not exactly the same as average costing, although it is the same eventually. Cin7 Core 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, which happens during fulfilment.

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

Stock Reconciliation and Stock Value

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

It is possible for Cin7 Core 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.

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

Cin7 Core 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 Cin7 Core’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. Cin7 Core 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 Cin7 Core 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 Cin7 Core. 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<br><br>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 items 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 from 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 into 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

Cin7 Core 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 Cin7 Core

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

Cr Cin7 Core

Invoice in Cin7 Core

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. Cin7 Core 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 Cin7 Core will only recognise accounts which have an account code set.
• Allow payments will allow Cin7 Core 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 Cin7 Core/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 chooses the account (of course) and 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.

Cin7 Core 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 Cin7 Core 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.

Cin7 Core requires some specific accounts

For official Cin7 Core notes, which are copied into this section of the document you 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 Cin7 Core to function correctly. You will have to create a new one with the settings listed above.

I recommend only one inventory account. Cin7 Core cannot use Xero’s built-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 Cin7 Core’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

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

When a payment is recorded, the user chooses a payment method, which maps to an account in Cin7 Core. Typically, these are bank accounts, such as a traditional cheque account, or a PayPal or Stripe current asset account. Therefore, Cin7 Core detects payment 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.

Cin7 Core expects a PO to have a 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

Cin7 Core fetches its tax rules from Xero.

There are priority rules.

Tax Rules in Cin7 Core 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

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