微信客服：xiaoxionga100
微信客服：ITCS521
MAY19 2023-qbus3330代写
时间：2023-07-02
View
Online
Export
Citation
CrossMark
RESEARCH ARTICLE | MAY 19 2023
Medical product sales forecasting at pharmaceutical
distribution company (Case study: PT. Lenko Surya Perkasa
Branch Office Sidoarjo)
Purbandini ; Indah Werdiningsih; Endah Purwanti; Annisa Anjani
AIP Conference Proceedings 2536, 020014 (2023)
https://doi.org/10.1063/5.0121350
30 June 2023 14:16:28
Medical Product Sales Forecasting at Pharmaceutical
Distribution Company (Case Study: PT. Lenko Surya
Perkasa Branch Office Sidoarjo)
Purbandini1,2, a), Indah Werdiningsih1, b), Endah Purwanti1, c), and Annisa Anjani1, d)
1Information Systems, Faculty of Science and Technology, Universitas Airlangga, Surabaya, Indonesia
2Robotics and Artificial Intelligent Engineering, Faculty of Technology Advance and Multidicipline Engineering,
Universitas Airlangga, Surabaya, Indonesia
a)Corresponding author: purbandini@ fst.unair.ac.id
b)indah-w@fst.unair.ac.id
c)endahpurwanti@fst.unair.ac.id
d)annisa.anjani-2017@fst.unair.ac.id
Abstract. The problems that occur in PT. Lenko Surya Perkasa Branch Office Sidoarjo based on sales and warehouse data
for the period October 2019 - August 2020 said that the number of damaged products continued to increase, around 23%
of the total product, low sales quantity, and manual restock quantity estimates that often missed, made the warehouse
storage level higher so that the product damaged due to being in the warehouse for too long. Because of that, we need a
system that can predict how many sold of the product as a basis for consideration restocking products to minimize the level
of warehouse storage and damaged products. The results of this study said that the least square method was not suitable
because the data did not have a physical meaning of a mathematical model. However, it can describe the trend of sales
which in this case tends to decrease. To explain the variation of a dependent variable y as a function of x most tempting
approach to the problem is to use a simple polynomial through least square with MAPE values 42,40468 and MSE
3687209.365 with the optimum orde of the polynomial is 6. The comparison method used in this study is a simple moving
average using Ma (2), Ma (3), Ma (4) stated that forecast results from SMA also produce curves that are similar to the
actual data. It shows that SMA is also suitable to use when the data did not have a physical meaning of a mathematical
model but did not fit when there is a big enough data spike, proven by the MAPE and MSE values which are still higher
than simple polynomial regression.
INTRODUCTION
Every company that produces drugs or what is known as a pharmaceutical company has a distributor to market its
products. Products from the factory will be sent directly to distributors and will offer these products to drugstores,
retailers, and other outlets. The role of distribution and transportation networks is very vital because it allows products
to move from production sites to consumer locations that often limited by very long distances1.
In order services to consumers, the problem of timeliness and number of deliveries is a problem faced by
pharmaceutical distributor companies, where companies must meet customer needs by delivering products in the exact
quantity, right place, and the right time. In distributor companies, the excess product can cause a decrease in revenue.
Therefore, one of the problems with pharmaceutical distributor companies is the large number of each product that
must be kept in stock. So that in its operational activities, the distributor company must be able to manage the strategy
for the entry and exit of goods2.
Proceedings of the International Conference on Advanced Technology and Multidiscipline (ICATAM) 2021
AIP Conf. Proc. 2536, 020014-1–020014-15; https://doi.org/10.1063/5.0121350
Published by AIP Publishing. 978-0-7354-4442-3/$30.00
020014-1
30 June 2023 14:16:28
Precise sales forecasting is a crucial and low-cost technique for every organization to increase revenues, lower
costs, and increase flexibility in the face of change. Proper sales forecasting, in other words, is used to capture the
tradeoff between customer demand satisfaction and inventory costs3. Due to the limited shelf-life of many
pharmaceutical items and the importance of product quality intimately tied to human health4, successful sales
forecasting systems can be advantageous for the pharmaceutical sector.
As faced by PT. Lenko Surya Perkasa Branch Office Sidoarjo that distributes medicines and health products with
an expiration date based on sales and warehouse data for the period October 2019 - August 2020, said the number of
damaged products continued to increase, around 23% of the total product, the low sales quantity, and manual restock
quantity estimation which often misses resulted in higher warehouse storage levels. Proven by several products reload
during the period October 2019 – August 2020, but no sales occurred, resulting in an increase in the number of products
in the warehouse and product damage due to being in the warehouse for too long.
Formulate a strategy for the entry and exit of appropriate goods into a problem at PT. Lenko Surya Perkasa Branch
Office Sidoarjo, forecasting is an alternative solution that can predict the number of sales in the future so that the sales
strategy is formulated according to sales in the market and can generate profits for the company. Because sales forecast
did with high accuracy and a short time, it is impossible to do it manually or traditionally. For this reason, it is
necessary to apply an appropriate forecasting method to increase the accuracy of sales forecasts and speed up the
process. As a result, the purpose of this study relates to an accurate sales forecasting model application for sales of
health products that occurred at PT. Lenko Surya Perkasa Branch Office Sidoarjo.
In this case, the forecasting model used is the Least Square Method (LSM), Simple Polynomial Regression, and
the Simple Moving Average (SMA) as a comparison model, all the methods commonly used in forecasting sales.
LITERATURE REVIEW
Forecasting
Forecasting is a process for estimating some future needs which include terms of quantity, quality, time, and
location needed to meet the demand for goods or services5. It involves collecting past data that is processed
mathematically using a particular model according to the characteristics of the data.
There are two sorts of forecasting methods: qualitative and quantitative. Quantitative forecasting is a statistical
method for creating future projections based on the past, whereas qualitative analysis is subjective and expert opinions.
Demand from solicited opinions modeled using qualitative approaches. Future activity anticipated utilizing
quantitative models from previous cycle sales for products with demand history available6.
Quantitative forecasting can be divided into two categories: time series and causal. Time series analysis uses
previous data and prior knowledge to forecast future characteristics. In order to forecast demand, this technique uses
time as the independent variable. The causal relationship is relevant if there is a causes and effects link between an
input variable and its corresponding output7.
Least Square
Least Square method is a curve fitting method that is widely used for data. Least Square is one of the most popular
methods used to determine the position of the trend line of a given time series. The resulting trend line is technically
called the best model8.
The time series analysis using the Least Square method is divided into two cases, the odd data case and the even
data case9. The Least Square method is one of the approaches used for forecasting, regression or equation formation
from discrete data points (in modeling), and measurement error analysis (in model validation). In the form of the
equation, the linear trend value is presented as follows:
̂ = + (1)
Where:
̂ = Forecasting value
x = Specific time in code form
a = Trend variable Coefficient
b = Trend Direction Coefficient
020014-2
30 June 2023 14:16:28
In determining the value of x, alternative steps are often used by giving a score or code. In this case, the data is
divided into two groups, namely:
1. Even data, then used the score of value x: …, -5, -3, -1,1,3,5, …
2. Odd data, then used the score of value x: …-3, -2, -1,0,1,2,3, …
The principle of the Least Square method is to minimize the number of squared deviations (∑( − ̂)2) from the
value of the independent variable (y) with the forecast value (̂)8. By using partial derivatives, ∑( − ̂)2 can be
minimized, so that two equations will be obtained, namely:
∑ = . + . ∑ (2)
∑ = . ∑ + . ∑ 2 (3)
By solving the two normal equations simultaneously, the values of a and b of equation ̂ = + can be
calculated. To make the calculation simpler, the coding for the value of x is attempted in such a way that ∑ = 0,
thus, the above normal equation can be simplified to:
=
∑
(4)
=
∑
∑2
(5)
Where:
n = Count of data
x = Specific time, in code form
y = Data variabel
Polynomial Regression Through Least Square
When presented with a data set it is often desirable to express the relationship between variables in the form of an
equation. The most common method of representation is a kth order polynomial which which can be seen in Equation
614:
=
+ ⋯+ 1 + 0 + (6)
The above equation is the general polynomial regression model with the error serving as a reminder that the
polynomial will typically provide an estimate rather than an implicit value of the dataset for any given value of x.
The maximum order of the polynomial is dictated by the number of data points used to generate it. For a set of N
data points, the maximal order of the polynomial is k = N-1. But the best practice is to use the lowest possible order
to represent your dataset accurately. The higher the degree of the polynomial as it passes through each data point, it
can exhibit erratic behavior between these points due to a phenomenon known as polynomial wobble.
The general polynomial regression model can be developed using the method of least square. The least-square
model aims to minimize the variance between the values estimated from the polynomial and the expected values from
the dataset.The coefficients of the polynomial regression model ( , −1, … , 1) determined by solving the following
system of linear equations which can be seen in Equation 714:
[
∑ =1 ⋯ ∑
=1
∑ =1 ∑
=1
2 ⋯ ∑ =1
+1
⋮ ⋮ ⋮ ⋮
∑ =1
∑ =1
+1 ⋯ ∑ =1
2 ]
[
0
1
⋮
] =
[
∑ =1
∑ =1
⋮
∑ =1
]
(7)
The optimum order is the one where the value of the variance is minimum or where its value is significantly
decreasing. To determine the optimum order of the polynomial model defined as:
020014-3
30 June 2023 14:16:28
−(+1)
(8)
Where:
sr = Sum of the square of residuals
n = Number of data point
m = The order of the polynomial
Simple Moving Average
Moving Averages is a method of forecasting value smoothing by taking a group of observed values which are
searched for the average, and the average is used for forecasting in the next period10. The Single Moving Average
method uses several new actual demand data to generate forecast values for future demand. This method will be more
effectively applied if we assume that market demand for the product will remain stable over time11. Moving averages
are described as follows:
+ 1 =
+−1+⋯.−+1
(9)
Where:
St+1 = Forecast for period t+1
xt = Data in specific period
n = Moving Average time period
with n value is the number of periods in the moving average11.
Measuring Forecast Accuracy
In forecasting, accuracy is seen as a criterion of rejection and acceptance of a forecasting method. The word
accuracy refers to the suitability of a forecasting method used to process data. If the method used is considered correct
for forecasting, then the selection of the best forecasting method is based on the level of prediction error 12. Jika yt is
the actual data for period t and (̂) is the forecast for the same period, then the error is defined as:
= − ̂ (10)
Where:
et = Error value at t period
yt = Actual data at t period
̂ = Forecast value at t period
If there are observed and forecast values for n time periods, then there will be n errors. There are several ways to
determine the error value, including the following:
1. Mean Absolute Deviation (MAD).
The average difference between projected and actual demand is measured by Mean Absolute Deviation
(MAD). It is the most basic predictor of forecast error. A lower MAD value indicates a more accurate
prognosis. The MAD formula is written as follows:
MAD =
∑ |et|
n
t=1
n
(11)
2. Mean Absolute Percentage Error (MAPE)
The MAPE is a percentage-based error comparison tool. In comparison to actual forecast error, the MAPE
indicates the average relative magnitude of forecast mistakes. MAPE is applicable to a variety of time series
approaches. As a result, it is included in this research. The MAPE formula is written as follows:
020014-4
30 June 2023 14:16:28
MAPE =
∑
|et|
yt
x 100%nt=1
n
(12)
3. Mean Squarred Error (MSE)
Each residual is squared in the MSE, which is analogous to the MAD. Larger forecast inaccuracies are
penalized more severely in this approach. It is used in this study to emphasize forecasts that have relatively
big mistakes. The MSE is calculated using the following formula:
MSE =
∑ et
2n
t=1
n
(13)
4. Mean Percentage Error (MPE)
Mean percentage error is computed average of percentage errors by which forecasts of a model
differ from actual values of the quantity being forecast. The formula for the mean percentage error is:
MPE =
∑
et
yt
n
t=1 x 100 %
n
(14)
RESEARCH METHODOLOGY
This section will go over the study design, sample selection strategy, data collecting, and criteria for selecting a
forecast method in detail. The study design is shown in Figure 1.
FIGURE 1. Flowchart for Research Methodology
Data Collection
There are more than 50 products contained in PT. Lenko Surya Perkasa Branch Office Sidoarjo, but not all products
are routinely sold. The product selected as the sample for forecasting calculations is a product that has sales almost
every month and for the calculation example, we take sales data on Napkin Nasa products.
For the product listed, monthly sales data was collected from January 2018 to August 2020. Data was gathered by
calling the management of PT. Lenko Surya Perkasa Branch Office Sidoarjo. For this study, data such as the number
of units sold, product code, and the number of products left in inventory were very important.
Data Collection
Forecasting Error
Analysis
Data Analysis
End
Start
020014-5
30 June 2023 14:16:28
Data Analysis
This study's research goal is based on historical sales data and presented using quantitative approaches. Each
forecasting approach is examined for ease of use, ease of analysis, and sensitivity to change to select the most
appropriate forecasting methods13. Least Square was chosen as the forecasting method for this investigation and
Simple Moving Average was chosen as a comparative forecasting method for this investigation.
The calculations using the Least Square method will produce an output in the form of a line equation that will
describe the trend of sales forecasting results based on the data. The simple Moving Average method was chosen due
to its ease of usage. It is simple to comprehend and implement.
Forecasting Error Analysis
The forecasting strategies are judged on their ability to estimate actual demand data accurately. The forecast error
was obtained using multiple methodologies such as Mean Absolute Deviation, Mean Squared Error, and Mean
Absolute Percentage Error. Because MAPE is expressed as a percentage, it is a relative metric that MAPE is chosen
over MAD. The MSE was chosen because it aids in more severely penalizing errors.
RESULT AND DISCUSSION
Sales Data
The actual monthly sales data was collected from Januari 2018 to August 2020 obtained from PT. Lenko Surya
Perkasa Branch Office Sidoarjo, especially for Napkin Nasa product. Napkin Nasa sales data can be seen in Table 1.
Jan – 2018 13380
Feb – 2018 7140
Mar – 2018 5100
Apr – 2018 7140
May – 2018 10560
Jun – 2018 8220
Jul – 2018 10560
Aug – 2018 12240
Sep – 2018 14160
Oct – 2018 12142
Nov – 2018 11940
Des – 2018 13500
Jan – 2019 14892
Feb – 2019 7810
Mar – 2019 6120
Apr – 2019 15666
May – 2019 6360
Jun – 2019 5520
Jul – 2019 1059
Aug – 2019 3420
Sep – 2019 732
Oct – 2019 564
Nov – 2019 660
Des – 2019 720
Jan – 2020 849
Feb – 2020 840
Mar – 2020 1560
TABLE 1. Actual Sales Data of Napkin Nasa Product
Month-Year Sales Quantity
020014-6
30 June 2023 14:16:28
Month-Year Sales Quantity
Apr – 2020 600
May – 2020 300
Jun – 2020 420
Jul – 2020 1080
Aug – 2020 480
including the following:
FIGURE 2. Sales Data Chart
From this figure, we know that the variance of the data is non-stationary, and there is no physically meaningful
mathematical model to explain. So, this study will see how the selected forecasting method can adjust to the
characteristics of the data and produce the appropriate forecasting results with the lowest error.
Least Square Method
The forecasting process using the Least Square method begins by determining the number of N (number of
periods/years) and the number of data pairs that will be used in forecasting as the base period. Then the value of a will
be determined (the value of the Trend). Then the system will calculate the value of b (change in Trend value) against
x (period). The results of these calculations will be used to determine the value of y (estimated) or forecasting results
in the period for which the sales level is forecasted. Manual calculation of Napkin Nasa sales forecasting using Least
Square can be seen in Table 2.
N Period y x x2 xy
1 Jan – 2018 13380 -31 961 -414780
2 Feb – 2018 7140 -29 841 -207060
3 Mar – 2018 5100 -27 729 -137700
4 Apr – 2018 7140 -25 625 -178500
5 May – 2018 10560 -23 529 -242880
6 Jun – 2018 8220 -21 441 -172620
7 Jul – 2018 10560 -19 361 -200640
8 Aug – 2018 12240 -17 289 -208080
9 Sep – 2018 14160 -15 225 -212400
Figure 2 is a graph for the actual data starting from January 2018 to August 2020 from Napkin Nasa item,
TABLE 1 Actual Sales Data of Napkin Nasa Product (continued).
TABLE 2. Manual Calculation of Least Square
020014-7
30 June 2023 14:16:28
N Period y x x2 xy
10 Oct – 2018 12142 -13 169 -157846
11 Nov – 2018 11940 -11 121 -131340
12 Des – 2018 13500 -9 81 -121500
13 Jan – 2019 14892 -7 49 -104244
14 Feb – 2019 7810 -5 25 -39050
15 Mar – 2019 6120 -3 9 -18360
16 Apr – 2019 15666 -1 1 -15666
17 May – 2019 6360 1 1 6360
18 Jun – 2019 5520 3 9 16560
19 Jul – 2019 1059 5 25 5295
20 Aug – 2019 3420 7 49 23940
21 Sep – 2019 732 9 81 6588
22 Oct – 2019 564 11 121 6204
23 Nov – 2019 660 13 169 8580
24 Des – 2019 720 15 225 10800
25 Jan – 2020 849 17 289 14433
26 Feb – 2020 840 19 361 15960
27 Mar – 2020 1560 21 441 32760
28 Apr – 2020 600 23 529 13800
29 May – 2020 300 25 625 7500
30 Jun – 2020 420 27 729 11340
31 Jul – 2020 1080 29 841 31320
32 Aug – 2020 480 31 961 14880
N = 32 195734 0 10912 -2336346
After the values of x, x2, and xy have been known, the next step is to determine the value of a and b through
Equation 4 and 5 as follows:
It is known that the total quantity of Napkin Nasa products sold during the period January 2018 - August 2020 was
195734 and total of x2 was 10912 with number of periods was 32, so based on Equation 4 and 5, then:
=
∑
=
195734
32
= 6116.6875
=
∑
∑2
=
−2336346
10912
= −214.1079545
With the known values of a and b, the amounts of sales can be done by determining the line equation based on
Equation 1 as follows:
̂ = + = 6116.6875 + (−214.1079545)
where the value of x corresponds to the time period to be forecasted. The sales forecast for Napkin Nasa products
can be seen in Table 3.
x x2 xy ̂
1 Jan – 2018 13380 -31 961 -414780 12754.03
2 Feb – 2018 7140 -29 841 -207060 12325.82
3 Mar – 2018 5100 -27 729 -137700 11897.6
4 Apr – 2018 7140 -25 625 -178500 11469.39
5 May – 2018 10560 -23 529 -242880 11041.17
TABLE 2 Manual Calculation of Least Square (continued).
TABLE 3. Forecasting Result Using Least Square
N Period y
020014-8
30 June 2023 14:16:28
̂
6 Jun – 2018 8220 -21 441 -172620 10612.95
7 Jul – 2018 10560 -19 361 -200640 10184.74
8 Aug – 2018 12240 -17 289 -208080 9756.523
9 Sep – 2018 14160 -15 225 -212400 9328.307
10 Oct – 2018 12142 -13 169 -157846 8900.091
11 Nov – 2018 11940 -11 121 -131340 8471.875
12 Des – 2018 13500 -9 81 -121500 8043.659
13 Jan – 2019 14892 -7 49 -104244 7615.443
14 Feb – 2019 7810 -5 25 -39050 7187.227
15 Mar – 2019 6120 -3 9 -18360 6759.011
16 Apr – 2019 15666 -1 1 -15666 6330.795
17 May – 2019 6360 1 1 6360 5902.58
18 Jun – 2019 5520 3 9 16560 5474.364
19 Jul – 2019 1059 5 25 5295 5046.148
20 Aug – 2019 3420 7 49 23940 4617.932
21 Sep – 2019 732 9 81 6588 4189.716
22 Oct – 2019 564 11 121 6204 3761.5
23 Nov – 2019 660 13 169 8580 3333.284
24 Des – 2019 720 15 225 10800 2905.068
25 Jan – 2020 849 17 289 14433 2476.852
26 Feb – 2020 840 19 361 15960 2048.636
27 Mar – 2020 1560 21 441 32760 1620.42
28 Apr – 2020 600 23 529 13800 1192.205
29 May – 2020 300 25 625 7500 763.9886
30 Jun – 2020 420 27 729 11340 335.7727
31 Jul – 2020 1080 29 841 31320 -92.4432
32 Aug – 2020 480 31 961 14880 -520.659
N = 32 195734 0 10912 -2336346
By using the least square method can be known the forecast results for the next several periods. The following
example illustrates how least squares forecast the number of sales for the next three periods, as follows:
It is known that the equation of the line in the previous calculation is:
̂ = + = 6116.6875 + (−214.1079545)
because the data is 32, so x is included in the even data, which in the last x value data is 31. Then for the next 3 periods
the x value is 33, 35, 37. The results can be seen in Table 4.
TABLE 4. Future Sales Forecasting Using Least Square
Number Periods Results
1 Sep 2020 ̂ = 6116.6875 + (−214.1079545)33 -948.8749985
2 Oct 2020 ̂ = 6116.6875 + (−214.1079545)35 -1377.090908
3 Nov 2020 ̂ = 6116.6875 + (−214.1079545)37 -1591.198862
The forecast result for the three next period is negative because the trend line in the data tends to decrease, which
allows no sales in the next period. So that the company does not need to restock the product for the next period, but
N Period y x x2 xy
020014-9
30 June 2023 14:16:28
FIGURE 3. Actual Data vs Forecasting Result Using Least Square
From the results above it can be seen that there is no match between the actual data and the forecast results, but
can be known that sales results tend to decrease, this is why the forecast result is negative. This result is due to the
data that does not have a certain pattern and the least square only focuses on linearity trends. When data is available
and there is no physically meaningful mathematical model to explain the variation of a dependent variable y as a
function of x linear least square (ordinary least square) is not suitable, but at least linear least square provides a general
description of sales trends, whether the trend tends to increase or decrease which can be used for companies to analyze
their sales strategies.
Polynomial Regression Through Least Square
To overcome the data with no meaningful mathematical model above, we can use the simple polynomial method.
To find out which order is closest to the data, a comparison between the orders of the polynomial regression method
is carried out by looking for the value of the variance is minimum or where its value is significantly decreasing. Table
5 shows the results of calculations using a simple polynomial.
TABLE 5. Polynomial Model and Optimum Orde
Orde of
Polynomial Polinomial Equation
− ( + )
2 y = 7300.152+(-214.107)x+(-3.470)x2 11862240.18
3 y = 7300.152+(-448.14) x+(-3.47)x2+0.381x3 8253283.9
4 y = 8014.414+(-448.14) x+(-10.484)x2+0.381x3+0.00802x4 8085902.632
5 y = 8014.415+(-776.472) x+(-10.484) x2+1.890x3+0.00802x4+(-0.00133)x5 4967190.034
6 y = 7342.133+(-776.472) x+ 3.480x2+ 1.890x3+(-0.0333)x4+(-0.00133)x5+
0.0000299x6
4719627.987
7 y = 7342.133+(-761.738)x+ 3.48x+1.758x2 +( -0.033)x3 +(-0.00104)x4 +
0.0000299x5+ -0.000000176x6
4912335.477
During the sales forecasting with polynomials regression through least square, it is advised to use the sixth orde of
-2000
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
y y ̂
the company must immediately plan a sales strategy to increase the number of product sales for the next period. Figure
3 is a plotting of actual data and forecast results for the period January 2018 - August 2020.
polynomials or seventh orde of polynomials. Taking into the sum of the square of residuals values that significantly
decrease in experiment, the sixth orde of polynomials is the most appropriate model. Table 6 and Figure 4 show the
result of the forecasting using a simple polynomial regression with sixth orde as the optimum orde.
020014-10
30 June 2023 14:16:28
TABLE 6. Forecast Result Using the Optimum Orde Model of Polynomial Regression Through Least Square
N Period y ̂
1 Jan – 2018 13380 12424.94
2 Feb – 2018 7140 8279.109
3 Mar – 2018 5100 6662.012
4 Apr – 2018 7140 6707.275
5 May – 2018 10560 7730.885
6 Jun – 2018 8220 9208.125
7 Jul – 2018 10560 10751.89
8 Aug – 2018 12240 12092.37
9 Sep – 2018 14160 13058.15
10 Oct – 2018 12142 13558.65
11 Nov – 2018 11940 13567.96
12 Des – 2018 13500 13110.07
13 Jan – 2019 14892 12245.45
14 Feb – 2019 7810 11059
15 Mar – 2019 6120 9649.48
16 Apr – 2019 15666 8120.164
17 May – 2019 6360 6570.998
18 Jun – 2019 5520 5092.081
19 Jul – 2019 1059 3758.54
20 Aug – 2019 3420 2626.781
21 Sep – 2019 732 1732.125
22 Oct – 2019 564 1087.819
23 Nov – 2019 660 685.4287
24 Des – 2019 720 496.6094
25 Jan – 2020 849 476.2593
26 Feb – 2020 840 567.0502
27 Mar – 2020 1560 705.3392
28 Apr – 2020 600 828.459
29 May – 2020 300 883.3897
30 Jun – 2020 420 836.8083
31 Jul – 2020 1080 686.5196
32 Aug – 2020 480 474.2659
FIGURE 4. Actual Data vs Forecast Result Using Polynomial Regression Through Least Square
0
5000
10000
15000
20000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
Real Orde Sixth
020014-11
30 June 2023 14:16:28
y = 7342.133+(-776.472) x+ 3.480x2+ 1.890x3+(-0.0333)x4+(-0.00133)x5+ 0.0000299x6
to determine the value of x in simple polynomial regression is the same as when determining x in the least square
method. So for the next 3 periods the x value is 33, 35, 37. The results can be seen in Table 7.
TABLE 7. Future Sales Using Simple Polynomial Regression
Number Periods Results
1 Sep 2020 y = 7342.133+(-776.472) 33+ 3.480 (33)2+
1.890(33)3+(-0.0333)(33)4+(-0.00133)(33)5+
0.0000299(33)6
299.9167843
2 Oct 2020 y = 7342.133+(-776.472) 35+ 3.480(35)2+
1.890(35)3+(-0.0333)(35)4+(-0.00133)(35)5+
0.0000299(35)6
337.038517
3 Nov 2020 y = 7342.133+(-776.472) 37+ 3.480(37)2+
1.890(37)3+(-0.0333)(37)4+(-0.00133)(37)5+
0.0000299(37)6
849.8433269
From the results above, it can be seen that the results of polynomial regression through least squares can provide
a predictive value for product sales in the next period. However, the results may change with the addition of actual
data in the previous period.
Simple Moving Average
N Period y MA (2) MA (3) MA (4)
1 Jan – 2018 13380 - - -
2 Feb – 2018 7140 - - -
3 Mar – 2018 5100 10260 - -
4 Apr – 2018 7140 6120 8540 -
5 May – 2018 10560 6120 7485 8190
6 Jun – 2018 8220 8850 7600 7485
7 Jul – 2018 10560 9390 9120 7755
8 Aug – 2018 12240 9390 9780 9120
9 Sep – 2018 14160 11400 11295 10395
10 Oct – 2018 12142 13200 12320 11295
11 Nov – 2018 11940 13151 12620.5 12275.5
12 Des – 2018 13500 12041 12747.33 12620.5
13 Jan – 2019 14892 12720 13118.5 12935.5
14 Feb – 2019 7810 14196 13444 13118.5
15 Mar – 2019 6120 11351 10580.5 12035.5
The results of the forecast can be seen in the table, after being plotted in a graph, the forecast has followed the
form of the actual data. To calculate the forecast for the next period using simple polynomial regression is to enter the
value of x according to the period into the equation that has the most optimal orde.
It is known that the optimal equation of the line is sixth orde, so the equation of the line is:
In the calculation of the simple moving average on sales data using three different moving average periods. To
determine the forecast in the future period requires historical data as a parameter for a certain period of time. The
parameters used are Ma (2), Ma (3), and Ma (4). Which forecast results are shown in Table 8 and Figure 5.
TABLE 8. Forecasting Result Using SMA
020014-12
30 June 2023 14:16:28
N Period y MA (2) MA (3) MA (4)
16 Apr – 2019 15666 6965 9607.333 10580.5
17 May – 2019 6360 10893 8989 11122
18 Jun – 2019 5520 11013 9382 8989
19 Jul – 2019 1059 5940 7151.25 8416.5
20 Aug – 2019 3420 3289.5 4313 7151.25
21 Sep – 2019 732 2239.5 2682.75 4089.75
22 Oct – 2019 564 2076 1737 2682.75
23 Nov – 2019 660 648 1344 1443.75
24 Des – 2019 720 612 652 1344
25 Jan – 2020 849 690 698.25 669
26 Feb – 2020 840 784.5 743 698.25
27 Mar – 2020 1560 844.5 992.25 767.25
28 Apr – 2020 600 1200 1083 992.25
29 May – 2020 300 1080 825 962.25
30 Jun – 2020 420 450 820 825
31 Jul – 2020 1080 360 600 720
32 Aug – 2020 480 750 600 600
FIGURE 5. Actual Data vs Forecast Result Using SMA
From the graph, it can be stated that the forecast results from different methods and in this case is SMA also
produce curves that are similar to the actual data. This shows that SMA is also suitable to use when the data does not
have a significant mathematical pattern, but not suitable when there is a large enough data spike.
To calculate the forecast for the next period using SMA, it can only be done for one period only. This is because
historical data must be sufficient for moving average. Table 9 shows the results of forecast calculation using SMA.
TABLE 9. The Result of Forecast Calculation Using SMA
Period Ma
Sep 2020 2 780
Sep 2020 3 660
Sep 2020 4 580
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
Real MA(2) MA(3) MA(4)
TABLE 8 Forecasting Result Using SMA (continued).
020014-13
30 June 2023 14:16:28
Forecasting Error Analysis
Evaluation of the method is done by calculating the error value using Mean Absolute Percentage Error (MAPE)
and Mean Squared Error (MSE). The results of the error values can be used for interpretation and consideration of
forecasting results in decision making. MAPE and MSE values for each method can be seen in Table 10 and Figure
6.
TABLE 10. Measuring Forecasting Error
Method MAPE MSE
Least Square 115.1002708 11867340.99
Polynomial Regression through
Least Square
42.40468734 3687209.365
SMA n = 2 67.92718 9356348.344
SMA n = 3 65.7723 5854598.403
SMA n = 4 88.9908 7922376.555
(a)
(b)
FIGURE 6. (a) Forecast Error Analysis Using MAPE, (b) Forecast Error Analysis Using MSE
CONCLUSION
Least Square method is usually used for data fitting, and the best fit minimizes the residual sum of squares. In
addition, Least Square method is a statistical method used to evaluate regression analysis to approximate the solution
of an overdetermined system. However, there is a hidden danger in using linear models that often plagues new users
of curve-fitting software, because the linear least square only focuses on linearity trends, so it can’t analyze the
fluctuation of the data. When data is available and there is no physical meaningful mathematical model to explain the
change of the dependent variable y as a function of x, one of the ways is to use a simple polynomial, because the orde
of simple polynomial regression can adjust to the actual data curve.
From the comparison of forecasting methods, it is found that polynomial regression through least square has the
smallest error value than the ordinary least square and simple moving average methods. This is because sales data do
not have a specific mathematical pattern and tend to be unstable. The higher orde of the polynomials used, more fit
0
50
100
150
MAPE
MAPE
Least Square
Polynomial Regression through Least Square
SMA n = 2
SMA n = 3
SMA n = 4
0
5000000
10000000
15000000
MSE
MSE
Least Square
Polynomial Regression through Least Square
SMA n = 2
SMA n = 3
SMA n = 4
020014-14
30 June 2023 14:16:28
between the forecast results and the actual data. The optimum order is considered as to be the one where the value of
the variance sr /n-(m+1) is minimum or where its value is significantly decreasing. In this research sixth orde of
polynomial become the most appropriate model with MAPE is 42.40468734 and MSE is 3687209.365.
ACKNOWLEDGMENTS
The authors gratefully acknowledge the support from the Manager of PT. Lenko Surya Perkasa Branch Office
Sidoarjo, Herwin Effendi, Pharmachist Coordinator of PT. Lenko Surya Perkasa Branch Office Sidoarjo, Anang
Subgan, S. Si., Apt., and Universitas Airlangga help the data collection and funding this research that sources from
Universitas Airlangga, the Faculty of Excellence Research Scheme 2021.
REFERENCES
1. Pujawan, I., N., dan Mahendrawathi. Supply Chain Management Second Edition. (Guna Widya.M. P., Surabaya,
2010)
2. Chopra S., Meindl P. Supply Chain Management. Strategy, Planning & Operation. (C., Elschen R. (eds) Das
Summa Summarum des Management, Gabler, Boersch, 2007), pp 265-275.
3. A. Gupta, C. D. Maranas, and C. M. McDonald. Mid-term Supply Chain Planning Under Demand Uncertainty:
Customer Demand Satisfaction and Inventory Management. Computers and Chemical Engineering, vol. 24, no.
12, pp. 2613–2621, (2000).
4. P. Doganis, A. Alexandridis, P. Patrinos, and H. Sarimveis, Time Series Sales Forecasting for Short Shelf-Life
Food Products Based on Artificial Neural Networks and Evolutionary Computing. Journal of Food Engineering,
vol. 75, no. 2, pp. 196–204, (2006).
5. Nasution, A. H., dan Prasetyawan, Y. Perencanaan & Pengendalian Produksi (1st Ed.). Yogyakarta: Graha
Ilmu. (2008)
6. Tersine, R. J. Principles of Inventory and Materials Management (4th Ed.). Englewood Cliffs NJ: Prentice Hall.
(1994)
7. Wheelwright, S. C., & Makridakis, S. Forecasting Methods for Management (4th Ed.). (John Wiley and Sons,
New York, 1985)
8. Molugaram, K., & Rao, G. S. Statistical Techniques for Transportation Engineering. (Butterworth-Heinemann,
United Kingdom, 2017)
9. Rahmawita, M., & Fazri, I. Aplikasi Peramalan Penjualan Obat Menggunakan Metode Least Square Di Rumah
Sakit Bhayangkara. Jurnal Ilmiah Rekayasa dan Manajemen Sistem Informasi, Vol. 4, No. 2, pp. 201-208. (2018)
10. Makridakis, Spyros. Metode dan Aplikasi Peramalan. (Erlangga, Jakarta, 1999)
11. Gaspersz, V. Production Planning and Inventory Control. (Gramedia Pustaka Utama, Jakarta, 1998)
12. Santoso, A., Manongga, D., & Sembiring I. Analysis on the Comparison Exponential Smoothing and Neural
Network in Forecasting the Trend of Toddler Nutritions in Community Health Centre. International Journal of
Computer Science and Software Engineering (IJCSE), Vol. 6, Issue 10, pp. 207-215. (2017)
13. Pilinkienė, V. Selection of market demand forecast methods: Criteria and application. (Engineering Economics,
3(58), 2017), pp 19-25.
14. Wolberg, John. Data Analysis Using the Least Square Method. (Springer-Verlag Berlin Heidelberg, Germany,
2006)
020014-15
30 June 2023 14:16:28
Online
Export
Citation
CrossMark
RESEARCH ARTICLE | MAY 19 2023
Medical product sales forecasting at pharmaceutical
distribution company (Case study: PT. Lenko Surya Perkasa
Branch Office Sidoarjo)
Purbandini ; Indah Werdiningsih; Endah Purwanti; Annisa Anjani
AIP Conference Proceedings 2536, 020014 (2023)
https://doi.org/10.1063/5.0121350
30 June 2023 14:16:28
Medical Product Sales Forecasting at Pharmaceutical
Distribution Company (Case Study: PT. Lenko Surya
Perkasa Branch Office Sidoarjo)
Purbandini1,2, a), Indah Werdiningsih1, b), Endah Purwanti1, c), and Annisa Anjani1, d)
1Information Systems, Faculty of Science and Technology, Universitas Airlangga, Surabaya, Indonesia
2Robotics and Artificial Intelligent Engineering, Faculty of Technology Advance and Multidicipline Engineering,
Universitas Airlangga, Surabaya, Indonesia
a)Corresponding author: purbandini@ fst.unair.ac.id
b)indah-w@fst.unair.ac.id
c)endahpurwanti@fst.unair.ac.id
d)annisa.anjani-2017@fst.unair.ac.id
Abstract. The problems that occur in PT. Lenko Surya Perkasa Branch Office Sidoarjo based on sales and warehouse data
for the period October 2019 - August 2020 said that the number of damaged products continued to increase, around 23%
of the total product, low sales quantity, and manual restock quantity estimates that often missed, made the warehouse
storage level higher so that the product damaged due to being in the warehouse for too long. Because of that, we need a
system that can predict how many sold of the product as a basis for consideration restocking products to minimize the level
of warehouse storage and damaged products. The results of this study said that the least square method was not suitable
because the data did not have a physical meaning of a mathematical model. However, it can describe the trend of sales
which in this case tends to decrease. To explain the variation of a dependent variable y as a function of x most tempting
approach to the problem is to use a simple polynomial through least square with MAPE values 42,40468 and MSE
3687209.365 with the optimum orde of the polynomial is 6. The comparison method used in this study is a simple moving
average using Ma (2), Ma (3), Ma (4) stated that forecast results from SMA also produce curves that are similar to the
actual data. It shows that SMA is also suitable to use when the data did not have a physical meaning of a mathematical
model but did not fit when there is a big enough data spike, proven by the MAPE and MSE values which are still higher
than simple polynomial regression.
INTRODUCTION
Every company that produces drugs or what is known as a pharmaceutical company has a distributor to market its
products. Products from the factory will be sent directly to distributors and will offer these products to drugstores,
retailers, and other outlets. The role of distribution and transportation networks is very vital because it allows products
to move from production sites to consumer locations that often limited by very long distances1.
In order services to consumers, the problem of timeliness and number of deliveries is a problem faced by
pharmaceutical distributor companies, where companies must meet customer needs by delivering products in the exact
quantity, right place, and the right time. In distributor companies, the excess product can cause a decrease in revenue.
Therefore, one of the problems with pharmaceutical distributor companies is the large number of each product that
must be kept in stock. So that in its operational activities, the distributor company must be able to manage the strategy
for the entry and exit of goods2.
Proceedings of the International Conference on Advanced Technology and Multidiscipline (ICATAM) 2021
AIP Conf. Proc. 2536, 020014-1–020014-15; https://doi.org/10.1063/5.0121350
Published by AIP Publishing. 978-0-7354-4442-3/$30.00
020014-1
30 June 2023 14:16:28
Precise sales forecasting is a crucial and low-cost technique for every organization to increase revenues, lower
costs, and increase flexibility in the face of change. Proper sales forecasting, in other words, is used to capture the
tradeoff between customer demand satisfaction and inventory costs3. Due to the limited shelf-life of many
pharmaceutical items and the importance of product quality intimately tied to human health4, successful sales
forecasting systems can be advantageous for the pharmaceutical sector.
As faced by PT. Lenko Surya Perkasa Branch Office Sidoarjo that distributes medicines and health products with
an expiration date based on sales and warehouse data for the period October 2019 - August 2020, said the number of
damaged products continued to increase, around 23% of the total product, the low sales quantity, and manual restock
quantity estimation which often misses resulted in higher warehouse storage levels. Proven by several products reload
during the period October 2019 – August 2020, but no sales occurred, resulting in an increase in the number of products
in the warehouse and product damage due to being in the warehouse for too long.
Formulate a strategy for the entry and exit of appropriate goods into a problem at PT. Lenko Surya Perkasa Branch
Office Sidoarjo, forecasting is an alternative solution that can predict the number of sales in the future so that the sales
strategy is formulated according to sales in the market and can generate profits for the company. Because sales forecast
did with high accuracy and a short time, it is impossible to do it manually or traditionally. For this reason, it is
necessary to apply an appropriate forecasting method to increase the accuracy of sales forecasts and speed up the
process. As a result, the purpose of this study relates to an accurate sales forecasting model application for sales of
health products that occurred at PT. Lenko Surya Perkasa Branch Office Sidoarjo.
In this case, the forecasting model used is the Least Square Method (LSM), Simple Polynomial Regression, and
the Simple Moving Average (SMA) as a comparison model, all the methods commonly used in forecasting sales.
LITERATURE REVIEW
Forecasting
Forecasting is a process for estimating some future needs which include terms of quantity, quality, time, and
location needed to meet the demand for goods or services5. It involves collecting past data that is processed
mathematically using a particular model according to the characteristics of the data.
There are two sorts of forecasting methods: qualitative and quantitative. Quantitative forecasting is a statistical
method for creating future projections based on the past, whereas qualitative analysis is subjective and expert opinions.
Demand from solicited opinions modeled using qualitative approaches. Future activity anticipated utilizing
quantitative models from previous cycle sales for products with demand history available6.
Quantitative forecasting can be divided into two categories: time series and causal. Time series analysis uses
previous data and prior knowledge to forecast future characteristics. In order to forecast demand, this technique uses
time as the independent variable. The causal relationship is relevant if there is a causes and effects link between an
input variable and its corresponding output7.
Least Square
Least Square method is a curve fitting method that is widely used for data. Least Square is one of the most popular
methods used to determine the position of the trend line of a given time series. The resulting trend line is technically
called the best model8.
The time series analysis using the Least Square method is divided into two cases, the odd data case and the even
data case9. The Least Square method is one of the approaches used for forecasting, regression or equation formation
from discrete data points (in modeling), and measurement error analysis (in model validation). In the form of the
equation, the linear trend value is presented as follows:
̂ = + (1)
Where:
̂ = Forecasting value
x = Specific time in code form
a = Trend variable Coefficient
b = Trend Direction Coefficient
020014-2
30 June 2023 14:16:28
In determining the value of x, alternative steps are often used by giving a score or code. In this case, the data is
divided into two groups, namely:
1. Even data, then used the score of value x: …, -5, -3, -1,1,3,5, …
2. Odd data, then used the score of value x: …-3, -2, -1,0,1,2,3, …
The principle of the Least Square method is to minimize the number of squared deviations (∑( − ̂)2) from the
value of the independent variable (y) with the forecast value (̂)8. By using partial derivatives, ∑( − ̂)2 can be
minimized, so that two equations will be obtained, namely:
∑ = . + . ∑ (2)
∑ = . ∑ + . ∑ 2 (3)
By solving the two normal equations simultaneously, the values of a and b of equation ̂ = + can be
calculated. To make the calculation simpler, the coding for the value of x is attempted in such a way that ∑ = 0,
thus, the above normal equation can be simplified to:
=
∑
(4)
=
∑
∑2
(5)
Where:
n = Count of data
x = Specific time, in code form
y = Data variabel
Polynomial Regression Through Least Square
When presented with a data set it is often desirable to express the relationship between variables in the form of an
equation. The most common method of representation is a kth order polynomial which which can be seen in Equation
614:
=
+ ⋯+ 1 + 0 + (6)
The above equation is the general polynomial regression model with the error serving as a reminder that the
polynomial will typically provide an estimate rather than an implicit value of the dataset for any given value of x.
The maximum order of the polynomial is dictated by the number of data points used to generate it. For a set of N
data points, the maximal order of the polynomial is k = N-1. But the best practice is to use the lowest possible order
to represent your dataset accurately. The higher the degree of the polynomial as it passes through each data point, it
can exhibit erratic behavior between these points due to a phenomenon known as polynomial wobble.
The general polynomial regression model can be developed using the method of least square. The least-square
model aims to minimize the variance between the values estimated from the polynomial and the expected values from
the dataset.The coefficients of the polynomial regression model ( , −1, … , 1) determined by solving the following
system of linear equations which can be seen in Equation 714:
[
∑ =1 ⋯ ∑
=1
∑ =1 ∑
=1
2 ⋯ ∑ =1
+1
⋮ ⋮ ⋮ ⋮
∑ =1
∑ =1
+1 ⋯ ∑ =1
2 ]
[
0
1
⋮
] =
[
∑ =1
∑ =1
⋮
∑ =1
]
(7)
The optimum order is the one where the value of the variance is minimum or where its value is significantly
decreasing. To determine the optimum order of the polynomial model defined as:
020014-3
30 June 2023 14:16:28
−(+1)
(8)
Where:
sr = Sum of the square of residuals
n = Number of data point
m = The order of the polynomial
Simple Moving Average
Moving Averages is a method of forecasting value smoothing by taking a group of observed values which are
searched for the average, and the average is used for forecasting in the next period10. The Single Moving Average
method uses several new actual demand data to generate forecast values for future demand. This method will be more
effectively applied if we assume that market demand for the product will remain stable over time11. Moving averages
are described as follows:
+ 1 =
+−1+⋯.−+1
(9)
Where:
St+1 = Forecast for period t+1
xt = Data in specific period
n = Moving Average time period
with n value is the number of periods in the moving average11.
Measuring Forecast Accuracy
In forecasting, accuracy is seen as a criterion of rejection and acceptance of a forecasting method. The word
accuracy refers to the suitability of a forecasting method used to process data. If the method used is considered correct
for forecasting, then the selection of the best forecasting method is based on the level of prediction error 12. Jika yt is
the actual data for period t and (̂) is the forecast for the same period, then the error is defined as:
= − ̂ (10)
Where:
et = Error value at t period
yt = Actual data at t period
̂ = Forecast value at t period
If there are observed and forecast values for n time periods, then there will be n errors. There are several ways to
determine the error value, including the following:
1. Mean Absolute Deviation (MAD).
The average difference between projected and actual demand is measured by Mean Absolute Deviation
(MAD). It is the most basic predictor of forecast error. A lower MAD value indicates a more accurate
prognosis. The MAD formula is written as follows:
MAD =
∑ |et|
n
t=1
n
(11)
2. Mean Absolute Percentage Error (MAPE)
The MAPE is a percentage-based error comparison tool. In comparison to actual forecast error, the MAPE
indicates the average relative magnitude of forecast mistakes. MAPE is applicable to a variety of time series
approaches. As a result, it is included in this research. The MAPE formula is written as follows:
020014-4
30 June 2023 14:16:28
MAPE =
∑
|et|
yt
x 100%nt=1
n
(12)
3. Mean Squarred Error (MSE)
Each residual is squared in the MSE, which is analogous to the MAD. Larger forecast inaccuracies are
penalized more severely in this approach. It is used in this study to emphasize forecasts that have relatively
big mistakes. The MSE is calculated using the following formula:
MSE =
∑ et
2n
t=1
n
(13)
4. Mean Percentage Error (MPE)
Mean percentage error is computed average of percentage errors by which forecasts of a model
differ from actual values of the quantity being forecast. The formula for the mean percentage error is:
MPE =
∑
et
yt
n
t=1 x 100 %
n
(14)
RESEARCH METHODOLOGY
This section will go over the study design, sample selection strategy, data collecting, and criteria for selecting a
forecast method in detail. The study design is shown in Figure 1.
FIGURE 1. Flowchart for Research Methodology
Data Collection
There are more than 50 products contained in PT. Lenko Surya Perkasa Branch Office Sidoarjo, but not all products
are routinely sold. The product selected as the sample for forecasting calculations is a product that has sales almost
every month and for the calculation example, we take sales data on Napkin Nasa products.
For the product listed, monthly sales data was collected from January 2018 to August 2020. Data was gathered by
calling the management of PT. Lenko Surya Perkasa Branch Office Sidoarjo. For this study, data such as the number
of units sold, product code, and the number of products left in inventory were very important.
Data Collection
Forecasting Error
Analysis
Data Analysis
End
Start
020014-5
30 June 2023 14:16:28
Data Analysis
This study's research goal is based on historical sales data and presented using quantitative approaches. Each
forecasting approach is examined for ease of use, ease of analysis, and sensitivity to change to select the most
appropriate forecasting methods13. Least Square was chosen as the forecasting method for this investigation and
Simple Moving Average was chosen as a comparative forecasting method for this investigation.
The calculations using the Least Square method will produce an output in the form of a line equation that will
describe the trend of sales forecasting results based on the data. The simple Moving Average method was chosen due
to its ease of usage. It is simple to comprehend and implement.
Forecasting Error Analysis
The forecasting strategies are judged on their ability to estimate actual demand data accurately. The forecast error
was obtained using multiple methodologies such as Mean Absolute Deviation, Mean Squared Error, and Mean
Absolute Percentage Error. Because MAPE is expressed as a percentage, it is a relative metric that MAPE is chosen
over MAD. The MSE was chosen because it aids in more severely penalizing errors.
RESULT AND DISCUSSION
Sales Data
The actual monthly sales data was collected from Januari 2018 to August 2020 obtained from PT. Lenko Surya
Perkasa Branch Office Sidoarjo, especially for Napkin Nasa product. Napkin Nasa sales data can be seen in Table 1.
Jan – 2018 13380
Feb – 2018 7140
Mar – 2018 5100
Apr – 2018 7140
May – 2018 10560
Jun – 2018 8220
Jul – 2018 10560
Aug – 2018 12240
Sep – 2018 14160
Oct – 2018 12142
Nov – 2018 11940
Des – 2018 13500
Jan – 2019 14892
Feb – 2019 7810
Mar – 2019 6120
Apr – 2019 15666
May – 2019 6360
Jun – 2019 5520
Jul – 2019 1059
Aug – 2019 3420
Sep – 2019 732
Oct – 2019 564
Nov – 2019 660
Des – 2019 720
Jan – 2020 849
Feb – 2020 840
Mar – 2020 1560
TABLE 1. Actual Sales Data of Napkin Nasa Product
Month-Year Sales Quantity
020014-6
30 June 2023 14:16:28
Month-Year Sales Quantity
Apr – 2020 600
May – 2020 300
Jun – 2020 420
Jul – 2020 1080
Aug – 2020 480
including the following:
FIGURE 2. Sales Data Chart
From this figure, we know that the variance of the data is non-stationary, and there is no physically meaningful
mathematical model to explain. So, this study will see how the selected forecasting method can adjust to the
characteristics of the data and produce the appropriate forecasting results with the lowest error.
Least Square Method
The forecasting process using the Least Square method begins by determining the number of N (number of
periods/years) and the number of data pairs that will be used in forecasting as the base period. Then the value of a will
be determined (the value of the Trend). Then the system will calculate the value of b (change in Trend value) against
x (period). The results of these calculations will be used to determine the value of y (estimated) or forecasting results
in the period for which the sales level is forecasted. Manual calculation of Napkin Nasa sales forecasting using Least
Square can be seen in Table 2.
N Period y x x2 xy
1 Jan – 2018 13380 -31 961 -414780
2 Feb – 2018 7140 -29 841 -207060
3 Mar – 2018 5100 -27 729 -137700
4 Apr – 2018 7140 -25 625 -178500
5 May – 2018 10560 -23 529 -242880
6 Jun – 2018 8220 -21 441 -172620
7 Jul – 2018 10560 -19 361 -200640
8 Aug – 2018 12240 -17 289 -208080
9 Sep – 2018 14160 -15 225 -212400
Figure 2 is a graph for the actual data starting from January 2018 to August 2020 from Napkin Nasa item,
TABLE 1 Actual Sales Data of Napkin Nasa Product (continued).
TABLE 2. Manual Calculation of Least Square
020014-7
30 June 2023 14:16:28
N Period y x x2 xy
10 Oct – 2018 12142 -13 169 -157846
11 Nov – 2018 11940 -11 121 -131340
12 Des – 2018 13500 -9 81 -121500
13 Jan – 2019 14892 -7 49 -104244
14 Feb – 2019 7810 -5 25 -39050
15 Mar – 2019 6120 -3 9 -18360
16 Apr – 2019 15666 -1 1 -15666
17 May – 2019 6360 1 1 6360
18 Jun – 2019 5520 3 9 16560
19 Jul – 2019 1059 5 25 5295
20 Aug – 2019 3420 7 49 23940
21 Sep – 2019 732 9 81 6588
22 Oct – 2019 564 11 121 6204
23 Nov – 2019 660 13 169 8580
24 Des – 2019 720 15 225 10800
25 Jan – 2020 849 17 289 14433
26 Feb – 2020 840 19 361 15960
27 Mar – 2020 1560 21 441 32760
28 Apr – 2020 600 23 529 13800
29 May – 2020 300 25 625 7500
30 Jun – 2020 420 27 729 11340
31 Jul – 2020 1080 29 841 31320
32 Aug – 2020 480 31 961 14880
N = 32 195734 0 10912 -2336346
After the values of x, x2, and xy have been known, the next step is to determine the value of a and b through
Equation 4 and 5 as follows:
It is known that the total quantity of Napkin Nasa products sold during the period January 2018 - August 2020 was
195734 and total of x2 was 10912 with number of periods was 32, so based on Equation 4 and 5, then:
=
∑
=
195734
32
= 6116.6875
=
∑
∑2
=
−2336346
10912
= −214.1079545
With the known values of a and b, the amounts of sales can be done by determining the line equation based on
Equation 1 as follows:
̂ = + = 6116.6875 + (−214.1079545)
where the value of x corresponds to the time period to be forecasted. The sales forecast for Napkin Nasa products
can be seen in Table 3.
x x2 xy ̂
1 Jan – 2018 13380 -31 961 -414780 12754.03
2 Feb – 2018 7140 -29 841 -207060 12325.82
3 Mar – 2018 5100 -27 729 -137700 11897.6
4 Apr – 2018 7140 -25 625 -178500 11469.39
5 May – 2018 10560 -23 529 -242880 11041.17
TABLE 2 Manual Calculation of Least Square (continued).
TABLE 3. Forecasting Result Using Least Square
N Period y
020014-8
30 June 2023 14:16:28
̂
6 Jun – 2018 8220 -21 441 -172620 10612.95
7 Jul – 2018 10560 -19 361 -200640 10184.74
8 Aug – 2018 12240 -17 289 -208080 9756.523
9 Sep – 2018 14160 -15 225 -212400 9328.307
10 Oct – 2018 12142 -13 169 -157846 8900.091
11 Nov – 2018 11940 -11 121 -131340 8471.875
12 Des – 2018 13500 -9 81 -121500 8043.659
13 Jan – 2019 14892 -7 49 -104244 7615.443
14 Feb – 2019 7810 -5 25 -39050 7187.227
15 Mar – 2019 6120 -3 9 -18360 6759.011
16 Apr – 2019 15666 -1 1 -15666 6330.795
17 May – 2019 6360 1 1 6360 5902.58
18 Jun – 2019 5520 3 9 16560 5474.364
19 Jul – 2019 1059 5 25 5295 5046.148
20 Aug – 2019 3420 7 49 23940 4617.932
21 Sep – 2019 732 9 81 6588 4189.716
22 Oct – 2019 564 11 121 6204 3761.5
23 Nov – 2019 660 13 169 8580 3333.284
24 Des – 2019 720 15 225 10800 2905.068
25 Jan – 2020 849 17 289 14433 2476.852
26 Feb – 2020 840 19 361 15960 2048.636
27 Mar – 2020 1560 21 441 32760 1620.42
28 Apr – 2020 600 23 529 13800 1192.205
29 May – 2020 300 25 625 7500 763.9886
30 Jun – 2020 420 27 729 11340 335.7727
31 Jul – 2020 1080 29 841 31320 -92.4432
32 Aug – 2020 480 31 961 14880 -520.659
N = 32 195734 0 10912 -2336346
By using the least square method can be known the forecast results for the next several periods. The following
example illustrates how least squares forecast the number of sales for the next three periods, as follows:
It is known that the equation of the line in the previous calculation is:
̂ = + = 6116.6875 + (−214.1079545)
because the data is 32, so x is included in the even data, which in the last x value data is 31. Then for the next 3 periods
the x value is 33, 35, 37. The results can be seen in Table 4.
TABLE 4. Future Sales Forecasting Using Least Square
Number Periods Results
1 Sep 2020 ̂ = 6116.6875 + (−214.1079545)33 -948.8749985
2 Oct 2020 ̂ = 6116.6875 + (−214.1079545)35 -1377.090908
3 Nov 2020 ̂ = 6116.6875 + (−214.1079545)37 -1591.198862
The forecast result for the three next period is negative because the trend line in the data tends to decrease, which
allows no sales in the next period. So that the company does not need to restock the product for the next period, but
N Period y x x2 xy
020014-9
30 June 2023 14:16:28
FIGURE 3. Actual Data vs Forecasting Result Using Least Square
From the results above it can be seen that there is no match between the actual data and the forecast results, but
can be known that sales results tend to decrease, this is why the forecast result is negative. This result is due to the
data that does not have a certain pattern and the least square only focuses on linearity trends. When data is available
and there is no physically meaningful mathematical model to explain the variation of a dependent variable y as a
function of x linear least square (ordinary least square) is not suitable, but at least linear least square provides a general
description of sales trends, whether the trend tends to increase or decrease which can be used for companies to analyze
their sales strategies.
Polynomial Regression Through Least Square
To overcome the data with no meaningful mathematical model above, we can use the simple polynomial method.
To find out which order is closest to the data, a comparison between the orders of the polynomial regression method
is carried out by looking for the value of the variance is minimum or where its value is significantly decreasing. Table
5 shows the results of calculations using a simple polynomial.
TABLE 5. Polynomial Model and Optimum Orde
Orde of
Polynomial Polinomial Equation
− ( + )
2 y = 7300.152+(-214.107)x+(-3.470)x2 11862240.18
3 y = 7300.152+(-448.14) x+(-3.47)x2+0.381x3 8253283.9
4 y = 8014.414+(-448.14) x+(-10.484)x2+0.381x3+0.00802x4 8085902.632
5 y = 8014.415+(-776.472) x+(-10.484) x2+1.890x3+0.00802x4+(-0.00133)x5 4967190.034
6 y = 7342.133+(-776.472) x+ 3.480x2+ 1.890x3+(-0.0333)x4+(-0.00133)x5+
0.0000299x6
4719627.987
7 y = 7342.133+(-761.738)x+ 3.48x+1.758x2 +( -0.033)x3 +(-0.00104)x4 +
0.0000299x5+ -0.000000176x6
4912335.477
During the sales forecasting with polynomials regression through least square, it is advised to use the sixth orde of
-2000
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
y y ̂
the company must immediately plan a sales strategy to increase the number of product sales for the next period. Figure
3 is a plotting of actual data and forecast results for the period January 2018 - August 2020.
polynomials or seventh orde of polynomials. Taking into the sum of the square of residuals values that significantly
decrease in experiment, the sixth orde of polynomials is the most appropriate model. Table 6 and Figure 4 show the
result of the forecasting using a simple polynomial regression with sixth orde as the optimum orde.
020014-10
30 June 2023 14:16:28
TABLE 6. Forecast Result Using the Optimum Orde Model of Polynomial Regression Through Least Square
N Period y ̂
1 Jan – 2018 13380 12424.94
2 Feb – 2018 7140 8279.109
3 Mar – 2018 5100 6662.012
4 Apr – 2018 7140 6707.275
5 May – 2018 10560 7730.885
6 Jun – 2018 8220 9208.125
7 Jul – 2018 10560 10751.89
8 Aug – 2018 12240 12092.37
9 Sep – 2018 14160 13058.15
10 Oct – 2018 12142 13558.65
11 Nov – 2018 11940 13567.96
12 Des – 2018 13500 13110.07
13 Jan – 2019 14892 12245.45
14 Feb – 2019 7810 11059
15 Mar – 2019 6120 9649.48
16 Apr – 2019 15666 8120.164
17 May – 2019 6360 6570.998
18 Jun – 2019 5520 5092.081
19 Jul – 2019 1059 3758.54
20 Aug – 2019 3420 2626.781
21 Sep – 2019 732 1732.125
22 Oct – 2019 564 1087.819
23 Nov – 2019 660 685.4287
24 Des – 2019 720 496.6094
25 Jan – 2020 849 476.2593
26 Feb – 2020 840 567.0502
27 Mar – 2020 1560 705.3392
28 Apr – 2020 600 828.459
29 May – 2020 300 883.3897
30 Jun – 2020 420 836.8083
31 Jul – 2020 1080 686.5196
32 Aug – 2020 480 474.2659
FIGURE 4. Actual Data vs Forecast Result Using Polynomial Regression Through Least Square
0
5000
10000
15000
20000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
Real Orde Sixth
020014-11
30 June 2023 14:16:28
y = 7342.133+(-776.472) x+ 3.480x2+ 1.890x3+(-0.0333)x4+(-0.00133)x5+ 0.0000299x6
to determine the value of x in simple polynomial regression is the same as when determining x in the least square
method. So for the next 3 periods the x value is 33, 35, 37. The results can be seen in Table 7.
TABLE 7. Future Sales Using Simple Polynomial Regression
Number Periods Results
1 Sep 2020 y = 7342.133+(-776.472) 33+ 3.480 (33)2+
1.890(33)3+(-0.0333)(33)4+(-0.00133)(33)5+
0.0000299(33)6
299.9167843
2 Oct 2020 y = 7342.133+(-776.472) 35+ 3.480(35)2+
1.890(35)3+(-0.0333)(35)4+(-0.00133)(35)5+
0.0000299(35)6
337.038517
3 Nov 2020 y = 7342.133+(-776.472) 37+ 3.480(37)2+
1.890(37)3+(-0.0333)(37)4+(-0.00133)(37)5+
0.0000299(37)6
849.8433269
From the results above, it can be seen that the results of polynomial regression through least squares can provide
a predictive value for product sales in the next period. However, the results may change with the addition of actual
data in the previous period.
Simple Moving Average
N Period y MA (2) MA (3) MA (4)
1 Jan – 2018 13380 - - -
2 Feb – 2018 7140 - - -
3 Mar – 2018 5100 10260 - -
4 Apr – 2018 7140 6120 8540 -
5 May – 2018 10560 6120 7485 8190
6 Jun – 2018 8220 8850 7600 7485
7 Jul – 2018 10560 9390 9120 7755
8 Aug – 2018 12240 9390 9780 9120
9 Sep – 2018 14160 11400 11295 10395
10 Oct – 2018 12142 13200 12320 11295
11 Nov – 2018 11940 13151 12620.5 12275.5
12 Des – 2018 13500 12041 12747.33 12620.5
13 Jan – 2019 14892 12720 13118.5 12935.5
14 Feb – 2019 7810 14196 13444 13118.5
15 Mar – 2019 6120 11351 10580.5 12035.5
The results of the forecast can be seen in the table, after being plotted in a graph, the forecast has followed the
form of the actual data. To calculate the forecast for the next period using simple polynomial regression is to enter the
value of x according to the period into the equation that has the most optimal orde.
It is known that the optimal equation of the line is sixth orde, so the equation of the line is:
In the calculation of the simple moving average on sales data using three different moving average periods. To
determine the forecast in the future period requires historical data as a parameter for a certain period of time. The
parameters used are Ma (2), Ma (3), and Ma (4). Which forecast results are shown in Table 8 and Figure 5.
TABLE 8. Forecasting Result Using SMA
020014-12
30 June 2023 14:16:28
N Period y MA (2) MA (3) MA (4)
16 Apr – 2019 15666 6965 9607.333 10580.5
17 May – 2019 6360 10893 8989 11122
18 Jun – 2019 5520 11013 9382 8989
19 Jul – 2019 1059 5940 7151.25 8416.5
20 Aug – 2019 3420 3289.5 4313 7151.25
21 Sep – 2019 732 2239.5 2682.75 4089.75
22 Oct – 2019 564 2076 1737 2682.75
23 Nov – 2019 660 648 1344 1443.75
24 Des – 2019 720 612 652 1344
25 Jan – 2020 849 690 698.25 669
26 Feb – 2020 840 784.5 743 698.25
27 Mar – 2020 1560 844.5 992.25 767.25
28 Apr – 2020 600 1200 1083 992.25
29 May – 2020 300 1080 825 962.25
30 Jun – 2020 420 450 820 825
31 Jul – 2020 1080 360 600 720
32 Aug – 2020 480 750 600 600
FIGURE 5. Actual Data vs Forecast Result Using SMA
From the graph, it can be stated that the forecast results from different methods and in this case is SMA also
produce curves that are similar to the actual data. This shows that SMA is also suitable to use when the data does not
have a significant mathematical pattern, but not suitable when there is a large enough data spike.
To calculate the forecast for the next period using SMA, it can only be done for one period only. This is because
historical data must be sufficient for moving average. Table 9 shows the results of forecast calculation using SMA.
TABLE 9. The Result of Forecast Calculation Using SMA
Period Ma
Sep 2020 2 780
Sep 2020 3 660
Sep 2020 4 580
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
Ja
n
-1
8
M
ar
-1
8
M
ay
-1
8
Ju
l-
1
8
Se
p
-1
8
N
o
v-
1
8
Ja
n
-1
9
M
ar
-1
9
M
ay
-1
9
Ju
l-
1
9
Se
p
-1
9
N
o
v-
1
9
Ja
n
-2
0
M
ar
-2
0
M
ay
-2
0
Ju
l-
2
0
Real vs Forecast Result
Real MA(2) MA(3) MA(4)
TABLE 8 Forecasting Result Using SMA (continued).
020014-13
30 June 2023 14:16:28
Forecasting Error Analysis
Evaluation of the method is done by calculating the error value using Mean Absolute Percentage Error (MAPE)
and Mean Squared Error (MSE). The results of the error values can be used for interpretation and consideration of
forecasting results in decision making. MAPE and MSE values for each method can be seen in Table 10 and Figure
6.
TABLE 10. Measuring Forecasting Error
Method MAPE MSE
Least Square 115.1002708 11867340.99
Polynomial Regression through
Least Square
42.40468734 3687209.365
SMA n = 2 67.92718 9356348.344
SMA n = 3 65.7723 5854598.403
SMA n = 4 88.9908 7922376.555
(a)
(b)
FIGURE 6. (a) Forecast Error Analysis Using MAPE, (b) Forecast Error Analysis Using MSE
CONCLUSION
Least Square method is usually used for data fitting, and the best fit minimizes the residual sum of squares. In
addition, Least Square method is a statistical method used to evaluate regression analysis to approximate the solution
of an overdetermined system. However, there is a hidden danger in using linear models that often plagues new users
of curve-fitting software, because the linear least square only focuses on linearity trends, so it can’t analyze the
fluctuation of the data. When data is available and there is no physical meaningful mathematical model to explain the
change of the dependent variable y as a function of x, one of the ways is to use a simple polynomial, because the orde
of simple polynomial regression can adjust to the actual data curve.
From the comparison of forecasting methods, it is found that polynomial regression through least square has the
smallest error value than the ordinary least square and simple moving average methods. This is because sales data do
not have a specific mathematical pattern and tend to be unstable. The higher orde of the polynomials used, more fit
0
50
100
150
MAPE
MAPE
Least Square
Polynomial Regression through Least Square
SMA n = 2
SMA n = 3
SMA n = 4
0
5000000
10000000
15000000
MSE
MSE
Least Square
Polynomial Regression through Least Square
SMA n = 2
SMA n = 3
SMA n = 4
020014-14
30 June 2023 14:16:28
between the forecast results and the actual data. The optimum order is considered as to be the one where the value of
the variance sr /n-(m+1) is minimum or where its value is significantly decreasing. In this research sixth orde of
polynomial become the most appropriate model with MAPE is 42.40468734 and MSE is 3687209.365.
ACKNOWLEDGMENTS
The authors gratefully acknowledge the support from the Manager of PT. Lenko Surya Perkasa Branch Office
Sidoarjo, Herwin Effendi, Pharmachist Coordinator of PT. Lenko Surya Perkasa Branch Office Sidoarjo, Anang
Subgan, S. Si., Apt., and Universitas Airlangga help the data collection and funding this research that sources from
Universitas Airlangga, the Faculty of Excellence Research Scheme 2021.
REFERENCES
1. Pujawan, I., N., dan Mahendrawathi. Supply Chain Management Second Edition. (Guna Widya.M. P., Surabaya,
2010)
2. Chopra S., Meindl P. Supply Chain Management. Strategy, Planning & Operation. (C., Elschen R. (eds) Das
Summa Summarum des Management, Gabler, Boersch, 2007), pp 265-275.
3. A. Gupta, C. D. Maranas, and C. M. McDonald. Mid-term Supply Chain Planning Under Demand Uncertainty:
Customer Demand Satisfaction and Inventory Management. Computers and Chemical Engineering, vol. 24, no.
12, pp. 2613–2621, (2000).
4. P. Doganis, A. Alexandridis, P. Patrinos, and H. Sarimveis, Time Series Sales Forecasting for Short Shelf-Life
Food Products Based on Artificial Neural Networks and Evolutionary Computing. Journal of Food Engineering,
vol. 75, no. 2, pp. 196–204, (2006).
5. Nasution, A. H., dan Prasetyawan, Y. Perencanaan & Pengendalian Produksi (1st Ed.). Yogyakarta: Graha
Ilmu. (2008)
6. Tersine, R. J. Principles of Inventory and Materials Management (4th Ed.). Englewood Cliffs NJ: Prentice Hall.
(1994)
7. Wheelwright, S. C., & Makridakis, S. Forecasting Methods for Management (4th Ed.). (John Wiley and Sons,
New York, 1985)
8. Molugaram, K., & Rao, G. S. Statistical Techniques for Transportation Engineering. (Butterworth-Heinemann,
United Kingdom, 2017)
9. Rahmawita, M., & Fazri, I. Aplikasi Peramalan Penjualan Obat Menggunakan Metode Least Square Di Rumah
Sakit Bhayangkara. Jurnal Ilmiah Rekayasa dan Manajemen Sistem Informasi, Vol. 4, No. 2, pp. 201-208. (2018)
10. Makridakis, Spyros. Metode dan Aplikasi Peramalan. (Erlangga, Jakarta, 1999)
11. Gaspersz, V. Production Planning and Inventory Control. (Gramedia Pustaka Utama, Jakarta, 1998)
12. Santoso, A., Manongga, D., & Sembiring I. Analysis on the Comparison Exponential Smoothing and Neural
Network in Forecasting the Trend of Toddler Nutritions in Community Health Centre. International Journal of
Computer Science and Software Engineering (IJCSE), Vol. 6, Issue 10, pp. 207-215. (2017)
13. Pilinkienė, V. Selection of market demand forecast methods: Criteria and application. (Engineering Economics,
3(58), 2017), pp 19-25.
14. Wolberg, John. Data Analysis Using the Least Square Method. (Springer-Verlag Berlin Heidelberg, Germany,
2006)
020014-15
30 June 2023 14:16:28
