Guideline Module 1
Guideline Module 1
Guideline Module 1
GOALS : 1. Students could understand the role of demand in the logistics system
2. Students could forecast demand in the logistics system
3. Students could evaluate the results of forecasting that have been designed
Practicum Tools:
1. PC
2. Excel
3. Data Demand
Module Framework:
1. Preprocessing
2. Forecasting Method
3. Tuning of Forecasting Method
Practicum Steps
Case Study 1:
PPSL Company is a private company which needs to forecast overall the sales over the near
future. The historical demand of carton during past 24 periods is reported in Table 2.1
Based on the table, help PPSL Company to evaluate the data, shows the data pattern,
forecast using appropriate method, Monitor the forecast by using accuracy measure, and
tuning the forecast method by minimize its accuracy value
Completion Steps:
Figure 2.10 Determine the Lower Limit and drag down cell until 24th period
6. Determine the upper limit value by input the formulas as follow:
=$H$20+1.5*($H$20-$H$19)
Figure 2.11 Determine the Upper Limit and drag and down until 24th period
7. So after you determine the upper and lower limit you will find the result that there’s an outlier
in 2𝑛𝑑 period. So replace the value in 2𝑛𝑑 period by sum the value of 1𝑠𝑡 period and value oh
3𝑟𝑑 period and divide it by 2.
11. Go to Level in 1𝑠𝑡 period and use this equation as show below to find the value
So, the formula could be typed as follow, don’t forget to stabilize its formula as shown as
Ft+1 = Lt
So based on the equation, we can use ROUNDUP(Lt;0)
-MAD is defined by averaging the 𝑨𝒃𝒔𝒐𝒍𝒖𝒕𝒆 𝑬𝒓𝒓𝒐𝒓. So, the formula could be typed as
follow:
Figure 2.21 Define MAD
-MAPD is defined by averaging the 𝑷𝒆𝒓𝒄𝒆𝒏𝒕𝒂𝒈𝒆 𝒐𝒇 𝑬𝒓𝒓𝒐𝒓. So, the formula could be typed
as follow:
PPSL Company is a private company which needs to forecast overall the sales over the near
future. The historical demand of carton during past 24 periods is reported in Table 2.2
-In Output options, choose Output Range and click the arrow symbol block the provided cells
-Click OK
4. Find the Level value of 1st period (Cell D8) to 24𝑡ℎ period. The equation is:
So we can type the formula in this cell as follow:
Ft+1 = Lt + Tt
For the forecast we need to make the value as integers, so the equation is added after
ROUNDUP function as shown below:
And then drag down the cell to 24𝑡ℎ period in cell F31
7. Find the Forecast value of 25th period (cell F32) to 36th period. The equation is:
Ft+K = Lt + K*Tt
For the forecast we need to make the value as integers, so the equation is added after
ROUNDUP function as shown below:
Figure 2.32 Define Forecast of Upcoming Month
Don’t forget to stabilize the value of Level and Trend in 24th period. And then drag down
the cell to 36th period in cell F43
8. Find the error value such as 𝐸𝑟𝑟𝑜𝑟, 𝐸𝑟𝑟𝑜𝑟 2 , 𝐴𝑏𝑠𝑜𝑙𝑢𝑡𝑒 𝐸𝑟𝑟𝑜𝑟, and
𝑃𝑒𝑟𝑐𝑒𝑛𝑡𝑎𝑔𝑒 𝑜𝑓 𝐸𝑟𝑟𝑜𝑟 for 1st to 24th period. Repeat the 13th steps in Case Study 1.
9. Find the accuracy measure such as MSE, MAD, and MAPD. Repeat the 14th
steps in Case Study 1.
10. Tuning of forecasting method
Go to Data tab, and in top right side, look into Solver and click it
-For Set Objective, set it to value of MAPD
-For To, choose Min
-For By Changing Variable Cells, set in to the parameter (alpha and beta value)
-Click Add in the Subject to the Constraint and in the cell reference, set it to the parameter
(alpha and beta value) and for the constraint fill it by 1. Make sure the symbol is less than or
equal to
-Choose Evolutionary as the solving method
Figure 2. 32 Solver Parameters of Linear Trend Case
-Make sure that the solver parameters are filled as same as the picture above, then click solve
11. Sum the forecasted demand from 25th to the 36th period using SUM function
Case Study 3:
PPSL Company is a private company which needs to forecast overall the sales over the near
future. The historical demand of carton during past 24 periods is reported in Table 2.3.
Period Quantity Period Quantity
1 437 13 425
2 623 14 1846
3 1535 15 2380
4 1946 16 2811
5 1552 17 4133
6 2467 18 5405
7 4483 19 7919
8 5929 20 8366
9 4290 21 7395
10 3398 22 3953
11 506 23 1080
12 347 24 581
Tabel 2.3 Sales of Carton During Past 24 Periods
Based on the table, help PPSL Company to evaluate the data, shows the data pattern, forecast using
appropriate method, Monitor the forecast by using accuracy measure, and tuning the forecast
method by minimize its accuracy value
Completion Steps:
1. Go to Seasonal Variant and input parameters randomly. (ex: is 0.4, β is 0.5, and γ is 0.6)
2. Go to Seasonal and find the seasonal index of 1st to 12th period (Cell F7 to F18). The value
is obtained by dividing demand with average of demand in 12 periods as shown bellow:
4. Find the Initial Trend. This value is defined in 13th period (Cell E19)
Figure 2.36 Define Initial Trend of Seasonal Variant Case
5. Define Level value of 14th period to 24th period using this equation
Based on this equation, we can define cell D20 as shown below:
We use ROUNDUP function to make the value as integers. Then, drag down the cell to 24th
period
We use ROUNDUP function to make the value as integers. Then, drag down the cell to to 36th
period
10. Sum the forecasted demand from to 25th to 36th period using SUM function