Guideline Module 1

Download as pdf or txt
Download as pdf or txt
You are on page 1of 19

𝟏𝒔𝒕 MODULE – FORECASTING LOGISTICS REQUIREMENT

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

Lab Requirement: Overview Module:


1. Student identity card

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

Period Quantity Period Quantity


1 84 13 87
2 95 14 87
3 81 15 96
4 93 16 81
5 86 17
6 92 18 89
7 130 19 91
8 81 20 87
9 93 21 95
10 84 22 90
11 95 23 82
12 80 24 87

Tabel 2.1 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 Preprocessing and determine the demand by referencing its value to demand in


case study 1. In cell C3, the formula could be typed as =’Case Study 1’!P6 and drag
down the cell until 12th period.
Figure 1.1 Filling Demand Values (1)
2. Then, go for 13th period and do the same like the previous step. Drag down the cell until 24th
period

Figure 2.2 Filling Demand Values (2)


3. There’s a missing data in period 17. Replace it by sum the value in previous period and value
in next period and divide them by 2. So, it could be typed as =(C16+C18)/2 and the result
would be 88.

Figure 2.3 Evaluate Missing Data


4. Then, fill the demand statistic value as follows:
-using AVERAGE function to calculate the mean of its value
Figure 2.4 Formula of Mean
-using STDEV.S function to calculate standard deviation

Figure 2.5 Formula of Standard Deviation


-using VAR.S function to calculate variance

Figure 2.6 Formula of Variance


-using QUARTILE.INC function to calculate first quartile

Figure 2.7 Formula of First Quartile


-using QUARTILE.INC function to calculate third quartile

Figure 2.8 Formula of Third Quartile


-Devide standard deviation by mean to find coefficient of variation

Figure 2.9 Coefficient of Variation Formula


5. Determine the lower limit value by input the formula as formula:
=$H$19-1.5*($H$20-$H$19)

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.

Figure 2.12 Insertion of Missing Data


8. Analyze the data
9. Go to Constant Trend and input the α randomly (ex: 0.5)
10. In Initial Level (Level for period-0), use AVERAGE function.

Figure 2.13 Initial Level of Constant Trend

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

Lt+1 = α*Dt+1 + (1-α)*Lt


picture below.
Figure 2.14 Level Value of Constand Trend
Drag down the cell to the 24th period.
12. Find the forecast from 1st to 25th period with this equation.

Ft+1 = Lt
So based on the equation, we can use ROUNDUP(Lt;0)

Figure 2.15 Define Forecast Value


Drag down the cell to the 25th period.
13. Find the error value such as 𝐸𝑟𝑟𝑜𝑟, 𝐸𝑟𝑟𝑜𝑟 2 , 𝐴𝑏𝑠𝑜𝑙𝑢𝑡𝑒 𝐸𝑟𝑟𝑜𝑟, and 𝑃𝑒𝑟𝑐𝑒𝑛𝑡𝑎𝑔𝑒 𝑜𝑓 𝐸𝑟𝑟𝑜𝑟 for
1𝑠𝑡 to the 24𝑡ℎ period.
-In 𝑬𝒓𝒓𝒐𝒓, the cell could be defined as (forecast-demand). So we can type formula as
follow:

Figure 2.16 Define Error Value

and drag down the cell to 24𝑡ℎ period.


-In 𝑬𝒓𝒓𝒐𝒓𝟐 , the cell could be defined as follow:
Figure 2.17 Define 𝐸𝑟𝑟𝑜𝑟 2 Value

and drag down the cell to 24𝑡ℎ period.


-In 𝑨𝒃𝒔𝒐𝒍𝒖𝒕𝒆 𝑬𝒓𝒓𝒐𝒓, the cell could be defined as follow:

Figure 2.18 Define Value of Absolute Error

and drag down the cell to 24𝑡ℎ period.

-In 𝑷𝒆𝒓𝒄𝒆𝒏𝒕𝒂𝒈𝒆 𝒐𝒇 𝑬𝒓𝒓𝒐𝒓, the cell could be defined as follow:

Figure 2.19 Define Value of Error Percentage

and drag down the cell to 24𝑡ℎ period.


14. Find the accuracy measure such as MSE, MAD, and MAPD
-MSE is defined by averaging the 𝑬𝒓𝒓𝒐𝒓𝟐. So, the formula could be typed as follow:

Figure 2.20 Define MSE

-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:

Figure 2.22 Define MAPD


