DLSU Engineering e-Journal Vol. 1 No. 1, March 2007, pp. 138-150

Automated Inventory System Using Portable Data Acquisition Module for Bell Electronics Corporation
Zarina Ira V. Bernabe Monchie C. Dela Cruz Karen K. Jao Department of Electronics and Communications Engineering De La Salle University, Manila email: ira.bernabe@yahoo.com, chie08_elx@yahoo.com, karenjao@yahoo.com, Jingel A. Tio, Antonio S. Gonzales Jr. Department of Electronics and Communications Engineering De La Salle University, Manila Email: tioj@dlsu.edu.ph, gonzalesa@dlsu.edu.ph

Bell Electronics Lab Corporation situated in Calamba, Laguna is engaged in test and assembly of optoelectronics and sensor devices. The company is concentrating on its resources in metal can packaging. The second phase of the company's program is towards the development of other types of optoelectronics and sensors device packaging to support their customer requirements. In this paper, the proponents will present an Automated Inventory System using Portable Data Acquisition (PDA) module to help the company’s growing business. The PDA module is composed of barcode scanner to scan issued and received items, and a Z8F6423 Zilog microcontroller to store the information. A Product Inventory System (PIS) application program is also discussed to show how the PDA module can be interfaced with the PIS. Test results presented in this paper will show apparent improvement of company’s inventory system.

1.0 INTRODUCTION Inventory system is an effective way for monitoring and tracking different materials that are transferred in and out of a company’s warehouse or establishment usually for accounting purposes. It is also important for a company to monitor all the transactions and

140

Bernabe, Dela Cruz, Jao, Tio and Gonzales

movement of goods in order to keep an account of all their stocks. However in some establishments, the inventory of materials is done manually in such a way that an employee writes down the information of different materials every time it is transferred in and out of the room. The same case applies in Bell Electronics Corporation; and as a result, the inventory system becomes prone to human error and would be more time consuming which will most likely be a loss for the company. In Bell Electronics Corporation, there is a corresponding form for every incoming or outgoing item (from the stock room). These forms serve as the basis of comparison when inspecting; that is, the amount of item that goes in or out must tally with those written in the forms. Afterwards, all the transactions will then be encoded manually in Microsoft Excel for future reference. In addition to the complexity of the process, the employee responsible on this task also needs to check the stocks once in a while so as to monitor which items are already getting below the allowable minimum quantity. Because of the draining work, there were circumstances when the inventory record in the computer does not tally with the actual amount of the available items. Another thing to consider in manual inventory is prone to errors since it is inevitable that the employee assigned to do the inventory would commit mistakes. Due to the inefficiency of manual inventory, security is also being risked. If not properly monitored, theft and loss of items can actually take place. It is also time consuming to take note of several details each time an item is brought in and out of the stock room. Since manual inventory consumes a lot of time and is susceptible to errors, it would be helpful to build a system that will automate the transactions from acquisition of the product description to updating the inventory database. Work efficiency of employees is defined as how productive they are in their work. The automated inventory system will improve employees’ work efficiency by automating routinely and time consuming tasks such as the time spent in taking down product information. As a result, the time can be used in more productive activities. Accuracy is defined as the correctness of accessing information. The accuracy of the current system will also be improved, since the automated inventory system would eliminate the need of constantly encoding lengthy details of products by having an inventory database. 2.0 SYSTEM BLOCK DIAGRAM

Figure 1: System Block Diagram

Automated Inventory using Portable Data Acquisition Module for Bell Corp.

141
The project is divided into two sub-systems: hardware and software components. The hardware part is made up of the barcode scanner which is responsible for decoding the equivalent binary information in the barcodes of the material to be recorded; the Control Panel or the Status Module, which comprises of the battery level indicator, material status buttons, and alert system; and finally, the Z8F6423 microcontroller. On the other hand, the software portion of the system is composed of the PC to module and module to PC serial communication algorithm assembled in Visual Basic software; Data transfer operations utilizing UART (Universal Asynchronous Receiver / Transmitter); a SQL-based database system; and a graphical user interface also in Visual Basic. The block diagram of the over all system of the project is shown in figure 1.

