Improve your margins by watching this video - (skip ahead to the section of interest)
BI4Cloud Business Intelligence makes it easy to understand your margins on inventory sales and then investigate what has caused any issues (number indicates the minute marker on the video if you wish to skip ahead):
- Low Margin Alerts - 0:57
- Margin by Customer, Customer Group, Distribution channel, Location - 3:00
- Margin by Item, Item Groups, Brands, Supplier - 6:30
- Margins By Salesperson including discounts - 8:55
- Margins over time - by day, week, month, quarter and year - 13:15
When selling inventory products, the sale of each item will have an associated Cost of Good Sold (COGS), Gross Profit and Margin.
Sales Revenue = Qty sold x Average Price
Cost of Good Sold = Qty sold x Average Cost
Gross Profit = Sales Revenue - COGS
Margin % = Gross Profit/ Sales Revenue
Having a clear understanding of all of these drivers will enable you to identify if you have any issues. Some of the common issues with low margins we see are caused by:
- excessive discounting
- not maintaining average selling price over time
- increases in average cost that is not being recovered
- a weak salesperson on the team
- product mix - selling too many low margin products v high margin
- a supplier or brand that is not performing
- errors either pricing errors on sales invoices or cost errors on inventory purchases
BI4Cloud will help you to maintain strong margins and deal with issues as soon as they arise.
Don't waste time wading through loads of detail to find low margin sales, setup a Bi4Cloud exception report to find any low margin sales. Using the Enterprise Version you can even schedule it to automatically send you an email Alert whenever there are margin violations!
Run the report Sales (Item) - Margin by Customer
- Filter to setup a Numeric Filter - e.g. Margin <15% (and Sales >$100)
- Change the Sales Date Range e.g. to Last Week or Last Month
- Change the Analyse by to Invoice Detail dimension to identify Invoices below this margin
- Change the Analyse by to Invoice Line Detail dimension to identify Line Item sales below this margin
- Save the Report and Schedule it to email off weekly or monthly (Enterprise Version only).
Customers below Margin
Invoices below Margin
Invoice lines below Margin
2. Margin by Customer, Customer Group, Distribution channel, Location
Focus on Gross Profit and Margin by who you are selling to and where they are located.
- Are your biggest customers getting the best prices or paying too much?
- Do you have different margin expectations for different distribution channels or buying Groups?
- Can you justify opening a new branch or hiring a new salesrep for a given location?
Run the Visualisation report
- VDB: Margin Analysis by Customer
- Change the Analyse By to Salesperson, Customer Group or Item to see Margins by other dimensions
- Change from All Customers to Top 10 or 50 Customers or Top 100, 250, 500 if too many
- Click on the Table icon to see the underlying numbers in more detail
Run the report Sales (Item) - Margin by Customer
- Sort your Customers by Total Sales, GP or by Margin by Clicking on the Column header
- Drill-down on a Customer to see what Items they have bought by Clicking on the Customer
- Sort Items bought by Total Sales, GP or by Margin by Clicking on the Column header
- Drill-down on an Item to see Invoice Detail - This can help you to identify the cause
To Create Margin by Customer Group - Run the report Sales (Item) - Margin by Customer
- Change Analyse by to Customer Group to see margins at a higher level
- Learn how to Group Customers together by Type using Custom Lists
- Change the Then by (Drill down) to Customer to drill down to underlying Customers
3. Margin by Item, Item Groups, Brands, Supplier
Focus on Gross Profit and Margin by what you are selling and who supplied it:
- Do you have different margin expectations for different product types or brands?
- Can you justify continuing with with every supplier or brand you currently sell?
Run the Visualisation report
- VDB: Customer Sales n Margins (Top 20)
- Change the Analyse By to Item Group, Item or Supplier to see Margins by other dimensions
- Change Top 20 to other options or to All depending on your volume of data
- Click on a Name to drill-down and filter on that Supplier or Item group
To Create Margin by Item or Supplier - Run the report Sales (Item) - Margin by Customer
- Change Analyse by from Customer to Item to change the row dimension
- Change Analyse by to Supplier to see margins by Supplier (this uses the Primary Supplier field on your Item card)
- Change Analyse by to Item Group to see margins at a higher level
- Learn how to Group Items by Type or Brand using Custom Lists
- Sort your Items & Groups by Total Sales, GP or by Margin by Clicking on the Column header
- Drill-down on a Supplier or Item Group to see what Items have sold
Margin by Item Group
Drill down to Margin by Item
Drill down to Invoice Detail to Find the Issue
Margin by Supplier
4. Margin and Discounts given by Salesperson
Focus on Gross Profit and Margin by who is selling - Salesperson
- Are your Salespeople achieving their target Margins?
- Do you reward your Salespeople based on Gross sales or on Gross profit?
- Do you know what discounts are given by each Salesperson?
- Do you track Salesperson based on each Sales Invoice or all sales made to their Customer?
Run the Dashboard Profit Margin Analysis
To Create Margin by Salesperson Report - Run the report Sales (Item) - Margin by Customer
- Change Analyse by to Salesperson for the Salesperson on each Invoice
- Change Analyse by to Customer Salesrep for the Salesperson on the Customer card
- Salesperson or Customer Salesrep? - Click here to see what is the difference
- If there is no Salesrep assigned you will see a Dot on your report - Click here for help to fix this
- To find out which Customers do not have a Salesrep assigned change the Analyse by to Customer then Group by Customer Salesrep
- To find out which Invoices do not have a Salesperson assigned change the Analyse by to Invoice Detail then Group by Salesperson
To Create Margin by one Salesperson Report - Run the report Sales (Item) - Margin by Customer
- Filter by Salesperson for the Salesperson on each Invoice
- Filter by Customer Salesrep for the Salesperson on the Customer card
- Click Done
Margin Report by Customer Filtered for Salesrep
- While filtered for one Salesrep
- To show more detail - Analyse by Invoice Detail (or Analyse by Item)
- Group by Customer
To Create Discount by Salesperson Report including other KPI's - Run the report Sales (Item) - Margin by Customer
- Change Analyse by to Salesperson for the Salesperson on each Invoice or
- Change Analyse by to Customer Salesrep for the Salesperson on the Customer card
- Click +/- cols to Show the Discount column and Hide some columns
- Click +/- cols to Show other KPI columns including Invoice Count and Customer Count
5. Margins over Time - by Day, by Week by Month, by Quarter
Focus on Gross Profit and Margin over time to identify trends
- Are your margins changing over time?
- Do you have different margins based on seasonal fluctuations?
Run the Dashboard - Sales Motion Visualisation
- Drag the Play button to see Margins change over time
- To track the changes as a Trail - tick the Customer
Create a Visualise Report to see Margin fluctuations by Month
To Create a simple Margin by Month Report - Run the report Sales (Item) - Margin by Customer
- Change Analyse by to Sales by Month (or by Day or by week)
- Change the Sales Date Range from the default " This Year" - Click here to learn more about Date Ranges
To Create a Pivot Report showing Sales & Margin by Salesrep by Month Report
- Run the Pivot report Sales (Item) - Monthly Sales by Customer
- Change Analyse by to Customer Salesrep (or Salesperson)
- Change the Display from Total Sales to All (V) (Display all Dimensions as a Vertical Pivot to easily compare month to month across the page)
- To Hide some Dimensions to simplify the report use the +/- Columns option
Remember to Save and Share your new reports with your Team