Creating a Stock Management System

I had 5 years experience with stock control some years ago. The basic method is to keep a record of all stock movements in and out of storage in much the same way that a bank statement shows movements of money. Excel and Access are both well suited to this task. The first decision is which application to use the bearing in mind that versions of Excel before 2007 are limited to 65,5536 rows. If the required number is likely to exceed this I would personally use Excel as a front end connected to an Access table.

The basis of the system is a single simple table with field headings at the top and data underneath. Do not be tempted to use separate stock listings. This is what big companies use and is the ideal. If you can keep closely to this Excel is still tool for the job. If you need to refer to multiple suppliers of a product and/or multiple outlets then you may need to use Access with its ability to use separate tables and link them together to avoid repetition of data - rather than using Excel lookup tables. If Sales need to be taken into account I would use separate tables for that. You might need a separate table to keep track of orders from external suppliers too.

Here is a rough example ('code' used to format the data). You will need to start everything off with a manual stocktaking.
The base table does not need to be much more complicated than this because updating the table and the hard work of pulling various items of data togetether eg. supplier name, price, etc. can be done with userforms and lookup tables (or not if required). There will be separate processes for calculating stock listings and showing details of individual stock items. Calculations from such tables are very fast and easy nowadays.

A way of helping to keep data accurate is to get stores to let you know when any item has none left when they remove anything.

Hope this helps.