I have a Excel 2010. I have a Sales Forecast Sheet where I'm wanting the sales team to enter a monthly amount. This amount needs to be repeated across several months as we bill monthly depending on the length of the contract. For Example: the monthly amount is £100 and they forecast it to land in January and to last for 4 months. From this what I want to achieve on a separate sheet is £100 to be epopulated in a row for Jan, Feb, Mar, Apr. Does that make sense?
I'm happy to send you the forecast spreadsheet as that might make more sense.
Hi, The best place to start is on the 'Pipeline' sheet columns H,I & J
Using the spreadsheet attached (Row 4) as the example I'm wanting excel to automatically calculate counting 4 months from March 13 and then placing the 'Weighted Forecast' figure (Column J) in this example £81 in to months March, April, May & June on the 'Forecast' sheet.
Hopefully that helps.
I've attached the file but it seems to only want to except a image. Let me know if I can send it via email if you have not received it.
Thanks for your help.
Yes, the formula in J is correct.
I have attached a revised spreadsheet which shows what I'm trying to achieve automatically.
On the 'Pipeline' sheet I have 3 opportunities. One starts in March and runs for 4 months, the other in January for 12 months and the last in August for 2 months. The weighted forecast (column J) is what I want to automatically appear in the 'Forecast' sheet for those months.
I've added the values on the 'Forecast' sheet (in yellow) to show the result that I'm try to automatically achieve.
This is looking good the only thing that needs amending is it seems to be adding on an extra month in the forecast.
June 13 contract length 3 months should finish in August 13 (not September)
February 13 contract length 8 months should finish in September not October. etc
I presume if a contract starts in Dec and runs for 5 months it would show it in Dec, Jan, Feb etc of the following year?
I'm happy to add the bonus but I can't see where I'm to do it. Any ideas?
Thanks so much :)
Here you go
Hope this is ok. Please feel free to let me know if you have any questions
It could be me but I seem to be having a problem.
With the examples that in the spreadsheet the you supplied if I increase the length of the contract months it works fine. However, if I change the month then it doesn't update the Forecast sheet.
Also, if I add a new entry in the pipeline it doesn't appear in the Forecast....any ideas?
The cells in yellow are what I've changed. As you will see the forecast sheet hasn't been updated to show these changes.
Great. I'll have a good play around with it. Thanks once again!