Forward Cover in Inventory
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! |
Forward Cover in Inventory
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! |
Forward Cover in Inventory
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! |
Forward Cover in Inventory
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! |
FCI
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 |
FCI
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 |
Forward
If the monthly sales stay constant, yes. My issue is that monthly sales values vary.
|
Forward
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. |
FCI
Anyone?
|
All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com