Forecast Setup

Basic Concepts

The Forecasting Engine that AGR has created relies on a third-party component to provide the base forecast. Forecast Pro delivers AGR with a forecast either on a monthly level or on a weekly level. AGR then distributes the forecast down to days based on the underlying sales data and the settings applied in the system. The Forecast Pro forecasting engine is included in the AGR system as an external dll. This dll is written in C++ that means that it is “unmanaged code” that requires special treatment from the application calling the dll. Some basic concepts can be seen below:

  • Automatic Model Selection -> What model family is chosen within the forecast module
  • Forecast type -> Whether a forecast is created by Forecast Pro based on weekly or monthly aggregation
  • Forecast horizon -> The number of periods into the future that the forecast makes predictions
  • Skip Forecast to Date -> How many months of historical data should the forecasting engine use
  • Main Forecast Tables and Their Columns -> dbo.forecasts and dbo.forecast_values

Automatic Model Selection

The forecast engine goes through an automatic expert selection each time a new forecast is created. This means that several different forecast model families are tried out and the one with the least forecast error, chosen based on the bayesian information criteria (BIC), is returned by the forecast engine, along with some statistics about the forecast. The chosen model family can depend on several things since different forecast models can perform better in different scenarios such as if item is seasonal, if item is only sold in low volume, if the demand of an item is intermittent, etc.

The AGR forecast engine has the following model families and the chosen model family for each forecast can be found in dbo.forecasts:

  • 1 -> Exponential Smoothing
  • 2 -> Box-Jenkins
  • 4 -> Simple Moving Average
  • 5 -> Discrete
  • 6 -> Croston’s Intermittent Demand Model
  • 11 -> Fixed Smoothing Model
  • 12 -> Curve Fitting
  • 13 -> Custom Box-Jenkins
  • 14 -> Very Simple Models
  • NULL -> Means that the confidence factor for the item is 0 and no forecast is calculated

The most common model families that the AGR module produces are 1, 2, 4, 5 and 6.

Keep in mind the following rules:

  • If there are less than five data points, then the Forecasting Module switches to a simple moving average model.
  • If an event model is assigned to an item, exponential smoothing will always be chosen, with extra smoothing weight related to the event modeling.
  • If there is less than two years’ worth of data, then the Forecasting Module does not consider seasonal models.
  • Seasonal Box-Jenkins model will not be considered unless there is around 3 years worth of data used.
  • One rule has been implemented, to better handle strictly seasonal items, which overwrites the automatic expert selection, it is explained here.

Automatic Model Selection Overwritten

One rule has been implemented which overwrites the automatic expert selection by the forecast engine. The rule is applied to items that are strictly seasonal, i.e. are only sold in a season, shown in image below. In order to know if an item is strictly seasonal, two conditions must be fulfilled. Firstly, the item must have seasonality > 0 (the parameter shown in dbo.forecasts) and secondly the zero ratio of an item must be larger than 60%, e.g. if 24 months of history are used to calculate monthly forecast, at least 15 months must have no sale at all which would give a zero ratio of 62.5%.

inst

If both conditions are fulfilled, a form of exponential smoothing that is referred to as an NA-Constant Level model or a “salt” model is used. The model omits the trend term and the level smoothing weight is constrained to a small value. This leads to a model that enforces a constant level and the seasonal component models the departures from the constant level. The model works particularly well for data that exhibit a “selling season”.

If the automatic expert selection has been overwritten, it can be seen in the model_description column which is in dbo.forecasts, where the model description field will start with the term NA-CL.

Forecast Type

The historical sales data is stored on the daily level in the AGR database. Dependent on the Forecast Type the data is aggregated before it is sent to the Forecast Pro dll. Forecast type can be configured globally and on item level.

Setting Value Explanation
Forc_type 1 Monthly
Forc_type 2 Weekly

Monthly Forecast Type

When the forecast is set to the Monthly type the system aggregates the data to the monthly level before calling the Forecast Pro dll. Forecast Pro returns the forecast and safety stock on the monthly level.

Weekly Type

Using weekly forecast type the aggregation level will be weekly.

