Kidson Talks | The E-commerce Chemist

Accounting with CA can be a nightmare at times. Especially if your accountant does not have experience with the downloads provided by Channeladvisor.

Well this is what I do for one of my clients accountants every month.

1. Using the sales > all sales view I use my ‘last months sales shipped‘ filter. This takes into account the completed sales only that were shipped in the last month. This is best done from the 2nd day of new month.

The parameters are:

(Shipping Date >= {{FIRSTDAYOFPRIORMONTH}})
AND (Shipping Date <= {{FIRSTDAYOFMONTH}})
AND (Checkout Complete = true)
OR (Checkout Disabled (Offline Sale = True) – this traps your off line sales too incase you have marked something as payment cleared manually for example.

Now you can leave it here, but bear in mind, even when you cancel a sale for whatever reason if will still show up in your download. To avoid that, mark your cancelled sales as ‘payment failed’ . You can if you file a dispute each time add this into the filter to remove those sales if you so wish. You accountant might want them left in the download though, depending on their process.

My clients accountant only wants completed sales.

So once filtered hit ‘Export All Invoice Information’ and open the CSV file.

Now you might ask…why not use transaction processing? Is that not what it is for? It is. But you need to have discipline to make sure you catch all of the sales. If you mark just one sale shipped manually from sales>all sales before the checkout report is complete, it won’t show up on a ‘Ready To Ship’ report. If you only every use transaction processing and automatically update transactions as “Payment Cleared” and “Order Shipped” when a Checkout Report is generated then you can start from step two.

2. The download.

Things to remember – the sales are listed vertically line by line so if you have multiple items on one sale the sheet will do this:

But, the shipping was £7.99 for the total order? Not EACH item….oh yes, so you will need an excel formula to remove the duplicate shipping in order to calculate the correct value. This duplicate also effects other attributes, so be wary.

The download doesn’t give you the unit price per line if your customers brought two items.

So insert a column to show the unit price of each line to makes things more crystal clear (Multiply Quantity by Unit Price) then you will have to use more excel magic to add up the unit price of each line corresponding to the order (remember you have the order number as a constant for the excel formula) then add the single shipping.

It is best to take such a complex task in steps:

First – blank out the extra shipping per order number.
Second – Insert a column to add up the value of the single lines
Third – Add up the unit prices per order number and add the shipping price.

Then you spit it out to a column somewhere which will give you the order number, total goods value and shipping value. This is a better format to work out the VAT for your goods and also you can cross reference the shipping location for the VAT in your shipping.

3. Refunds

If you only use paypal this is a cinch, as you can download your normal report and syphon off your refunds for that month to take off the total. If you have to make credit card refund or manual refunds, you just grab this info and spreadsheet it for your accountant (if they don’t handle it, but remember the hourly charge and you could probably do this yourself)

4. To VAT or not to VAT that is the question!

Make it easy to find your non vat-able items but using a reference in the SKU, tax class does not export from sales all sales or transaction processing, and you know CA doesn’t work unless you add on VAT at checkout for a sale, we all know this does not wash with eBay.

If you have a SKU like RED/E1234/JUMPER/AGE10/KIDS you can use an ‘If cell x contains ‘kids’ return true’ type function in an extra column to indicate where VAT does not apply. You can do the same thing with the shipping locations as the country code is separated into a different column and CA uses standard ISO codes.

5. Fees

Most accountants will just want the month to month value of this. Download if from eBay/Amazon/whatever marketplace and hand it over.

The reason I have not included any specific excel formulas is that every account and business set up is very different and the formulas are not generic but specific. I will attempt soon enough to give generic examples of the excel formulas in as simple format as I can. Currently all my formulas have sheet references in them for other things so would not make sense just plonked here. I will find ‘non’ complicated ways to preform the same function, even if it does take a little manual work.

Stay tuned for the follow up WITH simple instructions included.

1. Highlighting duplicate shipping in your Channeladvisor Download