+ Reply to Thread
Results 1 to 6 of 6

How to avoid double-counting inventory after it has been accounted for?

  1. #1
    Registered User
    Join Date
    04-05-2014
    Location
    Portland
    MS-Off Ver
    Excel 2013
    Posts
    3

    How to avoid double-counting inventory after it has been accounted for?

    Hi folks,

    This is a pretty simple add/subtract problem, but I'm not familiar with advanced Excel formulas, so wanted to get some tips. I'm trying to generate an inventory outlook/forecast which will show inventory levels for the Monday of every week for 10 weeks. I start with an on-hand stock. Sometime during the course of those 10 weeks there will be an inventory replenishment that will come in. The inventory outlook is generated by simply taking on-hand inventory + replenishment - depletion. I want to make sure that when I account for inventory replenishment on a certain date, I'm counting it only once on the Monday that will follow immediately, and not on subsequent Mondays. I can only think of something like IF(Date>Replenishment Date,Add Replenishment amount,0) type thing, but that would double count on all following Mondays.

    I've attached a screen shot with an example. In this example, I'm starting with 50,000 units of inventory. I have the depletion outlook provided by date. On 4/24/2014 I get a replenishment of 25,000 units (which would therefore be accounted for on 4/28/2014, and not beyond that date). Any ideas would be appreciated.

    Thanks!
    Attached Images Attached Images

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to avoid double-counting inventory after it has been accounted for?

    Hi and welcome ot the forum

    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-05-2014
    Location
    Portland
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: How to avoid double-counting inventory after it has been accounted for?

    Hi, thanks for the feedback. That definitely makes sense. I've uploaded a sample workbook with an illustrative example. I've hand-typed the formulas in the "Inventory Outlook" cells for my expected outcome, but I was hoping to have a logic-based intelligent formula that can do the same thing.

    Thanks!
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to avoid double-counting inventory after it has been accounted for?

    I would swaing your table around (see below), and then use this in E3, copied down...
    =E2-C3+D3

    A
    B
    C
    D
    E
    1
    Date
    Sku
    Qty Out
    Qty In
    balance
    2
    4/1/2014
    1
    50000
    50000
    3
    4/7/2014
    1
    12000
    38000
    4
    4/14/2014
    1
    11000
    27000
    5
    4/21/2014
    1
    0
    27000
    6
    4/24/2014
    1
    25000
    52000
    7
    4/28/2014
    1
    14000
    38000
    8
    5/5/2014
    1
    0
    38000
    9
    5/12/2014
    1
    12000
    26000
    10
    5/19/2014
    1
    9000
    17000
    11
    5/24/2014
    1
    25000
    42000
    12
    5/26/2014
    1
    0
    42000
    13
    6/2/2014
    1
    5000
    37000
    14
    6/9/2014
    1
    10000
    27000


    For E2, just use...
    =D2

  5. #5
    Registered User
    Join Date
    04-05-2014
    Location
    Portland
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: How to avoid double-counting inventory after it has been accounted for?

    That would work great, but the problem I will have is that there would be several lines of SKUs. My example just had 1 (should have put in a couple more), but in reality there would be about 200 rows of SKUs. So a logic-based formula would probably be required so that we can see the inventory outlook vs. SKU number as a grid. Does that make sense? If not, I could upload another example.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to avoid double-counting inventory after it has been accounted for?

    =SUMIF($B$2:B2,B2,$D$2:D2)-SUMIF($B$2:B2,B2,$C$2:C2)
    OK so change it to this...
    =SUMIF($B$2:B2,B2,$D$2:D2)-SUMIF($B$2:B2,B2,$C$2:C2)

    A
    B
    C
    D
    E
    1
    Date
    Sku
    Qty Out
    Qty In
    2
    4/1/2014
    1
    50000
    50000
    3
    4/7/2014
    1
    12000
    38000
    4
    4/14/2014
    1
    11000
    27000
    5
    4/21/2014
    1
    0
    27000
    6
    4/24/2014
    1
    25000
    52000
    7
    4/24/2014
    2
    200
    200
    8
    4/28/2014
    1
    14000
    38000
    9
    5/5/2014
    1
    0
    38000
    10
    5/12/2014
    2
    50
    150
    11
    5/12/2014
    1
    12000
    26000
    12
    5/19/2014
    1
    9000
    17000
    13
    5/24/2014
    1
    25000
    42000
    14
    5/26/2014
    1
    0
    42000
    15
    6/2/2014
    1
    5000
    37000
    16
    6/9/2014
    1
    10000
    27000


    You could create a small table that will give you a running total of all SKU's if you want, then just have A:D as data entry tables

    I
    J
    2
    SKU
    Balance
    3
    1
    27000
    4
    2
    150


    J3=SUMIF($B$2:$B$16,I3,$D$2:$D$16)-SUMIF($B$2:$B$16,I3,$C$2:$C$16) copied down

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] fill cell data after criteria from two lists are accounted for- from table
    By vtwinsport in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-16-2013, 09:19 AM
  2. [SOLVED] how to avoid null/double click
    By var in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  3. Avoid counting rows/columns with formula but no values
    By magnuc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2006, 06:30 AM
  4. How to avoid counting blanks in a list
    By Frank in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-03-2005, 09:05 AM
  5. inventory counting
    By AnimationMan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2005, 07:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1