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.

    =IF(F2=0, H2*12/I2, (H2*2 - F2)/F2)
    =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)
    =IF(F2=0, 1, (12*H2/I2)/F2 - 1)
    =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:


    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

    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. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Backfilling monthly volume forecast based on annual forecast

    Guest, Jul 23, 2004, in forum: Microsoft Excel Worksheet Functions
    Jul 23, 2004
  2. Guest

    To project inventory rundown and forecast

    Guest, Dec 8, 2006, in forum: Microsoft Excel Worksheet Functions
    Dec 8, 2006
  3. Guest

    date calcuations

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

    Function explaination need

    Guest, Jul 26, 2007, in forum: Microsoft Excel Worksheet Functions
    Jul 26, 2007
  5. 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
    T. Valko
    Dec 8, 2007