Setting Up Demand Planning for Historical Data Demo

Introduction

This document describes how to type in historical data with the Historical Demand Import, read this data into Demand Planning and create a forecast based on this new data. Historical Demand Import functionality was formerly contained in component HIDEIM, and as of IFS Applications 7 SP1 has been added to Demand Planning (DEMAND).

There are 2 ways of using the imported historical data.

The first way is the pure way. Doing it this way the only historical transactions used by IFS Demand Planning will be the transactions written by Historical Demand Import. This way the data in the Import historical demand table and the historical demand in Demand Planning will be equal. This way is ideal when you have historical data from a customer that you want to import and show in Demand Planning.

The other way is the mixed way. Doing it this way you can add new data to the already existing data in the External_Invent_Part_Issue_Pub. The resulting historical data in the Demand Planner will be a join of the transactions that are in the External_Part_Issue_Pub and the transactions that you write in the Import historical demand data table.

System Setup

You can of course use an existing demand plan server setup and modify it to include data from Historical Demand Import. But it is easier to get it right if you create a new server id for this purpose.

Setting up the Demand Plan basic data

Open the overview Demand Plan Server Setup form.

Enter the following record, noting that this example is based on monthly period version. This set up will create a one year forecast.

The group fields can be left empty. Save and exit the overview dialog.

Now open the Demand Plan Server Setup dialog, and select the Forecast Server ID you just created.

You will now see this dialog.

If you want to be able to group your parts in some kind of group criteria, then just select witch group you want to define and push list of values. Then you get a group selection dialog box, where you can select between the different groups that exists in IFS Applications. See dialog below.

Now define a period version, by selecting the period version tab. See below.

In this dialog you enter the start year, which has to be at least 4 years back in time from today. E.g. if we are doing this on the 01.10.2004 then an appropriate start year will be 2000. The end year should be 10 years into the future. Thus in the above example a suitable end year could be 2015.

Select “monthly” period version, and push the “Create New Period Version” button.

In this example we will not schedule any jobs since it is better for demo purposes to have manual control over the Demand Plan server.

This completes the Demand Plan Server basic data setup first step. The rest of the setup will be dependent on how you want to import the new test data. First we will look mainly at how to import pure customer test data. E.g. data only form the import historical data view.

Entering demo data with Historical Demand Import

First we are going to see how we import purely new customer data. This is very useful when you have an excel sheet with sale figures for the parts you want to import. Note that the sales figures can be aggregated to monthly sales for each part (in our example we are using monthly period version). Or it can be day by day transactions. But there will be less work with monthly.

First you have to create inventory parts of all the new parts that you want to forecast on a suitable site. This is done in Inventory/Inventory Part/Inventory Part.

When the new parts are ready you open the Import Historical Data table form. This is done in Demand Planner/Basic Data/Import Historical Data. Here you create the transactions. Remember to crate the transactions on the right site and on the right inventory parts.

The Import Historical Data table view has many fields, below is the name of the fields and an explanation on how they can be used in the Demand Plan base flow SQL.

If you have monthly sales figures you enter a transaction data on the first date of the month. You have minimum to fill in the Site, Part No, Issue Date, Issue Qty fields. This is done creating a new row in the Import Historical Data shown below.

Note that if you enter negative values in the quantity (Issue Qty) field so that the sum of all transactions in a month (period) is negative. Demand Planning will set a 0 as demand for this period.

Note that if you want to make an advanced setup that consists for multiple flows you also have to indicate the direction (receiver) of each transaction. This is done by stating who/where the transaction is heading this is done through one or more of the fields (Sales Part Group, Sales Price Group, Customer No, Customer Group, Customer, Category, Market, Region, District, Country or Salesman). Which field you use is not important, but you will have to use the same field when defining the base flows in the Demand Plan server basic data setup. More on this below.

Entering base flow SQL

Now we are ready to enter the base flow SQL statements. This is done in the base flow tab of the Demand Plan Server Setup dialog shown below. This is the same dialog as we used for creating period version earlier.

The SQL statement for base flows has to look like this:

SELECT contract, part number, date, quantity 
FROM some_view_or_table 
WHERE (the where clause is optional).

Let’s look at a base flow SQL statement based on historical data that only comes from the Import Historical Data table view.

SELECT contract, part_no, issue_date, issue_qty 
FROM Hist_Ext_Invent_Part_Issue_Tab

