# Thread: Forecasting help Thanks:  2 Post #5177325 (1)Post #5177398 (1) Likes:  2 Post #5177325 (1)Post #5177398 (1)

1. ## 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. ## Re: Forecasting help

You could try something like this for Scenario 1
Sheet2

 A B C 2 Scenario 1 3 Start base 1,000,000 4 Rate monthly sign up of residual base 2% 5 When Budget Sign up Residual base 6 ist month 20,000 980,000 7 2nd month 19,600 960,400 8 3rd month 19,208 941,192 9 4th month 18,824 922,368

 Cell Formula 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

 A B C 11 Scenario 2 12 Start base 1,000,000 13 Monthly growth of base 1% 14 Rate monthly sign up of residual base 2% 15 When Budget Sign up Residual base (Inc growth in base) 16 1st month 20,000 989,800 17 2nd month 19,796 979,704 18 3rd month 19,594 969,711 19 4th month 19,394 959,820

 Cell Formula 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. ## 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. ## 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