Forecasting help

computer120

New Member
Joined
Nov 12, 2018
Messages
2
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,165
Office Version
2016
Platform
Windows
You could try something like this for Scenario 1
Excel Workbook
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
Sheet2



And for Scenario 2
Excel Workbook
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
Sheet2
 

computer120

New Member
Joined
Nov 12, 2018
Messages
2
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
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,165
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,081
Messages
5,484,562
Members
407,454
Latest member
moomen1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top