Consignment item reconciliation Excel template

Actual file previews

The images below are the actual data entry and executive summary pages of the workbook to be downloaded.

Step by step usage

1. Examine the example and prepare the blank file

   Open unique row for dealer, product and deal period. Enter the quantities as the cumulative total for that row. Return is the quantity physically taken back from the dealer. The unit price is the agreed amount for your share, not the shelf price. If there is no collection, enter 0. Use the yellow input fields on the tracking page; Do not change the blue formula fields. The sample sheet is filled with fictitious records and does not count towards the actual work total.

2. Write actual records in the yellow fields

   The remaining quantity at the dealer is delivered - sold - returned. Sales receivable is units sold × agreed unit price, open collection is sales receivable − collection. If the total of sales and returns exceeds the delivery or the payment exceeds the receivables, a control warning occurs. Instead of adding up different product quantities, the summary shows the remaining lines of the product at the dealer and the open TL receivable. An empty mandatory field is distinguished from a real zero. Correct missing or incorrect records in the control column; Since these lines are excluded from the summary, do not use the total as a final report until the problem is resolved.

3. Fix control alerts and review summary

   Compare dealer count to remaining quantity. Reconcile items sold but not paid for.

What does this Excel file solve?

Track consignment products at the dealership by delivered, sold and returned quantities. Calculate the remaining amount and the open receivables arising from the sale. Open unique row for dealer, product and deal period. Enter the quantities as the cumulative total for that row. Return is the quantity physically taken back from the dealer. The unit price is the agreed amount for your share, not the shelf price. If there is no collection, enter 0. Use the yellow input fields on the tracking page; Do not change the blue formula fields. The sample sheet is filled with fictitious records and does not count towards the actual work total.

What's inside the file?

Quantity remaining at the dealer, Receivable due to sales, Remaining collection. The file contains sample rows, canned formulas, data validation fields, risk colorings, and executive summary.

Calculation and control logic

The remaining quantity at the dealer is delivered - sold - returned. Sales receivable is units sold × agreed unit price, open collection is sales receivable − collection. If the total of sales and returns exceeds the delivery or the payment exceeds the receivables, a control warning occurs. Instead of adding up different product quantities, the summary shows the remaining lines of the product at the dealer and the open TL receivable. An empty mandatory field is distinguished from a real zero. Correct missing or incorrect records in the control column; Since these lines are excluded from the summary, do not use the total as a final report until the problem is resolved.

Worked example: Consignment item reconciliation: calculation example

50 units delivered. 20 sales, 5 returns. 100 TL unit price, 1,000 TL collection. Result: 25 units left at the dealer; The sales receivable will be 2,000 TL and the open collection will be 1,000 TL.

Decisions that can be taken as a result of this

Compare dealer count to remaining quantity. Reconcile items sold but not paid for

Common usage mistakes

Using shelf price instead of supplier share. Deducting the return quantity from the sales and counting it twice. Using the summary total as the final result without correcting the lines with control warnings

Limits and proper use

It is not a shipping document, consignment agreement or official inventory accounting. Separately reconcile loss and damage items. It keeps a cumulative period summary, not a sales history. The file is prepared for 150 record lines. The parent summary includes all valid rows, regardless of filters. When capacity is full, open a new period copy; additional rows require the summary and validation ranges to be updated as well. There are no macros, external data connections or automatic notifications.

Compatibility and quality control

Two pages of this file were visually inspected; Formulas were recalculated and checked in empty, sample and incorrect entry scenarios. There was no separate device testing of the Excel desktop application. .xlsx without macro; formulas and drop-down lists are included in the file. The tracking page includes 150 preset record lines and a standalone Sample page. Prepared for desktop Excel; Check views and validations in other spreadsheet applications.

Scope of this file

This independent file is prepared for consignment product reconciliation. It's not that Shiftmate automates this industry workflow; The application focuses on team, shift and timesheet management.

Relevant official and technical resources

• Microsoft: SUMIFS function

Frequently asked questions