Implementing an Automated System to Simplify the Data Management Process at Parts Storage Area of PT. Z

This research concerns about the design and implementation of a new information system which will simplify the processes of the current system and its impact on the warehouse staffs. In order to automate the processes, a programmed information system is designed using Microsoft excel VBA, a basic programming function inside Microsoft excel. First, the current system is analyzed. Next, the user requirements and proposed system requirements are analyzed to make the system design. Then, the program is developed and tested by using VBA (Visual Basic for Applications). Then, the new system is implemented and the implementation results are analyzed. The results found that the new system successfully fulfilled the objectives of this research and the user requirements, although there are still some improvements which need to be done. During two months' period of implementation, there are some problems encountered which required the programmer to improve the system. Like every system, there are some advantages and disadvantages found in this new system.


Introduction
PT. Z Indonesia is a toy company which adapts the order principal in daily basis operation. By maintaining order, everything can easily be managed and processed. One of the efforts of PT. Z to maintain order is by implementing FIFO (First In First Out) concept for managing the materials in the warehouse.
The warehouse of PT. Z consists of few storage areas; one of them is an area which contains the parts of the products. There are two warehouse staffs which are involved in the information system at parts storage area; staff-1 and staff-2. Staff-1 responsible with most of the tasks in the system, while Staff-2 only responsible for two tasks in the system, but he has other tasks on another system which is not related to the focus of this research. The details of both staffs' tasks will be explained in Result and Discussion part.
The information system in parts storage area consists of two main processes; the kitting process and -------------------------------------------------------------*) Penulis Korespondensi. email: 1 hery.azwir@president.ac.id; 2 yafimaulidin@yahoo.co.id auditing process. The kitting process is the process which consists of every activities starting from adding new part until sending the part to production area which require the use of kit issue (a list of parts required for production). The auditing process is the process for checking all the data of parts and their details to see which parts are FIFO and which parts are not. The auditing process is conducted at the end of the month. All of the activities from kitting and auditing process will be explained by using use case diagram in figure 3 in results and discussion.
There are some non-value-added activities in both processes, and the chances of human error are high. Furthermore, the criteria for the FIFO in auditing process still based on month, which is still not specific enough. Thus, the main focus of this research is regarding the design and implementation of a new information system which will simplify and improve the data management process by automating some of the activities in the current system, and to analyze its impact on the warehouse staffs.

Methode
The method used in this study is a qualitative approach with descriptive analysis method, which is a design that provides ease for researchers to record, monitor and follow the process of an event or activities of an organization as it exists in a time frame and then interpreted to answer the certain research problem.

Warehousing
As the storage facility, the type and condition of the warehouse must be put in consideration in the supply chain network. All activities regarding the distribution of goods are all revolving around the warehouse; starting from raw materials, to work in process, until finished goods. The overall performance of the supply chain network will be directly affected by the warehouse's performance. Unproper design and bad warehouse management will result in an unnecessary high cost (Mohan, 2010).
By implementing a proper warehouse management system which implemented along an automated data collection process; it will likely increase the accuracy, reduce the labor cost if the labor required to maintain the system is saved, and increase the ability on serving the customer due to reduced cycle time resulting from the automation in the process. Reducing inventory and increasing the capacity of the storage might be hard to be realized due to the increase in cost (Ramaa et al, 2012).

FIFO (First In First Out)
FIFO (First In First Out) is a concept in inventory management system usually involving money, materials, and finished goods (Horngren et al, 2012). In FIFO concept, the material that arrive first, should be the one that be used first. For example, material that arrived at January must be used first before the material that arrived at March. The time duration of FIFO may vary depend on each company policies. In terms of materials or any tangible object, FIFO is the right concept to be choosen. This is because each tangible object has their own expiration date, regardles of how long or short that is. For raw material, chemical and organic in particular, some of them may have a short expiration date which require them to be used first.

