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.

 

[Rhodda]

Hi,

Just realised there is something slightly different with this. I only need to work out the order month for those orders that haven't been ordered (I'm trying to build a profile of orders not yet made).

The committed profile is made up of order date and order amount. Hence, I need to make sure these lines aren't counted in the same way. Is it possible to use some kind of IF formula?

I'm trying to get something like this:

I can't seem to paste the pic.

Basically the table is the same as before with an extra 2 columns (order date, and order total). These are used when orders I actually placed. I don’t want to track these amounts, as I track these already using that info.

Is there a way of IF formula on these columns are used, so that they aren’t calculated along with the non-committed lines.

Hope that makes sense.

-- removed inline image ---

 

[Rhodda]

http://cid-d36dae4e17aedc4c.office.live.com/self.aspx/Excel/What%20I%20want.jpeg

tried to upload my pic. Its online on a host site. Can't seem to embed the image.

Link doesn't seem to work - you'll need to copy and paste into address bar. Sorry, I know its a pain.

 

[Akashwani]

Hi,

In your snapshot I don't see an extra 2 columns (order date, and order total), you have Reg Date & Committed Date with a quantity in Committed date rather than a date.

Is this how your data is laid out?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	*	*	*	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Total	*	*
2	Committed Profiles	*	*	*	*	100000	*	*	*	40000	*	*	*	*	*	*	*	*
3	Committed	*	*	14000	9000	*	3000	*	*	*	*	*	*	*	*	*	*	*
4	Non Committed	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	Spend Profile	Reg Date	Committed Date	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Total	Forecast Date	Order Date
8	ProductA	4 Jun 2011	100000	*	*	*	20000	*	20000	*	20000	*	20000	*	20000	100000	Jul-11	May-11
9	ProductB	*	*	*	*	5000	5000	*	*	*	*	*	*	*	*	10000	Jun-11	Mar-11
10	ProductC	*	*	*	*	*	3000	3000	*	*	*	3000	*	*	*	9000	Jul-11	May-11
11	ProductD	8 Oct 2011	40000	*	*	*	*	*	*	*	*	20000	*	20000	*	40000	Dec-11	Oct-11
12	ProductE	*	*	*	*	*	*	*	1000	1000	*	*	1000	*	*	3000	Sep-11	Jun-11
13	ProductF	*	*	*	*	2000	*	*	*	*	2000	*	*	*	*	4000	Jun-11	Mar-11
Sheet2

If so, should the formula in Q8 return Sept 11 instead of July 11 and Q11 return Feb 12 instead of Dec 11?

It is unclear to me what you want to include/exclude.
I suggest you download this....
http://www.excel-jeanie-html.de/index.php?f=1
you can then post your data on here.

Ak

 

[Rhodda]

Hi,

Thanks for posting my pic to the site.

I'm trying to build up a committed (actually ordered) and a non-committed profile for the various different products.

The committed profile is easy enough as I have an order date and order total that is filled in B and C.

As non-committed items haven't yet been ordered I don't have this information. Therefore I need to base the profile on the first forecast date and then take in back two months (average lead time) I.e. Product A is forecasting 5k in Jun-11, 5k in Oct-11. My forecast non-commitment would be 10k in Apr-11. Hope that makes sense.

I did think this was all worked out with the previous formula provided, but I then realised that I would be counting the committed orders (they need to stay on the same sheet).

Therefore is there a way to do the previous formula only if the line doesn't have a committed date in 'C'.

I imagine this is all sounding rather complex, but I hope you guys can help.

 

[Rhodda]

Forgot to mention in that last post.

If an order has been committed (i.e. B and C are populated) I don't want a forecast or order date in Q and R to appear.

 

[Akashwani]

Is this how Q & R should look?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	*	*	*	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Total	*	*
2	Committed Profiles	*	*	*	*	100000	*	*	*	40000	*	*	*	*	*	*	*	*
3	Committed	*	*	14000	9000	*	3000	*	*	*	*	*	*	*	*	*	*	*
4	Non Committed	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	Spend Profile	Reg Date	Committed Date	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12	Feb-12	Mar-12	Total	Forecast Date	Order Date
8	ProductA	4 Jun 2011	100000	*	*	*	20000	*	20000	*	20000	*	20000	*	20000	100000	*	*
9	ProductB	*	*	*	*	5000	5000	*	*	*	*	*	*	*	*	10000	Jun-11	Mar-11
10	ProductC	*	*	*	*	*	3000	3000	*	*	*	3000	*	*	*	9000	Jul-11	May-11
11	ProductD	8 Oct 2011	40000	*	*	*	*	*	*	*	*	20000	*	20000	*	40000	*	*
12	ProductE	*	*	*	*	*	*	*	1000	1000	*	*	1000	*	*	3000	Sep-11	Jun-11
13	ProductF	*	*	*	*	2000	*	*	*	*	2000	*	*	*	*	4000	Jun-11	Mar-11
Sheet2

Ak

 

[Rhodda]

Yes thats how I'd like it to look.

Thank you.

 

[Akashwani]

You are welcome Rhodda.

Good luck with your project.
If you have any more Excel question you know where to ask, but you should check out these links for some great videos...

http://www.youtube.com/user/ExcelIsFun#g/u

http://www.youtube.com/user/bjele123

Just type something in the search box that is relevant to your problem...
Named Range, Vlookup, Pivot Table, Countif, Index, Match

Ak

 

[Rhodda]

Hi Akashwani,

I imagine you think I'm moving the goal posts, but I have a slight change required.

If the non-commitment month comes out as before the current month, I'd like to move that forecast non-commitment to the current month. I.e. I can't forecast an order month thats already past.

Some orders do seem to have less than 2 months lead, so this would come up from time to time.

Sorry to be a pain. Is this possible?

 

[Akashwani]

Hi Rhodda,

I'm not sure that I fully understand what you require, sorry.
Is this the formula you need in Q8 and copied down?...

=IF(MONTH(B8)=MONTH(TODAY()),TODAY(),IF(B8<>"","",INDEX($D$7:$O$7,MATCH(TRUE,INDEX(D8:O8<>"",1,0),0))))

Ak