p–ISSN: 2723 - 6609 e-ISSN: 2745-5254
Vol. 5, No. 10, October 2024 http://jist.publikasiindonesia.id/
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4051
Development of an Excel Dashboard-Based Warehousing
Administration System Using the Design Thinking, EOQ, and
POQ Approaches
Samuel Christian Hamonangan Silitonga Nim
1*
, Firman Hawari
2
Institut Teknologi Sepuluh Nopember, Indonesia
Email: [email protected]
1*
, [email protected]
2
*Correspondence
ABSTRACT
Keywords: stock system;
design thinking; eoq; poq;
inventory cost.
Warehousing is an important part of a manufacturing process
where ideal warehouse management will improve the
company's logistics in times of crisis. This research seeks to
gain an in-depth understanding of the problems faced by
warehousing and create user-focused solutions.
Furthermore, EOQ and POQ methods are used to optimize
stock and order management. EOQ helps determine the
economical number of bookings to be made, while POQ
considers demand variability in determining order
schedules. The results of this study show that with Design
Thinking, it was found that the needs of users are the use of
an easier application system and stock filling standards so
that the accuracy of stock is better maintained. It can be
concluded that the EOQ and POQ methods can overcome
understock and overstock. However, the EOQ method has
less risk than POQ, this is because the company does not
spend a lot of funds and takes with little frequency so it is
more effective and the warehouse movement space can be
more minimal.
Introduction
The strategy for increasing the company's profits is to develop business processes
and sales. According to TOPP, a business process is a logically related chain of repetitive
activities that uses company resources to process an object (physical or mental) to achieve
a product or result that has been measured and determined for internal and external
customers (Singh et al., 2019).
Many companies face challenges in developing these business processes, including
in managing inventory, such as high inventory costs, the risk of inventory shortages, and
operational complexity. Therefore, innovative solutions are needed to overcome these
problems. According to (Rushton et al., 2022), warehouses require expensive costs by
making 2-5 percent of a company's sales costs. Ideal warehouse management will improve
the company's logistics in times of crisis. Warehouse monitoring is such as sufficient
inventory of goods with the right arrival time and minimal inventory costs, how effective
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4052
and efficient employees are in working, the use of technology in the warehousing system,
the use of warehouse area, and the quality of goods in the warehouse (Jae, 2024).
In manufacturing companies, the problem that often occurs is the occurrence of poor
control of the stock in the warehouse, both raw material warehouses and finished
warehouses. Organizations or manufacturing companies store inventory including raw
materials, supplies used for production, and finished goods (Samsudin et al., 2023).
PT. XYZ is a manufacturing company where manufacturing is a company that
processes raw materials into a finished product (finished goods) with process stages
including, product design, material selection, and process stages (Amri et al., 2023). PT.
XYZ is a company engaged in textiles that are integrated from cotton to yarn, yarn to raw
fabric, raw fabric to color fabric, and color fabric into apparel. At all times, production and
delivery activities are carried out to increase customer satisfaction and commitment. At PT.
XYZ inventory control process at PT. XYZ such as recording production realization, stock
inventory management, recording of outgoing goods, and recording of incoming and
outgoing goods are still done manually by recording in books (Fujiati, 2023). This takes a
long time because the data that is worked on often experiences human error due to manual
processing. This system also has other problems, namely it takes a long time to work, less
effective and efficient (Dewi et al., 2022).
This study aims to discuss the improvement of warehousing administration through
the design of an Excel-based stock system with the Design Thinking, Economic Order
Quantity (EOQ), and Periodic Order Quantity (POQ) approaches. Where this system is
expected to be a strategy that aims to increase efficiency and effectiveness in managing
inventory in the warehouse and is included in the product development business process.
Method
In identifying problems, field studies and literature studies are carried out. The field
study was carried out by taking and seeing the real situation of the problem in the
warehouse of PT. XYZ. In the initial stage, interviews will be conducted with the
warehouse admin and warehouse staff related to the inventory system in the warehouse.
Through the interview, problems in the warehouse will be found regarding the inventory
system that is still not optimal (Kuantitatif, 2016).
In the literature study conducted in this study by studying theories related to problems
in the field that will be solved in this study, including theories about production, planning,
and control supply chain management. The theory will later be used as a guideline in
solving existing problems.
Data Collection Techniques
At this stage, several things must be done, including the following:
a. Observation
Observation is carried out by observing ongoing activities and data that is treated to
meet the needs of the system to be created. Then observations were made by finding several
problems, including the processing of the inventory system. The observation carried out is
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4053
the administrative process that has been happening in the PT.Xyz warehouse starts from
the process of receiving, controlling stock, and issuing finished goods.
b. Interview
Interviews are activities that are carried out by gathering the information and data
needed to build a system. This interview is conducted by conducting face-to-face
interviews with people who are experts in the field to be researched. Then the results of the
interviews that have been conducted can be used as a reference to develop a practical,
efficient, and good system. The interviews conducted were qualitative and quantitative.
Interviews will be conducted with related parties, namely the admin and warehouse
supervisor.
c. Questionnaire
According to (Sugiyono, 2017), a questionnaire is a data collection activity or
technique carried out by giving a set of questions or written statements to respondents to
answer. This questionnaire contains satisfaction regarding the administration of the
warehouse that has been running in the warehouse and will be given to the admin as a
person who is responsible for directly carrying out administration in the warehouse.
Warehousing Administration System Architectural Design
A good warehousing administration system architectural design must consider
several important aspects to ensure operational efficiency and effectiveness. Here are some
of the main elements as follows:
1. Organizational Structure
In the organizational structure, it is necessary to determine the hierarchy and
responsibilities of the warehousing team such as warehouse staff, administrative teams,
and operators in the field.
2. Inventory Management
Inventory Management here is used to search for stock, stock status, condition of
goods, and other related information.
3. Operational Process
Develop procedures or SOPs for the receipt, storage, retrieval, and receipt of goods.
4. Information Technology
The use of software to support warehousing management in automating and updating
data in real-time.
5. Warehouse Layout
Design a warehouse layout that can maximize the use of space and facilitate access
to goods.
6. Equipment
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4054
Selection of the right equipment such as forklifts, pallets, and other auxiliary tools to
increase productivity.
Results and Discussion
PT. XYZ is a company engaged in the textile and garment sector that was established
in 1974. Currently, PT. XYZ has 3000 employees and has a wide range of units. However,
this study will focus on one of the warehouses in the PT. XYZ. The finished warehouse at
PT. XYZ is a warehouse that can accommodate goods with a maximum capacity of up to
10 million meters. This warehouse serves sales or marketing requests to unload goods and
PPIC parts to inform the number of available stock (Apriyani, 2020). In the organizational
structure, the warehouse greige has several staff and employees who are responsible for the
process of activities in the warehouse. The number of personnel in the finished warehouse
is 2 admins and 6 operators. The following is the organizational structure in the finished
warehouse of PT. XYZ:
Chart 1
Organizational Hierarchy in Warehouses
The picture above is the arrangement of the organizational structure in the warehouse
of PT. XYZ. The duties of each are as follows:
a. Manager, as the highest leader of one of the units in PT. XYZ has a strategic role in
determining the policy and direction of the unit by the goals of top management. In
addition, managers must ensure that production targets are by the goals that have been
set.
b. Warehouse supervisor, as the head of the warehouse in one of the units at PT. XYZ has
the responsibility of managing the processes that occur in the field and administration
in the warehouse. The warehouse also has the responsibility of serving marketing, PPIC,
and internal buyer requests.
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4055
c. Warehouse leaders, have a responsibility to ensure that processes in the warehouse,
especially processes in the field, run smoothly. The leader is also responsible for
managing operators and admins in the warehouse.
d. Warehouse admins, have the responsibility of taking care of administrative processes in
the warehouse. In addition, the admin is in charge of completing the mutation documents
that occur in the warehouse.
e. Warehouse operators, have responsibilities in field activities starting from product
preparation and product delivery to buyers.
Emphasize
The emphasize stage is carried out by the researcher to make observations to
understand the condition of the user. This observation was carried out to obtain data
according to the problems faced by the warehouse team of PT. XYZ. The research is carried
out as follows:
a. Survey the warehouse admin team involved in warehouse activities to gain an
understanding of the situation that occurs in the warehouse related to the administration
that has been carried out in the warehouse so far.
b. In-depth interviews with warehouse supervisors and PPIC related to stock supervision,
production planning, and delivery of goods.
c. Interview with sales regarding the method of selling finished goods and passive stock
goods.
Questionnaire Survey Results
At this stage, the author shared a questionnaire involving PPIC, admins, and
warehouse supervisors in giving their responses regarding the use of the administration
system application that is currently running (Fujiati, 2023). The purpose of this
questionnaire is to give the author an overview of the elements that are prioritized for the
development of the administrative system in the warehouse.
Figure 2
Results of the Foxpro Usage Duration Questionnaire
In the results of the questionnaire above, it can be seen that 50% of respondents have
used the current warehouse administration system for more than 24 months or 2 years, then
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4056
as many as 25% have used this system within 6-12 months and another 25% have used this
system in the range of 12-24 months. Then the author distributed a questionnaire related to
user satisfaction with using the Foxpro visual administration system and the results
obtained were as follows:
Figure 3 Satisfaction Level with the Old Stock System
Satisfied with the administrative system used now. Furthermore, the researcher
shared a questionnaire related to the difficulties faced by users when using the application.
This is proven by the lead time required for the application to present data is 20 minutes.
The answer obtained is as follows:
Figure 5
Respondents' Results Regarding the Difficulties Faced Using Foxpro
From the results above, it can be seen that the majority of users complain about the
application of the administration system used still often experiencing problems and
inconsistencies in the data input and displayed.
Testing Stages
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4057
At this stage, the researcher will carry out the testing stage using UAT (User
Acceptance Test) so that the offered solution runs effectively and efficiently. The test
results of each feature can be seen in the table below:
Table 1
Testing on Sheet in Item
No
Functionality
Comment
Status
1
Construction columns
can be
automated
appears after
Master code input
Column size to be
enlarged so that
data is easily input
Done
2
Filter button
Data search may
appear
Done
3
Stock automatically
increases when
entering
data
Done
In the table above, the test related to the sheet in item on the dashboard stock balance
in the warehouse went smoothly. This sheet function can run well and the results are
satisfactory. The input from the user is to make the column larger so that it is easier for
users to see.
Table 2
Pengujian pada Sheet Out Item
No
Comment
Status
1
Done
2
Done
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4058
3
Done
In the test table for sheet items 3 tests are mandatory and important features in this
sheet, the feature is expected to run normally and users can have no difficulty using it.
Use of EOQ
EOQ (Economic Order Quantity) has never been applied in PT. XYZ in controlling
inventory. So sometimes there is often a shortage and excess stock of goods. So this EOQ
method is needed to determine accurate calculations. The implementation of the use of the
EOQ method can be obtained at the appropriate time and synchronously without making
inventory that exceeds the limit, which means that it can reduce the expenditure that
appears in the inventory (Andira, 2016).
The EOQ calculation in this study will use data from the last 1 year, namely 2023.
Where out of 350 types of goods listed, the author takes 3 types of goods with high receipts
and expenditures in 2023. These three types of goods are raw materials that are often
ordered for the fulfillment of the next process, namely the fabric dyeing process. In short,
the process that occurs is when the process of sending raw fabrics to the warehouse of PT.
XYZ is completed, later this fabric will be sent to the next unit from PT. XYZ is the dyeing
process. Where the fabric dyeing unit will dye the fabric before it will later be processed
in the convection unit or garment into a garment. This study will calculate data in the last
year, namely 2023, which is shown in the table below. PPIC in the fabric dyeing process
unit will coordinate with sales who already have a sales contract then PPIC will make a
schedule for the fabric dyeing process and take the fabric from the warehouse through the
fabric booking code and each process will be given an SP code (production letter).
The following is a table of raw material fabric requirements from dyeing PPIC
needed in 2023.
Table 2
CD Material Requirements 1206063/ Master Code 82
Moon
Inventory (meters)
Requirement (meters)
January
542.486,00
43.000,00
February
511.242,00
195.281,00
March
449.036,50
111.341,00
April
380.901,00
141.499,00
May
344.014,50
3.850,00
June
10.897,00
632.090,00
July
322.125,50
456.874,00
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4059
August
177.505,50
41.134,50
September
461.457,50
45.762,00
October
365.619,50
63.769,00
November
275.739,50
43.244,00
December
7.482,50
29.309,00
Total
1.807.153,50
Table 3
PC Material Requirements 1337263 / Master Code 101
Moon
Inventory (meters)
Requirement (meters)
January
332.211,00
118.310,00
Februar
y
133.670,00
250.183,50
March
22.185,50
327.518,50
April
10.239,50
15.000,00
May
252.602,50
19.500,00
June
291.512,00
57.000,00
July
199.040,00
88.057,00
August
28.541,00
303.260,00
Septem
ber
79.566,50
54.504,00
October
31.109,00
341.986,00
Novem
ber
89.555,50
39.002,00
Decem
ber
125.540,00
25.000,00
Total
1.639.321,00
Table 4
CD 887047 Material Requirements / Master Code 134
Moon
Inventory (meters)
Requirement (meters)
January
48.235,50
545.103,00
Februar
y
274.159,00
31.460,00
March
322.335,00
5.000,00
April
378.424,50
129.630,00
May
443.434,50
24.000,00
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4060
June
556.123,00
135.000,00
July
391.430,00
25.000,00
August
118.409,00
1.509,00
Septem
ber
79.621,50
21.058,00
October
64.484,00
15.088,00
Novem
ber
64.325,00
-
Decem
ber
25.669,50
202.668,00
Total
1.135.516,00
Based on the data above, the average cost of using these raw materials can be
calculated. The formula used is as follows:
The frequency of ordering raw materials is carried out 150 times a year, meaning that
orders are made 3 times a week. So the calculation is as follows:
Safety Stock
For the use of raw materials and on average, the Safety Stock model will be used.
The formula used is as follows:
𝑆𝑆 = 𝑆𝑎𝑓𝑒𝑡𝑦 𝑆𝑡𝑜𝑐𝑘 = 𝜎 𝑋 𝑍
Where the author will use a service level of 95% so that with the distribution table
the Z value is obtained below the normal curve of 95%, which is 1.65.
a. Safety Stock for the use of raw materials master code 82.
SS = (195.196,95 *1,65) = 322.075 metre
b. Safety Stock for the use of raw materials master code 101.
SS = (129.879,6 * 1,65) = 214.301 metre
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4061
c. Safety Stock for the use of raw materials master code 134. SS = (156.286.43 * 1.65)
= 257.873 meters
Comparative Analysis of Inventory Control
From the results of the calculations and analysis that have been carried out, the
company PT. XYZ will be able to find out the right and most optimal method to be applied.
The table below is the company's policy in ordering raw materials in 2023.
Table 5
Data Kebijakan Perusahaan Tahun 2023
No.
Policy
Company
Year Request
2023
1
Stock Request
Thing
12,048 meters
2
Booking Fee
IDR 300,000
3
Inventory Costs
IDR 26,278,500
Raw Material Inventory Analysis Master Code 82
Using the EOQ and POQ methods, we can compare the data used by the company to
calculate the raw material requirements of master code 82 by taking into account EOQ, and
the POQ calculation in the data below:
Table 6
Comparison of Corporate Policy Methods with EOQ and POQ in Ordering Raw Materials
Master Code 82
No.
Information
Policy
Company
EOQ
POQ
1
Stock Request
Average Goods
12,048 meters
273,068 meters
75,298
meters
2
Total Cost
IDR
41,276,011.00
IDR 3,970,781
IDR
10,162,832
3
Frequency
Most recent
booking
150
7
24
In the data above, we can see before using the EOQ and POQ methods, PT. XYZ
carries out its policy by requesting stock of master code 82 goods with an average of 12,048
meters per order for 150 orders in a year with an accumulated cost of Rp 41,276,011.00.
When compared to using the EOQ (Economic Order Quantity) method, PT. XYZ has made
a saving of Rp 37,305,230.00 by minimizing the frequency of retrieval to 7 times.
Meanwhile, by using the POQ (Periodical Order Quantity) method, it can save an inventory
of Rp 22,113,179.00.
Inventory Analysis of Master Code 101 Materials
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4062
Table 7
Comparison of Corporate Policy Methods with EOQ and POQ on Master Code 101 Raw
Material Orders
No.
Information
Policy
Company
EOQ
POQ
1
Average
Demand-
Average
Stock of
Goods
10,929 meters
247.708 meters
68.305 meters
2
Total Cost
IDR 41,278,316
IDR 3,970,781
IDR 6,454,312
3
Frequency
Most recent
booking
150
7
24
In the table above, you can see the data and calculations to calculate the raw materials
for master code 101, the company PT. XYZ requests an average of 10,929 meters per order
with an order frequency of 150 times and a total cost of IDR 41,278,316. Meanwhile, by
using the EOQ (Economic Order Quantity) method, there is a demand for a stock of goods
with an average picking of 247,708 meters and a picking frequency of 7 times so if using
EOQ, the company will save costs of IDR 37,305,230.00. Meanwhile, for calculations
using the POQ method, it can save as much as IDR 31,113,179.00 by minimizing 24 takes.
Inventory Analysis of Master Code 134 Materials
Table 8
Comparison of Corporate Policy Methods with EOQ and POQ on Master Code 134 Raw
Material Orders
No.
Information
Policy
Company
EOQ
POQ
1
Average Demand-
Average Stock of
Goods
7,570 meters
171.581
meters
31,542 meters
2
Total Cost
IDR 41,278,500
IDR
1,998,626
IDR
13,703,453
3
Frequency
Most recent
booking
150
7
36
In the table above, you can see the data and calculations to calculate the raw materials
of the master code 134, the company PT. XYZ requests an average of 7570 meters per
order with an order frequency of 150 times and a total cost of IDR 41,278,500. Meanwhile,
by using the EOQ (Economic Order Quantity) method, it is obtained that the demand for
the stock of goods with an average of 171.58 meters and a frequency of picking is 7 times
Development of an Excel Dashboard-Based Warehousing Administration System Using the
Design Thinking, EOQ, and POQ Approaches
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4063
so if using EOQ, the company will save costs of IDR 39,279,973.00. Meanwhile, for
calculations using the POQ method, it can save as much as IDR 27,575,146.00 by
minimizing 36 takes.
Excel Dashboard Stock Integration with EOQ
After looking at the comparison between the use of the company's system that has
been running, the EOQ system, and the POQ, the author will integrate with the safety stock
and ROP systems that have been sought before. So the results can be seen as shown in the
picture below:
Figure 6
Stock System Integrated with EOQ and POQ Methods
In the image above, it can be seen that the stock system will read the needs of
production. If the stock data has a value smaller than ROP (Reorder Point), then the status
column will turn red and show an "Order" status which means the warehouse must restock
at least according to the EOQ amount and if the stock is larger than ROP it will appear a
"Safe" status.
Conclusion
The conclusion of this study shows that by using the principles of Design Thinking,
companies can design a more efficient and accessible system for inventory management.
Some of the main needs identified by users are the creation of Key Products to facilitate
the identification of goods, a warehouse administration system that can be accessed from
anywhere and is easy to customize, real-time data displays, user-friendly input/output
forms, and more detailed stock displays. In its implementation, the EOQ and POQ methods
are compared to the methods used by the previous company, which placed orders 150 times
a year. After the implementation of EOQ, the frequency of orders was drastically reduced
to only 7 times a year for certain raw materials. Significant savings occurred, especially for
items with master codes 82, 101, and 134, with the EOQ method resulting in greater savings
than POQ. In conclusion, the EOQ and POQ methods are effective in avoiding understock
and overstock, but EOQ is considered more efficient and has less risk. This data is then
integrated into a new stock system compiled with Design Thinking principles, including
ROP and Safety Stock, EOQ, and POQ to ensure optimal inventory management.
Samuel Christian Hamonangan Silitonga Nim, Firman Hawari
Indonesian Journal of Social Technology, Vol. 5, No. 10, October 2024 4064
Bibliography
Amri, K., Latuconsina, H., Triyanti, R., Setyanto, A., Prayogo, C., Wiadnya, D. G. R.,
Isdianto, A., Panggabean, D., Noviyanti, R., & Nazzla, R. (2023). Pengelolaan
Sumber Daya Perikanan Laut Berkelanjutan. Penerbit BRIN.
Apriyani, L. (2020). Implementasi Finger Print dalam Meningkatkan Kedisiplinan Kerja
Karyawan Perspektif Etos Kerja Muslim (Studi Kasus PT Berjaya Tapioka di Desa
Kedaton Induk Kecamatan Batanghari Nuban Lampung Timur). IAIN Metro.
Dewi, P. P. A., Yudistira, C. G. P., & Widiantara, I. (2022). Evaluasi Kualitas Pelayanan
SWRO PT Pelabuhan dengan Metode Importance Performance Analysis dan TRIZ
(Studi Kasus: Pelabuhan Benoa). Politeknik Negeri Bali.
Fujiati, S. (2023). Sistem Informasi Geografis Pemetaan Wilayah Kelayakan Tanam
Tanaman Jagung Dan Singkong Pada Kabupaten Lampung Selatan. Jurnal Teknologi
Terkini, 3(3).
Jae, K. M. (2024). Exploring Role Of Perceived Benefit And Brand Awareness On
Purchase Intention Of Korean Electronic Products. Journal of Current Research in
Business and Economics, 3(1), 195–245.
Kuantitatif, P. P. (2016). Metode Penelitian Kunatitatif Kualitatif dan R&D. Alfabeta,
Bandung.
Rushton, A., Croucher, P., & Baker, P. (2022). The handbook of logistics and distribution
management: Understanding the supply chain. Kogan Page Publishers.
Samsudin, A. F., Darmawan, B., Dwiyanti, V., & Mupita, J. (2023). Attributes and Effect
of Implementation of Warehouse Management System (WMS) for Company
Sustainability. Jurnal Teknologi Transportasi Dan Logistik, 4(2), 199–212.
Singh, J., Sarupria, A., Kushwaha, G. S., & Kumari, M. (2019). Supply chain management
practices in automobile industry in India: ICT perspective. International Journal of
Management, Technology and Engineering, 9(6), 4303–4314.
Sugiyono, F. X. (2017). Neraca Pembayaran: Konsep, Metodologi dan Penerapan (Vol.
4). Pusat Pendidikan Dan Studi Kebanksentralan (PPSK) Bank Indonesia.