Follow

New Year's Resolution: Master Year End Reporting

Year_End_Reporting.jpg

The end of the year is a usually a time of reflections and resolutions, and you're probably thinking about the bigger picture for your business. How did your winery do this year? Is there anything you can work towards next year? 

With vinSUITE, you can use reports to find out metrics about how your winery is doing as a profitable business. This webinar reviews some key features of the Report Builder and explains how to get useful information out of our Excel reports, including:

  • Breakdown sales by state, sales associate, order type and more!
  • Discover your best-sellers and rank your products by sales. 
  • Learn more about your customers spending habits and find your biggest spenders.

Make it your end of year goal to master a few reports, and your New Year's resolution to run those reports every month! 

 

Breakdown your Sales Reports 

The best way to avoid getting overwhelmed with massive spreadsheets is to break down the data into digestible chunks that can help you learn something your sales. You can utilize the Sales Summary Report to:

  • Display order totals and the total amount collected from each order type (POS, Club, Store, etc.)
  • Sort sales data to find taxes and freight collected for each state.
  • Find out which Sales Associates had the most sales.

Please follow the steps below:

Create Sales Summary Report

1. In the vinSUITE admin panel, expand the Reports section, then click 'Report Builder'.

Dashboard.jpg

2. On the Create Report page, enter a name for your report.

3.  Select a Report Type to house this report. This is an optional but recommended step. 

Tip: Think of Report Types like folders to organize your reports. 

4. Select a Report Base that will serve as the basic fields in your report. For this case, you would select the Sales Summary Report Base.  

Note: The Sales Summary Report is the easiest Report Base to use if you just want to find out order totals, and not every product for each order. The end report will record each order in a single row in your Excel spreadsheet. 

ReportBuilderStage1.jpg

5. Click the 'Next' button when you have finished stage 1.

6.  On the Select Fields page, specify the fields you want to be include in your report. 

ReportBuilderStage2Fields__.jpg

Fields to Select From On the left side of the screen  These are the fields that are available for you to add to the report.
Chosen Sorted Fields On the right side of the screen   These are the fields that you have included in the report.

 

Note: Simply drag and drop the arrow icon of the chosen field to reorganize the lists. Be sure to include the Order Type and the Sales Associate fields in your report.  

ReportBuilderStage2.D_Dsnag.jpg

7. Click the 'Next' button when you have finished stage 2.

8.  On the Filter Data page, specify the filters you want to be include in your report. 

Tip: Filters can place certain restrictions on your report so you only get the specific information you want (like date ranges or statuses). Only filters listed under Current Filters will be used for the report. For more information on filters, please review the Report Filters page. 

Note: Verify that the Order Status and Order Date Completed filters are added to your Current Filters. 

  • Order Status: Should Equal (=) Completed Order
    • This means you are filtering your results to display only completed orders.
  • Order Date Completed: Should be Greater Than or Equal to your chosen date (01/01/2018)
    • This means you are filtering your results based on the date entered. The chosen date and any days after that will be included in your report.  

9) Add a new filter for Order Date Completed. Set the operator to 'Less than or Equal' and the date to 12/31/2018. Click the "Add Filter" button. 

Note: You have now created a date range, from 01/01/2018 to 12/31/2018, for your report results. You should always use the Greater than or Equal filter for the beginning of the date range and the Less than or Equal filter for the end of the date range.

Tip: If you are experiencing errors or slowness running the report, it is recommended that you separate the dates into 3-6 month date ranges. You can then combine the results of multiple reports into a single spreadsheet in Excel for your annual report. 

 

10. Click the 'Next' button when you have finished stage 3.

11. On the Preview & Complete page, review your report and click the 'Save and Run' button. This will generate your report as an Excel spreadsheet. 

12. The report will automatically download to your computer. The downloaded file will appear in the bottom left corner of the browser or your downloads folder (wherever your downloads are displayed).

 

Organize your Excel Report 

13. Open your downloaded report.

14. After you enable editing, expand your columns so you can read the titles.

15. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A.

16. When all the cells are highlighted, navigate over to the Sort & Filter menu and then select 'Filter'.

17. Each column header will now have a filter icon. The entire report can now be sorted by the information in any column. 

18. In the Order Date Completed column, select 'Sort Oldest to Newest' to see results in chronological order.

 

Find out your annual Order Totals

19. To calculate yearly totals, scroll to the bottom of the report. Select the cells beneath the columns you’d like to total (Order Shipping, Order Taxes, and Order Total) and click AutoSum in the toolbar.

20. The sum of each column will be automatically calculated.  

 

Calculate Taxes and Sales by State

21. Once your Order Totals are calculated, navigate to your Shipping State filter and select 'Sort A to Z'. 

Sort_by_State_Drop_Down.jpg

22. All sales should now be grouped according to state. 

Sort_by_State.jpg

23. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A.

24. When all the cells are highlighted, select the Data tab and then click the 'Subtotal' icon.

25. The Subtotal window will open. Enter the following information in the correct fields. 

  • At each change in: ShippingState
    • This drop-down menu gives you the option to choose from all the columns in your report.
  • Use function: Sum to calculate the total value for Shipping States
    • This drop-down menu provides a list of functions and calculation 
  • Add subtotal to: OrderShipping, OrderSubtotal, OrderTaxes and OrderTotal

ShipStateSubTotal.jpg 

26. Click the 'OK' button. 

27. Excel will automatically calculate the totals for each state. 

28. You can hide the individual sales by clicking the '2' button in the top left corner of the spreadsheet. Then, the totals for each state will be grouped together. 

Tip: You can expand the report to see all the individual sales by clicking the '3' button.

 

Determine Taxes and Sales by Order Type

Note: After using the the Subtotal tool, you must run a fresh report to calculate different sub-totals. 

1. To rerun a report, navigate back to the vinSUITE admin panel. Return to the My Reports module and search for the appropriate report. 

2. To rerun a report without making any changes, click the Graph icon for the appropriate report. 

3. Open your downloaded report. After you enable editing, expand your columns so you can read the titles.

4. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A.

5. When all the cells are highlighted, navigate over to the Sort & Filter menu and then select 'Filter'.

6. Each column header will now have a filter icon. In the Order Type column, select 'Sort A to Z' to see results in alphabetical order by Order Type.

7. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A. When all the cells are highlighted, select the Data tab and then click the 'Subtotal' icon.

8. The Subtotal window will open. Enter the following information in the correct fields. 

  • At each change in: OrderType
  • Use function: Sum to calculate the total value for Order Types
  • Add subtotal to: OrderShipping, OrderSubtotal, OrderTaxes and OrderTotal

OrderTypeSubTotal.jpg

9. Click the 'OK' button. 

10. Excel will automatically calculate the totals by each Order Type. Click the '2' button to hide the individual sales. Then, the totals for each Order Type will be grouped together.  

 

Figure Out Total Sales for each Sales Associate

Note: After using the the Subtotal tool, you must run a fresh report to calculate different sub-totals. 

1. To rerun a report, navigate back to the vinSUITE admin panel. Return to the My Reports module and search for the appropriate report. 

2. To rerun a report without making any changes, click the Graph icon for the appropriate report. 

3. Open your downloaded report. After you enable editing, expand your columns so you can read the titles.

4. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A.

5. When all the cells are highlighted, navigate over to the Sort & Filter menu and then select 'Filter'.

6. Each column header will now have a filter icon. In the Sales Associate column, select 'Sort A to Z' to see the results in alphabetical order by Sales Associate.

7. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A. When all the cells are highlighted, select the Data tab and then click the 'Subtotal' icon.

8. The Subtotal window will open. Enter the following information in the correct fields. 

  • At each change in: SalesAssociate
  • Use function: Sum to calculate the total value for Order Types
  • Add subtotal to: OrderShipping, OrderSubtotal, OrderTaxes and OrderTotal

SalesAssociateSubTotal.jpg

