Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Forward
If the monthly sales stay constant, yes. My issue is that monthly sales values vary.
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FCI
Anyone?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forward looking forecast/cover help required | Excel Worksheet Functions | |||
HELP! Single cell formula to calculate weeks cover of stock on forward sales. | Excel Worksheet Functions | |||
how to cover the formula | Excel Worksheet Functions | |||
How can i get an inventory list that adds and subtracts inventory | Excel Discussion (Misc queries) | |||
cover #s to text | Excel Worksheet Functions |