Forward Cover in Inventory

[neocuproine]

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!

 

[Ashish Mathur]

Hi,

Could you post an example please.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[neocuproine]

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.

 

[ShaneDevenshire]

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.

 

[Bryan V]

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

 

[Pete_UK]

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

Pete

 

[Pete_UK]

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