Monday, April 6, 2015

POS SYSTEM FOR SHOPS, GROCERIES AND SERVICE CENTERS FROM EXCEL with using barcodes


How to create a simple POS (point of sales) system for small shops groceries and service centers using MS Excel

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

v  Issue a sales bill using bar code reader.
v  Manage purchases
v  Control inventory
v  Day end and month end stock balance.
.....

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



1.       Bills
2.       Pur
3.       Purchase
4.       Sales
5.       Stock balance
6.       Setup

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.




Create Bar code:
You can download Bar code Generator or can do it Online.

Example for barcode I generate for sample file.



If you don’t have barcode printer just print to A4 Sheet and paste it to your Sales items.

Next
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
Next Create a bill sheet:









Create sheet according to this format and give below formula to each row and create macro with below codes. 
You can print bill with this sheet with using bar code reader or select the item code with drop down list

  • 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

Next Create Pur sheet: according to this format.



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

Sales data base







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.

watch this video to how system work:







11 comments:

  1. Hi
    I have a small grocery shop and i try your pos system and i am using excell 2007 and it is not opening in that.
    kindly advise how to open this in excel 2007

    ReplyDelete
  2. hi!
    I cannot use the "Save Bill" it prompted a run time error 1004.
    The sheet is unprotected, and needs a password.
    Is it okay if you could share the password for this?
    Thanks. :)

    ReplyDelete
  3. Hi,
    i run a small leather made-ups shop in Pakistan. i dont know about macros and stuff( not good in excell) i wanted to use ur workbook for my shop but every time save save a bill using the save bill button, it misses the first entry each time and when i press the previous bill button the first two entries are missing.pls correct these two .

    ReplyDelete
  4. Please help me out i have downloaded the file but it is not allowing me to give print save commands and also it is giving me macro disabled and i have enabled it also after that it is giving me error now please suggest me how to use it and what changes are required to make it working. Thanks

    ReplyDelete
  5. Hi,
    Very interesting excel software which is simple and easy to use for a small grocery shop. Pls sent me the password to activate the bill worksheet. Thanks

    ReplyDelete
  6. Total Column in bill sheet is not working.
    As a result balance amount to be paid is not showing.
    Please share password to unprotect the book.

    ReplyDelete
  7. Please you share password to unprotect the sheet bill. Thank you very much....

    ReplyDelete
  8. please did you want make a video of the making of

    ReplyDelete
  9. can you unprotect sheet or share password..... asim_usman@yahoo.co.uk

    ReplyDelete
  10. can you unprotect sheet or share password..... asim_usman@yahoo.co.uk

    ReplyDelete