The business is in a small location, hence, they want everything organized for easy access to records. Six months ago, the company decided to have an automated system to address the problem. They wanted that the records are systematically organized through a database. They have already bought a scanning device to be facilitating faster and easier recording of transactions. However, due to lack of applicable program, the device is not yet operational. 2 As IT students, one of the things that we have learned is to assess problems and create logical solutions.
We chose this area of study since it is important for every company to have an efficient method of keeping records. This study will give us an opportunity to apply and put into practice what we have learned. Our goal is to create system that will improve the performance and productivity of the company. 3 II. Objectives of the Study General Objective The main objective of the study is to design and develop sales and inventory system for Gilmore Wines and Spirits. This will cover the transaction in the cashier and the recording of supplies in the inventory system. Specific Objectives
Specifically, it attempts to satisfy the following objectives: a. To design sales and inventory system for Gilmore Wines and Spirits using DFD and ERD tools. b. To develop a program applicable using the scanning device. c. Create a base program with interfaces using VB. NET d. Have a database of records using MS SQL Server. e. To create an organized recording system. Problems encountered by Gilmore Wines and Spirits: i. Hand-written records ii. Missing transaction iii. Loss of Inventory Records iv. Unorganized files of past records v. Non-compatible software for scanning device III. Scope and Limitations The general purpose of this study is to provide an organized system of records both for sales and inventory. A detailed description about the current situation of the system, the step by step process of the whole system and the data required in creating a proposal was discussed by the two heads, Messrs. Villafuerte and Morales during scheduled interviews in the span of 2 months. The end users of the proposed program are the company heads, and all employees. The program will be installed in the hardware in the lower level of their business.
This will be done for easy access of all employees and both heads. This study is limited to the development of a sales and inventory system of Gilmore Wines and Spirits. A development of a network or a different system is not part of the study and or the program. The program will be developed under Microsoft Visual Basic. NET 2007 and will utilize database MYSQL Server 2005. The program will run at least under Windows XP operating system. The software development will focus on the automation of the company’s records on sales and product inventory. 5 IV.
Significance of the Study The main purpose of this study is to help the company of Gilmore Wines and Spirits with their problem in the recording of their sales and inventory system. The study will aim to resolve the liabilities that the manual system poses to them; missing records of transactions, amount of stocks which in turn creates loss of income. Upgrading a system to a much more organized and easy to use application is a small investment that could save money in the long run. Through this, time will be saved and all important documents and information will not be lost.
This will serve as valuable asset by a company in better management of their business. For us researchers, this study will serve as our stepping stone for more future projects. For this project, we gained experience that would serve as our guide in our future careers. This project has served as our training venue in helping a company solve their problems. For future researchers, our study could serve as their basis in sales and inventory. They could refer to our study if they would need a guide in creating a sales and inventory system. For us researchers, this study will serve as our stepping stone for more future projects. Chapter II TECHNICAL BACKGROUND Data Flow Diagram (Existing System) Supplies Figure 2: Context Diagram 7 In our system there are five entities involved; these are the head, the all-around staff, supplier and the customers. The customer gives the product and payment to the staff. When the staff receives the product, the staff will record the transaction and return the product with the official receipt to the customer. The head’s role is to receive information about the sales within the day. He also receives the records of inventory to check what stocks are low on supply.
When the head decides to restock or add a new product to the store, he submits an order list to the supplier and he gives the payment when he receives the product he ordered. After this transaction with the supplier, the staff will check if the delivered products are whether on the order list or not. Staff also checks whether the products are damaged. If there are unwanted products, the head returns it to the supplier. Then the details of the products that can be sold will be recorded by the head in the inventory record. 8 LEVEL 0: Top Level Figure 3: Top Level Diagram 9
At any transaction, the customer will give the product to the staff. The staff will then be scanning the product using the barcode technology. This will be easier for each product will be given a unique ID and product details will be embedded on the barcode. The scanning will automatically go through the database of the inventory system to locate the product and its price. After receiving the payment from the customer, the cashier will issue a printed OR. After the transaction, the product, quantity, time and date of transaction will be recorded to the sales record database.
The inventory record will also be automatically updated with deductions to the available stocks. In the end of the day, when the head wants to view the summary of sales from the day from the sales record, he can click the View Summary Button, this will show the summary to the head and the head can print it. The head will then check the inventory record if there is still sufficient supplies. There will be a notification if a product is low on supplies, the text on the row of the product low on supplies will be colored red and there will be an exclamation point beside the row itself.
This will give clear information to the head that there is definitely a product low on supplies. 10 When ordering the products needing of supplies, the list can be printed easily. During ordering of supplies from the supplier, this is still done manually. The head will call the supplier that supplies the products. When the products arrive, the head gives the payment to the supplier. When they receive the products, the staff checks for damages and whether the products delivered are the ones ordered by the head. If they see that a product is not on the list or damaged, they would return it to the supplier.
If the product passes the checking of the staff, it would be recorded in the inventory record database. If the product ordered is new, the head can click the Add button on the interface, then a form will popup where the head can enter the barcode, product name, retail and whole sale price, the available stocks they have and other details. The barcode will then be the basis of the new product when it is searched in the database. If the product is not new, the head will click the Update button and he can then update the Stocks column with the new supplies received.
When the update is done, the data from the inventory record database will automatically update the cashier station will full details of with the new data of each product. 11 Figure 4: Daily Sales Transaction The product is scanned. When the product is scanned, the product name, barcode and price can be viewed already. After finalizing the transaction, the sold product will be given to the customer and also a receipt if necessary. The transaction will be recorded to the Sales Record. The following information will be recorded: Amount of Payment, Product and Quantity Sold, and Date and Time.
The Record of Daily Transaction including the Product and Quantity Sold can be viewed by the Head. 12 Figure 5: When the product is being scanned, the barcode will be checked. After checking, the Product Name and Barcode will be shown. Following by the scanned barcode is the checking the product price thus resulting to a checked product. Figure 6: After scanning a product, the all-around staff will receive the payment. After receiving the payment, then the all-around staff will give the customer the product. If necessary, the all-around staff may give also a receipt to the customer thus finishing the transaction. 3 Figure 7: Record Transaction After a finished transaction, the amount of received money will be recorded in the Sales Record. Then after the amount is recorded, the time and date of transaction will be recorded also in the Sales Record. Lastly, after the date and time is recorded, the product and quality sold of transaction will be recorded also in the Sales Record. 14 Figure 8: Update Inventory Record After recording the daily sales, the product and quantity sold will be updated in the sales record. After that, the products that are low in supply will be updated. And also, the inventory will be updated. 15
Figure 9: Purchasing Transaction When the inventory record is updated, the items to be purchased will be listed. After listing, there will be ordering of stocks and purchasing of items. After purchasing, the products will be checked. If there are damaged or unlisted products, it will be returned. The checked products will be gathered to produce the product details. 16 Figure 10: List of Orders After updating the inventory record, the inventory will be checked for almost gone stocks. When done listing, an order will be made or list of items to be purchased. After that, a list of items to be purchased is made.
Figure 11: Purchasing Stocks When ordering the supplies, the head will submit the order list to the supplier. Then, the all – around staff will receive the supplies delivered by the supplier. Upon receiving the products, the head will give the payment to the suppliers. 17 Figure 12: Check Products After supplying the products, the products will be checked from the order slip. If there are products that aren’t on the list, it will be returned. After the products are checked, it will be checked for any damages. If there are also damaged products, it will also be returned. Only those checked products can pass. 8 Figure 13: Update Inventory After the product details are made, the product name will be recorded. If the product is new, the barcode will also be recorded. After that, if the product is again new, the product price will be recorded. Then, the number of stocks will also be recorded. If again the product is new, the minimum stock requirement will be recorded. All the information will be stored at the inventory record. 19 Data Flow Diagram: Top Level (Proposed System) Figure 14: Top Level (Proposed System) The processes inside the proposed area are the automation of the old manual recording system.
These are the updating of records in the sales and inventory system. 20 Entity Relationship Diagram Figure 15: Entity – Relationship Diagram 21 A customer transacts with one staff of the store. If the customer would want to buy a whole sale of products, he will need to give his full name, contact number and address. The contact number and address is required when the customer requests a delivery to their store. In a single transaction, the customer will give the product to the staff, the staff will then check the price of the product in the database. The customer would then pay for the products bought.
Any of the staff available at the time is authorized to update both the sales record and inventory record. During transactions, the staff will be recording each transaction with a customer. After each transaction, the staff will need to update the inventory record for the amount of products that were removed from their inventory. Then the head would be the one to check what products are low in supply. When he finds products low on supplies, he would list these products and order from the supplier. The list would contain the product(s) name, and the quantity.
After the order has been made, the suppliers will deliver the products and the head will give the payment. After the transaction with the supplier, the staff would handle the checking of the products. They will check if the product is damaged or included in the order list. When the checking is done, the head would update the inventory to update stocks or add new product(s) in the inventory record. If the head adds a new product he would input the product name, barcode, price and the minimum required stock and other parameters.