How to Create a Retail Point‐of‐Sale System with Excel

Do you need a simple POS (point of sale) system for your small retail shop? With this method you can manage following facilities without special software or expensive equipment:

• Issue a sales bill using barcode
• Manage purchases
• Control inventory
• Day end and month end stock balance
• Daily sales
• Daily purchases

Learn MicroSoft Excel. Get knowledge about Excel macro to do this simply.

• Create Excel workbook with 6 worksheets for followings steps like this:
  
• Bills
• Pur
• Purchase
• Sales
• Stock balance
• Setup

1. Create a setup page with this headings,setup your stock items
   • 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.
     
   • 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. If you don’t have barcode printer just print to A4 Sheet and paste it to your Sales items.
   • 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
2. 
   
   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 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
3. 
   
   Create Pur sheet: according to this format
   
   • Now create the Purchase and sales data save page with this format:
   • Sales data base