3.0 PORTABLE DATA ACQUISITION MODULE

Figure 2: Block Diagram of Portable Data Acquisition Module
The portable data acquisition module which is mainly considered as the hardware component of this project is responsible for the data acquisition of the barcode information as well as the storage and manipulation of these data through Zilog microcontroller. Shown in figure 2 is the block diagram of the portable data acquisition module which is basically comprised of the barcode scanner, Zilog microcontroller and the power supply.

3.1 Barcode Scanner
The MS9540 Voyager barcode scanner converts the barcodes into text messages and delivers them into its output serial port. These string output of the barcode scanner are then fed into the Z8F6423 microcontroller which are then processed whether for quantity update, input verification or just simply memory storage. This scanner was set to operate at a port setting of a data rate of 9600 bits per second, 8 data bits, no parity, 1 stop bit and no flow control. The scanner has an operating voltage of 5V and an

142

Bernabe, Dela Cruz, Jao, Tio and Gonzales

operating current of about 200mA. One of the features of this barcode scanner is its Codegate technology, which automatically activates the laser from the scanner in order to scan the barcode when it senses a barcode and automatically deactivates when the user is not scanning any barcodes. This feature, can be considered as a power saver, since the scanner only consumes energy during the scanning process and idles itself during no operation.

3.2 Zilog Z8F6423 Microcontroller
The Zilog Z8F6423 microcontroller is the heart and soul of the data acquisition module since it performs almost all of the functions of the module. One of the main features of this microcontroller was the DB9 female serial port connector that was already in the kit which allows the proponent to easily access the UART and SPI (Serial Peripheral Interface) of the microcontroller. These features effectively communicate with the barcode scanner and relay the scanned information and store it in a temporary memory of the microcontroller. Another feature that was greatly utilized was the GPIO or General Purpose Input/Output pins. These pins enabled to interface the necessary switches and LED indicators to the user in order for him/her to control the operation of the microcontroller. The pins are interfaced with the toggle switches, push-buttons and LEDs with the use of 2 pin connectors.

3.3 Power Supply
The portable module utilized two power supplies in order for it to operate effectively. One would be responsible to power up the barcode scanner and the other for the Zilog microcontroller. Both power supplies are similar in design since similar output parameters are required for them. A 7805 voltage regulator, 741 op-amp and 5V relay where utilized in the design. In addition to this, a Nokia BLK-4S Lithium Ion rechargeable Battery was used in the design which has an ampere hour rating of 800mAh.

4.0 SOFTWARE COMPONENT

4.1 Structured Query Language
There are three basic categories of SQL Statements. These include SQL-Data Statements, which are used to query and modify tables and columns, SQL-Transaction Statements which handles control of transactions and SQL-Schema Statements which are used to maintain schema. In this thesis project, SQL-Data Statements played a big role in the software part. They allowed access and modifications to the database once certain events happened while running the program. Three modification statements under this category are the action queries – INSERT statement, UPDATE statements, and DELETE statements. As the terminologies imply, INSERT Statement adds row/s to tables, UPDATE Statement modifies columns in table rows while the DELETE Statement removes row/s from tables. Another SQL Data Statement is the SELECT statement, which is used to retrieve data from the database. With these commands as

Automated Inventory using Portable Data Acquisition Module for Bell Corp.

143 the project’s basic tools, features related to editing of database records are made possible.

4.2 Updating the Database

