Do a Stocktake with Excel
Raw Material Stocktake with Excel
1. Menu option ‘Configure > Calendar Period’
Create new period from here and switch to that period
2. Menu option ‘Files > Raw Materials > Stocktake’
Insert new Stocktake period. Do NOT bring forward closing stock to new period
3. After blank opening stock entries have been created click <edit Stocktake>
(These previous 3 steps are the same as for doing a manual stock count entry.)
4. From the ‘Enter Opening Stock' screen click <Generate Stocktake Sheets>
5. Accept ‘save to excel.
6. From this screen browse to the location where you wish to save the blank Stocktake excel workbook. A name will automatically be generated based on the Stocktake period. You can change this to your own name if desired.
Do not tick ‘Suppress Zero Items’ since you want the entire stock list to be generated.
7. The workbook looks like this
On no account change the position of columns C or D. This is where the stock mass or volume is read from when you import the completed workbook.
If you have some items which are counted in volume you only need to enter the volume in column D.
When you import the completed workbook volume entries in column D will automatically be converted to mass using the entered SG of the material.
If you do not count any volume items you can ignore column D, but Column D must be a blank column otherwise and entries in that column could be interpreted as volume entries.
If you have stock at more than one location you can copy the workbook to different named workbook for entry at the different locations.
Once the workbooks have been filled in they can be imported by clicking <import from excel> on the ‘enter opening stock screen’
Once you have selected a saved workbook you will get this screen
Leave the ‘append to Existing Entries’ ticked if you are importing more than one workbook.
This will accumulate the stock counts from the several imported workbooks.
If you accidentally import the same workbook more than once you will have to delete the stock count and start the import procedure again.
Once all the workbooks have been imported you can proceed with running the Stocktake audit then posting the Stocktake exactly the same as if you had manually entered the stock count.
Warning:- before importing the workbooks make sure that there are no blank rows before the end of the data. The first blank row encountered signals the end of the data to import