+ Reply to Thread
Results 1 to 9 of 9

Forecasting Sales in Excel - Help Needed

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    CO
    MS-Off Ver
    ms office 2007
    Posts
    4

    Forecasting Sales in Excel - Help Needed

    Task: I need to create projected sales forecast for each of the remaining months of the 2015

    I am trying to create a formula that sums the current months totals and all those that precede it in Column B. The formula below shows the forecast in its simplest form. *The once catch is that the cells B5 through B12 must refer to the prospective values in prior year (Column C) because there is nothing to total yet.

    formula: (sum=B1+B2+B3+B4+C5+C6+C7+C8+C9+C10+C11+C12)



    --A------B-----C
    Month 2015 2014

    1 Jan. 900 925
    2 Feb. 950 925
    3 Mar. 975 950
    4 Apr. 950 975
    5 May --- 900
    6 Jun. --- 800
    7 Jul. --- 700
    8 Aug. --- 750
    9 Sep. --- 800
    10 Oct. --- 700
    11 Nov. --- 800
    12 Dec. --- 800

    TOTAL $3,775 $10,025
    Last edited by bcollins124; 05-20-2015 at 11:16 PM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Forecasting Sales in Excel - Help Needed

    Quote Originally Posted by bcollins124 View Post
    I am trying to create an excel formula that will automatically sum current 2015 year-to-date figures in column b as well as projected year figures for the remaining year (the projected figures will come from the 2014 actual figures in column c). [....] I do not want to use this basic formula:
    (sum=b1+b2+b3+b4+c5+c6+c7+c8+c9+c10+c11+c12)
    Try:
    Please Login or Register  to view this content.
    PS.... I assume your data are in rows 2:13, not 1:12 as you indicated. Row 1 seems to be for column titles.
    Last edited by joeu2004; 05-20-2015 at 11:02 PM. Reason: PS

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Forecasting Sales in Excel - Help Needed

    Perhaps this...
    A
    B
    C
    1
    Month
    2015
    2014*
    2
    Jan.
    900
    925
    3
    Feb.
    950
    925
    4
    Mar.
    975
    950
    5
    Apr.
    950
    975
    6
    May
    900
    7
    Jun.
    800
    8
    Jul.
    700
    9
    Aug.
    750
    10
    Sep.
    800
    11
    Oct.
    700
    12
    Nov.
    800
    13
    Dec.
    800
    14
    15
    TOTAL $3,775 $10,025
    10025

    B15=SUM(B2:B13,SUMIF(B2:B13,"",C2:C13))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    05-20-2015
    Location
    CO
    MS-Off Ver
    ms office 2007
    Posts
    4

    Re: Forecasting Sales in Excel - Help Needed

    Thank you for the helpful ideas but based off of the figures given in the example the annual forecast should be:

    $3,775 (2015 year to date figures (Jan-Apr)
    + $10,025 (projected year 2014 May-Dec)
    Grand Total = $14,000 (2015 projected forecast)


    Any Idea on how to get to $14,000 without having to manually sum 2015 Jan-April and 2014 May-Dec?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Forecasting Sales in Excel - Help Needed

    $3,775 (2015 year to date figures (Jan-Apr)
    + $10,025 (projected year 2014 May-Dec)
    Grand Total = $14,000 (2015 projected forecast)
    M-D 2014 is only 6250, not 10 025?

  6. #6
    Registered User
    Join Date
    05-20-2015
    Location
    CO
    MS-Off Ver
    ms office 2007
    Posts
    4

    Re: Forecasting Sales in Excel - Help Needed

    I believe the IMSUM function would also work in this case - here is the Formula =IMSUM(B1:B12,C5:C12)

    My only question is, in two weeks I am going to have to enter the sales results for May in cell B5 but I don't want to the formula to sum the sales in cell C5 - how can I make it so I don't have to adjust the formula range each month?

    * In May, the formula should sum cells B1 through B5 and C6 through C12

    -A------B-----C
    -Month-2015-2014

    1 Jan. 900 925
    2 Feb. 950 925
    3 Mar. 975 950
    4 Apr. 950 975
    5 May --- 900
    6 Jun. --- 800
    7 Jul. --- 700
    8 Aug. --- 750
    9 Sep. --- 800
    10 Oct. --- 700
    11 Nov. --- 800
    12 Dec. --- 800

    TOTAL $3,775 $10,025

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Forecasting Sales in Excel - Help Needed

    Did you test my question regarding...
    M-D 2014 is only 6250, not 10 025?

  8. #8
    Registered User
    Join Date
    05-20-2015
    Location
    CO
    MS-Off Ver
    ms office 2007
    Posts
    4

    Re: Forecasting Sales in Excel - Help Needed

    Yes, I did but for some reason the total amount was not correct ( I also checked it with a different sets of numbers)
    it appeared to be pulling the information from cells B1 through B4 and B13 only

    Any suggestions?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Forecasting Sales in Excel - Help Needed

    So are you saying that 10 025 is correct?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help with vehicle sales forecasting with Excel
    By annuarj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-15-2006, 10:45 PM
  2. [SOLVED] Help with vehicle sales forecasting with Excel
    By annuarj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-15-2006, 10:40 PM
  3. Forecasting Sales
    By tojo107 in forum Excel General
    Replies: 1
    Last Post: 07-29-2005, 06:23 PM
  4. Replies: 7
    Last Post: 04-28-2005, 01:06 AM

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