pos/inventory like excel file. how to?

[uveenhacked]

guys, i would like to create an excel file, it would be like a POS with Inventory but before i start, i would like to ask how can i do this better and easier? by the way, i will use this project for my small mini-mart, i dont need to track customers, just the items i sold and items in my inventory, my actual sales for the day. Thanks in advance.

 

[BrianB]

This will not be a trivial or simple task. It depends on how you operate. The main issue is where you will be getting your information from.

Think in terms of getting Sales records working before bothering with Inventory. You will hopefully have a cash till that keeps a record of each sale. (I believe there are some in existence that virtually do the Inventory as well).

The next issue is how you expect to transfer data to the worksheet. If you are going to rely on typing everything you have a big problem because any slight typo error will mean errors later on. This will be very time consuming.

All products nowadays carry a barcode for this very reason. So, if you consider how big supermarkets operate, they have a master table that is based on the barcode and carries all the product information (eg. Description, Cost Price, Selling Price etc). Excel is reasonable good at reading lookup tables. Later on you can add things like minimum stock levels to each product.

Apart from the lookup table, do not be tempted to put data in numerous worksheets. Keep everything in a single table. Excel can then do lots of hard data analysis fairly easily.

When it comes to Inventory, you have to start with a stocktake and from then on record any deliveries (stock plus) as well as sales (stock minus) - don't forget things like shortages and damaged goods too.
Here you will be transferring information from Delivery Notes to your system, and it would be a good idea to have some formal method of recording things like damages. Always update your database from pieces of paper - not memory.

You will need to periodically do a physical stockcheck to check your data. A good ongoing way of doing this is to check an item on your system if the physical stock is zero.

Hope this helps.