Sales - Understand your Margins

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

  1. Low Margin Alerts - 0:57
  2. Margin by Customer, Customer Group, Distribution channel, Location - 3:00
  3. Margin by Item, Item Groups, Brands, Supplier - 6:30
  4. Margins By Salesperson including discounts - 8:55
  5. 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. 

 

1. Low Margin Alert

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

Margin_Violation_filter.png

Invoices below Margin

Margin_Violation_result.png

 

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

vdb_Margin_Analysis_by_Customer.png

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

Margin_by_Customer.png

  • 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

Margin_by_Item.png

To Create Margin by Customer Group - Run the report Sales (Item) - Margin by Customer

Margin_by_Customer_Group.png

 

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

VDB__Sales_n_Margins.png

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

Margin_by_Item_Group.png

Drill down to Margin by Item

Drill_Down_to_Item.png

Drill down to Invoice Detail to Find the Issue

Invoice_Detail_-_Issue_Found.png

Margin by Supplier

Margin_by_Supplier.png

 

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 

Margin_by_Salesperson.png

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

Margin_by_Salesrep.png

  • 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

Customers_with_No_SAlesrep.png

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

Filter_by_Customer_Salesrep.png

Margin Report by Customer Filtered for Salesrep

Margin_-_by_Customer_o_ne_rep.png

  • While filtered for one Salesrep
  • To show more detail - Analyse by Invoice Detail (or Analyse by Item)
  • Group by Customer

Inv_Detail_by_Customer_by_Rep.png

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

Plus-minus_Cols.png

  • Click +/- cols to Show other KPI columns including Invoice Count and Customer Count

Discount_and_KPI_s.png

 

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

Sales_Motion_Visualisation.png

Create a Visualise Report to see Margin fluctuations by Month

 Margin_by_Cust_Group_by_month.png

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

Margin_by_Month.png

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

 Margin_by_Salesrep_by_month.png

 

Remember to Save and Share your new reports with your Team 

Bi4Cloud.com

 

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

Comments

Please sign in to leave a comment.

Powered by Zendesk