How To–Plan Launch Stock Effectively

How To: Plan Launch Stock Effectively

Report Objective:

This report is designed to support analysis of actual historic sales to build a forecast for new locations based on sales data, in order to deliver the right stock to a new location upon launch date.

Extracting Data from Floodlight:

  1. Log into to your Floodlight Analytics dashboard
  2. Navigate to ‘Floodlight Focus’ in the left sidebar
  3. Select the ‘Build Your Brand’ tab from the top tabs
  4. Scroll down to the ‘Identify Low Selling Category Items’ report in the ‘Align Menu to Customer Profile’ 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. Determine whether you are launching with a full menu or subset of the menu to reduce complexity at launch
  3. Filter for the products you want to sell at launch and look at the trading average – this is the weekly estimate
  4. Ratio this by a percentage based on expected demand (i.e. is there a significant event or marketing push, or are you expecting a ramp up to full sales?)
  5. Analyse actual sales vs. this number at the end of the first week and adjust ordering accordingly until you achieve approximate steady state

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