Introduction: POS SYSTEM FOR SHOPS, GROCERIES AND SERVICE CENTERS FROM EXCEL With Using Barcodes

By MY BLOG
About: I'm a Sri Lankan and like to communicate with others in the world with my knowledge. I'm employing as an Accountant and very familiar to create and develop Ms access software. my hobby is create crafts using …

I’m intractable with this blog to world how to create a simple POS (point of sales) system for small shops groceries and service centers.

With this method you can manage following facilities without special software or expensive equipment.

v Issue a sales bill using barcode

v Manage purchases

v Control inventory

v Day end and month end stock balance

v Daily sales

v Daily purchases


supporting file can download here

Step 1: Create Workbook

You need some basic knowledge about excel macro to do this simply,

First open an excel workbook with 6 worksheets as followings.,:

1. Bills

2. Pur

3. Purchase

4. Sales

5. Stock balance

6. Setup

Step 2: Create a Setup Page

Next:

Create a setup page with this headings and setup your stock items.

Category : item category

Code : Create specific code for your each items. This must be unique ID no for each items and use this to create the barcodes. Exp:

According to this, take all the inventory items & create a code and update sheet with opening stock, pur.price and Seles price. You have to give the correct purchase price and sales prices because when you issue a bill, price will pick from this sheet. Opening balance will link with stock balance sheet.


Bar code create: you can create bar code with your item code, using online barcode creator or you can download barcode software to do it.

Step 3: Create Stock Balance Sheet:

Create this sheet with below headings:

Copy this formula to each row and copy paste to down:

Code:  =IF(setup!$B$3:$B$323<>"",setup!$B$3:$B$323,"")
Description:  =IF(setup!$C$3:$C$323<>"",setup!$C$3:$C$323,"")
Opening Balance:   =SUM(IF(B3=setup!$B$3:$B$1021,setup!$D$3:$D$1021))
Purchase: =SUM(IF(B3=purchase!$B$2:$B$2005,purchase!$D$2:$D$2005))
Sales: =SUM(IF(B3=sales!$H$2:$H$2551,sales!$J$2:$J$2551))
Stock: =+D3+E3-F3

Step 4: Create a Bill Sheet:

Create sheet according to this format and give below formula to each row and create macro with below codes.


Line: =IF(C5="","",B4+1)

Code: Create a list box link with setup page item code and name. when you connect a bar code reader with bar code sticker details will auto pick.

Description: =I4

Qty : this column you have to enter manually according to customer purchase qty.

Price: =IF(E4="","",VLOOKUP(C4,al,5,0)*E4)


** macro for Save bill

Create a button called Save bill and copy this code:

You can download this supporting file above..

Sub Dayendsales()

' Dayendsales Macro

''

   Sheets("Tsales").Select

   Columns("G:G").Select

   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

   Range("E2:E255").Select

   Selection.copy

   Range("G2").Select

   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

       :=False, Transpose:=False

   Sheets("sales").Select

   Range("B3:D1572").Select

   Application.CutCopyMode = False

   Selection.ClearContents

   Range("D3").Select

End Sub

Sub DayendPurchases()

' ' DayendPurchases Macro

'  Sheets("Tpurchase").Select

   Columns("F:F").Select

   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

   Range("D2:D643").Select

   Selection.copy

   Range("F2").Select

   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

       :=False, Transpose:=False

   Application.CutCopyMode = False

   Sheets("purchase").Select

   Range("C3:D625").Select

   Selection.ClearContents

   Range("E3").Select

End Sub

Sub SaveBill()

'

' SaveBill Macro

 Application.Run "'shop sales control.xls'!copy"

 Application.Run "'shop sales control.xls'!SaleReplace"

End Sub

Sub DayEnd()

' DayEnd Macro

End Sub

Step 5: Create Pur Sheet:

create worksheet according to this format.


Step 6: Now Create the Purchase and Sales Data Save Page With This Format:

Now create the Purchase and sales data save page with this format:

Step 7: Macro Code You Can Copy Form My File.

Macro code you can copy and paste form above file.

After all the formula and code create you can run the system. When you save the bill, the details will save to sale database and it will update the inventory.

End of the month save the file as new name and delete old data of sale and purchase database worksheet. Before delete, copy the closing stock balances to setup page opening balances column.

I have created some useful office database software to give to world here

Thanks