Figure 3: Summarized Program Sequence
Figure 3 summarizes the program sequence of updating the database. After a serial connection between the PC and the module was established, the application reads the data sent by the module. The said data is comprised of the product’s barcode, quantity and transaction status. The first step is to determine the status of the product, whether it is an incoming or outgoing stock. This will then be followed by the updating of the database. If the product is an incoming stock, the application will simply add the quantity sent by the module to the original quantity in the database, otherwise a subtraction process occurs. If the product is incoming, the next step is to check if that same product also has outgoing transaction. If in case the product was not issued during that day, the monitoring of the critical inventory level follows. The critical inventory level consist the minimum and maximum limits for the quantity of each inventory item. If after the addition process, the program detected that the critical inventory level is reached or exceeded, the program will alert the user. On the other hand, if the same product was issued during that day, which is indicated by having an outgoing status transaction for the same barcode, the program will skip first the monitoring of the critical inventory level since the sum is still not yet the final quantity of the product because of the subtraction process that will still happen afterwards. The next step is to check if the item is one of the expected products uploaded in the module. If it is, the program then proceeds to the verification of the expected quantity. If the transaction status of the barcode being evaluated is outgoing and the subtraction process is already done, the program immediately proceeds to check if critical inventory level was reached or exceeded by the new product quantity. This is because the difference after the subtraction process is considered as the final quantity of the product for that day. The same procedure is repeated for every barcode detected from the data sent by the module. Note that a barcode “FINISH” is to be scanned by the user to signify end of transactions for the day; thus in the program, the data downloading code will be terminated once the barcode “FINISH” is reached.

4.1 Software Features
The system provides a graphical user interface which gives the user access to some features. The application program is created in Visual Basic .NET and uses a Structured

144

Bernabe, Dela Cruz, Jao, Tio and Gonzales

Query Language (SQL) for database maintenance. Table 1 shows the summary of GUI windows and its corresponding functions.

Table 1: GUI windows

Figures 4 to 6 present screenshots of GUI windows. The Log-in window will ask the user to input the username and password. If the log-in is successful the menu window will be shown on screen. The user can upload the information from the Data Acquisition module when Upload option is invoke. The Upload menu is shown in Figure 6.

Automated Inventory using Portable Data Acquisition Module for Bell Corp.

145

Figure 4: Log-in Window

Figure 5: Menu Window

Figure 6: Upload Window

5.0 ANALYSIS OF RESULTS The proponents conducted series of tests assessing the overall system performance to have a thorough analysis and evaluation of the system, To check and evaluate the efficiency of the new system, the group compared the quantity in the inventory generated by the program and the data written in the company’s MRIV forms (Materials Request Issuance Voucher) on that day (testing day). The result showed that the quantity of the products matched perfectly and thus proved that the new system is in fact 100% accurate.

146

Bernabe, Dela Cruz, Jao, Tio and Gonzales

Figure 7: Process Flow of Bell Electronics Corp. Automated Inventory
Figure 7 illustrates the checking of quantity and authentication step of the manual inventory process has been replaced by a simple step of barcode scanning and thus eliminates the risk of human error occurring. In the first procedure alone, the new system already has an advantage of speed over the manual process. On the other hand, manual checking of the availability of the material has also been eliminated since the records in the database system are already accurate and updated. This way, human manual encoding errors are reduced. Furthermore, a lot of time consuming manual work has been eliminated. Records are automatically updated once the information from the module is uploaded to the database. In addition, monitoring of the quantity level of each material is already being done by the software application program, so manual verification is also unnecessary anymore.

5.1 Simulations
Aside from the comparison and analysis of the steps and procedures of automated and manual inventory processes, simulations of two inventory processes were tested while taking note of the time consumed for each process. To compute for the total Standard Time (ST) per task, total Observed Time (OT) per cycle is first obtained. Note that one cycle is equivalent to one transaction / routine. Total Observed Time is the summations of all observe times divided by the number of observations.

OT=Xi/n

Equation 1

Then, the Total Observed Time was multiplied by a certain performance rating of the operator / user to obtain the Normal Time (NT). Calculation of Performance Rating (PR) was based on the Westinghouse System. [4]

NT = OT * PR

Equation. 2

Finally, to obtain the Standard Time, Normal Time is multiplied by an allowance factor. Allowance factor is the interruptions to process such as personal needs for rest and for

Automated Inventory using Portable Data Acquisition Module for Bell Corp.

147 reasons beyond his or her control. The Allowance Factor used is based on a Typical Allowance Percentages for Working Conditions Standards.[5]

