Follow

New Year's Resolution: Master Year End Reporting



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:

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 Yearly 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 as your base 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_BORDER.jpg

Tip: It is always best to run vinSUITE Report Builder with the Google Chrome or Mozilla Firefox browser. To learn more about Report Builder, please visit the Report Builder: Adding A New Report page.

2. On the Create Report page (Stage 1), enter a name for your report.

To_ReportBuilder_1_BORDER.jpg

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.

ReportBuilder_1B_BORDER.jpg

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

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

ReportBuilder_Stage_2_Fields_BORDER.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.

 

Simply drag and drop the arrow icon of the chosen field to reorganize the lists. Include the Order Type and the Sales Associate fields in your report.

ReportBuilder_Stage2_D_D_BORDER.jpg

Tip: Fields can be rearranged by clicking and dragging each field into the order you'd like on your report. In Excel, the top field will be on the far left. 

ReportBuilder_Stage2C_Arrange_BORDER.jpg

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

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

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/2019)
    • 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.  

ReportBuilder_Step_3_BORDER.jpg

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.

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

ReportBuilder_Step_3B_ADD_BORDER.jpg

Note: You have now created a date range, from 01/01/2019 to 12/31/2019, 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.

ReportBuilder_Step_3C_COMP_BORDER.jpg

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 (Stage 4), review your report and click the 'Save and Run' button. This will generate your report as an Excel spreadsheet.

ReportBuilder_Step_4_BORDER.jpg

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).

Excel_Download.png

Organize your Excel Report  

Proficiency in Excel can extremely helpful when it comes to understanding your Excel reports and pin-pointing key information. The most useful tools for vinSUITE Reports are:

  • Filters: Sorts all orders by the contents of any column
  • AutoSum: Automatically adds up multiple orders to find totals
  • SubTotal: Calculates the totals by any specified filter (like sales associate, order type, or state)
  • Pivot Tables: Rearrange report information into a new table to highlight specific information

Filters

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.

Filters1.jpg

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

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

Filter_Icon_Sort_TIME.jpg

Example: In the Order Total column, select 'Sort Largest to Smallest' to see results based on the order total amount.

Filter_Icon_Sort_ORDER_TOTAL.jpg

Order will then be displayed from largest to smallest order amount total.

Filter_Icon_Sort_ORDER_TOTAL_2.jpg

AutoSum

18. To calculate yearly totals, scroll to the bottom of the report. Select the cells beneath the columns you’d like to total up  and click AutoSum in the toolbar.

Example: To find out the totals for Order Shipping, Order Taxes, and Order Total, select the appropriate blank cells and click AutoSum in the toolbar.

AutoSum_Example_1.jpg

The sum of each column will be automatically calculated.
AutoSum_Example_2.jpg

 

Annual Order Totals by State

19. Once your Order Totals are calculated using the Excel AutoSum feature, navigate to your Shipping State Filter and select 'Sort A to Z'.

State_GROUP_Sales_1.jpg

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

SubTotal

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

SubTotal_STATES_1.jpg

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

SubTotal_STATES_2.jpg

23. 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

SubTotal_STATES_3.jpg

24. Click the 'OK' button. 

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

SubTotal_STATES_4.jpg

26. 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.

SubTotal_STATES_5.jpg

 

Annual Sales by Order Type

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

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

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

ReRun_Report_UPD.jpg

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

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

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

Filters1.jpg

4. 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.

Order_Type_Sort_1.jpg

5. 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.

Order_Type_SubTotal_2.jpg

6. 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

Order_Type_SubTotal_3.jpg

7. Click the 'OK' button.

8. 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.

Order_Type_Results_4.jpg

 

Annual Sales for each Sales Associate

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

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

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

ReRun_Report_UPD.jpg

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

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

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

4. 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.

Sales_Associate_Filter_Sort_1_.png

5. 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.

Sales_Associate_SubTotal_2.jpg

6. 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

Sales_Associate_SubTotal_3.jpg

7. Click the 'OK' button.

8. 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.

Sales_Associate_SubTotal_4.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_BORDER.jpg

