PDF download Download Article PDF download Download Article

Do you need a simple POS (point of sale) system for your small retail shop? With this method, you can manage the 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
  1. 1
    Learn Microsoft Excel. Learn about Excel macros.
    • Create Excel workbook with 6 worksheets for followings steps like this:
      18TODAY.png
    • Bills
    • Pur
    • Purchase
    • Sales
    • Stock balance
    • Setup
  2. 2
    Create a setup page with these headings, setup your stock items
    • Category Code : Create specific codes for your each item. This must be a unique ID number for each items. Use this to create the barcodes.
    • According to this, take all the inventory items & create a code and update the sheet with opening stock, pur, price, and sales price. You have to give the correct purchase price and sales prices because when you issue a bill, price will be selected from this sheet. The opening balance will be linked with the stock balance sheet. If you don’t have a barcode printer, just print the receipt to A4 Sheet and paste it to your sales items.
    • Create a 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
    Advertisement
  3. 3
    Create a bill sheet:
    • Create a sheet according to this format and give the below formula to each row and create macros 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
  4. 4
    Create a Pur sheet: according to this format
    24TODAY.png
    • Now create the Purchase and sales data save page with this format:
    • Sales data base
  5. Advertisement

Community Q&A

Search
Add New Question
  • Question
    How does this work with a scanner and bar codes?
    Community Answer
    A scanner replicates keyboard entry. Instead of entering all the barcode digits on the keyboard, the scanner reads them and enters them for you.
Ask a Question
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Thanks for submitting a tip for review!
      Advertisement

      About This Article

      Thanks to all authors for creating a page that has been read 202,487 times.

      Did this article help you?

      Advertisement