Value-added and non-value-added activity
The definition of value-added activity may vary depend on the point of view. In accounting, valueadded activity is any activity that may has an added value to the customer and fulfill three of the following criterias (Freivalds and Niebel, 2009): 1. The activity is not done for free, the customer willingly paid for it. 2. The activity must be done right the first time. 3. In some manner, the activity may somehow altered the product or service. In production, a value-added activity is any activity that may increase the value of the product in some way (Harikarthik et al, 2011). For example, by coating a can food with aluminium, it may prevent the can from rusting, thus it is resulting to a long term storage.
Non-value-added activity on the other hand, is any activity that does not result in anything except waste. The non-value-added activities relate closely to lean manufacturing. One type of non-value-added activity is ineffective motion that can be identified using motion studies (Freivalds and Niebel, 2009). The type of motions that are considered as waste based on the Gilbreth's 17 basic therbligs are search, plan, inspect, select, unavoidable delay, rest, position, avoidable delay, and hold.

System Development Life Cycle
System development life cycle contains the phases that must be done when developing a new system. The phases cannot be completed separately, some phases can be done simultaneously and some might be repeated if the target of the phase is not achieved or if there is a bug/error found in the system. As shown in figure 1, there are seven phases in it, and they usually involve three entity; system analyst, programmer and the potential user (Kendall , 2008) Phase 1: Identifying Problems, Opportunities, and Objectives.
In this first phase of the systems development life cycle, the analyst is concerned with correctly identifying problems, opportunities, and objectives. This stage is critical to the success of the rest of the project, because no one wants to waste subsequent time addressing the wrong problem. Phase 2: Determining Human Information Requirements. The next phase the analyst enters is that of determining the human needs of the users involved, using a variety of tools to understand how users interact in the work context with their current information systems. The analyst will use interactive methods such as interviewing, sampling and investigating hard data, and questionnaires, along with unobtrusive methods, such as observing decision makers' behavior and their office environments, and all-encompassing methods, such as prototyping. Phase 3: Analyzing System Needs. The next phase that the systems analyst undertakes involves analyzing system needs. Again, special tools and techniques help the analyst make requirement determinations. Tools such as data flow diagrams (DFD) to chart the input, processes, and output of the business' functions, or activity diagrams or sequence diagrams to show the sequence of events, illustrate systems in a structured, graphical form. From data flow, sequence, or other diagrams, a data dictionary is developed that lists all the data items used in the system, as well as their specifications.

Phase 4: Designing the Recommended System.
In the design phase of the SDLC, the systems analyst uses the information collected earlier to accomplish the logical design of the information system. The analyst designs procedures for users to help them accurately enter data so that data going into the information system are correct. In addition, the analyst provides for users to complete effective input to the information system by using techniques of good form and Web page or screen design. Phase 5: Developing and Documenting Software.
In the fifth phase of the SDLC, the analyst works with programmers to develop any original software that is needed. During this phase the analyst works with users to develop effective documentation for software, including procedure manuals, online help, and Web sites featuring Frequently Asked Questions (FAQs), on Read Me files shipped with new software. Because users are involved from the beginning, phase documentation should address the questions they have raised and solved jointly with the analyst. Documentation tells users how to use software and what to do if software problems occur.
Phase 6: Testing and Maintaining the System. Before the information system can be used, it must be tested. It is much less costly to catch problems before the system is signed over to users. Some of the testing is completed by programmers alone, some of it by systems analysts in conjunction with programmers. A series of tests to pinpoint problems is run first with sample data and eventually with actual data from the current system. Often test plans are created early in the SDLC and are refined as the project progresses. Phase 7: Implementing and Evaluating the System. In this last phase of systems development, the analyst helps implement the information system. This phase involves training users to handle the system. Vendors do some training, but oversight of training is the responsibility of the systems analyst. In addition, the analyst needs to plan for a smooth conversion from the old system to the new one. This process includes converting files from old formats to new ones, or building a database, installing equipment, and bringing the new system into production.

UML (Unified Modelling Language)
The UML is a general-purpose, developmental, modeling language in the field of software engineering that is intended to provide a standard way to visualize the design of a system (Walkenbach, 2013). UML can be used to analyze the use case model, and to derive system object behavior, attributes, and relationships (Kendall, 2008). Some examples of the UML diagrams are use case diagram, use case scenario and activity diagram.

Operation Process Chart
Operation process chart is a chart that defines the sequence of activities in a process with the details of each operation; the work flow, the system, the procedures, and time required for each operation which will help record the important features in a work situation. There are many variations of process charts where each of them is designed for a specific level or stage of analysis (Harikarthik et al, 2011).

