How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9654
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

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.


 


Thanks for your help.

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.


 


Thanks for your help.


 


Attachment: 2013-01-08_225721_partner_forecast_rev2_wip.xlsx

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


graphic

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

Uploading the file...
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9654
Experience: Bachelor of Science - Engineering Consultant
John D and 9 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 1 year ago.

Thanks so much :)

 

Here you go

 

 

Attachment: 2013-01-09_165655_2013-01-08_225721_partner_forecast_rev2_wip1.xlsx

 

 

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

JustAnswer in the News:

 
 
 
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.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...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
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

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