ST = NT * AF

Equation 3

Tables 2 to 5 present the sample computations of outgoing and incoming product simulations for both the manual and automated systems.

Table 2: Sample Computation of Outgoing Product Simulation (Manual Process)

Table 3: Sample Computation of Outgoing Product Simulation (Automated Process)

148

Bernabe, Dela Cruz, Jao, Tio and Gonzales

Table 4: Sample Computation of Incoming Product Simulation (Manual Process)

Table 5: Sample Computation of Incoming Product Simulation (Automated Process)

From those 30 samples, the average standard time for a transaction with an outgoing product in the manual inventory process amounts to 41.55 seconds while 30.52 seconds for the automated inventory system. This results to a percentage difference of 30.61% and the automated process was able to save 26.55% of the manual inventory time. On the other hand, the average standard time for a transaction with an incoming product in the manual inventory process amounts to 44.19 seconds while 29.36 seconds for the automated inventory system. A percentage difference of 40.33% resulted and using this data, the automated process saved 33.56% of the manual inventory time as shown in Table 6. With regard to the generation of inventory reports, the automated inventory system has significantly reduced the time from 72.04 minutes to 1.55 minute thus saving 97.86% of the time. Table 2 shows the average standard time for transactions with outgoing and incoming materials while the average standard time in creating summary of inventory report is illustrated in Table 7.

Automated Inventory using Portable Data Acquisition Module for Bell Corp.

149
Table 6: Average Standard Time of Manual and Automated Processes

Table 7: Average Standard Time for the Generation of Inventory Report

The group also measured the amount of time consumed in downloading the data from the module as well as the time it took the application to display the summary of inventory from the moment the user clicked the appropriate button in the graphical user interface. The standard times for these tests are summarized in Table 8.

Table 8: Standard Time of other operations

6.0 CONCLUSION In this paper, the design of Automated Inventory System using Portable Data Acquisition Module for Bell Electronics Corporation is presented. The tests that have been conducted in Bell Electronics Corporation lessen a significant amount of time dedicated in creating summary reports of inventory along with other related records. In addition, the company finds the proposed system more effective in their inventory management not only because of the improved speed, but also due to the increased of efficiency by reducing manual interventions. However, it is recommended to automate other manual transactions in order to improve the productivity of the company. Furthermore, to increase the storage capability of the portable data acquisition module the researchers recommend incorporating EEPROMs to the microcontroller. Finally, it is recommended to make the inventory system web-enable for easy access. REFERENCES [1] Tersine, R. Principles of inventory and material management. New York: North Holland Inc. [2] Young, J.B. (1991). Modern Inventory Operations. New York: Van Nostrand Reinhold [3] Mansfield R. (1993).The Visual Guide to Visual Basic for Windows, 2ed. Philippines: Global Publishing [4] S.M. Lowry et. al. (1940). Time and Motion Study and Formulas for Wage Incentives, 3rd ed. New York : Mc Graw Hill

150

Bernabe, Dela Cruz, Jao, Tio and Gonzales

[5] Stevenson, W. J. (1982) Production/Operations Management. Illinois: Irwin Professional Publishing [6] Bar Code Technology. http://www.aimglobal.org/technologies/barcode [7] Visual Basic Tutorial1-The Basic IDE. http://www.theopensourcery.com/vb01tut.htm

About the Authors

Zarina Ira V. Bernabe, Monchie C. Dela Cruz and Karen K. Jao obtained their B.S. Electronics and Communications Engineering degree from De La Salle University-Manila last October 2006. Jingel A. Tio is an Assistant Professor of Electronics and Communications Engineering at De La Salle University-Manila. She obtained her Master of Computer Science from the same university. She specializes in microprocessor-based and microcontroller-based systems design and application. Antonio S. Gonzales, Jr. is an Assistant Professor of Electronics and Communications Engineering at De La Salle University-Manila. He obtained his Master of Engineering degree in from De La Salle UniversityManila, and his BS ECE degree from University of the East. He specializes in Industrial Electronics and Instrumentation and Computer programming.