Forecasting help
Results 1 to 4 of 4

Thread: Forecasting help

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

    Default Forecasting help

    Hi, I'm having trouble with this problem

    Construct a forecasting worksheet to calculate:

    1. the number of new clients each month, and
    2. the total client base (cumulative number of clients signed up) each month.

    In your model, make projections for 60 months (Periods) for the two scenarios below:

    Scenario 1: Constant Market - Total market potential is 1,000,000 customers. Each month you sign up2% of customers in the market that have not yet signed up.

    Scenario 2: Growing Market - Total market potential is initially 1,000,000 customers but grows at 1%per month. Each month you sign up 2% of customers in the market that have not yet signedup.

    What functions am I suppose to use

    Thank you

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Location
    Gold Coast, Queensland
    Posts
    1,113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Forecasting help

    You could try something like this for Scenario 1
    Sheet2

    ABC
    2Scenario 1
    3Start base1,000,000
    4Rate monthly sign up of residual base2%
    5WhenBudget Sign upResidual base
    6ist month20,000980,000
    72nd month19,600960,400
    83rd month19,208941,192
    94th month18,824922,368

    Spreadsheet Formulas
    CellFormula
    B6=B3*B4
    C6=B3-B6
    B7=C6*$B$4
    C7=$C6-B7
    B8=C7*$B$4
    C8=$C7-B8
    B9=C8*$B$4
    C9=$C8-B9


    Excel tables to the web >> Excel Jeanie HTML 4


    And for Scenario 2
    Sheet2

    ABC
    11Scenario 2
    12Start base1,000,000
    13Monthly growth of base1%
    14Rate monthly sign up of residual base2%
    15WhenBudget Sign upResidual base (Inc growth in base)
    161st month20,000989,800
    172nd month19,796979,704
    183rd month19,594969,711
    194th month19,394959,820

    Spreadsheet Formulas
    CellFormula
    B16=B12*B14
    C16=SUM(B12-B16)+SUM(B12-B6)*B13
    B17=C16*$B$14
    C17=SUM(C16-B17)+SUM(C16-B17)*$B$13
    B18=C17*$B$14
    C18=SUM(C17-B18)+SUM(C17-B18)*$B$13
    B19=C18*$B$14
    C19=SUM(C18-B19)+SUM(C18-B19)*$B$13


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Forecasting help

    Thank you! its just what I was looking for. Do I have to manually input those functions in each cell or is there a way to apply it through periods 1-60
    Thanks

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Location
    Gold Coast, Queensland
    Posts
    1,113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Forecasting help

    Thats good and there is a way to apply it to all cells as set out below.
    You will note that in both scenarios the formulas are different as follows
    Scenario 1
    B6 and B7
    C6 AND C7

    So enter these as on example.
    Now if you highlight cells B7 and C7 you will notice a little black box (fill handle)on bottom right of C7 . Hover your cursor over the fill handle and a crosshair will come up .
    While holding left mouse button drag the fill handle as far down as you need and it will fill the cells in columns B and C
    So far as column A is concerned you might be best to name the cells Jan 2018 , Feb 2018 etc and perform the same action as above with fill handle.
    Scenario 2.
    B16 and B17
    C16 and C17
    Enter as per example

    Perform the same actions as outlined above in Scenario 1 starting with highlighting cells B17 and C17.

    If you google “fill handle excel” you will get heaps of instructions
    Good luck

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
  •