Result and Discussion
The focus of the research is to simplify the process from one part of the information system (subsystem) from the entire information system at PT. Z. The information system at PT. Z revolves around all departments at PT. Z. The data that has been inputted from each department are combined into one big database using the application installed in AS400, a mid-range server. This application is functioning as the main server of PT. Z, where employees can input and download data from it.The sub-system that is going to be simplified is the kitting and auditing process in inventory management system, while using the time indicator as the base for measuring the warehouse performances. As can be seen from figure 2, kitting and auditing process are small parts of the system which are revolving around the warehouse, especially the parts storage area. A1 to A7 symbolize the flow process of the inventory management system, while B1 to B2 symbolize the flow process in other systems.
Before parts are sent to the warehouse after their arrival, they always go through an administration process where every part is given traceability number. Traceability number is the number that corresponds to each part's details given from Vendors Company which then recorded in the database and inputted in the AS400. For every batch of arriving part the traceability number will be different. Further explanation regarding activities done after the administration process; including the kitting and auditing process, will be explained in the next part.

Current System Analysis 1.Use Case Diagram
In figure 3, the kitting process is labeled with number 1 to 4, while auditing process is labeled with number 5 based on the order of the task performed. The label 1-1 in figure 3 means that it is the first step of task 1; 1-2 means that it is the second step of task 2, etc.
Kitting process: Parts that have went trough the administration process as explained in the previos part will be carried by staff-1 to the locator. Then staff-1 will scan the traceability number of the parts using the barcode scanner in which the results will shows up at an opened Microsoft excel, and take note of the locator number which will be inputted manually. Then, he download two databases from main server, the one which contains the information details of each traceability number, and the data which contains each parts location. Then, staff-1 will look for the matching numbers and parts using excel formula, and combine the data manually to create a new database in excel format.
To find the required parts which need to be sent to production area, staff-1 first needs to download the kit issue (list of parts needed for production) data from main server. Then, he look through the excel database to find the location of these parts, and select the oldest one for each type of part based on the incoming month. Then staff-1 gives the list to staff-2 which is responsible to take them from the listed locator to production floor. If a part changed location or no longer available, staff-2 will inform staff-1 and staff-1 need to type the changes in the excel database manually. Auditing process: For auditing process, the staffs have to compare the data from main server, the scan results in excel database, and the remaining parts in the inventory manually. Since the auditing process will take a lot of time when done manually, the staffs only do the auditing process at some type of parts that cannot last long in the warehouse. Because the FIFO is based on month, as long as the parts which came to the storage at the previous month were used first, then it is still considered FIFO. For example, parts which came at March will be used first before the parts which came at April. Figure 3 shows the use case diagram of current system.

2.Current System Advantages
Below are the advantages of the current system: 1. Keep on track: By processing the data manually, staff-1 was able to keep track every data he/she inputs; where the data are from, why were they merged, how to manage them, what to use them for, and where to keep them. 2. Specific auditing information: Because the auditing process is done manually, it allows the auditor to get the complete documented and physical information regarding each part during the auditing process.

3.Current System Disadvantages
But, there are some disadvantages of current system: 1. There are some non-value-added activities: Because some of the processes still done manually, there are some non-value-added activities that slow done the process. For example typing the data, especially in the auditing process where there are a lot of data. 2. The chances of human error are really high: When processing a lot of data, human tend to make mistakes due to the loss of focus. In this system there are a lot of processes which require the staffs to process the data, a simple misspell or mistype of the part name will cause a problem. 3. The auditing process not yet done in full capacity: Currently, the auditing process only covered some of the parts; parts that will not last long in the warehouse and the FIFO are still based on month. This is because the auditing process will take few hours to finish.

Proposed System Analysis 1.User Requirements
Based on the interview done on the warehouse staffs, the user requirements are as follows: 1. The basic functions are the same: The system does not change completely; the basic functions are still for parts' data management, looking for the required parts, and auditing process. The processes itself are not different in term of concepts, only simplify due to the processes done by the program automatically. 2. The processes of the program must be automatic: The processes do not require a lot of manual activities like typing, comparing, combining, and looking through the data because it will take a while and require a lot of focus, especially for the auditing process. The method for inputting new data only by scanning the traceability number. When looking for the required parts based on kit issue, the program should generate the list automatically based on the parts with the oldest date available. The auditing process is done automatically and the staffs only do the checking process on non-FIFO parts. 3. The program is easy to use: The program does not require much technicality that allows the program to be easily learned and used by the staffs. This way, the current staff can easily teach the next staff regarding on how to use the program if his/her duty is replaced by that other staff.

