Skip to content

Elliott Physical Count Processing

Q. How can we process physical count with Elliott using a spreadsheet export and import method? We are prefer to have a variance report before posting to inventory.

A.
Elliott provides a verify efficient tool to create a worksheet with a spreadsheet format and allow you to update the tag file with a mass import method. Afterwards, the user may generate different reports to investigate the variance before posting.

Performing Physical Count Processing

1.     Preparation:

a.       Date: Decide physical count Start Date and End Date. There should be no Inventory moving during the period, including production, shipping, receiving and adjustments.

b.     Warehouse Planning: Perform warehouse cleanup and re-arrangement of stocking room, merge the same-category items in the same area and job assignment.

c.       Material Issued to Shop Floor: Do not count QTY-ON-HAND (Stocking).

d.     Post Transactions: Post all inventory-related transactions, including invoices, production, receiving and adjustments before start date.

e.     Freeze Inventory: Process Inventory Freeze at either Prior Start Date or Start Date (book value should be identical of both days).

f.       GL Inventory Count Variance Creation: Make sure GL chart account and IM Setup field # 25 flag is set to “Y” if you’d like to create a GL transaction for the variance.

 

2.     Prepare Count Sheet:

You may create a worksheet based on the Elliott layout using third-party tools or from Elliott Physical Processing. What follows is the procedure to create an Elliott Count Sheet.

a.       Make sure the Tag file is clean: Print “List.” You should see “*** Nothing In Range ***” showing up.

b.     Mass create Tags:  Create 0 (or Computer QTY) tag from Physical Count Utility.

(IM --> Processing --> Physical Count Processing --> Utilities --> Create Zero QTY Count Tag)

c.       There are 3 import questions:

          i.     Location – You may create one location or multiple locations at a time.

          ii.     Generate Tag For QTY-OH = 0? (Y/N) – Tag list will be very long if your majority item Qty-OH is 0.

          iii.     Generate Tag with Zero or Computer Qty (Z/C) -- You may create either 0 QTY or copy from system QTY-ON-HAND as tag count QTY  (recommend: C). 
                  

d.       Export Tag to CSV through Export Menu (Physical Count Processing --> Utility --> Export).

You may create a CSV file and store in the proper folder.

                  

Speadsheet: COUNT2014-10.CSV is shown below.

Suggestion:

(1).       Save file to an Excel worksheet format (XLSX).

(2).       Location 01 should save to text format (make sure location is 01, not 1).

(3).       You may insert a blank “Qty Count” column at E, then hide “Quantity” if you created a tag with computer QTY.

(3-1) 

                  

(3-2) After inserting “QTY Count” on E, Computer QTY, push F, and change header to “Beginning QTY.”

                 

(3-3) Hide “Beginning QTY” for count worksheet, then enter new count QTY in column E.

                 

3.     Enter Counted QTY on Count Sheet:

         a.       Enter Counted QTY in column E.

         b.     Quick Compare Count QTY and Computer QTY (unhide column F and insert a Variance column right after).

Important: Enter and work on the spreadsheet in an Excel format instead of CSV. Use CSV format for import only. *** Never reopen a spreadsheet from an CSV file if you need to revise or fix some line information. Opening a spreadsheet from CSV may remove the leading 0 from the text columns, especially Item# or Location Code.

 

4.     Import New Counted Spreadsheet Back to Tag file:

        a.       Keep only columns A to E (delete columns F and beyond):

   

                  

        b.       Make sure there are no errors on pre-interface report.

                  


            c.       Interface – Import one location at time.

Important: In Field # 4, select “O” overwrite at first location (i.e., 01) then select “A” append for secondary location (i.e., 02, 03, 07, etc.)

                 

                 


5.     Evaluation -- Variance Report: (Physical Count Processing Report)

         Elliott provides a very powerful report to compare variances with the system and count quantity with cost amount.

           a.       Generate Tag Work File – User should run “Generate Tag Work File” first (very important). T= Total Count, “Generate Date” should be same as “Freeze Date” if your count is based on “Freeze Date.”

           b.       By Warehouse/Variance Report – The Variance Report is sorted by Variance Amount (descending), which allows the user to focus on the largest amount first.

           c.       By Item/Variance Report – Variance Report Item number sequence.

           d.      Tag Reconciliation Report – Showing missing or multiple tags by item number.

                 

                 

                 (Sample: Variance Report by Variance Value) �

                 

                  Option: Convert Variance Report to Excel Pivotable to summarize multiple locations.

                      

6.     Post Physical Count to Update Inventory:

       a.       Before posting - 1: Backup files: IMITMFIL.BTR, IMINVLOC.BTR, IMTAGFIL.BTR, IMINVTRX.BTR, IMDISFIL.BTR (K:\elliott7\data fold)

       b.     Before Posting - 2: Print Frozen Stock Status Report to disk (option) (IMà Report à Frozen Stock Status Report).

        c.       Print “Update Edit List” for final confirm before Post.

                 (IM --> Processing --> Post Physical Count)      

                  

��

Feedback and Knowledge Base