Read PDF-version

– so let the system take the strain

With fresh food forecasting and replenishment the short shelf life of the items makes accuracy essential. As sales fluctuate significantly day-to-day, precision is only possible through day-level forecasting and replenishment parameter calculation. In this mini white paper we will look at the practical building blocks that underpin this: weekday profiles, dynamic daily safety stocks, delivery schedules and efficient ways to manage them.

Identify weekly patterns and use them in your forecasts

There are people who always eat pizza on Saturday evenings and a roast on Sundays – even at Christmas and Easter. Not everyone is quite that predictable, but analyse the data for larger customer groups – e.g. all consumers shopping on one store – and you can see patterns emerge and identify different shopping habits for each day of the week.
Weekday profiles simply capture how the weekly demand is spread over each day of the week. The profiles are essentially percentages defining what proportion of weekly sales is expected to be sold on each day. For instance, if Friday is setup within a profile at 50%, this means half of the week’s total sales is forecast to be made on Friday when customers shop for the weekend. There are occasions, when it makes sense to create profiles manually, but generally it’s more effective to harness automated statistical tests to mine the profile data for each product, i.e. automatically recognize the demand patterns for weekdays and assign the correct proportion to each day.

Find the right calculation level for weekday profiles – go low

The basics of creating the weekday profiles for individual lines seems pretty simple – the system just calculates what percentage of the week’s sales are made on each day of the week. However there needs to be significant sales of an item at a particular store to provide sufficient data to create a reliable profile. In practice SKU-Store -level profiling works best for products that sell every day. For the other products you have to aggregate data from across stores to arrive at a statistically significant data set and avoid the possibility of the profile becoming random.

The two easiest ways to aggregate data are to combine figures for lines in the same product group, or combining sales for multiple stores in the retail chain. Whether product or by stores it’s important to aggregate at a level where the demand profiles of the products is quite similar. For example in packed meat, ‘basic’ products such as packed chicken and ‘deli’ type products such as beef fillet often have quite different sales profiles. Not surprisingly, weekday-profiling can often be improved by splitting a larger product group into sub–categories, for example by price point. Also when aggregating data across a number of stores within a chain, it is important to take individual stores’s profiles into account, especially if they’re smaller outlets. If a smaller store serves a sparsely populated area, and is the main shopping outlet for many of its customers, weekday profiles may be quite different than if it has a convenience store profile where shoppers just top up. For example fresh milk sales will probably peak on Fridays and weekends where a store is a primary shopping destination, but on Mon-Weds if it’s treated as a convenience outlet.

As a good rule of thumb we suggest using profiling at as low a level as possible. In practice, seek the lowest level where you have unbroken sales data for all days.

How to find out whether the day-profiling level is incorrect

The forecast for normal sales days usually consists of two components – the weekly forecast and the day level split with the profile. We have found tracking and analyzing day-level forecast accuracy in tandem with weekly level accuracy, is the best way to identify problems arising from inadequate day-profiling. Looking at the same data at different levels – SKU-store, Product group-Store, or SKU-Chain – often helps finding out at what point errors creeps in.


Picture 1.  An example of Sales and forecast review that shows a problem with profiling.

Build capability for day-level safety stocks

The first principle of forecasting is that the forecast always contains a margin of error. This principle holds true even if you have calculated beautifully accurate weekday profiles from just the right level of data aggregation. So, to reach the desired shelf availability levels, we need safety stocks to account for that margin of error.

Safety stock levels can be scaled to demand forecasts simply by setting safety stocks in terms of days of forecast demand, e.g. 0.2 days for each day. However, forecast accuracy for each weekday often varies, as sales volumes vary, and so safety stocks needs to cover those eventualities to maintain the desired shelf availability. We have found it helps to calculate day-level safety stocks for products with differing day-level forecast accuracy.

Another thing that often makes sense to take into account in safety stock management is delivery patterns. In many retail chains delivery patterns change over the weekend, and some smaller stores sometimes have only a few delivery days each week, even for fresh products. In those cases our clients have sometimes decided to profile safety stocks differently where delivery intervals are longest, as they present the biggest risk of spoilage.

Remember that some weeks are different

A retailer’s year is full of exceptional sales situations. To forecast normal sales accurately, you need to segregate promotional and holiday sales from your normal weekly profiles. However, you also have to be able to predict accurately the near insatiable appetites of Christmas shoppers.

Public holidays have a huge impact both on demand volume for some products, but just as importantly on the weekly sales profile of all products. They profoundly change the way in which people shop for food.

We have found that weekday profiling for ‘steady holidays’ such as bank holidays or Easter – which fall on the same das of the week – is usually very effective as it’s based on previous years’ data and can be automated. The weekday profiles for holiday weeks (often also the weeks before and after) are also calculated from historic data, and are assigned for those holiday weeks based on user set rules. Typically however Christmas is more difficult, as you often have to look as far back as five years to find for comparable week – and the market usually has changed in that time. Several of our customers have found that, for Christmas, day level review and adjustment of forecasts based on, for example, a store cluster level is generally the most effective way of managing them.



Picture 2. An example of Sales review for ‘a steady holiday’, Easter in 2013 and 2012, showing similar sales pattern

But let’s not dwell on holidays for too long and steal the thunder from the next issue…


Related topics

Inventory Management Best Practices for Food Wholesalers

Our free six-week email course tells you how to manage inventory for maximum results. We’ll cover the essentials to improve your food supply chain management for increased sales and happier customers.

Read more