2.Use Case Diagrams
In figure 4, the kitting process is labeled with number 1 to 5, while auditing process is labeled with number 6 based on the order of the task performed.

Figure 3. Use Case Diagram of Current System
Kitting process: In the proposed system, the processes will be much simpler since the program will do most of the data processing automatically. The process started when new parts arrived at their designated locator, staff-1 first refresh the data from the main server to the excel database using one of the program's functions.
The data are the current inventory, the kit issue, and the traceability number details. Then staff-1 will scan the traceability number and the locator of the parts, while the program will combine the scan results with the traceability number's information details automatically to create the excel database. The parts data in the excel database will be sorted based on the oldest date automatically.
To find the required parts, staff-1 has only to execute one of the program's functions by pressing a button and it will automatically generate the list of parts needed and their locations based on the part with the oldest date available. Staff-1 then gives the list to staff-2 to take the parts from the listed locator and transport them to production floor. If a part changed location or no longer available, staff-2 will scan the traceability number and the locator to update the excel database. Auditing process: For auditing process, staff-1 also has only to execute of the program's functions by pressing a button and it will automatically combine and compare the data from the main server with the excel database to generate a FIFO report database. The database contains the auditing results of every part in current inventory and the description whether they are FIFO or not. Staff-1 then will take the list of non-FIFO parts to check them manually. After checking them, staff-1 will type the description of non-FIFO parts in the report database which explains why the parts are not FIFO. Figure 4 shows the use case diagram of proposed system.

Proposed System Design
This part consists of the design for the program interface. Figure 5 shows the first program interface. It is used to add new parts data by scanning the barcode of the locator and the barcode of the parts' traceability number. The scan result for locator will show up at range C2, and for parts traceability number will show up at range C6. The mode can be change from "Input" to "Delete" by pressing the scroll bar beside the number "1". The "Input" mode is used to input part data, while the "Delete"mode is used to erase it.
By clicking the "Refresh Data" button, the interface as shown in figure 6 will show up, showing the option for refreshing the data. Refreshing the data means the program will download the new data from the main server. If one of the options in figure 6 is selected, the database of that following option will be refreshed automatically. Selecting "Refresh All" will refresh all three databases, while selecting "Cancel" will close the options interface and execute nothing.
Clicking the "Stop Program" button will allow the user to stop the program from operating, and clicking the "Refresh Program" button will allow him to make the program to operate again, it can also be used if the program is stuck (give no response).
Clicking "Delete Duplicate" will allow the user to delete every data that have same traceability number and same locator and leaving only one of that data. Clicking "Convert" will allow the user to transfer the traceability number, date received and locator of the oldest parts to the Data_Kit sheet, based on the required parts on Kit Issue. The transferred data will become the list of required parts that will be given to staff-2. The list also showed the number of label needed to be used for the outgoing parts which is the roundup of the part's quantity divided by 5.  Figure 5 is located in an excel file named "FIFO.xlsm". This excel file is used to record the data of the parts, and creating the list of parts used for production during the kiting process. The database for traceability number details, kit issue, and current inventory are also included in this file. Figure 7 is located in an excel file named "AUDIT.xlsm". This file is used to create the FIFO report and cheking for non-FIFO parts during the auditing process.
By typing the toy part's name in the box shown in figure 7 then clicking "Check", the user will be able to find out whether or not that part is FIFO. The program can also be used by typing "all" in the box then click "Check" which will show the user all of the part he/she has scanned and shows whether or not each of them is FIFO. The file "FIFO.xlsm" must also opened for this process.
The "Refresh Program" button has the same function with the "Refresh Program" of the interface at file "FIFO.xlsm".   Selecting "Yes" will delete all the data of the auditing process results, while selecting "No" will close the interface and execute nothing.

