Inventory management solution
Today's competitive advantage for retailers is the timely response to fluctuations in demand for their goods. Customers who choose the LEAFIO AI Retail Platform to manage their stock can adapt quickly to changes in demand, as the average daily usage (ADU), which reflects the current demand for goods, is used as a basic calculation when generating an auto-order. In our system, this ADU (average daily usage) is the number of sales over a certain period.
Discover how precision in average daily inventory calculations ensures businesses remain agile in responding to shifts in demand. From the foundation of accurate data, our Retail Guide for 2024 navigates the intricate terrain of managing ADU for an optimized supply chain.
Empower Your Retail with AI: Streamline Inventory Efficiency Like Never Before!
Explore LEAFIO AI
How to Calculate Average Daily Sales?
When calculating the average daily inventory sales per item, it is very important to get the period required for the statistics right. If the period is too long for a product with frequent sales, the average sales indicator will react slowly to changes in demand, resulting in an order that does not match demand (lags).
Conversely, if a short period is used for rare sales, the average will be inaccurate (the amount of goods in the order will not match the demand). If you have one day in a period with an occasional large sale, the ADU will be overestimated. Conversely, if there is not a single sale in the period, the average sale will be 0. To reduce the impact of such situations, LEAFIO assigns items to three groups according to the frequency of their sales:
- Rarely sold goods;
- Moderately sold products;
- Frequently sold and FRESH products.
The customer can decide which average sales value will correspond to each group or can use the default value.
- The following thresholds can be selected for the rare products group: 0.1; 0.2; 0.3; ... 0.8 (default value 0.5). All products with an ADU lower than this will belong to this group.
- The following ADU values can be set for the frequently sold and FRESH products group: 0.6; 0.7; 0.8; ... 2.0 (default value 1.1). This group includes all products with average sales equal to or higher than the selected value.
- All goods that fall within the range between the above values fall into the moderately-selling goods group.
Average sales indicator ADU
For the order to be created correctly, for each product group the ADU inventory is recalculated by the system after a certain period, which is set in the settings.
- For the rare items group, it is possible to select 5; 10; 14; 18; 21 (the default value is 14) days.
- For the moderately-selling items group, 3; 5; 7; 10 (the default value is 7) days.
- For frequent sellers and FRESH products, the average sale is calculated on the day the order is placed, since this group includes products with greaterfluctuations in demand.
Also, according to the group, the system determines which period to take the sales for ADU calculation. The less often an item is sold, the longer the period needed to calculate the correct average sales value. These periods can be set separately for each group in the individual company settings:
- Rare goods (in days) - 21, 30, 45, 60, 75, 90, 120, 180 (default value 60);
- Average sales (in days) - 14, 21, 30, 45, 60, 75 (default value 30);
- Frequently sold and FRESH goods (in days) - 7, 14, 21, 28 (default value 14).
ADU is calculated for all active products with active schedules for which orders are automatically generated.
Daily grocery chain case study
What can the minimum ADU value be used for?
To avoid cases where the calculated value is very small or equal to zero, it is possible to specify by an additional setting the minimum ADU value below which the system will not set the average sale. The minimum selling average can be selected from the following options: 0; 0.01; 0.02; ... 0.05; 0.1.
Related: Convenience Store Merchandising
Valid days for calculating average sales
To correctly count the average daily sales formula for a given period, the system will identify valid days and exclude from them those days that may in some way artificially lower or raise it. The criteria for determining such days are as follows:
- The balance or sale of the product on that day must be greater than 0.
- The product must not be in stock.
- Days with wholesale sales are excluded.
The wholesale criterion for infrequent and average sales can be chosen from the following values: 0; 5; 10; 15; 20; 40 (in units of goods). If there are more sales than the specified quantity on any given day, that day will be excluded from the calculation of the average sale.
Since ADU for frequently sold items is not limited in the maximum possible value and can be higher than the selected criterion for this group, wholesale is determined by a different logic, namely: you must specify how many times the sale for the day must be greater than the current average sale for the system not to consider that day as valid. The selectable criteria are 0; 10; 20; ... 300 times. For example, if we have a current average sales value of 5, the criterion of 20 times selected and 120 items were sold on the day, then this day is excluded from ADU calculation.
- Days with irregular demand are excluded.
LEAFIO also has the option to exclude days with predictably irregular demand. This includes the days before March 8th, Thanksgiving, Easter, etc. These days are date-dependent, and they can be predicted in advance. The customer chooses the days with irregular demand, which will be excluded for all products when calculating the cleared average sales.
AI-powered solution for automated replenishment
Automate demand forecasting and order generation to ensure timely replenishment and maintain smooth operations across all levels of the supply chain
By dividing products into 3 groups and being able to specify your settings for each group, our customers get flexible functionality for calculating average sales, which in turn allows them to react quickly to changes in demand in today's retail environment.
How to calculate average daily sales in Excel?
To calculate average daily sales in Excel, you can use the AVERAGE function along with some basic arithmetic. Here are the steps:
Data Setup:Ensure that you have a column in Excel containing your daily sales data. For example, if your sales data is in column B, starting from B2 and extending downwards with each row representing a day.
Calculate Total Sales:In a new cell, you need to calculate the total sales. Let's say you want the total in cell C1. You can use the SUM function like this:
excelCopy code=SUM(B2:B100) // Assuming your sales data is in cells B2 to B100
Calculate Total Days:In another cell, calculate the total number of days. Assuming you want this value in cell D1:
excelCopy code=COUNTA(B2:B100) // Assuming your sales data is in cells B2 to B100
Calculate Average Daily Sales:Now, you can calculate the average daily sales by dividing the total sales by the total days. Assuming you want this value in cell E1:
excelCopy code=C1/D1
This will give you the average daily sales. Adjust the cell references based on your actual data range.
Alternatively, you can use a single formula to calculate the average directly without calculating the total and total days separately:
excelCopy code=AVERAGE(B2:B100)
Replace B2:B100 with your actual data range.
Remember to adjust the cell references based on the actual location of your data in your spreadsheet.
Calculating Average Inventory - 3 Main Tips for 2024
#3 - Choose an Appropriate Time Frame
Ensure that you choose a relevant and consistent time frame for your average daily sales calculation. Whether you're analyzing daily, weekly, or monthly sales, the time frame should align with your business goals and reporting needs. Consistency is key for accurate comparisons and trend analysis.
#2 - Handle Missing or Zero Sales Days Appropriately
If you have days with zero sales or missing data, consider how you want to handle them. One approach is to exclude them from the calculation to get a true average for the days with sales. Another approach is to treat zero sales days as part of the calculation, especially if they are significant for your business context. The method you choose should reflect your business strategy and the nature of your sales data.
#1 - Use Dynamic Formulas for Automation
Implement dynamic formulas in your spreadsheet to automate the calculation and adapt to changes in your sales data. For instance, use Excel functions like AVERAGE or AVERAGEIFS to calculate average daily sales based on your specific criteria. This not only saves time but also ensures accuracy when your sales data evolves.
What is the average daily sales mean?
The term "average daily sales" refers to the average amount of sales a business generates daily over a specific period. It is a metric used to gauge the regular performance and productivity of a business in terms of its sales activities. Calculating the average daily sales involves dividing the total sales over a given period by the number of days in that period.
For example, if a business has total sales of $10,000 over a month with 30 days, the average daily sales would be $10,000 divided by 30, resulting in approximately $333.33 per day.
This metric is valuable for businesses to assess their day-to-day performance, identify sales trends, and make informed decisions related to inventory management, marketing strategies, and overall business planning.
Have a question?
Have inquiries about retail automation or optimization? Talk to our expert for solutions!
Mary Makarchuk
Retail Optimization Expert