15. Tuning of forecasting method
Go to Data tab, and in the top right side, look into Solver and click it
-For Set Objective, set in to value of MAPD
-For To, choose Min
-For By Changing Variable Cells, set it to the parameter (alpha value)
-Click Add in the Subject to the constraints and in the cell reference, set it to the parameter
(alpha 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.23 Solver Parameters of Constant Trend Case


-Make sure that the solver parameters are filled as same as the picture above, then click solve
16. Interpretate the result
Case Study 2:

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

Period Quantity Period Quantity


1 255 13 348
2 259 14 354
3 267 15 360
4 270 16 369
5 279 17 376
6 289 18 380
7 295 19 382
8 319 20 383
9 324 21 387
10 334 22 392
11 340 23 395
12 342 24 397

Tabel 2.1 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 Linear Trend and input parameters randomly. (ex: α is 0.6 and β is 0.7)
2. Define the initial level and initial trend by follow this instructions below:
-For initial level in cell D7, we can type =INTERCEPT(Demand:Period)

Figure 2.24 Using Intercept Function


-For initial trend in cell E7, we can type =SLOPE(Demand:Period)

Figure 2.25 Using Slope Function


3. Go to Data tab, in the top right side, look into Data Analysis and click it
-Find Regression and click OK
-In Input Y Range, click the arrow symbol and block data Demand

Figure 2.26 Regression: Input Range of Y


-In Input X Range, click the arrow symbol and block data Period

Figure 2.27 Regression: Input Range of X

-In Output options, choose Output Range and click the arrow symbol block the provided cells

Figure 2.28 Regression: Input Parameter

Lt+1 = α*Dt+1 + (1-α)*(Lt +Tt)

-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:

Figure 2.29 Define Value of Level


Don’t forget to stabilize the alpha value.
And then, drag down the cell to 24𝑡ℎ period in cell D31
5. Find the Trend value of 1st period (Cell E8) to 24𝑡ℎ period. The equation is:

Tt+1 = β*(Lt+1 - Lt) + (1-β)*Tt

So we can type in this cell as follow:

Figure 2.30 Define Value of Trend


Don’t forget to stabilize the beta value.
And then drag, down the cell to 24𝑡ℎ period in cell E31
6. Find the Forecast value of 1st period (Cell F8) to 24th period. The equation is:

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:

Figure 2.31 Define Value of Forecasting

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

Figure 2.33 Total Amount of Demand Forecast


12. Interpretate the result

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:

Figure 2.34 Define Seasonal Index of First Period

And then drag down the cell to period 12.


3. Find the Initial Level. This value is defined in 13th period (Cell D19) and can be obtained by
divide the actual demand with value of seasonal index in the same period last year (for this
case is seasonal index in 1st period).

Figure 2.35 Define Initial Level of Seasonal Variant Case

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:

Level t = α*Dt/Seasonal t-M + (1-α)*(Level t-1 + Trend t-1)

Figure 2.37 Define Level of Seasonal Variant Case


Where C1 is value of alpha, and don’t forget to stabilize every parameters (using “$”).
Then, drag down to 24th period.
6. Defined Trend value of 14th period to 24th period using this equation

Trend t = β*(Level t - Level t-1) + (1-β)*Trend t-1


Based on this equation, we can define cell E20 as shown below:

Figure 2.38 Define Trend of Seasonal Variant Case


Where C2 is value of beta. Don’t forget to stabilize every parameters (using “$”).
Then, drag down to 24th period.
7. Define the Seasonal value of 13th to 24th period using this equation

Seasonal t = γ*Dt/Level t + (1-γ)*Seasonal t-M

Based on this equation, we can define cell F19 as shown below:

Figure 2.39 Define Seasonal Index of Second Period


Where C3 is value of gamma and F7 is seasonal index of the same month in previous year.
Don’t forget to stabilize every parameters.
Then, drag down the cell to 24th period.
8. Demand the Forecast value of 14th to 24th period using this equation

Ft+1 = (Level t + Trend t) * Seasonal t-M+1

Based on this equation, we can define cell G20 as shown below:

Figure 2.40 Define Forecast Value

We use ROUNDUP function to make the value as integers. Then, drag down the cell to 24th
period

Ft+K = (Level t + K*Trend t)*Seasonal t-M


9. Define the Forecast value of 25th to 36th period using this equation

Based on this equation we can define cell G31 as shown below:

Figure 2.41 Define Forecast Value of Upcoming Month

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

Figure 2.42 Total Amount of Demand Forecasted


11. Tuning of forecasting method
Go to Data tab, and in the 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 it to the parameters (alpha, beta and gamma)
-Click Add in the Subject to the Constraints and in the cell reference, set it to the parameters
(alpha, beta and gamma) 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.43 Solver Parameters of Seasonal Variant Case


-Make sure that the solver parameters are filled as same as the picture above, then click solve
12. Interpretate the result

You might also like