Digital record keeping ? Retailers need to be a pivot table expert and have a maths degree to calculate the figures first!

The internet has opened up the whole world as potential customers for those in retail. People are still buying goods but from online sources instead of physical stores. There are many “platforms” through which retailers can promote their goods – but the accounting aspects can be a real nightmare!

Let’s take a look at Etsy. A nice summary is provided.

Plug these figures in to try to agree the opening and closing balances on the statement and it differs. Ah….. they withdrew some money to their bank account. The accounting to date shows

Credit to Sales                        3426.18

Debit to Bank      3426.18         

So plug these figures in….and there is still a difference between opening and closing balances.

It is possible to export the monthly transactions to a csv file. It was a good month and there were 828 lines of transactions, but there were no running totals in the output as there are on the daily listings. In Excel Auto-total the three columns – and sales column is £7471.13 – which differs from the £7490.50 and total fees and taxes is £766.27 – different from the £425.45 in the graph.

Time to create a pivot table in Excel.

The £424.45 in the graph is clearly the sum of the £38.88 and £385.57 fees. However, it is not possible to reconcile the sales figure to the £7490.50 in the graph. It was therefore necessary to try a different month where the transactions were much smaller.  This revealed the graph entitled Sales less credits only includes the sales less the transaction fees – refunds are ignored!!

If we accept that is the case, this month the total is £7529.12 – which does not agree to the £7490.50 in the graph. Some head scratching and further manipulation of the Excel data revealed that the sales column total of 7471.13 less the fees and taxes of £766.27 does not equal the Net column figure - £6704.86 is the calculated figure – yet the Excel sheet shows £6666.24 – a difference of £38.62 – which so happens to be the difference between our sales figures referred to above.

A further column was calculated in the Excel sheet to sum the gross sale less expenses less the net and this identified 6 or so rows which did not “cross cast”. One would expect to see £138.85 but it is £128.91 – a difference of £9.44.

Finding the original transaction in the detailed sales transaction and drilling down revealed this £9.94 was a Minnesota state sales tax. These are not shown anywhere in the csv files!

The final summary is therefore:-

But that’s not the end of the story! Sales of £7471.13 need posting to the accounts software. However, there is no way of knowing whether these sales are UK or EU and therefore liable to VAT or whether they are sales elsewhere in the world! We could look at the delivery details for each sale - but there are 244 sales in this month alone!

And to further complicate matters – the charges levied are subject to Reverse Charge VAT, so needs to be put through the accounts and marked as paid. The invoice shows £754.21 – but the total fees are £424.45 + £341.82 = £766.27 – the difference being due to the conversion of Euros to sterling at the invoice date.

HMRC – take note. This is the “real world” in which we live. MTD is not easy!!