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

I’m introducing  with this blog to you a 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







Step 1: Create Workbook

You need little knowledge about excel macro to do this simply, otherwise you can download file from my blog here.

First create excel workbook with 6 worksheets for followings like this:

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 file form my file.

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:

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 form my 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.

You can download sample file form my blog

Comments

author
rgull1 made it!(author)2016-10-20

just copy paste from Wikihow.com
Here is the link
https://www.google.com.pk/amp/m.wikihow.com/Create-a-Retail-Point‐of‐Sale-System-with-Excel%3famp=1#amph=1

author
ColeW18 made it!(author)2016-09-21

Hi MelissaP103!

You can check this information on this site

http://pos-ar.com/

author
Sunniva made it!(author)2016-03-16

I need sheet pasword Plz

author
SushilL4 made it!(author)2015-10-23

I paste forumla =SUM(IF(B3=setup!$B$3:$B$1021,setup!$D$3:$D$1021)) in stock balance on opening stock but not work why it wrong 29

author
JasonY7 made it!(author)2015-09-11
author
zaitar made it!(author)2015-07-20

cheap and simplified for smaller businesses

author
GlaizaA made it!(author)2015-05-30

HOW CAN I CREATE A LIST BOX THAT IS LINK WITH THE SETUP

author
tariqulfjm made it!(author)2015-05-09

author
msworldshany made it!(author)2014-08-14

can i generate a discount button with this system?

author
madams3 made it!(author)2014-02-23

Hello,

Nice system just having a few issues, i dont quite get what you mean about the Description list thingy, so how dod you make it copy certain Cells from the setup page when a certain code is typed in? i tried looking at yours but it password protected. Thanks

About This Instructable

36,676views

43favorites

License:

Bio: I'm Sri Lankan and like to learn new things and communicate with others in the world. I'm employing as an Accountant in Maldives ... More »
More by WYGWORLD:INVOICING BILLING AND INVENTORY CONTROL SYSTEMDesign a form filling database using Microsoft access.Flower Vars from newspaper tube
Add instructable to: