Cumulative sales Forecast Formula
Results 1 to 5 of 5

Thread: Cumulative sales Forecast Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cumulative sales Forecast Formula

    I have a year end sale figure of £80,000 and want to divide this between 12 months in way that starts from Jan to Dec, and the Jan Sale not to be less than £4,000 and each month the sale increase slightly with a few fluctuations in Sep, Feb and March when there is seasonality and the sales are slow.

    The total of all 12 months should add up to £80,000. is there an formula that forecasts the sales amounts in each month for me?

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Cumulative sales Forecast Formula

    Welcome to the forum.

    This becomes too complex if you try to use formulas. This is more straightforward if you use Solver, which is an Add-in for Excel. Do you want to know how to do that?
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cumulative sales Forecast Formula

    Quote Originally Posted by DRSteele View Post
    Welcome to the forum.

    This becomes too complex if you try to use formulas. This is more straightforward if you use Solver, which is an Add-in for Excel. Do you want to know how to do that?
    No, I appreciate if you let me know how to do it step by steps .. many thanks!!!!

  4. #4
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Post Re: Cumulative sales Forecast Formula

    Huh? "No", you don't want to know how to do that?

    Let's just try this for starters.

    I created an algorithm that lays out the sales figures. There are growth factors applied to certain months (10% growth here for each of the months May, Jun, Jul, Aug, Oct, Nov, Dec); three of the other months have seasonality factors (C3,D3,E3,&J3) that reduce sales (Feb 20%, then Mar 5%; Sep 10%). You can alter those figures and then adjust B7 until the total sums to near 80,000 in N7. That should be good enough.

    A B C D E F G H I J K L M N
    3 growth/seasonality 0.8000 0.9500 1.1000 1.1000 1.1000 1.1000 1.1000 0.9000 1.1000 1.1000 1.1000
    4
    5
    6 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Year
    7 Sales 6261.59 5009.27 4758.77 5234.65 5758.11 6333.92 6967.31 7664.05 6897.64 7587.40 8346.15 9180.76 79999.62
    Sheet6

    Worksheet Formulas
    Cell Formula
    F3 =E3
    G3 =F3
    H3 =G3
    I3 =H3
    K3 =I3
    L3 =K3
    M3 =L3
    C7 =B7*C3
    D7 =C7*D3
    E7 =D7*E3
    F7 =E7*F3
    G7 =F7*G3
    H7 =G7*H3
    I7 =H7*I3
    J7 =I7*J3
    K7 =J7*K3
    L7 =K7*L3
    M7 =L7*M3
    N7 =SUM(B7:M7)
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cumulative sales Forecast Formula

    Thank you so much . This is very nice of you but can you Tell me how you created the algorithm or how you do the add-in to excel? I need to use this feature many times in the future !!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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