OverviewThe below guide provides support on understanding Sale or Return/Direct to Customer Sale or Return Data Reports and Coupa Invoice Lines How to Understand Your Weekly SOR or D2C Excel Sale or Return Data Report?Once you have downloaded your Excel weekly sale or return report you will be able to perform any reviews you deem necessary. We recommend that you apply a filter on this report if you wish to view transactions by a specific location (shop,) or invoice line, or transaction type. Please note that your Coupa weekly sale or return data may differ from your EDI due to timing differences of transactions being recorded. Transactions which occur on the last day of the weekly reporting period may be missing from your Coupa weekly sale or return report but show on your EDI report. These will appear on the following week’s Coupa report instead. Should these transactions still not appear on your following week’s Coupa report, please visit our SOR FAQs page, which can be found here, for next steps. This table explains what the headings on the report mean: | Column | Description | | (A) Week Ending Date | The week ending date associated with your sales data and issued purchase order. | | (B) Day Date | The specific day which the transaction was recorded within the week ending date week. | | (C) Ordering Supplier Name | Your supplier account name. | | (D) Ordering Supplier Number | The ordering number registered to your supplier account | | (E) Branch | The location code number for the John Lewis location where the transaction was recorded. | | (F) Branch Short Name | The name for the John Lewis location where the sale was facilitated. | | (G) EAN Product Code | The European Article Number barcode for the product covered in the transaction. | | (H) Product Code | The product code held on the John Lewis systems for the product covered in the transaction. | | (I) Product_Description | The product description for the item involved in the transaction. | | (J) Total Sales Value | The total sales amount (including VAT if applicable) for this transaction. | | (K) Sales Units | The number of units involved in the transaction for this item. | | (L) Returns Value | If the transaction is a return this will show the total sales value of the items returned under this product. | | (M) JLP Sales Margin | The JLP sales margin for the product involved in the transaction. This total will match your SPTI % rate.
To calculate the SPTI deduction rate percentage, please use the formula (Sale value xVAT) - JLP Cost of sales) / Total Sales Value.
JLP deduction = Total Gross sales x SPTI deduction rate
E.g. Sales Value xVAT (80) minus the Cost of sales (42) which is 38 divided by the Total Sales Value (92) then your SPTI rate as a percentage would be 41%. | | (N) JLP Cost of Sales | The JLP Cost of Sales for the product involved in the transaction.This value will contribute towards the total PO line value. | | (O) Dissection | The dissection number listed on your account which relates to the product involved in the transaction. | | (P) Dissection Name | The dissection name stated on the internal John Lewis systems for dissection number stated in column O. | | (Q) Sales Value | The total sales value (including VAT if applicable) for this sale. This field will show as 0.0000 if this transaction relates to a return rather than a sale. | | (R) Returns Units | The total number of units involved in the return transaction for this item. This field will show as 0.0000 if this transaction relates to a sale rather than a return. | | (S) VAT Rate | The VAT rate (%) registered for this transaction. | | (T) Sales Value xVAT | The sales value for the transaction, excluding VAT if applicable. | | (U) VAT in Gross Sales | The total VAT amount recorded for this transaction, this is excluded from the total in column T above. | How to Understand Your Weekly SOR or D2C PO Invoice Lines? Once you have selected the purchase order you wish to view, you will see a summative breakdown of each location and tax code for transactions occurring within the week ending date period on the Excel report. Your POs will show as positive and negative. All sale transactions will show as a positive order and all return transactions will show as a negative order. Note: You are able to search for specific PO lines in the search bar 
| Reference | Description | | A | The John Lewis branch number which all transactions under this PO took place, this relates to column E on your full Excel sale or return report. | | B | The VAT tax code registered to the invoice lines, this should show one of the below
depending on the VAT registration held on your supplier account.
XXXX_Standard_XX (20.0% VAT)
XXXX_Reduced_XX (5.0% VAT)
XXXX_Zero_XX (0.0% VAT) | | C | The SPTI rate in % rate which is held on your supplier account.
To calculate the SPTI deduction rate percentage, please use the formula (Sale value (xVAT) - JLP Cost of sales) / Total Sales.
JLP deduction = Total Gross sales x SPTI deduction rate. | | D | The total quantity allocated to the PO invoice line raised for this John Lewis branch in the week ending period. This number should match the monetary amount from all entries from the JLP Cost column (row N) on your full Excel sale or return report.
The PO line raised QTY matches the monetary value total for your account currency.
Please do not amend this value if you are submitting an invoice. | | E | This price field shows as 1.00, this refers to the 1x PO line raised, this does not relate to the unit price for items sold. Please do not amend this value if you are submitting an invoice. | | F | The total payment being paid to you for transactions which took place on the week ending period at this John Lewis branch code. This includes all entries from the JLP Cost column (row N) on your full Excel sales data report. The PO line raised QTY matches this monetary total for your account currency. | | G | This figure shows how much of the total invoice line value allocated on the PO have been invoices against. If you are not self-billing and this shows as 0.00 you will need to convert this PO to an invoice. Once this invoice has been submitted this figure will update to show the PO line amount invoiced against. If you are registered as a self-billing
supplier you will not need to convert this purchase order to an invoice with this automatically completed. | | H | This date shows the latest date which this purchase order needs to be converted to an invoice and submitted to us to ensure the scheduled payment date aligns with your payment terms. If you are not a self-billing supplier and your invoice is not submitted against this PO by the date stated you will not receive payment within your payment
terms. If you are registered as a self-billing supplier you will not need to convert this purchase order to an invoice with this automatically completed. | What Happens Next?If you are not a self-billing supplier, and you need to convert this weekly PO to an invoice, this can be completed by following our ‘Converting a SOR or D2C PO into an invoice guide’. Note: Only positive orders (sales transactions) should be converted into an invoice, any negative order (return transactions) that is converted into an invoice will be rejected. If you have any questions about this sales data report or purchase order you can visit our FAQs If the above article has not answered your question and you require further assistance then please submit a help request below. |