The Margin Enquiry report is a sales tool that allows you to analyze the revenue and profit margins for each product sold through completed Sales Orders. This report helps you evaluate your business's sales performance over specific time periods and can be used to calculate sales commissions for individual sales representatives.
To access the Margin Enquiry, navigate to Reports > Sales > Margin Enquiry. The report opens with the last month's date range already populated by default. The Run button generates the report with your selected filters applied; remember to click Run after making any filter changes to refresh your data. Whilst the Export button lets you download your report in PDF, CSV, or XLSX format.
Tutorial Videos
Tracking Sales Performance by source
Calculating Sales team commissions
Filtering the Margin Enquiry
The Margin Enquiry offers comprehensive filtering options to help you analyze specific aspects of your sales data.
Margin Enquiry filters | Description |
Date From | Set the start date to filter Sales Orders and Credits by their completed date. |
Date To | Set the end date to filter Sales Orders and Credits by their completed date. |
Sales Order Number | Filter for a specific Sales Order. |
Credit Note Number | Filter for a specific Credit Note. |
Sell Price Tier | Filter for Sales Orders by the Sell Price Tier applied. |
Warehouse | Use the multi-select drop-down menu filter Sales Orders and Credits by the warehouse assigned. |
Product Group | Filter for Sales Orders and Credits by the product's assigned Product Group. |
Product Brand | Filter for Sales Orders and Credits by the product's assigned Product Brand. |
Product Code | Filter by a specific product's code. |
Product Description | Filter by a specific product's description. |
Sales Group | Filter for Sales Orders based on the Sales Group assigned. When a custom Sales Group Field Label is used, the filter label will show the custom label. |
Customer Type | Use the drop-down menu to filter Sales Orders and Credits by the Customer Type assigned to the customer. |
Customer Code | Filter Sales Orders and Credits by the customer's code. |
Customer Name | Filter Sales Orders and Credits by the customer's name. |
Delivery Method | Use the drop-down menu to filter Sales Orders by the delivery method assigned to the order. |
Delivery Address | Filter for Sales Orders by the Delivery Address selected in the order. Only available when a customer code or name is selected. |
Sales Person | Use the drop-down menu to filter Sales Orders by the Sales Person assigned in the order. |
Understanding your Margin Enquiry
The Margin Enquiry displays your sales data in a comprehensive grid format, with most columns serving as clickable links that allow you to drill down into specific records for detailed analysis. This interactive design helps you quickly navigate between related data points and investigate specific transactions.
Margin Enquiry grid columns | Description |
Order No. | Displays the Sales Order or Credit's transaction number. |
Completed Date | Displays the date the transaction was completed. |
Warehouse | Displays the warehouse that was assigned to the Sales Order or Credit. |
Customer | Displays the Sales Order or Credit customer's name. |
Product Code | Displays the product's code. |
Product Description | Displays the product's description. |
Quantity | Displays the total number of units sold or credited. |
Cost | Displays the total stock value of the quantity sold, calculated as Quantity x ALC. |
Sell | Displays the total sale or credit value of the product, calculated as Quantity x Discount Price (or Credit Price). |
Charge | Displays the total charge value from the Sales Order |
Profit | Displays the profit calculated for the Sales Order, calculated as Sell - Cost. |
Margin | Displays the margin percentage calculated for the sale, calculated as (Profit/Sell) x 100%. |
Hidden Columns
The following column headers are also available to include on your Margin Enquiry and can be found in Show Toolbar > Hidden Columns.
Margin Enquiry hidden columns | Description |
Customer Code | Displays the Sales Order or Credit's customer's code. |
Customer Delivery Address Name | Displays the name of the customer's delivery address assigned to the Sales Order. |
Delivery Method | Displays the Delivery Method assigned to the Sales Order. |
Product Brand | Displays the Product Brand that the product is assigned. |
Product Group | Displays the Product Group the product is assigned. |
Product Sub Group | Displays the Product Sub Group the product is assigned. |
Sales Group | Displays the Sales Group assigned to the Sales Order. When a custom Sales Group Field Label is used, the grid column header will show the custom label. |
Sales Person | Displays the name and email address of the Sales Person assigned to the Sales Order. |
Type | Displays the Customer Type assigned to the Sales Order or Credit's customer. |
Units | Displays the product's base unit of measure. |
Enquiry totals
The Margin Enquiry's footer provides essential summary statistics to give you a quick overview of your filtered results'. These totals are particularly valuable for commission calculations, performance benchmarking, and understanding the overall financial impact of the sales data you're analyzing.
Margin Enquiry footer total | Description |
Transaction Count | The total number of Sales Order and Credit transactions listed in the results. |
Total Quantity | The sum total number of product units involved on the Sales Orders and Credits in the results. |
Total Profit | The combined profit of all the results. |
Overall Margin | The average margin percentage of all transactions in the results. |
Total Sell | The combined sale price for all products in the results. |
Total Charge | The sum of all charges in the results. |
Total Turnover | The total revenue for all Sales Orders. |
Total Cost | The sum of costs of all the results. |
Profit and margin calculations
The Margin Enquiry calculates tax-exclusive profit and margin percentages for each product on completed sales orders, providing crucial insights into your business's financial performance. These calculations use the product's actual sale price, including any discounts applied, at the time of the Invoice completing and the Average Landed Cost (ALC) at the time of Shipment dispatch, ensuring accuracy based on real transaction values.
Important:
Profit is calculated as: Sell Price - ALC
Margin percentage is calculated as: (Profit / Sell Price) x 100%
Example: A Sales Order is completed for one unit of a product; BOOKSHELF.
BOOKSHELF Sell Price: £2,000.
BOOKSHELF ALC at dispatch: £1,000.
Profit = Sell Price - ALC = £2,000 - £1,000 = £1,000.
Margin % = (Profit / Sell Price) x 100% = (£1,000 / £2,000) x 100% = 50%
Calculating Sales commissions
The Margin Enquiry report is an essential tool for accurately calculating sales commissions because it provides verified data from completed Sales Orders only. This ensures that commission calculations are based on actual finalized sales rather than pending or cancelled orders, giving you confidence in the accuracy of your commission payments.
Since the report displays both profit margins and sales values for each transaction, you can structure commissions based on various metrics such as total sales value, profit generated, or margin percentages achieved. The ability to filter by date ranges also makes it easy to calculate commissions for specific pay periods, whether weekly, monthly, or quarterly.
There are two effective approaches for commission calculations, depending on whether you need to analyze individual sales representatives or review all sales people collectively.
Individual Sales commissions
Go to Reports > Sales > Margin Enquiry.
Set your desired date range using Date From and Date To.
Select a specific sales person from the Sales Person drop-down.
Click Run to generate the filtered results.
Use the footer totals to calculate commission based on your company's structure.
Multiple sales commissions
Go to Reports > Sales > Margin Enquiry.
Set your desired date range using Date From and Date To.
Click Run without selecting a specific sales person.
Go to Show Toolbar > Hidden Columns.
Drag the Sales Person column into the grey header area above existing columns.
The report will now group all results by Sales Person.
Each group shows totals for Quantity, Cost, Sell Price, Charges, Profit, and Margin.
Click the + icon next to each group to expand and see individual order details.
Export the grouped results to CSV for further commission calculations.
Customising your view
Drag any column header into the grey area above the grid to group your data by that field. Add multiple grouping levels and use the +/- icons to expand or collapse sections. This is particularly useful for analyzing data by supplier, product group, or period.
Reorder columns by dragging headers, resize columns by dragging borders, and sort data by clicking column headers - first click for descending order, second click for ascending order.
To add a Hidden Column to the Margin Enquiry's grid:
Go to Reports > Sales > Margin Enquiry.
Click Show Toolbar.
Click Hidden Columns.
Drag a column header from the Hidden Columns list and drop it into the grid.
To remove a Hidden Column from the Margin Enquiry's grid:
Go to Reports > Sales > Margin Enquiry.
Click Show Toolbar.
Click Hidden Columns.
Drag a column header from the grid into the Hidden Columns list.
Grid Layouts
After customising your column layout, save it for future use:
Click on the Show Toolbar tab.
Select New.
Enter a unique name for the new layout.
Click Add.
The layout will now be saved to your user profile, only available to select from the Show Toolbar's Grid Layout drop-down menu.
Edits can be made to your custom grid layout at any time; just click Save in the Show Toolbar options after you've made the necessary adjustments. Similarly, custom grid layouts can be removed by clicking Delete in the Show Toolbar tab.
Default grid layouts
Initially, whenever you navigate to the Margin Enquiry, the default grid layout "None" will be displayed.
To make one of your custom grid layouts the default view:
Click on the Show Toolbar tab.
Select the layout from the Grid Layout drop-down menu
Tick the Default checkbox.
Click Save.
Share grid layouts
If you've created a grid layout that other users in your account would benefit from using, you can make it available to their user profiles too:
Click on the Show Toolbar tab.
Select the layout from the Grid Layout drop-down menu
Tick the Shared checkbox.
Click Save.
The layout will then be available to select from the Show Toolbar > Grid Layout menu when other users open the Margin Enquiry page.
Shared layouts cannot be a default for another user's profile, but they can save the shared layout as a new layout in their own profile, then set that as their default. Any edits you make to your shared layout will not apply to their saved default version.