Re: Forecast of Inventory - Explaination of Calcuations

Discussion in 'Microsoft Excel Worksheet Functions' started by joeu2004, May 8, 2012.

  1. joeu2004

    joeu2004 Guest

    <tippytoe> wrote:
    > USG - stands for usage I assume
    > Columns I need explained are:
    > M (Usg% Inc/Dec) what is being calculated in the formula
    > =IF((F2=0),((((H2*12)/I2)-F2)/1),(((H2*2)-F2)/F2))
    > N (New Inv Min) how and what is being calculated to come up with this #
    > =SUM((((K2/365)*12)*J2))*L2
    > O (New Inv Max) how and what is being calculated to come up with this #
    > =ROUNDUP(((N2*2)+0.1),0)


    Since you are just learning Excel yourself, I would like to point that these
    are good examples of how __not__ to write formulas.
    1. Needless use of parentheses makes the formulas difficult to read.
    2. SUM in this context is completely useless and unnecessary.
    3. Division by 1 is just plain silly.
    4. Subtract F2 from H2*12/I2 is also silly since we know F2 is zero.

    M:
    =IF(F2=0, H2*12/I2, (H2*2 - F2)/F2)
    N:
    =(K2/365)*12*J2*L2
    O:
    =ROUNDUP(N2*2 + 0.1, 0)

    Even my use of parentheses in the formula for column N is unnecessary. I
    use it there for clarity. I would prefer to write: =12*J2*L2*K2/365.

    Note: There are differences of opinion regarding parenthesizing expressions
    like H2*12/I2-F2. It would not be unreasonable to write (H2*12/I2)-F2,
    albeit unnecessary.

    As for an explanation, the following may or may not help.

    -----

    1. M2 should be the percentage change (increase; minus decrease) of 2012
    usage (H2) over 2011 usage (F2).

    I would use one of the following formulas (see explanation of choices
    below), in decreasing order of preference:

    =IF(F2=0, "N/A", (12*H2/I2)/F2 - 1)
    or
    =IF(F2=0, 1, (12*H2/I2)/F2 - 1)
    or
    =IF(F2=0, 12*H2/I2, (12*H2/I2)/F2 - 1)

    Read that as: If F2 is zero, return 1 (or 12*H2/I2), which is appears as
    100%. Otherwise (F2 is not zero), return (12*H2/I2)/F2 - 1, which is the
    annualized (prorated) change of 2012 usage over 2011 usage.

    If F2 is zero, the percentage change is undefined; so we return an arbitrary
    value. Some people would return the string "N/A" (not applicable). I
    rationalize that it is a "100%" increase. Others might rationalize that the
    change is the 2012 usage expressed as percentage. (If H2 were 1000, that
    would be a 100000% change!)

    If F2 is not zero, ostensibly the percentage change is H2/F2 - 1, which is
    equivalent to (H2-F2)/F2.

    But since H2 represents only I2 months, we must annualize (prorate) the
    average monthly amount. H2/I2 is the average monthly amount; so 12*H2/I2 is
    the annualized amount.

    -----

    By the way, I think the formula in G2 (2011 per month) should be =F2/12.

    And I think the formula in J2 (2012 per month) should be =H2/I2.

    Also, the title in J1 is correct.

    -----

    2. N2 should be the minimum (new) inventory.

    The formula is: =(K2/365)*12*J2*L2

    To be honest, I am not an inventory person. So I can only explain the
    arithmetic that I see. I cannot comment on its correctness or not.

    Since K2 is lead-time in days, K2/365 is the lead-time as a fraction of
    year. So (K2/365)*12 is the lead-time in months.

    Since J2 is the current average monthly usage (should be H2/I2),
    (K2/365)*12*J2 is the amount (inventory) that would be used during the
    lead-time period.

    L2 seems to be a "fudge factor" (so-called buffer), an arbitrary number. By
    multiplying inventory used during the lead-time by L2 (1.2), we are saying
    the minimum required inventory is 1.2 times the current average usage.

    -----

    Assuming that N should be a whole number, I suspect the correct formula
    should be:

    =ROUNDUP(K2/365)*12*J2*L2, 0)

    See the explanation of ROUNDUP below.

    -----

    3. O2 should be the maximum (new) inventory.

    The formula is: =ROUNDUP(N2*2 + 0.1, 0)

    Again, I cannot comment on its correctness or not. But I suspect the
    formula should be simply:

    =ROUNDUP(2*N2,0)

    This arbitrarily says that the maximum is about 2 times the minimum.
    ROUNDUP computes the integer amount greater than or equal to 2 times the
    minimum.

    I suspect the addition of 0.1 is a kludge; and it is probably incorrect.

    If 2*N2 were 2000.91, ROUNDUP(2*N2,0) would result in 2001, which is
    probably the intent.

    ROUNDUP(2*N2+0.1,0) would result in 2002. I don't know why that would be
    more desirable. But arguably, the maximum inventory is an arbitrary amount;
    it can be defined anyway you wish.

    -----

    The use of SUM in R2, S2 and T2 is unnecessary and useless. The formulas
    should be simply:

    R2: =E2*Q2
    S2: =O2*Q2
    T2: =S2-R2
     
    joeu2004, May 8, 2012
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Cindy Lou

    Forecast Function

    Cindy Lou, Jul 30, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    301
    Paul Corrado
    Jul 31, 2003
  2. Guest

    Backfilling monthly volume forecast based on annual forecast

    Guest, Jul 23, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    647
    Guest
    Jul 23, 2004
  3. Guest

    To project inventory rundown and forecast

    Guest, Dec 8, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    358
    Guest
    Dec 8, 2006
  4. Guest

    date calcuations

    Guest, May 2, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    311
    Fred Smith
    May 2, 2007
  5. Guest

    Function explaination need

    Guest, Jul 26, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    207
    Pete_UK
    Jul 26, 2007
  6. Nilay Excel 2003

    How to get Holidays in cell/s? (Find explaination in details)

    Nilay Excel 2003, Dec 7, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    237
    T. Valko
    Dec 8, 2007
  7. Mitchell

    Explaination of Logical If Then Statement

    Mitchell, Jul 18, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    211
    jayray
    Jul 19, 2008
  8. G.M.Golabhanvi

    I WANT RSWORDS FORMULAS EXPLAINATION

    G.M.Golabhanvi, Oct 21, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    1,106
    Gord Dibben
    Oct 22, 2008
Loading...