I inherited an Inventory Spreadsheet that calculates min/max levels and is used for Forecasting. However, I'm not sure what the some of the columns are calculating. Unfortunately, I'm no excel guru - in fact I'm a beginner and I'm taking classes. Therefore I've attched a copy of the spreadsheet for review in hopes that someone can explain to me what my predecessor was calculating. The formulas arer there, I just need to explain how things are being determined. Thank you in advance!

Or if someone has a better Forecasting Inventory Spreadsheet that would be great as well.

USG - stands for usage I assume
Columns I need explained a
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)

<tippytoe wrote:
USG - stands for usage I assume
Columns I need explained a
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

[quote='joeu2004[_2_];1601597']<tippytoe wrote:
USG - stands for usage I assume
Columns I need explained a
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.

This is probably the best excel lesson I've ever had. Your information was not only informative but easy to read and understand. Thank you very much for your response and patience with my ignorance towards excel. i appreciate the time you took to explain this information as well as the thoroughness.

If there is anyone else that has input who may have inventory or forcasting skills or experience, please advise on this subject as well.

Thank you tremedously.

[quote='joeu2004[_2_];1601597']<tippytoe wrote:
USG - stands for usage I assume
Columns I need explained a
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)

I hate to ask but have you ever used the moving average analysis in excel? I am new to the function and have no idea how it would works. I have all the data, would you be able to interpret and help me make the calculations?

this link expalins how it works but its for 2003 and i'm using 2010. http://office.microsoft.com/en-us/ex...001086480.aspx
Also, this is for 2003, anyone have any idea how I would use this function in 2010?

I've attached my 6 months worth of data. If there is anything else needed, please do not hesitate to ask.

Thank in advance. Tippy