How To–Negotiate Cost Reduction

How To: Negotiate Cost Reduction

Report Objective:

This report is designed to present actual historic product sales and trends, in order to inform supplier negotiations and enable cost reductions based on expected volume commitment.

Extracting Data from Floodlight:

  1. Log into to your Floodlight Analytics dashboard
  2. Navigate to ‘Floodlight Focus’ in the left sidebar
  3. Select the ‘Increase Profit’ tab from the top tabs
  4. Scroll down to the ‘Negotiate Cost Reduction’ report in the ‘Reduce Cost of Goods Sold’ section
  5. Select the desired Start Date and End Date (e.g. 8 weeks ago and today)
  6. Click the ‘Download CSV’ button
  7. Save the file historic_product_sales_qty_by_week to an appropriate location (e.g. local or cloud drive)

Report Overview:

All products with at least one sale in the selected time period will be presented in this report, along with the number of units sold within each week.

Row Structure

  • One row per product sold in period

Description Column Structure

  • categoryID: ID of the first alphabetical category assigned to the product
  • categoryName: Name of the first alphabetical category assigned to the product
  • productID: ID of the product
  • productName: Name of the product

Crosstab Structure

  • Crosstab Columns: Each column represents a single week of sales, and is labelled according to the date of the Monday of each week
  • Crosstab Units: Each datapoint represents the number of units sold in the week

Summary Structure

  • Total: Total number of units sold in the period
  • Accounting Average: Total number of units divided by the total number of distinct weeks in the report
  • Trading Average: Total number of units divided by the total number of distinct weeks with a non-zero unit count
  • All Datapoints: Number of distinct weeks in report
  • Non-Zero Datapoints: Number of weeks for which there were sales greater than zero
  • Zero Datapoints: Number of weeks for which there were no unit sales
  • categoryNameOverallTotal: Total unit sales for the category relating to this product
  • categoryNamePercentage: Percentage contribution of this product to the overall category sales, in terms of units sold over the full period
  • percentageOfAll: Percentage contribution of this product to total unit sales over the full period

How to Use This Report:

Initial Preparation

  1. Apply formatting for readability (optional)
    • Adjust column widths
    • Adjust row height
    • Bold text for top row
    • Format cells: top, centre align and wrap text
  2. Freeze panes to improve viewing experience (optional)
    • Excel:
      1. Click in cell E2
      2. View -> Freeze Panes
    • Google Sheets:
      1. Click in cell D1
      2. View -> Freeze -> Up to Current Row (1)
      3. View -> Freeze -> Up to Current Column (D)
  3. Add filter to report
    • Select columns A-V
    • Data -> Filter

Analysis within Category

  1. Filter down to the category you are interested in analysing
    • Note that the column sum of categoryNamePercentage should be equal or very close to 100% (due to rounding errors)
      1. To quickly  find this sum, highlight the column and check the bottom right of your screen
  2. Review products within each category to identify any with a high value in the ‘Total’ or the ‘categoryNamePercentage’ column – these will be your highest sellers within the category
  3. Multiply the ‘Trading Average’ or ‘Accounting Average’ number by your annual trading weeks (e.g. 50) to give an approximate annual unit consumption
  4. Use this as a basis for supplier negotiations on price for this item, bearing in mind that this number will not correct for seasonal variation – however it is a good starting point

Additional Points to Note

  1. If using Excel for analysis, remember to save as an Excel file (e.g. xlsx) instead of csv or you will lose formatting and formulae upon saving