+ Reply to Thread
Results 1 to 4 of 4

Line Graph (Actual plus forecast data)

  1. #1
    Registered User
    Join Date
    07-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Line Graph (Actual plus forecast data)

    Hi,

    I would like to plot one line graph in Excel 2010 with the following sets of data (Set 1-6).
    Quarter as x-axis AND Actual/Forecast Sales as y-axis

    Set 1 (one line for actual)


    QUARTER Actual Sales
    Q1 2007 500
    Q2 2007 600
    Q3 2007 560
    Q4 2007 650
    Q1 2008 600
    Q2 2008 575
    Q3 2008 595
    Q4 2008 505
    Q1 2009 615
    Q2 2009 630
    Q3 2009 625
    Q4 2009 500
    Q1 2010 495
    Q2 2010 625
    Q3 2010 565
    Q4 2010 600
    Q1 2011 605
    Q2 2011 625
    Q3 2011 555
    Q4 2011 665
    Q1 2012 495
    Q2 2012 525


    Forecast (Sets 2-5) – 4 graph lines for forecast data


    Set2
    QUARTER Forecast Sales 1
    Q3 2012 522
    Q4 2012 524
    Q1 2013 527
    Q2 2013 500
    Q3 2013 568
    Q4 2013 537
    Q1 2014 588
    Q2 2014 601
    Q3 2014 600
    Q4 2014 590



    Set3
    QUARTER Forecast Sales 2
    Q3 2012 526
    Q4 2012 599
    Q1 2013 527
    Q2 2013 528
    Q3 2013 530
    Q4 2013 522
    Q1 2014 521
    Q2 2014 519
    Q3 2014 519
    Q4 2014 518



    Set4
    QUARTER Forecast Sales 3
    Q3 2012 615
    Q4 2012 630
    Q1 2013 635
    Q2 2013 640
    Q3 2013 650
    Q4 2013 664
    Q1 2014 570
    Q2 2014 589
    Q3 2014 620
    Q4 2014 635



    Set5
    QUARTER Forecast Sales 4
    Q3 2012 510
    Q4 2012 590
    Q1 2013 515
    Q2 2013 619
    Q3 2013 662
    Q4 2013 700
    Q1 2014 599
    Q2 2014 499
    Q3 2014 533
    Q4 2014 577



    I tried to add a new series for each forecast set (2-5) one at a time, but it does not allow me to extend the x-axis to the forecast periods.

    I tried using the scatter plot graphs as an alternative, but it appears that it doesn’t work for non-numerical x-axis variables.

    Could anyone please help…

    Thanks,

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,878

    Re: Line Graph (Actual plus forecast data)

    but it appears that it doesn’t work for non-numerical x-axis variables.
    This is true. My solution would be to figure out some way to make the x data numeric. Maybe x = number of quarters since Jan. 2007. This scheme makes Q1 2007 = 1, Q1 2008 = 5, Q1 2009 = 9, and so on to Q2 2012 = 22.

  3. #3
    Registered User
    Join Date
    07-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Line Graph (Actual plus forecast data)

    Hi MrShorty,

    Thanks for a quick answer, Just to be on the same page, you would use the numerical convention for the scatter plot graph not line graph, correct.

    Thanks,

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,878

    Re: Line Graph (Actual plus forecast data)

    Yes, I would use a scatter plot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1