In a hospitality business, you will have many different types of products which have a short shelf life, and which you don’t want to throw in the bin at the end of the day. If you’re selling anything from sandwiches, soup or salads, bread to bagels or Banh Mi, your ideal scenario is that you make (or order) enough to satisfy every single customer who wants one, but still have none left on the shelf at the end of the day. No waste and no missed sales. Simples.
Well, not really. We should all understand that this achieving this precise scenario every day is not only difficult, it is actually impossible. Your demand for different products will depend on so many different factors, some predictable and some unpredictable, so you’ll never be able to achieve this day-in, day-out. But by analysing your historic data in specific ways, you can get close, pretty reliably.
By using your historic data to understand the average sales by day of week (as different days of the week will typically have different sales characteristics), it can act as a good – if conservative – starting point for the number you should stock or order in the future.
Try and build a basic forecast based on this approach and then track how closely it matches actual sales. You can complete this analysis in any spreadsheet application, as long as you have access to the data and can manipulate it into the right format.
If you are a Kounta user and connect to Floodlight Analytics, you can use the Floodlight Focus report ‘Reduce Wastage’ to download this directly. The instructions are included below – happy forecasting!
N.B. The next steps are towards building a more advanced forecast are then:
- Understanding what your stockout vs. wastage thresholds are for different products based on their relative margins (template and guide coming soon)
- Analysing historic last sale times to identify potential stockouts, where you might be leaving money on the table, and adjusting your forecast accordingly (check out the ‘Reduce Stockouts’ Floodlight Focus report and guide)
‘Reduce Wastage’ Report Objective:
This report is designed to enable you to use actual historic sales to build a conservative forecast based on sales data, in order to minimise end-of-day wastage of perishables.
Extracting Data from Floodlight:
- Log into to your Floodlight Analytics dashboard
- Navigate to ‘Floodlight Focus’ in the left sidebar
- Select the ‘Increase Profit’ tab from the top tabs
- Scroll down to the ‘Reduce Wastage’ report in the ‘Increase Margin’ section
- Select the desired Start Date and End Date (e.g. 8 weeks ago and today)
- Click the ‘Download CSV’ button
- Save the file average_product_sales_qty_by_day_of_week to an appropriate location (e.g. local or cloud drive)
All products with at least one sale in the selected time period will be presented in this report, along with the average number of units sold on each day of the week.
- 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 Columns: Each column represents a single day of the week, ordered from Monday to Sunday
- Crosstab Units: Each datapoint represents the average number of units sold for each day of the week during the time period
- Total: Average number of units sold for a week in the period
- categoryNameOverallTotal: Average unit sales for the category relating to this product
- categoryNamePercentage: Percentage contribution of this product to the average 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:
- Apply formatting for readability (optional)
- Adjust column widths
- Adjust row height
- Bold text for top row
- Format cells: top, centre align and wrap text
- Freeze panes to improve viewing experience (optional)
- Click in cell E2
- View -> Freeze Panes
- Google Sheets:
- Click in cell D1
- View -> Freeze -> Up to Current Row (1)
- View -> Freeze -> Up to Current Column (D)
- Add filter to report
- Select columns A-V
- Data -> Filter
Analysis within Category
- 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)
- To quickly find this sum, highlight the column and check the bottom right of your screen
- Review daily historic average sales for each product within each target category to understand a baseline sales forecast by day of week
Additional Points to Note
- 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 weeks when you have been selling these items, these were the average sales recorded’.
- 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