The Summaries module in Tradebox allows users to summarise sales from a specific channel over a given period and provides a full breakdown of the VAT on each sale group within the summary.
By running periodic summary entries (e.g. daily or monthly) you can amalgamate hundreds or thousands of order item lines down into a handful of transaction lines by grouping like with like. If you run your bookkeeping as 'financials-only', you can use summaries to create as few ledger transactions as possible while keeping a trail back to the original orders in Tradebox.
A summary groups your chosen date range of orders by sales channel. Within a summary, order lines are grouped by payment method and item tax code, and display sales and carriage values separately.
For example, you could summarise webstore orders for July and you'd see 0% VAT Paypal sales as one line, 20% VAT Paypal sales as another, 20% VAT Credit/Debit Card sales as another, etc.
If you're using our accounts integration, Tradebox can post the summaries through to your accounts package (Sage 50 posts SI transactions directly to your ledgers, Xero posts one sales invoice per summary), which is a great way for high-volume sellers to still have accurate bookkeeping records of their online sales for financial reporting and an MTD-compliant VAT return.
There are some limitations with summaries compared to processing with traditional one-in/one-out Sage or Xero posting. There's no means for Tradebox to adjust stock in your accounts package if you process with summaries, you won't get a printable customer invoice in your accounts package (although you can print a customer invoice from within Tradebox), and any reporting in your accounts package will be per-summary line rather than based on distinct orders.
How is a summary calculated?
Each summary line is calculated as:
Total value of items from orders or carriage from orders:
- That have been downloaded through the selected sales channel
- Where the order date is within the selected date range
- That share a payment method
- That share a tax code
Total value of items or carriage from sales credits:
- Raised via fully- or part-refunding orders that were downloaded through the selected sales channel. This refers to Tradebox's refund procedure, click here for a guide.
- Where the credit date is within the selected date range, regardless of the original order date
- That share a tax code
VAT Calculation Method
VAT on summary entries can be calculated as either a percentage of the item prices, or as a percentage of the total of each order.
Different systems calculate VAT slightly differently where there are multiple items sold; there's no strict right or wrong answer whether a net/tax split should be calculated on the order total or per unit sold. Rounding of part-pence VAT amounts can lead to discrepancies, so we offer two different calculation methods.
This is controlled by the Calculate VAT On setting, within Configuration > Accounting. There are two options to calculate VAT on either:
- Items (default method): the net/VAT split is calculated at the level of each unit on each order. Where this breaks into a part-pence split per unit, VAT is rounded up and net is rounded down. These amounts are then multiplied by quantity, and the resulting figures are totalled to make the summary lines.
- Total: the line total of each order is split into net and VAT. If this figure is a part-pence split, VAT is rounded up and net is rounded down.
What is not included in the summary?
Orders with a Query status will always be excluded from summaries, as the financials or tax rate on the order may be recalculated at the point when the query is resolved. Best practice is to make sure you have resolved any Query orders within the date range you're summarising, prior to running the summary.
Unpaid orders, orders on hold, and refunds and credit notes are included by default. There's an option to exclude each of these different order types in the criteria screen that pops up as you create a new summary.
Any individual order can also be opted out by de-selecting it during the summary creation process. Any unsummarised orders can be summarised at a later date by creating a second summary for the same date range. Each order will only be included in a summary once, so there's no risk of duplicating values by covering the same date range multiple times.
Creating a Summary
Click Summaries and you'll be taken to the summaries list. If it's your first time doing this, the screen will be empty. Later, you'll see a list of the summaries you've previously created, and you can always double-click to drill into the detailed view to see the breakdown of summary lines or individual order lines.
To create a summary, click New.
Choose the Sales Channel you're running the summary for, and the dates From and To. The dates are inclusive, so to run a daily summary you'd set both From and To to the same date.
Show Values In may be greyed out; it will be if you've linked your chosen channel to an accounts package, as you'll already have set the Posting Currency within the accounts link of each channel. If you don't have an accounts link set up, you can choose to view summaries for foreign currency channels in their native currency, or in GBP based on the exchange rate applied at the point of download.
Unpaid orders are included by default. Tick Exclude Unpaid Orders to remove them from the summary.
Once you've set your criteria, click OK. Tradebox will then start to poll all the orders that have been downloaded, to segregate those that match the criteria. Each order can only appear on a summary once, so if you set criteria that overlaps with a previous summary, any already-summarised orders are automatically excluded.
Unsummarised prior orders
If there are any orders within your Tradebox data that are dated earlier than the From date of your summary and haven't already been on an earlier summary, Tradebox will prompt to ask if you want to include these earlier orders. The assumption is that you want every order to appear on a summary at some point and this check makes sure any late entries are accounted for (e.g. a November order wasn't downloaded until after you've ran your November summary; the prior-date check on your December summary will pick it up).
You can choose to include these orders in the current summary. Or if you click No, the earlier orders will remain unsummarised; they're still valid to appear on any subsequent summaries you run. If you choose yes, you can still opt individual orders in or out in the next step.
Confirm orders to include
You'll now see the orders that are set to be included in the summary. By default, they'll all be included. If you wish to exclude any orders from this summary, untick them in the Select column on the left. When you're happy with the selection, click Calculate Summary and Tradebox will total up the order values.
Once the calculation's finished, you'll see a preview of the summary.
Each line represents a grouping of order elements by:
- Payment method
- Tax code
- Income type: sales or carriage
Nothing's finalised at this point. If you want to see a breakdown of where the figures are coming from, click View Breakdown and you'll see a CSV spreadsheet of the calculations. Remember we're now working with the item lines and carriage elements individually so it's not unusual to see the same order number multiple times. You can filter on the Payment Method, Tax Code (1=zero rated, 2=standard, 3=EU B2B) and Description (Carriage or non-carriage) fields of the CSV to get an accurate per-summary-line breakdown.
When you're happy with the summary, click Mark as Summarised and the summary will be finalised.
You'll be dropped back at the summaries list. If you're posting the summary to an accounts package and you've already set up your accounts link, double-click the summary to drill back into it and then click the Post To Accounts button to send it to Sage or Xero.
Completed Summaries: viewing the data and posting a summary to your Accounts package
When you click Summaries, you'll see the summary list. Each summary has a reference number, and a description made up of the summary ref, channel name and date range.
The list view will also show the total net, VAT and gross amounts of the summary. The Posted column shows whether the entry has been posted to an accounts package, and if so the Posted Date column will show the date this was done.
To drill into a summary, double-click on it in the list or highlight it and click View.
The overview shows the summary lines, which are order elements grouped per payment method, tax code and income type.
To trace back to the individual orders (and order lines) that make up the summary, click View Breakdown and you'll see a CSV spreadsheet of the elements:
- Type - SO (Sales Order) or SC (Sales Credit from refund raised in Tradebox)
- Order Date
- Channel Name
- Tradebox Order Number
- Marketplace Order Number
- Payment Method
- Tax Code
- Net Amount
- Tax Amount
- Gross Amount
This CSV file is by default saved within Tradebox's Summaries subfolder within Tradebox's Data Folder. If you're doing any further manipulation of this data, we strongly recommend first using Save As to save a copy somewhere else such as your Documents folder or your Desktop rather than changing the original breakdown.
Using the CSV, you can filter on the Payment Method, Tax Code (1=zero rated, 2=standard, 3=EU B2B) and Description (Carriage or non-carriage) fields to get an accurate per-summary-line breakdown.
Posting Summaries to your accounts package
When viewing any summary, if an accounts link has been set up for that channel to Sage or Xero, you'll also see a Post to Accounts button. When you select this, Tradebox will post the summary information to your accounts package using the preferences you set up in your accounts link. If you haven't set up your accounts link yet, you'll need to do this first: click here for our guide for Sage or here for Xero.
Summary postings in Sage
In Sage, summaries will be created as outstanding SI (batch invoice) transactions, which are visible in the customer account Activity and the Transactions screen. You'll then need to pay these off using the Customer Receipts function. The Reference on each SI will show as 'TBx' where x is the Tradebox summary number. Sage will report the audit trail transaction number back to Tradebox which will be stored as the Trans. No against each summary line. So whether you're viewing the posting from Tradebox or from Sage you'll have easy traceability.
For foreign currency sales, the accounts link will have been set up as either foreign-to-foreign (which requires the Foreign Trader module in Sage) or as foreign-to-GBP. You can check this by opening the relevant sales channel and clicking on Sage 50 > Postings; the Sales Currency shows the currency orders are downloaded in and Posting Currency shows the currency that will be used for the summary transactions in Sage.
- If you're set foreign-to-foreign, the exchange rate used is shown in the summary screen at the point you click Post To Accounts, and you can edit this before posting. This rate is passed to Sage with the transactions and you'll later be able to see it as the Exchange Rate against the SIs in the Transactions screen. We recommend you use this same exchange rate when raising the Customer Receipts in Sage.
- If you're set foreign-to-GBP, the exchange rate used will be the one tied to each order when the order was downloaded. This is taken from Configuration > Currencies within Tradebox; the default setting is to automatically update from an online service prior to every download so it should reflect the real-life rate as it was when the order downloaded to Tradebox. You won't see this in Sage, as the conversion takes place entirely within Tradebox.
Summary postings in Xero
In Xero, each summary creates a single Awaiting Payment sales invoice, with one item line per line in the summary. The Reference on an invoice created from a Tradebox summary entry will show as 'TB_Summary_x' where x is the Tradebox summary number. The Xero invoice number will report back to Tradebox and be stored as the Trans. No against each line in the summary. So whether you're viewing the posting from Tradebox or from Xero you'll have easy traceability.
For foreign currency sales, the accounts link will have been set up as either foreign-to-foreign or as foreign-to-GBP. You can check this by opening the relevant sales channel and clicking on Xero > Postings; the Sales Currency shows the currency orders are downloaded in and Posting Currency shows the currency that will be used for the summary transactions in Xero.
- If you're set foreign-to-foreign, the order values are passed natively to Xero. Tradebox tracks and stores an at-the-time exchange rate against each order and this GBP equivalent is visible within Tradebox but is not passed to Xero.
- If you're set foreign-to-GBP, the exchange rate used will be the one tied to each order when the order was downloaded. This is taken from Configuration > Currencies within Tradebox; the default setting is to automatically update from an online service prior to every download so it should reflect the real-life rate as it was when the order downloaded to Tradebox. You won't see this in Xero, as the conversion takes place entirely within Tradebox.