A very simple stock register, where stock changes can be entered manually, can be created using an Excel spreadsheet.
The following example uses a PLU, or stock code, a stock description, a minimum stock level, stock in, stock out and a field displaying the total stock balance.
After opening a new blank workbook, type the name for each column into the first row, ie in field A1 type PLU, B1 is Description, in C1 type Minimum_Stock, D1 is Stock_in, E1 for Stock_out and F1 is Current_Stock.
Next, enter the first PLU, description etc in the second row. Enter incoming stock in the next column, then stock going out. The current stock column needs a formula. Double click on the field, then type =SUM(C2:D2)-E2 into it.
For the next row, the formula entered has to be =SUM(C3:D3)-E3, the =SUM(C4:D4)-E4, etc. Alternatively, highlight column F and type Current_Stock!(C:D)-E. This saves a lot of time as it applies the formula to all rows at once. The value for each row will then be automatically inserted.
A completed row will look like this, for instance:
PLU Description Minimum_Stock Stock_In Stock_Out Current_Balance(C:D)-E
123 Watnot 5 10 2 13
As in or outgoing stock are entered, the current stock will be adjusted automatically. Clicking on 'Data' at the top of the screen and selecting 'Form' will produce a small panel displaying a single PLU at a time. Entering the required PLU will bring up the corresponding data and entries can be made without searching through the whole sheet.
For more advanced, complicated systems, it is possible to download free stock register templates from<a href="http://3d2f.com/tags/download/stock/register/">3d2f.com</a>.
Another alternative is to have a look at some free tutorials at<a href="www.bettersolutions.com/excel.aspx">BetterSolutions& or <a href="www.free-training-tutorial.com/">FreeTrainingTutoria and then try to set up a register with the help of this newly gained knowledge.
The following example uses a PLU, or stock code, a stock description, a minimum stock level, stock in, stock out and a field displaying the total stock balance.
After opening a new blank workbook, type the name for each column into the first row, ie in field A1 type PLU, B1 is Description, in C1 type Minimum_Stock, D1 is Stock_in, E1 for Stock_out and F1 is Current_Stock.
Next, enter the first PLU, description etc in the second row. Enter incoming stock in the next column, then stock going out. The current stock column needs a formula. Double click on the field, then type =SUM(C2:D2)-E2 into it.
For the next row, the formula entered has to be =SUM(C3:D3)-E3, the =SUM(C4:D4)-E4, etc. Alternatively, highlight column F and type Current_Stock!(C:D)-E. This saves a lot of time as it applies the formula to all rows at once. The value for each row will then be automatically inserted.
A completed row will look like this, for instance:
PLU Description Minimum_Stock Stock_In Stock_Out Current_Balance(C:D)-E
123 Watnot 5 10 2 13
As in or outgoing stock are entered, the current stock will be adjusted automatically. Clicking on 'Data' at the top of the screen and selecting 'Form' will produce a small panel displaying a single PLU at a time. Entering the required PLU will bring up the corresponding data and entries can be made without searching through the whole sheet.
For more advanced, complicated systems, it is possible to download free stock register templates from<a href="http://3d2f.com/tags/download/stock/register/">3d2f.com</a>.
Another alternative is to have a look at some free tutorials at<a href="www.bettersolutions.com/excel.aspx">BetterSolutions& or <a href="www.free-training-tutorial.com/">FreeTrainingTutoria and then try to set up a register with the help of this newly gained knowledge.
