Forecast of Inventory - Explaination of Calcuations

[tippytoe]

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

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

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

+-------------------------------------------------------------------
|Filename: Simple Inventory Forecasting v1.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=368
+-------------------------------------------------------------------

 

[joeu2004]

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