Entering Inventory via Spreadsheet

 

 

When entering inventory counts into Formulator, users may select one of 3 methods:  manual inventory adjustments (menu options 230, 231 & 232), physical inventory count (menu options 260 & 261) or spreadsheet imports (also menu options 260 & 261).  This document details the procedure for spreadsheet import - the preferred method for many customers.

 

 

1.      Check each item for proper data setup

 

a.      Check the <Cost> tab to be certain the proper cost and cost unit is entered

b.      Check the <inventory> tab for the following:

                                                    i.     Enter in the default inventory unit.

                                                   ii.     If you plan on lot tracking, make sure you check off the box.  Important:  once inventory has been entered, it is not recommended that you change lot tracking status.  Therefore, make sure you have this set properly for each item before entering your first inventory.

                                                  iii.     Optional: enter On Order, Minimum Stock, Shelf Life and/or Location

                                                 iv.     

 

2.      Make sure all batches and fill tickets are posted if they have been completed.  If you have batches that are not completed, you will need to count the raw materials that are in process and enter them as part of the physical count.

 

3.      Use menu option (260) Bulk Physical Inventory Manager for raw materials and bulk formulas:

a.      Click on the <New> button to create a new inventory.  Note:  if you have never created an inventory with this menu option, the <New> window below will automatically load.

b.      Enter the code and description for the count. 

c.      Optional:  Enter a code range, class or status to restrict which items will be included on the count.  If you leave these blank, ALL items will be included

d.      Hint: If you prefer separate spreadsheets for different groups of items you may want to create more than one code.  For example, if you want one class on each count, create a count for each class.  This will allow you to distribute separate count lists and spreadsheets.

e.      Click on  <Create New Inventory>

f.       

 

4.      Click on <Export To Excel> button

 

5.      Optional: Print count sheets using menu option (274) Bulk Physical Inventory Reports

a.      Select the physical count inventory code

b.      Decide whether or not you want the quantities to print on the report

Click <print>

 

6.      Enter the counts in Excel under the “Quantity” column.  If the item is lot tracked, also enter the lot number.  All other columns to the right are optional. 

 

7.      Save the excel spreadsheet with the proper counts and lot information

 

8.      Under Formulator menu option (260) Bulk Physical Inventory Manager, open the physical count record you created

 

9.      Click on the Import button and select the spreadsheet file you entered the counts into

 

10.   Spot check a few items to make sure the import was successful

 

11.   DO NOT import the spreadsheet a second time.  If you do, you will double the counts!

 

12.   Once the counts are imported, click on the <Save> button

 

13.   Click on the <Post> button to post the inventory