9. Click the 'OK' button. 

10. Excel will automatically calculate the totals for each Sales Associate. Click the '2' button to hide the individual sales. Then, the totals for each Sales Associate will be grouped together.  

SalesAssociateTotal.jpg

Note: Any sales that are not attached to a sales associate will be displayed below the sales associates. 

 

Discover your Best-Sellers  

You always want to highlight your bestsellers and promote customer favorites. By using the Sales Detailed report, you can:

  • Find out which products have been bringing in the most sales
  • View what products were sold on each order
  • Break down sales by product using the Excel Pivot table

Please follow the steps below:

Create Sales Detailed Report

1. In the vinSUITE admin panel, expand the Reports section, then click 'Report Builder'.

Dashboard.jpg

2. On the Create Report page, enter a name for your report.

3.  Select a Report Type to house this report. This is an optional but recommended step. 

Tip: Think of Report Types like folders to organize your reports. 

4. Select a Report Base that will serve as the basic fields in your report. For this case, you would select the Sales Detailed Report Base.  

Note: The Sales Detailed Report is great for learning about the different products in an order. The end report will divide the total order amount by each product, separating the taxes and shipping evenly among the products.

5. Click the 'Next' button when you have finished stage 1.

6.  On the Select Fields page, specify the fields you want to be include in your report. 

ReportBuilderStage2Fields__.jpg

Fields to Select From On the left side of the screen  These are the fields that are available for you to add to the report.
Chosen Sorted Fields On the right side of the screen   These are the fields that you have included in the report.

 

7. Click the 'Next' button when you have finished stage 2.

8.  On the Filter Data page, specify the filters you want to be include in your report. 

Tip: Filters can place certain restrictions on your report so you only get the specific information you want (like date ranges or statuses). Only filters listed under Current Filters will be used for the report. For more information on filters, please review the Report Filters page. 

Note: Verify that the Order Status and Order Date Completed filters are added to your Current Filters. 

  • Order Status: Should Equal (=) Completed Order
  • Order Date Completed: Should be Greater Than or Equal to your chosen date (01/01/2018)

9) Add a new filter for Order Date Completed. Set the operator to 'Less than or Equal' and the date to 12/31/2018. Click the "Add Filter" button. 

Note: You have now created a date range, from 01/01/2018 to 12/31/2018, for your report results. You should always use the Greater than or Equal filter for the beginning of the date range and the Less than or Equal filter for the end of the date range.

Tip: If you are experiencing errors or slowness running the report, it is recommended that you separate the dates into 1-3 month date ranges. You can then combine the results of multiple reports into a single spreadsheet in Excel for your annual report. 

 

10. Click the 'Next' button when you have finished stage 3.

11. On the Preview & Complete page, review your report and click the 'Save and Run' button. This will generate your report as an Excel spreadsheet. 

Note: Sales Detailed reports pull much more data than Sales Summary reports, so the report can take significantly longer to run (up to several minutes for larger wineries). 

12. The report will automatically download to your computer. The downloaded file will appear in the bottom left corner of the browser or your downloads folder (wherever your downloads are displayed).

 

Organize your Report with a Pivot Table 

13. Open your downloaded report.

14. After you enable editing, expand your columns so you can read the titles.

15. To insert a Pivot tablet, select any cell from the list of results. Then, navigate over Insert tab and select the 'Pivot Table' icon.

 

16. The Create Pivot Table window will open. 

17. The range of the report will automatically be detected and calculated for you. Click the 'OK' button. 

 

18. Once you click the 'OK' button, a second Excel worksheet will open and display the Pivot Table.

Note: All the original data will remain on the first Excel worksheet.

19.  To define your Pivot table, navigate to the Pivot Table Fields.

BLANKPivotTable.jpg

Note: The available Pivot Table fields are based on each column header, which will include every field you selected in the vinSUITE Report Builder. 

20. To build your second report, click and drag the Product Name field into the Rows box. All the Products will populate into the rows of the Pivot Table. 

