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.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/e\/e0\/19TODAY.png\/460px-19TODAY.png","bigUrl":"\/images\/thumb\/e\/e0\/19TODAY.png\/640px-19TODAY.png","smallWidth":460,"smallHeight":27,"bigWidth":640,"bigHeight":38,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    2
    Create a setup page with these headings, setup your stock items
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/9\/92\/22TODAY.png\/460px-22TODAY.png","bigUrl":"\/images\/thumb\/9\/92\/22TODAY.png\/728px-22TODAY.png","smallWidth":460,"smallHeight":319,"bigWidth":728,"bigHeight":505,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    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. {"smallUrl":"https:\/\/www.wikihow.com\/images\/1\/18\/23TODAY.png","bigUrl":"\/images\/thumb\/1\/18\/23TODAY.png\/376px-23TODAY.png","smallWidth":460,"smallHeight":489,"bigWidth":376,"bigHeight":400,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    4
    Create a Pur sheet: according to this format
    24TODAY.png
    {"smallUrl":"https:\/\/www.wikihow.com\/images\/b\/bf\/24TODAY.png","bigUrl":"\/images\/thumb\/b\/bf\/24TODAY.png\/320px-24TODAY.png","smallWidth":460,"smallHeight":318,"bigWidth":320,"bigHeight":221,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    • 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 24
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
      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 196,423 times.

      Did this article help you?

      Advertisement