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 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

Download Article

1
Learn Microsoft Excel. Learn about Excel macros.
- Create Excel workbook with 6 worksheets for followings steps like this:
- Bills
- Pur
- Purchase
- Sales
- Stock balance
- Setup
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
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
Create a Pur sheet: according to this format
- Now create the Purchase and sales data save page with this format:
- Sales data base

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 How.com.vn 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 How.com.vn
Yes No
Not Helpful 3 Helpful 24