ProdNAMEPivotTable.jpg

21. Drag and drop the SubTotal field into the Values box. The sum of the SubTotals will populate into the Value fields of the Pivot Table. 

SUBTOTALPivotTable.jpg

22. To view all the SubTotal amounts with a currency (Dollars $), right-click any value and open the Value Field Settings.

23. A Value Field Settings window will open. Click the 'Number Format' button. 

 

24. The Format Cells window will open. Click the Currency tab and then click the 'OK' button for both pop-up windows.

 

25. The Pivot Table now displays the amount of each product sold in dollars.

26. To sort the values in the table, right-click one of the values. Once you expand the 'Sort' options, select 'Sort Largest to Smallest'.

27. All the products will be listed by the sales total, with the largest amount at the top. Now it's easy to find your bestsellers and customer favorites that bring in the most sales. 

 

 

Find your Big Spenders

At the end of the year, you may want to thank your largest customers for all the support you've received. By using the Member Value Report, you can:

  • Find out which products have been bringing in the most sales
  • Uncover information such as lifetime purchases, average order total, and number of purchasers by customer

Please follow the steps below:

Create Member Value Report

1. In the vinSUITE admin panel, expand the Reports section, then click 'Report Builder'.

Dashboard.jpg

2. On the Create Report page, enter a name for your report.

3.  Select a Report Type to house this report. This is an optional but recommended step. 

Tip: Think of Report Types like folders to organize your reports. 

4. Select a Report Base that will serve as the basic fields in your report. For this case, you would select the Member Value Report Base.  

MemberValue1.jpg

5. Click the 'Next' button when you have finished stage 1.

6.  On the Select Fields page, specify the fields you want to be include in your report. 

MemberValue2.jpg

Fields to Select From On the left side of the screen  These are the fields that are available for you to add to the report.
Chosen Sorted Fields On the right side of the screen   These are the fields that you have included in the report.

 

7. Click the 'Next' button when you have finished stage 2.

8.  On the Filter Data page, specify the filters you want to be include in your report. 

Tip: Filters can place certain restrictions on your report so you only get the specific information you want (like date ranges or statuses). Only filters listed under Current Filters will be used for the report. For more information on filters, please review the Report Filters page. 

MemberValue3.jpg

Note: Verify that the Total Purchases and Purchase Date Most Recent filters are added to your Current Filters. 

  • Total Purchases: Should be Greater Than (>) to 0
    • This means you are filtering your results to display only customers with more than 0 purchases.
  • Purchase Date Most Recent: Should be Greater Than or Equal to your chosen date (01/01/2018)
    • This means you are filtering your results to display only customers who have purchased an item since 01/01/2018.

 

9. Click the 'Next' button when you have finished stage 3.

10. On the Preview & Complete page, review your report and click the 'Save and Run' button. This will generate your report as an Excel spreadsheet. 

11. The report will automatically download to your computer. The downloaded file will appear in the bottom left corner of the browser or your downloads folder (wherever your downloads are displayed).

 

Sort your Report for your Big Spenders 

12. Open your downloaded report.

13. After you enable editing, expand your columns so you can read the titles.

14. Select the entire spreadsheet by clicking the triangle between Row 1 and Column A.

15. When all the cells are highlighted, navigate over to the Sort & Filter menu and then select 'Filter'.

MemberAddFilter.jpg

16. Each column header will now have a filter icon. The entire report can now be sorted by the information in any column. 

17. In the Total Purchases column, select 'Sort Largest to Smallest' to see results sorted by their total amount.

MemberSortFilter.jpg

18. Your report results are sorted by customers who have made the largest purchases.

Note: This list displays lifetime purchases for a customer. The Purchase Date Most Recent limits the dates in this report to your chosen date range, but does not affect the other fields. If you would like to find out a customer's purchases for a specific date range, you must use a Sales Summary or Sales Detailed report.

MemberSorted.jpg 

 

If you have any questions about building or analyzing reports, please reach out to our Client Services Team at support@vinsuite.com

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk