• 100% Satisfaction Guarantee

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
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

### Customer Question

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.

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  John D replied 1 year ago.
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 1 year 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.

Expert:  John D replied 1 year ago.
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 1 year 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.

Expert:  John D replied 1 year ago.
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 1 year 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.

Expert:  John D replied 1 year ago.
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 1 year ago.

Hi John,

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

Thanks

Expert:  John D replied 1 year ago.
Thanks. After rating the answer you will get the bonus button. Thanks

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9659
Experience: Bachelor of Science - Engineering Consultant
Expert:  John D replied 1 year ago.

Thanks so much :)

Here you go

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

Customer: replied 1 year 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

Expert:  John D replied 1 year ago.
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 1 year 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

Expert:  John D replied 1 year ago.
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 1 year ago.

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

Expert:  John D replied 1 year ago.
You're welcome :) Glad to have been able to help

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• ### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair

### JasonJames122

#### Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

### Jess M.

#### Satisfied Customers:

301
Computer Software Specialist for more than 10 years

### The-PC-Guy

#### Satisfied Customers:

274
20 years experience providing remote computer support

125

### Chris L.

#### Satisfied Customers:

124
Certified Expert with over 10 years experience.

### IT Miro

#### Satisfied Customers:

97
Bachelor's Degree in Information Technology, Microsoft Certified Professional

## Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
John D
385 Satisfied Customers
Bachelor of Science - Engineering Consultant