Skip Forecast to Date

Explanation on Columns in the Forecast Table

Valid

Model Family

Seasonality

Trend

Model Description

Update Count Daily

Update Count Interval


Settings

Global Settings

There are multiple global settings that can be configured to change the functionality of the forecast module:

Setting Default Value Condition Remarks
forecast_type 1 Indicates the default forecast type used in the system
forecast_confidence_factor 95 The default value for the confidence factor, if an item does not have it defined this value will be used
forecast_number_of_days 380 The default value for the number of days that the forecast should be created for
forecast_skip_forecast_to_date 36 How many months of historical data the forecasting engine should use to create future predictions. Set the value greater than 24 months to capture any seasonality. Recommended value to capture seasonality is 36 months.
Forecast_day_distribution False If set to true AGR will calculate average weighted daily distribution of the forecast otherwise every day in a period will get the same value
forecast_sale_on_saturdays False If set to true, the system will generate forecasts for Saturdays
forecast_sale_on_sundays False If set to true, the system will generate forecasts for Sundays
forecast_closed_SKU False Only applicable via the forecast service. The client will always force the creation of a forecast. If set to true, the system will generate forecasts for closed skus
forecast_safety_stock_sqrt_bridging False If set to true, the safety stock between the first and second period will be bridged with the sqrt(x) function instead of x
forecast_number_of_weeks_used 30 Only used when forecast_day_distribution is set to true Indicates how many weeks are used when calculating the daily distribution
forecast_number_of_sale_days_valid_week 0 Only used when forecast_day_distribution is set to true How many non-zero sales days in a week needed for the week to be used in the day distribution calculations. If set to 0 this setting is not in use
forecast_number_of_valid_weeks for_day_distribution 0 Only used when forecast_day_distribution is set to true How many valid weeks needed to calculate a day distribution. If set to 0 this setting is not in use
forecast_outlier_detection 0 The values can be 0 => No detection, 1=> Detect, 2=> detect and correct. The results are stored in dbo.forecast_outliers
forecast_safety_stock_type 0 The values can be 0 => Use Forecast Pro (old way), 1 => AGR calculated, Client side, 2 => AGR calculated, DB side
forecast_use_week_trend False Only applicable if forecast_type = 1 By default, all weeks within a month get the same total amount assigned to it. By setting this setting to true the module uses historical data to calculate how individual weeks are trending within a month and assigns the total week value accordingly

Per Item Settings

The per item forecast settings are stored in the dbo.item_details table. Each setting has a default setting and an override setting. The default settings are either populated from the ERP or via business logic in the staging data base. The override setting is either populated via user input from the client or a consultant in the table directly

Setting Overrides Global Explanation
confidence_factor Yes The confidence factor to use for the item
skip_forc_to_date Yes How many months of historical data the forecasting engine should use to create future predictions
forecast_from_date No If the date is set all the forecast values will be zeroed out before this date
forecast_to_date No If the date is set all the forecast values will be zeroed out after this date
forecast_type Yes Define the forc_type on the item level
forecast_event_header_id No Sets the event model used for the item, see 5.2
forecast_index_header_id No Sets the index model used for the item, see 5.2
is_sale_on_saturdays Yes Populate forecasts on Saturdays
is_sale_on_sundays Yes Populate forecasts on Sundays
exclude_from_forecasts No If set to true this setting explicitly prevents a forecast to be created for an item

How to Run Forecasts

To run a forecast job it must be done through the scheduler in the system, either by running it manually or creating a schedule. The forecast job information must be inserted into the following staging control tables:

  • core.stg_element
  • core.stg_group
  • core.stg_group_element
  • core.stg_run
  • core.stg_run_grup

More information on how to work with and insert into the staging control tables can be found here.

Templates of different types of forecast jobs can be found in the core.stg_element table by filtering on object = ‘FORECASTS’ as shown below. You should be able to adjust the templates to your needs by changing the The parameters column in core.stg_element table.

inst

Forecast Parameters

The parameters column in core.stg_element table determines what type of forecast should be used. The different parameters are listed here below.

