Hi everybody,

I am stuck in finding a smooth way of calculating forward covers in
inventory figures. Let me explain:
I have Monthly Inventories in column A, Sales in column B and Forward Cover
in column C
The inventory in A2 is A1 - B1; A3 = A2-B2;...
The Forward Cover should indicate at each month the months of Inventories I
have left until I turn negative...

Can someone please help?`

Thank you!

Hi,

Could you post an example please.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"neocuproine" wrote in message
...
Hi everybody,

I am stuck in finding a smooth way of calculating forward covers in
inventory figures. Let me explain:
I have Monthly Inventories in column A, Sales in column B and Forward
Cover
in column C
The inventory in A2 is A1 - B1; A3 = A2-B2;...
The Forward Cover should indicate at each month the months of Inventories
I
have left until I turn negative...

Can someone please help?`

Thank you!

Hi,

Here's the example:
Month Inv Sales FC
Jan 350 50 3.5
Feb 300 100 2.5
Mar 200 150 1.5
Apr 50 100 0.5
May -50

Hope it is clear!

Thanks.



"Ashish Mathur" wrote:

Hi,

Could you post an example please.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"neocuproine" wrote in message
...
Hi everybody,

I am stuck in finding a smooth way of calculating forward covers in
inventory figures. Let me explain:
I have Monthly Inventories in column A, Sales in column B and Forward
Cover
in column C
The inventory in A2 is A1 - B1; A3 = A2-B2;...
The Forward Cover should indicate at each month the months of Inventories
I
have left until I turn negative...

Can someone please help?`

Thank you!

Hi

The problem here is we don't know the logic your company uses to calculate
forward cover. Suppose you assume a month requires 100 units then in the FC
column you would enter =INV/100 or in your example =A1/100. On the other
hand if you calculate that number after sales it would be =(A1-B1)/100.

But again the logic of your calculation is based on business decisions, not
on math. Once we know the logic we can set up the math for you.


If this helps click the Yes button.
--
Thanks,
Shane Devenshire


"neocuproine" wrote:

Hi,

Here's the example:
Month Inv Sales FC
Jan 350 50 3.5
Feb 300 100 2.5
Mar 200 150 1.5
Apr 50 100 0.5
May -50

Hope it is clear!

Thanks.



"Ashish Mathur" wrote:

Hi,

Could you post an example please.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"neocuproine" wrote in message
...
Hi everybody,

I am stuck in finding a smooth way of calculating forward covers in
inventory figures. Let me explain:
I have Monthly Inventories in column A, Sales in column B and Forward
Cover
in column C
The inventory in A2 is A1 - B1; A3 = A2-B2;...
The Forward Cover should indicate at each month the months of Inventories
I
have left until I turn negative...

Can someone please help?`

Thank you!

I'm in somewhat the same boat

If given his values, my company would apply the logic that demand per month taken from initial inventory results in FC.

So given 100 units in sales per month, you'd need to take your starting inventory from Month A (350), subtract 100, then move another month out, etc..until you hit a potential negative. In this case the return we'd be looking for would be 3.5 months on hand, but the formula escapes me as well.

350-100 = 1 mo
250-100 = 2 mo
150-100 = 3 mo
50 -100 = 0.5 mo

Giving a total of 3.5 months

And isn't that just starting amount (350) divided by monthly sales
(100) ?

Pete

On Nov 7, 12:51*am, Bryan V wrote:
I'm in somewhat the same boat

If given his values, my company would apply the logic that demand per month taken from initial inventory results in FC.

So given 100 units in sales per month, you'd need to take your starting inventory from Month A (350), subtract 100, then move another month out, etc...until you hit a potential negative. *In this case the return we'd be looking for would be 3.5 months on hand, but the formula escapes me as well.

350-100 = 1 mo
250-100 = 2 mo
150-100 = 3 mo
50 -100 = 0.5 mo

Giving a total of 3.5 months

If the monthly sales stay constant, yes. My issue is that monthly sales values vary.

Well, your example didn't exactly show that !!

If you have variable (but known) monthly sales figures, you could put
these in a table somewhere and use VLOOKUP or a SUMPRODUCT formula.

Give a more realistic example of your data and what you want to
achieve.

Pete

On Nov 10, 6:02*pm, Bryan V wrote:
If the monthly sales stay constant, yes. *My issue is that monthly sales values vary.

Anyone?