Inventory Ledger in Excel (a good exercise of advanced functions)

Since I am jobless recently, I am thinking of introducing more advanced Excel course.  Further, I happen to know that there are still lots of small & medium size factories operating without a good warehouse system to manage its inventory in & out, let alone a ERP system.  As we all know, the acquisition cost of an ERP system is only part of the whole system cost.  If the people are not accustomed to the working pattern of system, the deployment of a ERP is just creating another problem.

For consumables, tools, stationery & mold, people may not want to keep record in ERP system.  Obviously, an Excel template for keeping record is beneficial.  Besides, it is a good reference for studying advanced Excel functions.

For this reason, I have constructed an Excel template with embedment of sophisticated functions and some simple VBA programming.  All sources are open.  You may press [Alt] + [F11] to see the program code.

The file can be obtained from the following links:

1. http://www.slideshare.net/dicklam128/stock-ledger-in-excel
2. https://www.box.net/shared/fa2ib3dtjo

The template has the following functions:

1. A tab, named [TranNote] for inputting Transaction Note, i.e. you can use the standard template worksheet for inputting multiple lines of items for the same type of transaction
   • Before you input any transaction, you need to set up the individual inventory ledger of the particular item.  To this end, select the tab [PartNo], input new part number information below row 7 and copy the right hand side formula from the above rows
   • Then, double click the cell you just enter the part number.  By doing this, a VBA program is triggered to create the inventory ledger, the standard ledger, [stdlgr] which is hidden, is being copied actually.
   • For proper coding of part number, you should only alphabet and digit.  You can also use “-“.
2. Stock Ledger is automatically updated when you click the post button in [TranNote]
3. In the tab [PartNo], there is summary of inventory balance and transaction summary for a particular month.  The critical is you need to enter the current month in cell [B4]

The file is only functional in Excel 2007 and you need to enable Macro when opening the file.

You are welcome to comment on it.  Thanks