<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