Parameter Remarks
/M Missing only forecast. Forecasts all items that do not have forecasts and forecasts that are invalid.
/LB: Location Batch Forecast. The value after the semicolon refers to the batch_id in dbo.location_batch_forecast on prod.
/L: Create forecast for a single location. The value after the semicolon refers to location_id.
/N: Number of days that the forecast should be created for. Should be used sparingly, instead configure through Settings tab in the system.
/I: Create a forecast for a single item, the value afture the semicolon refers to item_id (should only be used for testing purposes).
/B: Creates forecasts for items in a given batch where the value after the semicolon refers to batch_id. Batch is defined in dbo.forecast_processes on prod.
/T: The type of forecast to create: M -> monthly, W -> weekly. If nothing is passed then forecasts for all types is created.
/cpus: Flag used to trigger the parallel forecast, if the value after the semicolon >1 then the parallel forecast is triggered.
/D: Dynamic Forecast. The value after the semicolon refers to the id in dbo.data_element_dynamic_forecast
/DU: Dynamic Forecast. If you want to forecast single item/unit for a specific dynamic forecast id.
/DB: Dynamic Forecast. If you want to forecast a single batch for a specific dynamic forecast id as defined in dbo.data_element_dynamic_forecast.
/O: Number of days to offset, e.g. -1 -> Forecast will run with the date from yesterday, 1-> Forecast will run with the date tomorrow.

Examples of combination of parameters:

Parameter Combination Explanation
/T:W /N:180 Forecast job which runs and calculates forecasts 180 days ahead for items with weekly forecast type.
/M /O:1 Missing only forecast job will run where the forecast date will be tomorrow which means that if the job is scheduled at the last of the month, the forecast engine will actually believe that the date is tomorrow which would be first day of next month.
/cpus:4 /LB:1 Forecasts for all items in locations within batch_id = 1 in dbo.location_batch_forecast table will run in a parallel forecast job where 4 cpus are used.
/D:37 /DU:2 Forecast for “unit” id = 2 only, within the dynamic forecast id = 37 (dbo.data_element_dynamic_forecast). In the standard setup id 37 refers to item group forecast which means that forecast will be created for item group id 2 only.

Single Item or Location Forecast Run


Advanced Functionality

Day Distribution

Once the “base” forecast is received from Forecast Pro, AGR distributes the Monthly/Weekly amount down onto days. The Algorithm works the following

Week Trend

Event Model

The underlying sales data can be influenced by external events such as promotions, holidays and special events. To be able to model these events AGR can supply the Forecasting Engine with an event model. The event model is built with supplying the Forecasting Engine with an array of integer values that covers all the history as well as the forecast horizon. The integer values should can be for example the following:

  • 0-> No event (this is universal for all event models)
  • 1-> Easter
  • 2-> Mother’s Day

Example of an event model

To create an event the user has to fill in information in four different tables:

  • Forecast_event_header
  • Forecast_event
  • Forecast_event_values
  • Item_details

To give an example the user is going to create an event for “Bolludagur” which is always seven weeks before Easter. The system never knows exactly when “Bolludagur” is because Easter is not always on the same date. Because of this the forecast is inaccurate and this is when the events software comes in handy.

  1. Insert into dbo.forecast_event_header

create

  • Period_type defines whether the event is using monthly (1) or weekly (2) forecast.
  1. Insert into dbo.forecast_event
  • A large number of the data points are in the week before “Bolludagur” and because of this the user has to create two events “Week before Bolludagur” and “Week of Bolludagur”.

create

  1. Insert into dbo.forecast_event_values:
  • Assign the values in the forecast_event table to their corresponding date. By doing this the system will know when “Bolludagur” is going to be held in the future and which data to use for forecasting.

create

  1. Assign forecast_header_id to the items that belong to the event in item_details

create

Smoothing

Parallel Forecasts

AGR Calculated Safety Stock

Outlier detection

Dynamic Forecasts

For information about Dynamic Forecasts, see here

Forecast Accuracy Tracking

For information about forecast logging and accuracy tracking, see here