Analyse Sales by Client, Stock Item and Branch

This article is for anyone who wants to see which clients buy which stock items, how often they buy them and which branch sells them, so they can plan stock levels and spot buying patterns.

Overview

The Receivable Invoice Across Months report can only group your sales by two things at once, such as stock item and branch, or stock item and client. If you need to see more than two together, for example every client who buys an item, which branch they buy it from and which months they bought it in, you can export your invoiced sales from the Receivable Invoice Analysis report and summarise them in Excel using a pivot table.

This is useful for managing stock levels, working out which branches need to hold more of an item, finding your regular buyers for a product, and seeing sales history by customer, product, supplier or month. You don't need to know how to use pivot tables already. This article walks you through every step.

Export Your Sales from Lentune

First, run the Receivable Invoice Analysis report as a spreadsheet. This gives you one row for every item on every invoice in the period you choose.

1. Go to Receivables > Reports > Receivable Invoice Analysis.
2. Enter the Invoice – start date and Invoice – end date for the period you want, for example the last 12 months. Invoices dated on the end date are included.
3. If you only want items from one supplier, enter it in Supplier (payable account). Leave it blank to include all suppliers.
4. Leave Client (receivable account) and Invoice branch blank so you get every client and every branch.
5. Make sure Summary only is not ticked. This is the most important step, because Summary only removes the detail you need.
6. If you like, tick Exclude sundry to leave out sundry lines, or tick Invoice only to leave out credit notes.
7. Click Create spreadsheet, then click Ok on the message that appears.

The report runs in the background, so you can keep working while it's created. When it's ready, you'll see a new alert under Alerts and receive an email. Click the alert to download the spreadsheet.

Tip: The Supplier field uses each stock item's preferred supplier, as it's set on the item today. If an item was bought from a different supplier, or its preferred supplier changed during the period, its sales appear under its current preferred supplier.

Tip: If you use Save defaults to keep your settings, check the Supplier field each time you run the report. Save defaults currently remembers your dates and ticked options, but not the supplier.

Split a Large Date Range into Smaller Chunks

A long period for a busy supplier can produce a lot of data. If the report takes too long, run it in smaller chunks, such as one quarter at a time, and join the files together in Excel.

1. Run the report once for each chunk, changing only the dates. Start each chunk on the day after the previous one ended, for example 1 Oct–31 Dec, then 1 Jan–31 Mar, so no days are missed or counted twice.
2. Download each file as soon as it's ready and give it a clear name, such as Q1, Q2, Q3 and Q4. Alerts don't show a date, and the newest one is at the top.
3. Open your first file. This will become your combined file.
4. Open the next file. If it only has a row of headings and nothing underneath, there were no sales in that period, so close it and move on to the next one.
5. Otherwise, click cell A2, press Ctrl + Shift + End to select all the data, and press Ctrl + C to copy it.
6. Go back to your combined file, click in column A and press Ctrl + Down arrow to jump to the last row. Press the Down arrow once more, then press Ctrl + V to paste.
7. Repeat for each remaining file, then save the combined file under a new name.

Create a Pivot Table

A pivot table adds up the rows in your spreadsheet into a short summary. You don't need any formulas; you just drag column names into boxes.

1. Open the spreadsheet and click any cell that has data in it, such as cell A2.
2. On the ribbon at the top, click Insert, then click PivotTable.
3. Excel will already have selected all your data, so leave the selection as it is. Make sure New Worksheet is selected, then click OK.

A new tab opens with an empty area on the left and a PivotTable Fields panel on the right. The top half of the panel lists every column in your spreadsheet. There are a lot of them, so type a column name into the Search box to find it quickly.

The bottom half of the panel has four boxes. Rows is what you want listed down the left side, Columns is what you want listed across the top, Values is the numbers you want added up, and Filters lets you pick one thing to look at, such as a single branch.

See Which Clients Buy Each Item and from Which Branch

Drag these columns from the list into the boxes shown:

The left side now lists each stock item. Under each item are the clients who bought it, and under each client is the branch they bought it from, with the total quantity.

Check that the Values box says Sum of Quantity. If it says Count of Quantity, click it, choose Value Field Settings, choose Sum and click OK.

See How Many Times Each Client Bought

Quantity shows how much a client bought, but not how often. To see how many times they bought, drag InvoiceCode into the Values box underneath Sum of Quantity. A new Count of InvoiceCode column shows how many invoice lines there are for each client and item.

This makes it easy to tell a client who buys a few every month from one who bought a large amount once. Credit notes are counted too, so if you only want to count sales, tick Invoice only when you run the report in Lentune.

See Buying Month by Month

Drag TransactionMonth into the Columns box. Each month now has its own column across the top, so you can see which clients buy an item regularly and which bought it only once.

If Excel shows years or quarters instead of months, click the small + next to the year to open it up. If you also added Count of InvoiceCode, each month shows two numbers. That's normal, and you can drag Count of InvoiceCode back out of the Values box if it gets too busy.

See How Much of Each Item Each Branch Sells

To start again with a clean layout, drag everything in the four boxes back out into the list, then set it up like this:

This gives you a grid with stock items down the side and branches across the top, showing how many of each item every branch sold in the period. Compare it with your current stock on hand to see which branches need to hold more.

Here are some handy tips while you work with pivot tables:

• Click the small + and – buttons to open and close groups.
• To sort from biggest to smallest, right-click any number and choose Sort > Sort Largest to Smallest.
• To look at one branch, drag InvoiceBranchCode into the Filters box and pick a branch from the drop-down above the table.
• If the PivotTable Fields panel disappears, click anywhere inside the pivot table to bring it back.
• You can't break anything. The pivot table is on its own tab and your original data isn't changed. If it gets messy, delete the tab and start again.

FAQ

Q: Why can't I use the Receivable Invoice Across Months report for this?
A: That report groups your sales by two things only (Group one and Group two). Exporting from Receivable Invoice Analysis gives you every detail on every invoice line, so you can combine as many as you need in a pivot table.

Q: Why are some quantities negative?
A: Credit notes are included as negative quantities, so returns reduce the totals. Tick Invoice only when you run the report if you want to leave credit notes out.

Q: Which branch does InvoiceBranchCode show?
A: It's the branch that invoiced the sale. If your branches sometimes invoice for stock that's sent from another branch, the figures show where the sale was made, not where the stock came from.

Q: Can I see sales for all suppliers at once?
A: Yes. Leave Supplier (payable account) blank when you run the report, then add StockPayableAccountFullDescription to the Rows box in your pivot table. This shows each item's preferred supplier. For a busy business this can be a lot of data, so consider running it in smaller date ranges.

Q: My spreadsheet has no data in it. Did something go wrong?
A: No. If there were no matching sales in the dates you chose, the spreadsheet only has a row of headings. Check your dates and supplier, or try a longer date range.

Q: Does the report include invoices on the end date?
A: Yes. Invoices dated on the start date and the end date are both included.

Need more help? We're here! We hope our article was helpful. If you need more info or want to connect, drop us an email at support@lentune.com.