Open Forum

Expand all | Collapse all

Scheduling new items for cycle counts?

  • 1.  Scheduling new items for cycle counts?

    Posted 5 days ago
    We use the stock count feature for cycle counts as well as physical inventories.After a full physical count, I re-start the cycle count schedule for the next year. However, when we add new items, we rank them as ABC, but the items are not incorporated into the existing count. Without starting the count schedule over, is there any way of folding these new items into an existing stock count schedule?

    Side note since I am on the topic: is there any way for the stock counts to be randomized so they do not schedule alphanumerically? For control purposes, it would be best if what was coming up on the next count were a surprise.

    ------------------------------
    Audrey Forrest
    Senior Accountant and Business Analyst
    GP 2015 R2, D365 CRM V9
    CRMUG Chapter Leader - Seattle
    ------------------------------


  • 2.  RE: Scheduling new items for cycle counts?

    GPUG ALL STAR
    Posted 5 days ago
    Edited by Beat Bucher 5 days ago
    Hi @Audrey Forrest,
    Microsoft provides little information about the stock count tables, aside of what's in the inventory user guide..
    Here is an article about what to do when you experience stock count errors with your inventory :
    https://support.microsoft.com/en-us/help/4093724/inventory-stock-count-errors-in-microsoft-dynamics-gp

    Here is also one of the rare blog posts from a partner about using Stock Count with ABC analysis :
    https://www.isolutionspartners.com/2014/06/17/dynamics-gp-inventory-stock-count-overview/

    I've been doing custom stock counts for many years, long ago when still using GP 8.5 & Manufacturing.  The essential tables about stock count are :

    ivStockCount - IV10300 - Stock Count
    ivStockCountLine  - IV10301 - Stock Count Line
    ivStockCountSerialLot - IV10302 - Stock Count Serial Lot
    ivStockCountUofM - IV10303 - Stock Count U of M
    ivStockCountHist - IV30700 - Stock Count History
    ivStockCountLineHist - IV30701 - Stock Count Line History
    ivStockCountSerialLotHist - IV30702 - Stock Count Serial Lot History
    ivABCAnalysisTemp - IV50300 - ABC Analysis Temp


    The main ones being IV10300 & IV10301 if you're not using Serial # & Lots.  I've never worked with the ABC Analysis which seems to be a way in GP to 'classify' your Inventory by Usage based based on past transactions. But I'd guess that by using ABC analysis, GP would also limit the number of Items it would add to a stock count if they don't fall into the criteria s range ?
    Needless to say, but if the 'Add Item to Stock count' doesn't fulfill your request, you could always add them by using SQL scripts from the back-end.. that's what I used to do when creating large stock counts, when you have more then 20k inventory items, you don't want to do that thru the front-end as it takes way too long.
    Let us know if that helps. I'm going to dig a little more into that 'ABC Analysis' feature that I've never used before.. might actually learn something new :-)

    PS: I discovered that once you've run the ABC Analysis, GP is coding every inventory item in that 1 field (ABCCODE integer) in that 1 table (IV00101). 1=None, 2=A, 3=B, 4=C.You may want to Run the utility and then after, set anything not relevant to ABCCODE = 1.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------



  • 3.  RE: Scheduling new items for cycle counts?

    GPUG ALL STAR
    Posted 5 days ago
    @Audrey Forrest
    I remembered that Willoware does have a stock count add-on module for GP :
    https://willoware.com/modules/complete-count/

    You may want to get in touch with @Brenner Klenzman to inquire about it's functionality. It seems to extend on the ABC Analysis and offers more options. You then can print count tags with Barcodes on it that you could leverage hand held scanners if you have some in larger warehouses.
    Hope this helps.



    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------



  • 4.  RE: Scheduling new items for cycle counts?

    TOP CONTRIBUTOR
    Posted 5 days ago
    Beat has a lot of good stuff there to read.

    To answer your specific question:
    Once you Start the Count, you cannot add new Items to it.
    Once the Count is completed, then you can add new items either manually to the Stock Count Schedule ID or by using the Mass Add feature and any new items will be added to those already there and will be on the next Count you start.

    In the Item Count Cycle Assigns, I would add only by ABC, Item Class Code  and or by Warehouse Site ID. Not by Item Number. This way any new items in a Class or ABC group or Warehouse get the correct Cycle (30 days, quarterly, annually etc.) . The specific Item would only be used for exceptions to cycle.

    And your last question about the randomizing the count. I know of no way to do that.


    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------



  • 5.  RE: Scheduling new items for cycle counts?

    GPUG ALL STAR
    Posted 5 days ago
    Some good tips by Thaddeus..
    One way you could 'randomize' the items in the stock count so they don't come out in alpha order, is to sort them by Qty.. Whatever tool you use to count the items, the reports could be modified to sort the items by any other criteria than the Item itself.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------



  • 6.  RE: Scheduling new items for cycle counts?

    Posted 5 days ago
    Beat and Thaddeus - all good things here. I will try the sorting a different way to see if that helps with randomization. My main issue is not with the stock count itself, but rather with the stock count schedule. For physical counts, this is a non-issue. Just add everything to the count in that site ID and click start count.

    However, with cycle counts, the team is adding the counts based on "next count date". My understanding is that that the bulk procedure occurs with the "Stock Count Cycle Assignment" card where you can set the interval days, site ID, ABC code, etc. However, each time this is run, it starts the entire cycle over again, which is not desired. I know that the next count date and interval is set at the Item Quantities Stock Count screen from Item Quantities Maintenance. Does anyone know the stored procedure that runs for Stock Count Cycle Assignment, so I could run it in SQL and only pick up items that have an ABC code but do not have a next count date?

    ------------------------------
    Audrey Forrest
    Senior Accountant and Business Analyst
    GP 2015 R2, D365 CRM V9
    CRMUG Chapter Leader - Seattle
    ------------------------------



  • 7.  RE: Scheduling new items for cycle counts?

    TOP CONTRIBUTOR
    Posted 5 days ago
    When the item shows up in the Stock Count Schedule ID and the next Count Date is 00/00/00 this is because it does not yet have a Cycle assigned even though it does have an ABC in IV00101.

    You can find these in advance in Smartlist - Item Quantities (Table IV00102) the STKCNTINTRVL is blank (when it should be maybe 30) also the NXTCNDT will be blank.
    So find these in Smartlist first, then use Cards Item Count Cycle Assignment and pick just the new Item to assign to the 30 day cycle. You could also update in SQL IV00102.
    When a new Item is added in Cards, the Count Cycle should be assigned


    You cannot change the selection order that they are selected for counting. So if you have 360 items and a 30 day cycle then the count will be 30 per per month over one year (counting once per month). The 30 selected will be numeric-alpha so January will be the first 30 in the list of the 360 and so on each month numbers 1-9 then A- Z. Predictable as you say.
    Of course you can change the monthly count sheet sort order but this will not change the selection into the count.

    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------



  • 8.  RE: Scheduling new items for cycle counts?

    Posted 2 days ago
    Audrey,

    Since we can manually change the next count date for each item  (cards > inventory > item > goto quantities/sites > select site > stock count), you could do your randomization in excel and then use a macro to update every item.

    Macros run very fast.  It just takes a bit of effort to set one up for the first time.  And, they're fun to watch!

    I hope this helps.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 9.  RE: Scheduling new items for cycle counts?

    Posted 2 days ago
    Can I use the inventory count schedule that includes all of the items to transferring inventory between site ID's? Thank you

    ------------------------------
    Dima B.
    ------------------------------



  • 10.  RE: Scheduling new items for cycle counts?

    TOP CONTRIBUTOR
    Posted yesterday
    Audrey,

    Just adding to the thread here.  Thad provides a nice detail listing and thorough explanation of the related tables that are used for Cycle Counting/Stock Counts.  It has been my understanding that if you assign the A, B, C codes to your items (either through the wizard or via update to table IV00102 manually or via sql), populate the Stock Count Calendar with your Down Days (days the company is closed/not working) and then run the Cycle Count Assignment I recommend the following to customers:

    Set all A items to be counted every 30 days, B items counted every 60 days and C items counted every 90 days.  This way in a given Quarter you will have counted A items 3 times, B items 1.5 times and C items 1 time.  No need to conduct a full inventory count at year end under this method.  In a large organization with 50k + items this model may not work entirely and therefore you may want to adjust the incremental days accordingly.  The Cycle Count Assignment would need to be run 3 separate times to establish the interval based above (below I don't filter by Site Id but in your case you may need/want to add additional Restrictions).  In theory this method is supposed to load balance the items to be counted in that 30 day range where you don't work weekends so that if you only had about 20 items (number of usual working days in a given month), you would count 1 A item every working day of that month (obviously Feb, July, etc. may not have any items on some days).



    Now that all said if you truly want some level of random item selection my recommendation to those customers is to use sql to generate the list of random items to be counted for that day.  SQL has random functions build in such as RAND(), NewId() and TableSample.  Not sure how large your data is and/or what your random considerations need to be but below returns a random list of 10 items that are assigned a site id.

    Select top 10  * From [IV00102] Where locncode <> '' Order by newid()

    I would normally restrict the above script to only show items in specific warehouses/site ids and then use that Random result set to update the NXTCNTDT (next count date) to be today where the last count date was something greater than 90 days.  Basically you only want to count items that haven't been counted in awhile.  Assign the update/assignment to a sql job that runs every morning at 2am or some such thing and now your stock count schedules should be built off the 'Next Count Date' where the date is set for today.  It is important for the count to be started and finished on the same day, If the count isn't finished in the same day then you would need/want to add additional criteria to your Next Count Day assignment to look at stock count schedules created from the tables that Thad notes above.

    My experience with creating the count sheets is that you want to keep them small enough for someone to actually go and count and finish in a reasonable amount of time.  If you create a count sheet every day that has 1000 items and the idea is to split that up to 10 different people or more you would be better off creating 10 plus count sheets so that the counters are not overwhelmed and they will actually count vs just write numbers on the paperwork.  if it's determined that users can count 15 items in 10 or 15 minutes start with that approach as inventory and counting gets better increase the number of items to count slowly, before you know it they are counting 30-40 items daily or whatever the threshold is that works for you and them.

    Just my 2 cents.


    ------------------------------
    Wally Dodds
    Dorado Solutions
    West Chester PA
    ------------------------------



  • 11.  RE: Scheduling new items for cycle counts?

    Posted 3 hours ago
    Wow, all great ideas. We are actually not on a 30 day cycle, and since it spans such a large time frame, I did not want to use the GP assignment tool, as that "restarts the clock" so to speak. What I ended up doing was a mashup of the suggestions here.

    I used Smartconnect's SQL query to find any items that have a positive quantity in the site and a next count date of '01-01-1900'. I ran a VB script to set an outside variable 29 days forward (I didn't want something to show up on the cycle count immediately after showing up at the warehouse). Then, as the map loops through each row of the query updating IV00102, I have the outside variable increment one day each, so they don't all just land on a single day, skipping the weekends. I also have an If statement checking the ABC code and site combination to determine the increment days and then it should get on the schedule with the rest of the items.

    I don't get randomization out of this, but I think that is OK as long as all the items are getting picked up in the counts. If anyone else decides to go this route, note that you need to set the Global Variable in both the MapVariable and GlobalVariable tables to pick them up in the map.

    ------------------------------
    Audrey Forrest
    Senior Accountant and Business Analyst
    GP 2015 R2, D365 CRM V9
    CRMUG Chapter Leader - Seattle
    ------------------------------