How to Create a Custom Report for Consignment

Here is a simple report that you can export from SPOS to help with paying out consignment vendors. 

Setting Up Products as Consignment

The easiest way to flag a product as Consignment is to set up a department named "Consignment" and assign this to the products when you add them to SPOS. If you prefer to keep your departments as categories, you can also flag these products using tags (see option B Below). 

Option A) Departments.

1) Go to the Products Dashboard in the SPOS Web App and select Departments.

2) Click on  to add your new "Consignment" department. Note: You can add Consignment as a Main Department with other Sub Departments for Categories. Or you can have it be a Sub-Department. Neither of these options will impact your reporting ability.

3) Select  to save your new department. And click to go back to the Product Dashboard.

4) From the Product Dashboard, you can update your existing products with the new category. Search for the brand name or product name in the search bar and hit enter to filter. Then click the checkbox in the title line to select all. 

5) Click the change department button at the bottom of the screen to update all products at once. Select your new department from the drop down and click save.

6) For any new Consignment brands added to the SPOS, simply select the department at the from the "Add Product" Page. 

Option B) Tags: 

1) Go to the Product Dashboard and look for the product that you want to tag as Consignment. Click the Green Button to Edit. 

2) Type "Consignment" into the Tags field and click Enter to apply. Scroll to the bottom of the page and click .

3) For all new product entered into the system, enter the consignment tag when creating the product. 

Reporting on Products as Consignment

1) Go to the sales dashboard in the SPOS Web App and filter by your desired time range from the  "Start Date --> End Date" at the top of the screen and select 

2) Select the checkbox in the title bar to select all. Then scroll to the bottom of the page and select 

3) OPTIONAL (This is only necessary if you used tags instead of departments to set up consignment, or if you need to have the original sales price). Go to the Products Dashboard and repeat step 2 above. Click the checkbox in the title bar to select all and then scroll to the bottom to click 

IF YOU ONLY EXPORTED SALES: Open the export in excel to filter by Department - Consignment. And you will be able to view your report.

IF YOU EXPORTED BOTH CONTINUE: 

1) Open both excel documents and merge into one sheet by selecting the bottom tab of the excel and dragging it into the other sheet. 

2) The Sales export will have the following header: 

The Products export will look like this: 

Rename the tabs at the bottom SALES and PRODUCTS accordingly. 

From the sales tab, enter a column in front of the effective price to pull in the original sales price using a VLOOKUP formula. Copy this formula into the first row of the column: =VLOOKUP(F2,PRODUCTS!$B$1:$0$100000,5,FALSE) and click enter. 

Double click in the righthand corner of the box (K1 in example above) to copy the formula for each row of the column. 

3) Enter a row after Brand to pull in the tags. Enter the formula =VLOOKUP(F2,PRODUCTS!$B$1:$0$100000,12,FALSE) into the first row and then copy down. 

From there you should have a report that you can filter based on your preferences! 

Here is a quick video for visual instructions: