For many businesses, the number of transactions coming from a sales order system are significant and requires a considerable amount of effort to reconcile payments to invoices.
Web-based sales fall into this category. Usually, the sale is only allowed when payment has been received at the webstore, so reconciling the receipt of money related to a sale is generally not required – at least not on an invoice by invoice basis.
In the discussion below a methodology is described related to the use of Cin7 inventory management software and Xero accounting software.
It is preferable to group sales in a logical manner that makes it easier to tie into methods of payment. Examples might be by currency, Paypal, Amex, other Credit Cards, etc.
Let’s assume sales are split by currency.
Transactions flow from Cin7 to Xero for each sales order but classified to one of three ‘Customers’.
These become Contacts in Xero and accumulate a massive number of transactions waiting for payments to be applied to them. For most businesses, there is a need to match every payment to specific invoices. When the volume of invoices is large and payment required before a sale occurs there is a tendency to be less specific.
To this end, bank deposits appearing in the Xero bank accounts for sales related transactions may get allocated across sales transactions in a broad-brush approach.
The best way to do this is to use the find and match function in Xero bank reconciliation. Search for the contact – eg NZD Sales, a list of the outstanding NZD sales invoices will display. Apply the payment to the oldest invoices (ideally, they would be no more than a day or two old) balancing to the amount of the deposit. There may be a need to split an amount on an invoice to achieve a balance, but the remaining amount will clear in the next round.
This approach will ensure all transactions that flow from a webstore through to inventory management and through to the accounting system will be properly accounted for. It completes an audit trail, ensures GST is accounted for correctly and will highlight issues if balances in each of the three contacts start to build up.
This would work best for a business that is approving their drafts every day and has a tight focus on being up to date every day.
Note: IF you have a historical imbalance you can create a stock adjustment and set to “Do Not Import”.
This will update your balance sheet to be in line with your stock on hand. But tread warily!!! All data must be complete (sales/purchase orders adjustments, credits, branch transfers).
This option would only be recommended ONCE and ONLY when you’re able to also complete a total stocktake across all products/locations.
If you receive freight bills and a portion of the bill contains GST – you don’t want this to affect your COGs in Cin7.
You can either split the bill – enter the landed costs in Cin7 and the GST directly into Xero (Use the same supplier invoice number, just add “A” to one, and “B” to the end of the other.
Alternatively, it’s possible to create an alternative Tax Rate for GST – so the data can be entered in Cin7 but pass straight through to the GST code within your Xero accounts.
First – make sure you’ve done “Get Tax Rates” from Xero on set up.
In Settings, Alternative Tax Rates enter as below:
Name: GST on Imports
Apply To: (leave blank)
Quick Books Tax Rates ID: (leave blank)
Xero Tax Types Codes: GSTONIMPORTS
Purchases or Sales: Purchases
Tax Rate: 0
Then when entering the freight invoice, simply select GSTONIMPORTS from the account column.
How to account for Warranties and R&M product in Cin7
The process below allows for reclassification of COGS to a Warranty, Promo or Advertising/Marketing cost/expense code.
Customers may order parts for the repair of equipment. The transaction is treated as a sale and invoiced to the customer in the first instance with the intention that the customer will pay for the items.
If you consider the need for the parts to be covered by a warranty claim, the customer will be issued with a credit note for the items invoiced. This puts the items back in stock and nullifies the sale.
The items still need to be taken out of stock. Do so by setting up a customer called “YOUR BUSINESS NAME Warranties” and create a sale to this “customer” at a price of $0.01.
The 1 cent is vital to allow the transaction to work its way from Cin7 to Xero correctly.
The balance due in the internal Warranties account may be written off as rounding amounts in Xero. Do so by creating a credit note in Xero and allocating it to the invoice.
This process means that the reduction in your stock will be accounted for and the cost of the items will have been deducted from inventory and added to Cost of Goods sold in the General Ledger.
If you wish to reclassify the costs from COGS to Warranty claims expense code create an entry on Xero as a Purchase – a bill to debit Warranties on one line item and credit COGS on another line, with a net impact of $0 outstanding. The COGS figure relevant to the transaction will be visible on the Review screen of the transaction in Cin7.
The same process applies to Promo and R&D stock.
Cin7 suggests the following in their help menu.
There are two ways to deal with promotional or free stock.
See the options below:
Make sure you’re on a page in Cin7
Right-click on the screen.
Top right, click on Application (if not showing, click on the double chevron)
Click on Clear Cache button at the bottom
If a section of data has leading zeros dropped (often for NZ Postcodes) you can add using the Custom function under Format Cells.
You can also select all on a worksheet and format all cells to “Text” before copy/pasting your text into it. This will retain the leading zeros and prevent sizes (like 3-5 or 9-11) converting to dates.
If Cin7 emails are going to your customers' “junk” file – here is the process to correct it. It can be a bit complex, so if you need assistance your domain provider should be able to do this for you.
When you email from Cin7, it is sent on your behalf, i.e. the ‘from’ address will be the one associated with your user account. This is great, however, you may find that this causes the emails you send from Cin7 to be blocked by your recipient’s spam filters. The simple solution is for your recipient to add an “SPF” record.
To notify ISPs that Cin7 is allowed to send emails on your behalf, add the TXT record below to the specific DNS Records of the domain you are sending from.
v=spf1 mx a ip4:18.104.22.168/32 ip4:22.214.171.124/32 ip4:126.96.36.199/32 ip4:188.8.131.52/32
If you already have an SPF record – for example, if you are using Outlook you may have “v=spf1 include:spf.protection.outlook.com -all”
In this case, you will need to add the Cin7 address before the “include” statement. Note that you can only have one SPF record per domain so you will need to edit the existing one.
v=spf1 mx a ip4:184.108.40.206/32 ip4:220.127.116.11/32 ip4:18.104.22.168/32 ip4:22.214.171.124/32 include:spf.protection.outlook.com -all
If you make a payment to an offshore supplier – don’t process your Cin7 PO prematurely or create an invoice in Xero – simply reconcile it as a Pre-Payment (or Over-Payment if in a foreign currency so you can calculate the rate) to the supplier contact in Xero. It can remain there on the account until the Purchase Order is completed in Cin7 and sent through, and then you can match it up.
This treats it in the correct way for accounting purposes (as you don’t own the goods yet – you’ve just sent a financial transaction to your supplier) and also keeps the data correct in Cin7 for managing your stock.
Cin7 will update the last cost of products on Purchase Orders as long as if follows these rules:
This will update the “cost” on a product automatically so you don’t have to go in and update.
After you’ve received in product from your stock supplier, you may receive a separate bill from your freight forwarder for the cost of shipping the goods from their warehouse to yours.
There may be charges without GST for the movement of the goods from their location to your local Port, then there may be additional charges for movement locally that do include a GST portion. There may also be the Import GST content of your products that they have paid on your behalf and are requesting back from you (unless you have a New Zealand Customs deferred GST account).
To apply these costs to the products you’ve received, you raise a separate purchase order for the freight and transaction link it to your product/stock purchase order. Follow these steps:
First, raise a new purchase order to the freight forwarder. Enter “Freight” – this product should be set up as non-stock. Once it’s transaction linked, Cin7 converts it to a stock cost.
You may have some lines that include GST and some that don’t. There is no need to type in each and every line, just run one line and sub-total all the costs that include GST, and then do another line a sub-total those without GST. For the inclusive, it’s important to just leave the accounts column blank, but for the GST exclusive you can select “No GST”. The total at the bottom should match what is on their invoice.
If a portion of your bill involves GST on Imports – set up a new product called GST on Imports (non-stock) and create an Alternative GL account (in settings) to ensure this goes to the GST on Imports account in Xero – this alternative account can be set up to default to this account when you’re creating these freight bills. We do this all the time, so get in touch if you would like some help setting it up correctly.
Once you’ve created this bill for freight, fully receive and add invoice date/ref and approve – it’s important to complete and save the PO before trying to do the transaction link or it sometimes won’t link.
To do the transaction link:
In the Admin screens, you’ll be able to see the linked transactions in both the freight/landed bill and the product purchase order/s. You can click on Admin to go to that transaction to view it and also check the report that will show the landed costs per product for that particular PO and how it’s allocated line by line.
The freight bill will also now be in the Xero queue ready to import and you can push this through and approve. Do not change the data once it’s gone through to Xero. It should show in the Inventory GL account if it’s been done correctly.
For many businesses in New Zealand and Australia, trading conditions are far from normal at present. Many essential services are operating at maximum capacity to keep up with demand while others have had to shut down to comply with the LockDown rules. For those organizations using cloud-based software, working from home is an option, even when normal operations have ceased.
This could be a good time to do a little housekeeping on your system. We have some useful tips for Cin7 users that will help you tidy up your data and gain greater accuracy of your inventory figures. It may also clear some of the clutter that can build up in systems and allow you to understand some ‘best practice’ techniques for your software.
Comments will be approved before showing up.