Sales Profit Summary Report

Sales Profit Summary Report

This report shows profit and additional financial summary for sales. Use this report to see an overview of the amount invoiced, paid, COGS, profit, markup, gross margin and FX gains/losses per order or per customer for a selected reporting period. The output does not show product details.

Prerequisites

 

 

Data from this report can be exported in Excel, PDF or RTF format.

Input:

Reporting Period

Allows the user to select the reporting period from a list of options or set a custom date range. The report is filtered by transaction dates (COGS within range) or invoice date (all COGS). (See NOTE below.)

Show COGS

Choose whether COGS are calculated from COGS within the date range or all COGS on the record.(See NOTE below.)

Layout

Set to the Default layout. Allows selection of saved layouts, if any.

Save Layout As

Allows saving a layout for potential future use.

Reset Layout

Resets the layout to the default layout or selected saved layout.

 

Select Apply Filter to set filter fields for the report and Update to refresh the output with any new changes. See Managing Cin7 Core Reports - Filtering Reports for more detailed information about using filters.

Note: Sales profit summary pulls entries based on transactions dates (if COGS within date range is selected) or invoice dates (if all COGS is selected.

Transaction dates include invoice authorization, ship tab authorization (COGS) and payments. For dropshipped products, COGS transactions are triggered when dropship purchase invoice is authorized. If you need COGS to have same date as the invoice, you need to change delivery date same as the invoice date (you can undo the ship tab to do this.)

If only COGS within date range is selected, a) you may see invoice dates from before the reporting period (with COGS transactions within the reporting period) and b) may see some 0 COGS values (if the ship date is out of the reporting period).

If All COGS is selected, the report will pull entries by invoice date, but COGS will be shown regardless of when the ship tab was authorized. If the ship tab has NOT yet been authorized, COGS will be 0.

Gift Card sales do not have COGS so will not show a margin or profit. When a gift card is redeemed, it will be used as payment, so it will be reflected as a payment amount.

Output:

The following output fields are available for this report. You may have to open the Configure Layout menu to add some of them to your report.

Fields

Customer

Customer name

Order #

Sale Order number identifying the sale

Invoice #

Invoice number identifying the sale invoice

Invoice Date

Invoice authorization date

Credit Note #

Credit note number if applicable

Credit Note Date

Credit note authorization date if applicable

Sales Representative

Name of the sales representative associated with the sale

Due

Payment amount due (Invoice - Paid)

Due Date

Date when the invoice is due for payment

Customer Tags

Comma-separated customer tags

Customer Additional Attribute 1-10

Values of customer additional attributes

Shipment Date

Date of the shipment

Shipping Address 1

Shipment address line 1

City

Shipment address city

State

Shipment address state

Country

Shipment address country

Postcode

Shipment address postcode

Ship By

Required by date from the sale document header

Data Headers

Ordered

Total of the sale order including tax in base currency

Sale

Total of the sale order excluding tax in base currency

Invoice

Total of invoice - Total of credit note including tax in base currency

Paid

Total Paid - Total Refunded in base currency

COGS

Cost of Goods Sold in base currency

Journals

Total of all Additional Transactions associated with the sale in base currency

Profit

Profit = Sale - COGS (in base currency)

Profit less Journals

Profit - Journals in base currency

Gross Margin

Gross Margin = (Sale - COGS) * 100 / Sale. In base currency. If Sale or COGS is 0 it will show as N/A.

Markup

Markup = Gross Margin / (1 - Gross Margin). In base currency. If Sale or COGS is 0 it will show as N/A.

FX Gain/Loss

Foreign Exchange gains and losses

 

Note: If you have added an Additional Attribute set to your sale process, the attributes will appear as additional report fields.

FAQs

Can you please tell me why a sale order is missing from this report?

Usually, this is an issue with the date filters. This report pulls sales according to their invoice date or transaction  date. Double-check that the reporting period covers the invoice/transaction date or try switching between All COGS and COGS within the date range. Transaction dates can be checked from the Logs & Attributes tab of a sale order.

I have just run the above stated report and it is showing invoices from many months ago.

These invoices are shown despite the invoice date being in another month is because of when the payment or COGS were assigned for the sale. Since sale profit summary report shows all transactions including payments entered within the report period selected, we suggest that you use Sales Overview report as this shows all data filtered by sale order date. This will reflect all the sales created within the report period.

Can we add location to this report?

It is not currently possible to add Location to the Sale Profit Summary. As a workaround, you can export Sale Profit Summary and another report and combine them with VLOOKUP.

The COGS transaction date does not match the ship date.

There will be times when the COGS journal will have a date other than the ship date, this can happen

  • when the invoice date of the purchase order is after ship date of the sales order

  • when a transaction involving the product in the order has an effective date after the ship date of the order.

 

Example: Consider stock used for an order that has been pulled from a stock adjustment with an effective date later than the ship date. To respect proper accounting, the system uses the date of the stock adjustment instead of the ship date to create a COGS entry.

Can I see a separate column for tax on this report?

Instead of Sales Profit Summary Report, please try Sales by Product Details Report, which has a separate tax header. Just remove the the SKU and product name and save it in a separate layout. Please see below sample:

  

Why am I getting different totals from Sale Overview Report and Profit Summary Report?

The two reports have different bases. Sales Overview Report sorts data by sale order date. Profit Summary Report is transaction based (authorization of invoice, COGS (ship date), etc.), so it contains all transactions within the date range no matter the order date. Some sales may fall out of the date range of one or the other of the reports.

Is there a way to show the marketplace order ID in the Sale Profit Summary report.  I'd like to link the Cin7 Core Invoice # with the Order ID# that marketplaces provide?

The marketplace ID is captured in Cin7 Core in the Reference field. Unfortunately, this field is not available for Sale Profit Summary report but you could combine the Sale Overview Report to the Sale Profit Summary Report with VLOOKUP to include the Customer Reference of the order.

Why am I getting negative profit in Sale Profit Summary?

Negative profit values can happen when the report is set to show only COGS within date range, when the invoice date falls outside of the range but the COGS date is within range. To avoid this, make sure the invoice date is within range, or select All COGS filter option.

When we generate the sales profit summary report, there are many invoices with no data under the ‘Invoice date’ field, why is this happening?

Unfortunately in this report, sales created via advanced sale module won't show Invoice dates. This report shows single line per new multiple fulfillments/invoices sale with aggregates sales/cogs data (single line per sale order number). We can provide invoice numbers as comma-separated list of text, but this won't work for invoice dates as dates cannot be text. The technical and development team are aware of this concern, however at this stage no technical solution has been found.

I have several sale orders that have additional costs after the sale order was created. So I created another purchase order for that additional cost for that sale order. How do I make sure that this additional cost will be deducted from that sale order's profit in the profit summary report?

If you have additional costs to a purchase and created a separate PO for that, then you will have to allocate that cost to the original purchase where the items are so that the cost of those items will be automatically updated. See Landed Cost Expense Distribution for more details. The COGS will then show the updated amount automatically.

Why is the margin showing different on the invoice screen to the Sale Profit Summary report?

The correct margin is on the report as it uses the actual purchase price data according to your costing method. The margin showing on the sales screen is based on average cost, which is based on all purchase prices of the product and is only as reference.

Still no answer to your question? Please do not hesitate to get in touch with Cin7 Core support.

Was this article helpful?

Have more questions? Submit a request