Decision Support System (DSS): Demo PoS for a Retail Store

In this tutorial, we will create a simple point of sale system for a fictitious retail store. The system will be used by the operational staff to record the day to day business transactions, the tactical staff will also use the system to monitor the retail store’s current performance.

The system will have the ability to export data to excel. Excel will be used as a decision support system tool to analyze the data. The data in Excel can also be combined with data from other sources.

Retail store MIS components

1. Business Procedures

Business procedures define how the day to day business transactions must be conducted. The point of sale system will be used to record daily sales.

The following list outlines the activities to be done when a customer buys an item

  1. Customer presents items to be purchased to the cash till
  2. Cashier searches the system for the presented item in the system to retrieve the price
  3. Cashier enters the quantity of the purchased item, and the system computes the total cost. This process is repeated for all the purchased items
  4. If a cashier wrongly adds an item or item quantity into the system, they should be able to remove it if the payment hasn’t yet been posted. If the payment has been posted, the cashier should not be able to remove anything.
  5. After verifying the total customer bill, the system should allow the cashier to post the customer payment and print a receipt for the customer.

2. People

The users who will be using the system will be cashiers, accountants and inventory control users. All the users must be computer literally and be trained on how to use the point of sale system.

3. Hardware

The point of sale system will Microsoft Access as the database engine. Microsoft access is a standalone database engine and as such, the system will be run on a single computer. The computer used will need to have the following specifications.

  • A minimum of 1GB RAM
  • A minimum of 10GB free storage space on the local drive where the database will be
  • The computer should run a windows based operating with at least windows XP and above
  • The processor speed should have a minimum of 1GHz

4. Software

The computer that will be used with the system should have the following minimum specifications.

  • Microsoft based operating system
  • Microsoft Office Suite (Specifically Microsoft Word and Microsoft Access)

5. Data

The day to day business transactions will be stored in a Microsoft Access database.

Point of Sale Database Dictionary

A database dictionary describes the database fields, what they do and the data type.

The following describes the tables that we will have in our database. For the sake of simplicity, we will keep the number of tables to only four (4).

Products Table

S/NField NameData TypeDescription
1idNumericRecord primary key
2Product_nameStringName of the product
3PriceNumericThe price of the product
4Quantity_at_handNumericAvailable quantity at hand on any given time
5Recommended_supplier_idNumericForeign key that links to suppliers table

Customers Table

S/NField NameData TypeDescription
1idNumericRecord primary key
2Customer_nameStringName of the customer
3Total_purchasesNumericAccumulated value of the total sales to the particular customer

Suppliers Table

S/NField NameData TypeDescription
1idNumericRecord primary key
2Supplier_nameStringName of the customer
3Contact_numberNumericContact number for the supplier
4Email_addressStringEmail address for the supplier

Sales Table

S/NField NameData TypeDescription
1idNumericRecord primary key
2Customer_idNumericCustomer unique identifier
3Transaction_dateDateThe date that the transaction was recorded
4Amount_paidNumericTotal purchase amount
5PaidBooleanBoolean field that shows whether a customer has paid or not

Sales Details Table

S/NField NameData TypeDescription
1idNumericRecord primary key
2sales_idNumericCustomer unique identifier
3Product_idDateThe date that the transaction was recorded
4quantityNumericTotal purchase amount
5PaidBooleanBoolean field that shows whether a customer has paid or not

Point of Sale database entity relationship diagram

The entity relationship diagram shows the relationship between tables in the database. The following table describes the relationships between the database tables

S/NRelationshipPrimaryForeign keyRelationship nature
1Products to SalesId in productsProduct_id in sales1 to Many
2Sales to customersId in customersCustomer_id in salesMany to Many
3Products to suppliersId in suppliersRecommended_supplier_id in productsMany to 1

The following image shows the relationships among the tables in our database.

Decision Support System (DSS): Demo PoS for a Retail Store

How a transaction is processed in the system

  1. The system retrieves all customer records from the customers table and populates the drop down combo
  2. The cashier clicks on new transaction button. This creates a new record in the sales table and generates a transaction id.
  3. The system retrieves all products from the products table and populates the drop down combo
  4. The cashier selects a product from the drop-down list. The system keeps the product id and price for the current item.
  5. The cashier enters the quantity purchased. The system calculates the subtotal based on the stored price and entered quantity. This process is repeated until all products have been posted into the system.
  6. The cashier enters the amount that the customer has paid. The system calculates the change if the customer pays more than the price.
  7. Cashier clicks on post payment button. The system updates the;

1.     product quantity at hand in products table

2.     updates the amount paid field and marks the transaction as paid in the sales table

3.     updates the total purchases field in customers table

Point of Sale Graphical User Interface Screenshots

System main window – this is the entry point into the system. It allows the users to enter data, process transactions, print reports and export sales data to Excel for further analysis.

Here we have demonstrated various data entry window used during POS, to give an overview of the field that is filled while performing the transaction.

Decision Support System (DSS): Demo PoS for a Retail Store

Point of Sale Window used by cashiers at the cash point

Decision Support System (DSS): Demo PoS for a Retail Store


Customer data entry window

Decision Support System (DSS): Demo PoS for a Retail Store

Products data entry window

Decision Support System (DSS): Demo PoS for a Retail Store

Summary

This tutorial shows you the practical steps involved in developing an information system and explained a prototype of a POS (point of sale) system. The system has been developed using Visual Basic for Applications (VBA) in Microsoft Access.

Related Posts

© 2024 Business Management - Theme by WPEnjoy · Powered by WordPress