This base flow SQL will collect only the transactions that you type in the Import Historical Data View, and all transactions will be added to one flow. If you want to have multiple base flows you can for example change the SQL to

SELECT contract, part_no, issue_date, issue_qty 
FROM Hist_Ext_Invent_Part_Issue_Tab 
WHERE customer_no = ‘Cust1’

As the first base flow, and

SELECT contract, part_no, issue_date, issue_qty 
FROM hist_ext_invent_part_issue_tab 
WHERE customer_no != ‘Cust1’

As the second base flow statement, this will create one base flow with all transactions from customer “cust1” and one base flow with all transactions for the rest of the customers. For this to work you also have to define the customer_no field for each transaction that you input in the Import Historical Data table view. Similar SQL’s can no be constructed from all the fields in the Hist_Ext_Invent_Part_Issue_Tab.

If you want to be able to append transactions already in External_Invent_Part_Issue_Pub view with transactions in the Import Historical Data table view. Then you must write your base flow SQL’s selecting from the Com_Ext_Part_Issue_Pub view instead, this view is a join between the External_Invent_Part_Issue_Pub view and the Import Historical Data table view.

Creating new forecast parts or setting the forecast parts to reaggregate state

There are different things that have to be done if the parts that you want to forecast have not been forecasted before or not. The parts that have not been forecasted before needs to be created as forecast parts. This is done from the Overview forecast parts, found in Demand Planner/Overview-Forecast Parts. You just crate a new part and then specify the flow id (base flow defined above) and the part number, and then push save.

However if the parts in question have been forecasted before you have to check that the forecast parts have the correct aggregation type? Open the Overview Forecast part table view, found in Demand Planner/Overview-Forecast Parts.

In this screen you must set aggregation type equal to recalculate historical demand, instead of incremental. If the original state is delete forecast data then it can be left alone.

When all parts that are to be forecasted have got the right aggregation type (recalculate historical demand or delete forecast data), and all new parts have been created as forecast parts then remember to save the changes to the database. You are now ready to start the demand plan server to see the newly entered forecast data.

Before moving on you should examine that all base flow/parts that you want to forecast exists in the Overview forecast parts table view.

Demo Procedure

To start, go to the Demand Plan Client and open the Forecast Graph window. You can select what to view on the View menu.

Making a Statistical Basis Forecast

The first task is to make a good basis forecast for the parts in the different flows to avoid viewing all the parts in one flow. We will sort the parts according to the MAPE error measure, which will provide a list showing the part with the highest MAPE value at the top. See the figure below.

Then we will start with the top part (the part with the highest error), and work our way down the list until we get a MAPE value that is low (i.e., good) enough for our needs. We will not view the rest of the parts.

An easy way of getting a good statistical forecast for a part is using the Classification toolbar/list, which shows the class to which a part belongs. The part’s class indicates the historical demand pattern of that part. There are 11 different classes, which we can subsume under three main classes:

• Intermittent—These are parts that do not have demand in every period, i.e., parts that do not sell well. Use Croston’s intermittent as the forecasting model.
• Level—These are parts that have had steady sales (fairly the same amount). Use either moving average, AEWMA Level, or EWMA Level as the forecasting model.
• Trend—These are parts whose sales have been increasing or decreasing over time. Use either EWMA Level/Trend, Brown’s Level/Trend, or Least squares as the forecasting model.

In addition, you can combine these classes with seasonality, which means that the respective parts are selling more in some periods of the year than in others. There are also classes for increasing and decreasing trends.

All of these classes help you decide which forecast model to use. Demand planning has forecast models for demand patterns that have trends (Brown’s Level/Trend, Least squares, and EWMA Level/Trend) and that are intermittent (Croston’s intermittent) and flat/level (Manual, Moving Average, EWMA Level, and AEWMA Level). There is also a best-fit forecasting model, which selects the best performing forecasting model based upon a competition among the different forecasting models on known historical demand.

There also exists a forecast model called Bayesian, this is an all-round multipurpose forecast model that preforms well on parts with have level and level/trend patterns. So if you have limited knowledge of how to choose a forecasting model and adjust its parameters then this model will be a good choice.

In addition there also exists an automatic season profile. Selecting this profile the system will analyse the parts historical demand and see if there are a seasonal pattern in the historical demand. If a seasonal pattern is fond then there will be applied a seasonal profile based on the found seasonal component on the part. If no seasonal component is found then the forecast model will not include seasonality. Note that this has seasonal component test is a strict test and you might find parts where you want a seasonal profile added to the model but the automatic profile is not approving. On these parts you have to crate a manual seasonal profile and use this on the part.

