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

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

Steps

PDF download Download Article
  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.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 3 Helpful 25
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

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

      You Might Also Like

      How to
      Use a Cash Register
      How to
      Count Out Change
      How to
      Give Back Correct Change
      How to
      Sell Shoes
      How to
      Stock Grocery Shelves
      How to
      Greet Customers Arriving in a Store
      Advertisement

      About This Article

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

      Did this article help you?

      Advertisement