How To–Reduce Stockouts

How To: Reduce Stockouts

Report Objective:

This report is designed to enable you to analyse the average last transaction time for each product on different days, to identify potential stockouts and therefore potential to reduce customer disappointment.

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 ‘Reduce Stockouts’ report in the ‘Improve Customer Experience’ 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 average_product_last_sale_time_by_day_of_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 average time that the last item was sold on each day

Row Structure

  • One row per product with non-zero sales within the 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 day of the week
  • Crosstab Units: Each datapoint represents the average time when the product was sold for the last time on each day of the week during the period

How to Use This Report:

Initial Preparation

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

Product Analysis

  1. Filter down to the category you are interested in analysing (one which has potential regular stockouts e.g. perishable products)
  2. Filter down to individual products you are interested in analysing
  3. Compare the average last sale time to the known closing time or expected last sale time for each day of the week in
  4. Potentially adjust your production/ordering up for products where the last sale time is earlier than expected/desired

Additional Points to Note

  1. The averages presented here are trading averages, to enable you to plan better if you have regularly changing menu items.  As such this should be interpreted as ‘for days when you did sell any of these items, this was the average last sale time recorded’.
  2. Note that this will not record definite stockouts, but it will help you identify where potential stockouts are occurring and therefore where your forecast might be too low
  3. 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