Implementation Results
Before the new system was implemented, staff-1 was taught how to use the program. After that, staff-1 taught other staffs, so that other staffs also know how to operate the program. Thus, if staff-1's duty is replaced by other staff, heor she will understand how to operate the program. The new system was implemented for two months, with the period of June 2016 -July 2016. The observation results and analysis contain the encountered problems, improvements made, advantages, disadvantages and comparison of the previous system with the new system.

Problems Encountered
1. There are too many data in the traceability number database which slow down the process for adding new part, because one of the processes of the program is searching through the database for the matching traceability number. 2. The auditing process took longer than expected to finish. This was because the program searches through the data one by one.

2.Improvements made
1. To make the process for adding new part faster, the data in traceability number database is reduced by eliminating the data from finished goods commodity and the data from the year 2014 and below. 2. The process of looking for the matching traceability number in database was replaced. In the new process, the program only types the "vlookup" formula in one of the column in scan quantity and copies it to the rest of scan quantity columns. The results of the formula will fill the scan quantity column with the respective quantity according to the match found in traceability number. If not found, the column will be filled with error notice which will be replaced by blank column later in the checking process.

3.Advantages
1. The processes are faster due to simplification done by the program which eliminated the nonvalue-added activities like typing, searching, combining, etc.
2. The chances of human error are really slim, because most of the processes is done by the program, thus it does not require too much focus. 3. The program is easily learned and used, which allows other staffs beside staff-1 to use it when staff-1 is not present. 4. The program does not require the locator database, because the locator information already acquired from the scanning process. 5. By using the program, the auditing process can be done in full capacity; it will cover all the parts in parts storage area and the FIFO is based on date, not month.

4.Disadvantages
1. The program is not using actual quantity of the traceability number for the process of adding new part, but instead it is using the arrival quantity. Thus, the excel database only shows whether or not the part with the designated traceability number still exist. This is due to the barcode scanner which is still an old model with no quantity input function. 2. The excel file which is used for the adding new part process must remain displayed during the scanning process. Thus, the computer used to open that file cannot be used for other purposes during the scanning process. 3. All cell in the traceability number column of excel database from scanning process results must not be empty, otherwise the auditing process will be interrupted and stop running when encountering the empty cell. 4. If there is a flaw in the programming language, it cannot be detected by the staffs. It can only be detected by the programmer or other persons with the same programming skill.

5.Operation Process Chart
This part shows the difference of the activities performed between the old system and the new system which include the time differences of the activities. Figure 9 shows the operation process chart of old system (left) and new system (right). The time count on each activity is collected by performing the same procedures as the one performed during the testing scenario process, which is also done by using 10 different parts as example 6.Use Case Diagrams Figure 10 shows the use case diagram difference between the old system (up) and the new system (down). The three download activities for three different database in old system has been simplified by the program which cause the download activity only occur once in the new system.
The position of edit info details may look the same in both diagram, but it is actually different in process. In the old system, staff-2 has to inform staff-1 about the changes, and then staff-1 will edit info details. While in the new system, staff-2 can directly edit info details by giving the required input for the program.
In the new system, most of the processes are done by the program by giving certain input; therefore the processes are simpler as explained in the previous part. This allows him to add new part only by scanning the barcode which contain the traceability number and locator information.
During auditing process, staff-1 only need to check for non-FIFO parts, not parts that will not last long since the program will create the FIFO report of all scanned parts. Therefore, the auditing process can be done in full capacity. The auditing process which usually requires 2-3 hours to complete, now can be done within 1 hour or less. A-3 = Create requirement list O-6 = Take parts to production area A-4 = Take parts to production area O-7 = Edit info details A-5 = Edit info details O-8 = Download inventory data A-6 = Create FIFO report O-9 = Combine inventory data with excel database A-7 = Check for non-FIFO parts O-10= Create FIFO report A-8 = Check actual inventory

Conclusion
Based on the Results and Discussion above regarding the analysis, design, and implementation of the new automated system and its impact on the warehouse staffs, here are the conclusions that can be derived from it: 1. According to the interview done on the warehouse staffs, their requirement for the new system is a program that will simplify the information management process by automating activities like typing and combining data. The program itself must be easy to use with basic functions for parts data management, looking for the required parts, and auditing process.