exponential curve / growth formula

[rogerthat]

Please help! This one's a thumper and everyone (all my excel 'genius' friends) are stumped. And we may be doing this wrong but if anyone can shed light, would be extremely grateful.

We are given a few numbers to start with:

100,000 This is our "overall" goal (number of calls to be made).
20 This is the number of "week" we have to reach our overall goal.
500 This is the goal for Week 1

With those numbers, we need to come up with weekly goals for 20 weeks that total 100,000 (for example):

Week, Goal
1, 500
2, 603
3, 727 ... and so forth

The trick is that these goals "cannot" be created on a linear path but rather need to be created on a exponential curving growth path. In other words, the goals will start low and over time create a curving increase.

Step I would assume is figuring out the goals with only 100,000 total, 500 start, and 20 weeks. This is where we got lost. So I worked backward to try and see how is "should" look.

So I hard coded a percentage of 20.5682366195176% for each week and this number works perfect when applied to the next weeks goal. I.E. It is this 20.5682366195176% that we're trying to find a formula for given ONLY the numbers we have from the beginning.

For example, see list below:

500 x 20.5682366195176% = 603
603 minus 500 = 103
103 divided by 500 = 20.5682366195176%

If you plot the "goal" numbers on a graph, you get a nice exponential growth curve. As well as, if you add ALL the goals up, you get 100,000. So finding the 20.5682366195176% is the key. But is it possible???


Week, Goal, Growth, %
1, 500,
2, 603, 103, 20.5682366195176%
3, 727, 124, 20.5682366195176%
4, 876, 149, 20.5682366195176%
5, 1,057, 180, 20.5682366195176%
6, 1,274, 217, 20.5682366195176%
7, 1,536, 262, 20.5682366195176%
8, 1,852, 316, 20.5682366195176%
9, 2,233, 381, 20.5682366195176%
10, 2,692, 459, 20.5682366195176%
11, 3,246, 554, 20.5682366195176%
12, 3,913, 668, 20.5682366195176%
13, 4,718, 805, 20.5682366195176%
14, 5,689, 970, 20.5682366195176%
15, 6,859, 1,170, 20.5682366195176%
16, 8,269, 1,411, 20.5682366195176%
17, 9,970, 1,701, 20.5682366195176%
18, 12,021, 2,051, 20.5682366195176%
19, 14,493, 2,472, 20.5682366195176%
20, 17,474, 2,981, 20.5682366195176%

So if we can figure out the formula to get 20.5682366195176%, we can then apply that to create the weekly goals and thus also have our curving growth all in one.

Thanks for any help out there..

 

[circledchicken]

Hi,

One option might be to use Goal Seek. For example set your spreadsheet up as follows:

Excel Workbook
	A	B
1	Target	100,000
2	Rate	0.2056824
3
4	Week	Goal
5	1	500
6	2	603
7	3	727
8	4	876
9	5	1,057
10	6	1,274
11	7	1,536
12	8	1,852
13	9	2,233
14	10	2,692
15	11	3,246
16	12	3,913
17	13	4,718
18	14	5,689
19	15	6,859
20	16	8,269
21	17	9,970
22	18	12,021
23	19	14,493
24	20	17,474
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=SUM($B$5:$B$24)
B6	=B5*(1+B$2)
B7	=B6*(1+B$2)
B8	=B7*(1+B$2)
B9	=B8*(1+B$2)
B10	=B9*(1+B$2)
B11	=B10*(1+B$2)
B12	=B11*(1+B$2)
B13	=B12*(1+B$2)
B14	=B13*(1+B$2)
B15	=B14*(1+B$2)
B16	=B15*(1+B$2)
B17	=B16*(1+B$2)
B18	=B17*(1+B$2)
B19	=B18*(1+B$2)
B20	=B19*(1+B$2)
B21	=B20*(1+B$2)
B22	=B21*(1+B$2)
B23	=B22*(1+B$2)
B24	=B23*(1+B$2)

Then using Goal Seek Set cell B1 equal to your target value, by changing cell B2.

 

[shg]

100000/500 = 200 = ((1+rate)^20 - 1) / rate. I can't solve that for rate, but it seems like one of the financial functions should be able to be cajoled to.

 

[rogerthat]

Thanks for the reply and I see where you're going (which is correct) BUT, the "rate" you're using in b2, the (20.5682366195176%).. that is the "unknown" value we're trying to figure out. So really, I should have referred to it as the "rate" and not the growth percentage. In any case, if you were given:

100,000 as your overall goal with 20 weeks to reach that goal and 500 was your week 1 goal, how do you find the "rate of growth" for each week (the 20.5682366195176%)?

 

[rogerthat]

Thanks for the reply. Curious as to where the value of 200 comes into play? Also, I agree with you.. it's the "rate of growth" I'm after. That at least puts me on the right track. We'll research a little more on financial functions but I feel like we've literally exhausted that direction (10 hours today alone on this).

 

[circledchicken]

Thanks for the reply and I see where you're going (which is correct) BUT, the "rate" you're using in b2, the (20.5682366195176%).. that is the "unknown" value we're trying to figure out. So really, I should have referred to it as the "rate" and not the growth percentage. In any case, if you were given:

100,000 as your overall goal with 20 weeks to reach that goal and 500 was your week 1 goal, how do you find the "rate of growth" for each week (the 20.5682366195176%)?

Yes, that unknown rate is what Goal Seek figures out. Here are some links that might help if you haven't used Goal Seek before:
http://www.ozgrid.com/Excel/goal-seek.htm
http://www.youtube.com/watch?v=sPj6z8_TsAM&feature=relmfu

So for example when you use Goal Seek you choose any target e.g. lets say you wanted 200,000 this time and it would figure out the rate and all the weekly amounts would update as they are linked to the rate.

 

[shg]

Curious as to where the value of 200 comes into play?

200 is the ratio of your two givens, the total value (100000) and the starting value (500).

 

[rogerthat]

Roger that and thank you. If you happen to figure out the formula to get to 20.5682366195176% without using "goal-seek", please let me know. The end goal obviously would be for us to enter "any" total (i used 100,000, you used 200,000) and ideally, a formula would use that number and the "length of time" (i used 20 weeks), and it would kick back to us what the rate of growth should be for each weekly goal. side note: i did use the goal seek and it did give me back 20.5682366195176% but not the actual formula (which is what we're after) so hats off to you for suggesting that route.

 

[rogerthat]

Gotcha. We're literally sitting here clueless starring at this spreadsheet trying to figure out this darn formula. i'm not sure here but i think this will end up being a hard formula to figure out given that we need the ratio percentage of growth per week so that all weeks add up to the total (so we're ideally filling in a series between 500 and X to = 100,000 AND THEN each of those values returned would plot on a chart that showed a gradual increasing growth by week. This is a doozy...

 

[circledchicken]

Your welcome.

I'm assuming the formula your after is the one posted by shg - solved for the rate.

But just out of curiosity, why do you need a pure formula solution? You can vary the target/initial value/number of weeks and goal seek would solve for the rate you need.