Tuesday, April 3, 2018

POS (point of sales) system for small shops groceries and service centers

How to create a simple POS (point of sales) system for small shops groceries and service centers using MS Excel


With this method you can manage following facilities without special software or expensive equipment.

v  Issue a sales bill using bar code reader.
v  Manage purchases
v  Control inventory
v  Day end and month end stock balance.

First create excel workbook with 6 worksheets for followings like this:

1.       Bills
2.       Pur
3.       Purchase
4.       Sales
5.       Stock balance
6.       Setup

Create a setup page with this headings and setup your stock items

Category : item 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. Exp:

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.

Create Bar code:
You can download Bar code Generator or can do it Online.

Example for barcode I generate for sample file.

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:

Next Create a bill sheet:

Create sheet according to this format and give below formula to each row and create macro with below codes. 

You can print bill with this sheet with using bar code reader or select the item code with drop down list

Next Create Pur sheet: according to this format.

Now create the Purchase and sales data save page with this format:

Sales data base

After all the formula and code create you can run the system. When you save the bill, the details will save to sale database and it will update the inventory.

End of the month save the  file as new name and delete old data of sale and purchase database worksheet. Before delete, copy the closing stock balances to setup page opening balances column.

watch this video to how system work:

No comments: