Sales Analysis Report

The Sales Analysis report totals your invoiced sales and ranks them, so you can answer questions like "who are my top ten customers this year", "which items sell the most", "which product categories carry the best margin" and "how have sales moved month by month". You choose what the figures are grouped by, what they are sorted by, and how many rows to show, and the report returns one row per group with quantity, revenue, cost, margin and average price. When you are grouping by item it can also show what you still have in stock, so one run answers both "what sold" and "what is that stock worth".

Open it from Reports -> Billing -> Sales Analysis.

Running the Report

1) Set Group By to the dimension you want one row per: Customer, Item Category, Item, Salesperson, Inventory Location, Month, Year, Item + Month or Item + Year.

2) Set Sort By to the figure that decides the ordering: Sales, Quantity, Margin, Margin %, Avg Price, Invoices, or Name/Period. Ranking by revenue and ranking by units sold rarely give the same order, so pick the one your question is really about.

3) Set Begin Date and End Date. The range defaults to the last twelve months, and the end date is included.

4) Use Order to choose High (the default, for "best" or "top" questions) or Low (for "slowest" or "worst" questions).

5) Set Limit to how many groups you want listed. The allowed range is 1 to 500 and the default is 50.

6) Optionally narrow the figures with Customer, Item Category, Item, Inventory Location and Salesperson.

7) Use the Filter list to decide which groups are listed at all: Show All (the default), Exclude Rows With No Sales (drops groups with no revenue in the period), or Only Rows With Positive Sales (keeps only groups whose quantity and revenue are both positive).

8) Tick On Hand Value to add On Hand Quantity and On Hand Value columns showing what you hold in stock right now. This applies only when Group By is Item, Item Category or Inventory Location; on any other grouping a single row covers too many items for a stock figure to mean anything.

9) When Group By is Item, the Has Stock At list appears. Leave it blank (the default) to list every item that sold. Choose Any to keep only items you still hold stock of at some location, or pick a single inventory location to keep only items with stock at that one location.

10) Click Next to run the report, or Download CSV to take the same results into a spreadsheet.

Reading the Results

Each row shows the group name followed by Invoices, Qty, Sales, % of Sales, Avg Price, Avg Cost, Cost, Margin and Margin %, plus On Hand Quantity and On Hand Value when you asked for them. A dash in place of a figure means it could not be calculated, for example an average price where no quantity was recorded, or a stock figure for a group that holds no stock at all.

The Grand Total line at the bottom covers every group that matched your dates and filters, not only the rows on screen. That is deliberate: it lets you see what share of the whole period the listed rows account for. % of Sales is calculated against that same period-wide total, so the percentages of a shortened list will not add up to 100%.

Notes

• The report is built from invoiced sales only. Quotes and orders that have not been billed yet are not included, and cancelled invoices are excluded.
• For a "slowest selling" or "lowest selling" question, set Filter to Only Rows With Positive Sales as well as setting Order to Low. Ranking from the bottom without it brings back credits, write-offs and deposits, which carry negative quantities and are not slow-moving stock.
• For a "dead stock" or "what is our money tied up in" question, add Has Stock At set to Any. A slow seller that sold out is not tying up any money, and this drops those rows.
• Has Stock At only ever removes rows. Because the report is built from invoices, an item with no sales in the period never appears no matter how much of it you are holding.
• The stock figures are what you hold today, not what you held on the end date, so they do not move when you change the date range. On Hand Value is the quantity valued at weighted average cost. Both columns respect the Item Category, Item and Inventory Location filters, and the stock totals on the Grand Total line cover the listed rows only, unlike the sales figures beside them.
• Ranking is applied before the row limit, so raising Limit adds the next-ranked groups rather than reordering the ones you already had.
• When more groups matched than were listed, the report says so above the table, along with the figure it sorted by and the direction. Read that line before treating a list as complete.
• Margin and cost come from the cost recorded on each invoice line. If some or all lines in the period have no recorded cost, the report warns you and the margin columns are understated or meaningless for that run.
• An invoice whose customer record is missing or has no company name is still counted. Grouped by Customer those invoices are collected under a placeholder name rather than being left out, so a category, period or item total is never quietly reduced by an unrelated customer-record problem.
• Grouping by Month, Year, Item + Month or Item + Year is meant to be read as a trend, so it is listed oldest first by default. Change Sort By to Sales to turn the same period grouping back into a ranking.
• Sales figures are shown in the currency recorded on the invoices that matched.
• If nothing matched, the report says so instead of returning an empty table. The usual cause is a date range with no invoices in it, or a filter that excludes everything.