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 or accounts package 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.
Sales are grouped by:
- Income Type - Sale or Carriage
- Payment Method - PayPal, Sage Pay etc
- Tax Code - Standard Rated, Zero Rated etc
So, for example, if you had both standard 20% VAT and zero rated sales, were taking payments using both PayPal and Visa and were charging carriage to your sales, a summary would be broken down as follows:
|Income Type||Payment Method||Tax Code||Net||Tax||Gross|
If you use more or fewer payment methods than this, or if you have more or fewer tax treatments then your number of summary lines will reflect this based on the orders handled in the date range. If for example, we don't take payment for any Zero Rated goods by PayPal in the date range specified, the summary grid above would have one fewer sale line and one fewer carriage line.
This summary can then be entered into an accounts package manually, or if an accounts link exists with a supported accounts package then Tradebox can pass the summary entry directly into your ledgers. You can also export the summary data to an Excel spreadsheet for further analysis. Every transaction contained within a summary can be viewed and/or exported in a CSV file.
All orders contained within the summary are marked as 'summarised' and the Tradebox summary number is added to the order. Once an order has been included in a summary it cannot be included in another summary, even if another summary is later attempted for the same date range. The idea is that every order will be included in a summary once, and only once.
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
- 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 on either Item Prices or on Total Prices. This setting is established in Configuration > Preferences > Summary Entries. 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 results can lead to discrepancies, so we offer two different calculation methods.
Item Price VAT calculation (default method) reflects the total combined VAT charged on each transaction within all transactions in the summary but may not reflect the exact VAT level ratio because of rounding on large volumes of data.
Total Price calculation will reflect the exact VAT level ratio of the combined transactions in the summary but may not reflect the the exact VAT charged on each transaction combined.
Creating a Summary
Before creating your first summary, establish a VAT calculation method in Maintenance > Preferences > Summary Entries.
To create a summary, go to Summaries > New.
Choose a channel from the Sales Channel drop down menu and then choose the date range by choosing a date in both the Order Date From and Order Date To drop down fields then choose OK. Tradebox will then generate a screen of all orders matching the submitted criteria, that have not already been included in another summary. This can take some time depending upon the volume of sales within the given range.
Note - each summary entry is for a single sales channel only, there is no option to amalgamate multiple sales channels into the same summary.
So you never miss an order in the event of late entries or disputes, Tradebox will also check for older orders beyond the date range specified that have never previously appeared on a summary. If any are found, Tradebox will ask whether you'd like these to be included in this summary. Click No to omit all older entries. Click Yes and all older unsummarised entries will be included by default, though you can opt in and out at the individual order level in the next step.
By default, all orders in the range are are opted in to be summarised, although individual orders can be opted out of the summary by unticking the Select box next to each order. When you're happy to create a summary of the included orders, click on the Calculate Summary button. Again, this may take some time depending upon how many orders are within the range. This will create the following summary screen:
This screen contains all of the individual summary entries based upon Income Type (Sales or Carriage) Payment Method and Tax Code; essentially taking many order item lines and amalgamating them into as few transactions lines as possible. The screen also displays the Net, Tax and Gross values for each entry.
If there's anything about the summary you'd like to change, you can use the Back button to go back to the previous screen. This is the last chance you'll have to make amendments to any part of this summary before it's finalised.
To create the summary, click Mark as Summarised. This action will complete the summary entry and write the Tradebox summary number to every order contained within the range, ensuring these orders cannot be contained in any future summary. This is not reversible; a completed summary can't be amended later. When finished, a message box will pop to let you know the summary has been completed; click OK. You'll then be dropped back at the list of Summaries.
Completed Summaries: viewing the data and posting to Accounts packages
The Summaries module displays a single entry for each summary that has been created. Each summary has a summary reference number and a description that is made up of the:
- Ref Number
- Channel Name
- Date Range
The overview entry will also display:
- the total Net amount
- the total VAT amount
- the total Gross amount
- an indication of whether the entry has been posted to an accounts package
- the date the entry was posted to an accounts package
To drill into the summary information simply double click on the entry, or select it from the list and choose the View button.
The summary screen displays a full breakdown of each summarised entry. Every transaction within the summary can be sent to a CSV file by choosing the View Breakdown button. This will create a line in a CSV file for every transaction in every order contained in the summary and will display the following information:
- Type - SO (Sales Order) or SC (Sales Credit from refund)
- Order Date
- Channel Name
- Tradebox Order Number
- Marketplace Order Number
- Payment Method
- Tax Code
- Net Amount
- Tax Amount
- Gross Amount
This file is by default saved within Tradebox's Summaries folder, alongside the 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.
If a Sage accounts link or a Xero accounts link has been set up in the sales channel and configured for summary entries, this screen will also display a Post to Accounts button. When this is pressed the summary data will be passed to the accounts package that Tradebox is integrated with.
In Sage, these create 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.
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 each sales channel and clicking on the Sage 50 tab > Postings tab; Sales Currency shows the currency orders are downloaded in and Sage 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 SI's 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 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.
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.
For foreign currency sales, the accounts link will have been set up as foreign-to-GBP. The exchange rate used will be the one tied to each order when the order 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.