# Re: Forecast of Inventory - Explaination of Calcuations

joeu2004
Guest
Posts: n/a

 8th May 2012
<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

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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?Y2hhcmxpZQ==?= Microsoft Access 1 19th Oct 2005 08:23 PM =?Utf-8?B?QnJ1Y2UgRC4=?= Microsoft Excel Misc 8 4th Feb 2005 05:27 PM =?Utf-8?B?SmF5Skc=?= Microsoft Word Document Management 3 16th Jan 2005 05:52 PM =?Utf-8?B?RUxM?= Microsoft Access Database Table Design 1 7th Oct 2004 02:26 PM =?Utf-8?B?U3RldmU=?= Microsoft Excel Worksheet Functions 2 23rd Jul 2004 04:40 AM

Features