Tip: It is always best to run vinSUITE Report Builder with the Google Chrome or Mozilla Firefox browser. To learn more about Report Builder, please visit the Report Builder: Adding A New Report page.

2. On the Create Report page (Stage 1), 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.

Report_Bulder_Sales_Detailed_1_.jpg

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

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

Report_Bulder_Sales_Detailed_2_.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 (Stage 3), 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.

Report_Bulder_Sales_Detailed_3_.jpg

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/2019)
  • Order Date Completed: Should be Less than or Equal to your chosen date (12/31/2019)You have now created a date range, from 01/01/2019 to 12/31/2019, 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.

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

10. On the Preview & Complete page (Stage 4), 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).

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.

Report_Bulder_Sales_Detailed_4_.jpg

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).

Excel_Download.png

Organize your Report with a Pivot Table  

12. Open your downloaded report.

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

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

Excel_Pivot_Table_1.jpg

15. The Create Pivot Table window will open.

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

Excel_Pivot_Table_2_UPD.jpg

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

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

Excel_Pivot_Table_3.jpg

18. Navigate to the Pivot Table Fields.

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

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.

Excel_Pivot_Table_4.jpg

The rows of the Pivot Table will now be populated by each product listed in the report.

Excel_Pivot_Table_5.jpg

19. Drag and drop the SubTotal and Quantity fields into the Values box.

Excel_Pivot_Table_6.jpg

The Quantity and SubTotal for each product will populate the Value fields.

Excel_Pivot_Table_7.jpg

20. To view all the SubTotal amounts with a currency (Dollars $), select the entire column and right click to navigate to Format Cells.

Excel_Pivot_Table_8.jpg

21. The Format Cells window will open. Click the Currency tab and then click the 'OK' button.

Currency_Tab.png

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

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

Excel_Pivot_Table_9.jpg

24. 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.

Excel_Pivot_Table_10.jpg

 

Club Memberships

You can check how many club memberships you sold this year!  By using the Club Member Report report, you can:

  • Total how many members signed up for clubs this year
  • Find out which club levels were popular with new club members
  • Itemize club memberships using the Excel Filters

Please follow the steps below:

Create Club Member Report

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

Dashboard_BORDER.jpg

Tip: It is always best to run vinSUITE Report Builder with the Google Chrome or Mozilla Firefox browser. To learn more about Report Builder, please visit the Report Builder: Adding A New Report page.

2. On the Create Report page (Stage 1), 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 Club Member Report Base.

Report_Bulder_Club_Member_1_.jpg

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

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

Report_Bulder_Club_Member_2.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 (Stage 3), 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.

Report_Bulder_Club_Member_3.jpg

Note: Verify that two Club Member Date Added filters are added to your Current Filters. 

  • First Club Member Date Added: Should be Greater Than or Equal to your chosen date (01/01/2019)
  • Second Club Member Date Added: Should be Less than or Equal to your chosen date (12/31/2019)

You have now created a date range, from 01/01/2019 to 12/31/2019, 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.

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

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

Report_Bulder_Club_Member_4.jpg

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).

Excel_Download.png

Organize your Club Membership Report with Filters

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.

Excel_Filter_Club_Filter_1.png

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

Example: The Club Member Added column will then be displayed from the first club memberships of the year to the last.

Excel_Filter_Club_ADD.jpg

Note: You create a similar report to see Club Membership cancellations for the year by changing the filters to Club Member Date Canceled to set a new date range on the Filter Data page (Stage 3).

Report_Builder_Club_Member_CANCEL_3.jpg

 

Bonus: Focus on 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:

  • Uncover information such as lifetime purchases, average order total, and number of purchasers by customer
  • Pin-point your biggest spenders with Excel Filters

Please follow the steps below:

Create Member Value Report

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

Dashboard_BORDER.jpg

2. On the Create Report page (Stage 1), 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 (Stage 2), 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 (Stage 3), 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/2019)
    • This means you are filtering your results to display only customers who have purchased an item since 01/01/2019.

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.

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

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

MemberValue4.jpg

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)

Excel_Download.png

Sort through your Report for Big Spenders with Filters

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