Saturday, October 15, 2011
Inventory Control Program
When I worked in the mining project, my staff complained about the difficulty of recording inventory transactions (Store, Fuel, Tool, etc.). We do have an online computerized system with a database based on Microsoft Access. But it often had problems like: slow connection, even sometime no connection, the program is too complicated, it took at least 2 hours a day only for daily transaction data input.
I tried to solve the problem by using Excel which is easily operated by anyone, and sent easily via email so that headquarters can also monitor the inventory in the project.
Microsoft Excel can always follow the update version. Unlike Microsoft Access that if a new version issued, then the data on the old version is difficult to import into the new version, and there is the possibility of data loss. Good thing about Access is that it has no data limit, database size can be as big as hardisk capacity.
The attached Excel file is a simple example to monitor the inventory that can be operated by anyone, suitable for mining projects, oil, construction, shops, small businesses, etc.. Easily be sent via email and can be opened and read by anyone as Microsoft Excel is more popular if compared to Access.
I also have a more complicated Excel spread sheet using Visual Basic program with database protected by passwords to prevent data manipulation. We used it for about two and a half years until the project was completed.
Excel SUMIF and VLOOKUP functions are described in a separate article.