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]

That last one didn't seem to work.

I'll try and explain a little better. I want the formula on R to work the same, except in the following case:

Its Jun-11 and I'm looking at my non-committed order profile. If the month in R is before I Jun-11 I need a formula that recognises that I can't forecast an order for a month thats already been and pushes that order date to the current month (jun) for that occasion.

This might be something like travel expenses where the lead time isn't likely to be as long as the usual 2 months.

So if there was a forecast order date for May-11 in R, I'd like that to be changed to Jun-11.

Hope that makes more sense.

 

[Akashwani]

Hi Rhodda,

Does this work in R8?...

=IF(MONTH(Q8)<=MONTH(TODAY()),TODAY(),IF(Q8="","",DATE(YEAR(Q8),MONTH(Q8)-3,DAY(Q8))))

Ak

 

[Rhodda]

Hi Ak,

That last formula doesn't seem to work. Works fine for line 9 - forecast Jun-11 and keeps the order date as Jun-11.

However, where the forecast date is Jul-11 on line 10, the order month appears as May-11.

I think I might be asking a lot of Excel.

 

[Rhodda]

Thought I'd also add something extra...something that got raised today by the guys I work with.

Although it was previously agreed that a 2 month lead time on non-committed purchases would work, I've been told that this is too general.

I'm now been asked for a column after column A which would contain a lead time (1-12 months).

I.e. An input in the new column would mean...a 4 in B10 would mean the order is place 4 months earlier to the forecast month in Q10, and the month in R10 would be May-11. (bad example as I also want anything with a would be order date before the current month included in the current month).

Not the easiest thing to explain.

 

[Akashwani]

Hi Rhodda,

What should be the result on line 10?

Ak