To return to our sample forecast, let us look at the Coca-Cola part (taken from the std test data set on the testdata.bac file shipped with the demand plan server), which is the part with the fifth highest MAPE value (19,15). The figure below shows how this part appears in the forecast graph in the Demand Plan Client.

Instead of using the best-fit forecast model, we can use our own judgment. First, we need to look at the Classification list on the Classification toolbar to identify the class that is Level/Trend(+)/Season. This class shows that the previous sale of this part has been increasing over time and that the part has seasonality. This is also obvious if we look at the Adj. Demand line in the graph. It suggests that we should use one of those forecasting models that apply level in the calculation (Brown’s Level/Trend, Least squares, or EWMA Level/Trend).

We will select Brown’s Level/Trend. To take seasonality into account, we can either make a new season profile that matches the part’s seasonality (this is done with the aid of the season profile graph), or we can use a similar profile that has already been created. Instead of looking for matching profiles in the library, we can have the system find one for us by clicking Search in the Season Profile toolbar. We can also search for the optimal forecast parameters (alpha and beta) on the selected forecast model by clicking Search in the Part Selection combo.

Once these steps are completed, the Demand Plan Client will look like shown below.

The yellow line is the new resulting system forecast. The green adjusted line would initially be the same, until we make an update. We want this system forecast to be our forecast suggestion for the chosen part (Coca-Cola). To achieve this, you can either click System to adjusted in the Part Selection toolbar, or you can right-click in the forecast graph and then select System to adjusted in the menu. The same menu option is also available if you click System to adjusted on the Part menu. See the figure below.

If you want to make adjustments to the forecast based on your knowledge of the market, you can make direct adjustments to the adjusted forecast with the mouse cursor by clicking on the adjusted forecast ball and then move it up or down. Or you can type the adjustments into the adjusted forecast column in the forecast table. The resulting basis forecast will consist of a mix of data derived from the statistical forecasting models and data based on your market assessment.

Remember that it is always the adjusted forecast that serves as the master forecast. This forecast is used by IFS Applications as the forecast for a part. This forecast is also used as the suggestion for the collaboration partners. It is, therefore, crucial that you identify the adjusted forecast as a suggestion reflecting your estimate of what the future sales/consumption are going to be like.

Note: There is a difference between the Forecast Model and Std Forecast Model fields in the Detail view. The fields without the Std prefix are tied to the forecast model and parameters that are used to create the currently displayed system forecast. However, as time goes by and the sales numbers for the next period become known, the Demand Plan Server will aggregate the previous period’s demand and create a new forecast, starting with the first period in which the sales are unknown. For this forecast, the Demand Plan Server is going to use the standard forecast model and parameters to calculate the new forecast. In other words, the Forecast Model and Std Forecast Model fields are all equal after the Demand Plan Server has created new forecast for all parts. This is usually done in the start of each forecast period.

To ensure that the selected forecast model, parameters, and selected season profile will be used again the next time, the Demand Plan Server calculates a new forecast (when the forecast moves one period into the future). The model and parameters have to be moved to the Std fields. To do so, right-click in the Detail view and then click Copy forecast parameters, as shown below.

The figure below shows the new Detail view after the forecast parameters have been copied.

At this point, we have created a good forecast suggestion for the chosen part (Coca-Cola). Repeat this procedure for the rest of the parts with a high MAPE value.

Writing the forecast back to the database

Now when we are finished creating the forecasts for all parts in the Demand Planner we must do a write back to db job from the Demand Plan Server. The reason for this is that the server always holds all forecast data in memory and must be told when to write the results back to the database so that you can use the forecast for further planning in IFS Applications.

First you must exit the Demand Plan Client. This is done from the file menu file-exit.

Then you have to open the Demand Plan Server GUI, if this is not visible on your desktop then you must double click the Demand Plan Server icon on system tray, se figure below.

When you have got the Demand Plan Server GUI on your desktop you select the Upload to DB menu selection form the file menu.

Now it is only to wait until the write back job is reported to be finished and then the forecast that you just made are ready for further planning in IFS Applications.

Conclusion

In the course of the forecasting session described in this document, we have seen how we can use Historical Demand Import to import historical data into IFS Demand Planner. In addition we have touched upon the major activities of forecasting.