• 100% Satisfaction Guarantee
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now

# I have a Excel 2010.I have a Sales Forecast Sheet where

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.

Please attach your file using the paperclip so I can have a look and let you know if I'd be able to help with it under this question
Customer: replied 4 years ago.

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.

Attachment: 2013-01-08_212730_partner_forecast_wip.xlsx

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.

You already have formulas in column J. Are they incorrect?

So you want to add in the forecast sheet the values starting with the month stated in cell H4 for the number of months stated in cell I4, correct?
Customer: replied 4 years ago.

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.

Ok, finished writing the formulas in all the table

Here is what the result look like for the values shown in the red frame

Let me know if that's ok
Customer: replied 4 years ago.

Hi John,

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?

Many thanks.

Yes can exclude the extra month, and yes it will show next year provided the that year has been set up on the forecast table. Will finalize and send it shortly, In the meantime don't forget to add bonus as per the price adjustment report

Thanks
Customer: replied 4 years ago.

Hi John,

I'm happy to add the bonus but I can't see where I'm to do it. Any ideas?

Thanks

Thanks. After rating the answer you will get the bonus button. Thanks

Thanks so much :)

Here you go

Hope this is ok. Please feel free to let me know if you have any questions

Customer: replied 4 years ago.

Hi John,

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?

Cheers

Hi again

I am not sure where you're changing the month and what you are changing it to. Could you please upload the file that has the changed month and let me know which cell that change is in
Customer: replied 4 years ago.

Hi John,

The cells in yellow are what I've changed. As you will see the forecast sheet hasn't been updated to show these changes.

Thanks

Attachment: 2013-01-09_175606_partner_forecast_rev3.xlsx

I see.

The problem is you're not typing the date as real date but as text which Excel cannot understand it as date. Just type the date in cell H7 as date, i.e. 1-Feb-2013 and everything should be ok

Hope this helps
Customer: replied 4 years ago.

Great. I'll have a good play around with it. Thanks once again!

You're welcome :) Glad to have been able to help