Find out which month purchases are 1st forecast

[Rhodda]

Hi,

I’m trying to work out which month a forecast is first made.

Probably need to explain a bit more. I have a list of purchases along the left, 12 months along the top and then the various cost in the middle followed with a total at the end.

I’m trying to work out which is first month the forecast is made.

I.e. I’m purchasing hardware A costing $1000 in Mar, Jul, Sept. I need a formula which tells me automatically which is the first month of that forecast line. I then know the commitment month for the purchase of hardware A will be made in Mar. (Although I'm forecasting payments in 3 different months, its actually one purchase).

I imagine I’ll have a column next to the totals column which tells me the month of first forecast for each purchase.

Hopefully that makes sense, and can be done in Excel.

Thanks.

 

[jim may]

Enter as Cont+Shift+Enter, not just enter...
C1 is first Month Header; Row 2 (c2:K2 (this example only thru Sept)) is your 1st dat row

=OFFSET(C1, ,MATCH(FALSE,ISBLANK(C2:K2),0)-1)

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">Jan</td><td style="text-align: center;;">Feb</td><td style="text-align: center;;">Mar</td><td style="text-align: center;;">Apr</td><td style="text-align: center;;">May</td><td style="text-align: center;;">Jun</td><td style="text-align: center;;">Jul</td><td style="text-align: center;;">Aug</td><td style="text-align: center;;">Sep</td><td style="text-align: center;;">Oct</td><td style="text-align: center;;">Nov</td><td style="text-align: center;;">Dec</td><td style="text-align: center;;">Total</td><td style="text-align: center;;">1st Month</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Hardware A</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">$1,000.00</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">$1,000.00</td><td style="text-align: center;;"></td><td style="text-align: center;;">$1,000.00</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">$3,000.00</td><td style="text-align: center;background-color: #FFFF99;;">Mar</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">{=IF(<font color="Blue">COUNT(<font color="Red">B2:M2</font>)=0,"",INDEX(<font color="Red">$B$1:$M$1,1,MATCH(<font color="Green">TRUE,B2:M2>0,0</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Akashwani]

Hi,

Edit:
Beaten to it, but here's my attempt anyway....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	*	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	First Month
2	Product1	*	1,000	*	*	*	*	*	*	*	*	*	*	1,000	Feb
3	Product2	*	*	*	1,000	*	*	*	*	*	*	*	*	1,000	Apr
4	Product3	*	*	1,000	*	*	*	*	*	*	*	*	*	1,000	Mar
5	Product4	*	*	*	*	*	1,000	*	*	*	*	*	*	1,000	Jun
6	Product5	*	500	*	*	*	*	*	*	*	*	*	*	500	Feb
7	Product6	*	*	*	500	*	*	*	*	*	*	*	*	500	Apr
8	Product7	*	*	*	*	1,000	*	*	*	*	*	*	*	1,000	May
9	Product8	*	1,000	*	*	*	*	*	*	*	*	*	*	1,000	Feb
Sheet2

The formulas in N2 & O2 need to be copied down.

Ak

 

[Rhodda]

Thanks guys, very helpful.

One last thing I need to do is take 2 months off that initial month. I.e. the order will be made on average 2 months before the first forecast date. Could you help me expand the formula to take this into account?

 

[AlphaFrog]

Do you have actual dates in row 1 (the forecast dates)?

If not, and the 1st forecast is in say Jan, then 2 months previous would be Nov. Do you want to return Nov 2010 to distinguished it from Nov of this year?

 

[Rhodda]

The months along the top currently use mmm-yy. So if first forecast was Jan-11, I'd hope to track that the order would have been made in in Nov-10.

Thanks.

 

[Akashwani]

Hi,

does this work for you?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	*	Jan-11	Feb-11	Mar-11	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Total	Forecast Date	Order Date
2	Product1	*	1,000	*	*	*	*	*	*	*	*	*	*	1,000	Feb-11	Nov-10
3	Product2	*	*	*	1,000	*	*	*	*	*	*	*	*	1,000	Apr-11	Jan-11
4	Product3	*	*	1,000	*	*	*	*	*	*	*	*	*	1,000	Mar-11	Dec-10
5	Product4	*	*	*	*	*	1,000	*	*	*	*	*	*	1,000	Jun-11	Mar-11
6	Product5	*	500	*	*	*	*	*	*	*	*	*	*	500	Feb-11	Nov-10
7	Product6	*	*	*	500	*	*	*	*	*	*	*	*	500	Apr-11	Jan-11
8	Product7	*	*	*	*	1,000	*	*	*	*	*	*	*	1,000	May-11	Mar-11
9	Product8	*	1,000	*	*	*	*	*	*	*	*	*	*	1,000	Feb-11	Nov-10
Sheet2

Ak

 

[Rhodda]

Fantastic, thats what I've been after.

Should I want just one output for the order date, is there a way to combine the two?

 

[AlphaFrog]

If you're not concerned about the day of month (only mmm-yy) a simple method would be to use the EOMONTH function. It returns the date that is last day of the month for a given date. But it also can offset from a given date n months.

O2
{=IF(COUNT(B2:M2)=0,"",EOMONTH(INDEX($B$1:$M$1,1,MATCH(TRUE,B2:M2>0,0)),-2))}

The above returns the last day of the month. Below returns 1st day of the month.

O2
{=IF(COUNT(B2:M2)=0,"",EOMONTH(INDEX($B$1:$M$1,1,MATCH(TRUE,B2:M2>0,0)),-3)+1)}