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..

 

[rogerthat]

Thank YOU!! It worked like a charm! I ended up bringing in our in-house techies to look at your macro and they all said "Wow, who ever wrote this macro is a genius".. All credits were given to you my friend. Again, thank you and Circled Chicken for all the time and energy you spent. we're off and running now..

 

[shg]

You're welcome, good luck.

A little clean up:

Function GeoRatio(ScaleFactor As Double, Sum As Double, nTerms As Long) As Double
    Dim x           As Double
    Dim dMin        As Double
    Dim dMax        As Double
 
    ' shg 2012
    ' UDF or VBA
 
    ' The geometric series having scale a, ratio r<>1, and n terms has the sum
    ' a + a*r + a*r^2 + a*r^3 + ... + a*r^(n-1) = a*(1-r^n)/(1-r)
 
    ' This function returns the ratio r (limited to series with r>=0, and
    ' accommodates r=1), given a, n, and the sum
 
    ' Returns 0 if:
    '   o   ScaleFactor = 0
    '   o   Sum <= ScaleFactor
    '   o   nTerms < 2
 
    ' This could be modified to support r < 0
 
    If ScaleFactor = 0 Or _
       Sum <= ScaleFactor Or _
       nTerms < 2 Then Exit Function
    x = Sum / ScaleFactor
 
    Select Case Sgn(x - nTerms)
        Case -1
            dMin = 0
            dMax = 1
        Case 1
            dMin = 1
            dMax = Sgn(x) * Abs(x) ^ (1 / (nTerms - 1))
        Case Else    ' (0)
            GeoRatio = 1
            Exit Function
    End Select
 
    GeoRatio = (dMax + dMin) / 2
 
    Do While SearchIndex(dMin, GeoRatio, dMax, _
                         (1 - GeoRatio ^ nTerms) / (1 - GeoRatio) > x)
    Loop
End Function
 
Private Function SearchIndex(ByRef dMin As Double, _
                             ByRef dMid As Double, _
                             ByRef dMax As Double, _
                             bTooBig As Boolean) As Boolean
    ' shg 2011
    ' UDF or VBA
 
    ' Changes the search limits dMin and dMax and
    ' the next test value dMid based on their
    ' current values and the results of the last test.
 
    ' Returns False when there are no more values to test.
 
    'Debug.Print dMin, dMid, dMax, IIf(bTooBig, "Too big", "Too small")
 
    If dMid = dMin Or dMid = dMax Then Exit Function
 
    If bTooBig Then dMax = dMid Else dMin = dMid
    dMid = (dMax + dMin) / 2
    SearchIndex = True
End Function

 

[rogerthat]

shg... this function you wrote is fantastic.. working out great.

One question though.. is it at all POSSIBLE to write an actual excel formula in a cell that does exactly what this macro does?

Using this criteria:

total weeks: 20
starting week 1: 2,500
overall goal: 100,000
weekly % growth:________ (% is what we need to find)

So
week 1: 2,500
week 2: (week 1 * (1+weekly growth %))
week 3: (week 2 * (1+weekly growth %))
week 4: (week 3 * (1+weekly growth %))
week 5: (week 4 * (1+weekly growth %))
week 6: (week 5 * (1+weekly growth %))
week 7: (week 6 * (1+weekly growth %))
week 8: (week 7 * (1+weekly growth %))
week 9: (week 8 * (1+weekly growth %))
week 10: (week 9 * (1+weekly growth %)) etc etc until week 20
Overall Goal Total: 100,000

Here's why I'm asking. I was trying to figure out a formula that did what i'm asking. So i used your Function first with this criteria to come up with the right % growth:

total weeks: 20
starting week 1: 2,500
overall goal: 100,000
weekly % growth: 0.0677448750910066 or 6.8%
So this 6.8% is the magic number we need. Perfect.

So then in my attempt to write a formula to do this, and using the same criteria as before i did this:

2,500 x 52 (weeks) = 130,000
then
100,000 x 19 (weeks) = 1,900,000.
[i used 19 because the 1st week goal is already given (2,500).

so, 130,000 / 1,900,000 = 0.068421052631579 or 6.8%
the number is not perfect like your function (the decimal numbers are different, BUT, this is so darn close!) and i thought maybe i was onto something.. but then when i change the 2,500 to 3,000 in my formula the % goes up, not down (which it needs to go down).

Anyways, i thought maybe it was possible but i'm obviously not smart enough and doing something wrong. thought you might know if it were possible..

Any thoughts?

thanks..

 

[shg]

I think I don't understand what you're trying to do ...

 

[rogerthat]

I'd like to replace the function you wrote and have an actual excel formula do the calculation and return the 6.8% answer from the criteria below.

If you're overall goal is to make 100,000 calls in 20 weeks, and your starting week 1 goal is 2,500, what are your goals for the rest of the weeks (weeks 2-20) so that when you sum all the weeks up (weeks 1-20), you have 100,000. What % do need to add for each subsequent week?

The vba function you wrote gives us that answer: 6.8%. But let's assume that your organization said they didn't want a macro-enabled spreadsheet and you had to write an actual formula to do what the macro function did.

That's where we are.. and I'm wondering whether or not it's possible.

Thoughts on that?

 

[Armando Montes]

Try this for CAGR:
=(A2/A1)^(1/(A$3-1))
A2 = 100000
A1 = 500
A3 = 20

 

[rogerthat]

Armando.. thanks for the reply. I'm familiar with the CAGR formula and it does work but that's not what i'm looking for. Note: 100,000 is not a goal, but rather the OVERALL goal. With you're formula, 100,000 would be the week 20 goal when in fact, the week 20 goal (using your 500 calls as week 1 goal) should be 17,474. See below.

For example, let's use your 500 calls as week 1 starting goal.

The correct goals should be:
Wk1 - 500 (this is a given starting #)
Wk2 - 603
Wk3 - 727
Wk4 - 876
Wk5 - 1,057
Wk6 - 1,274
Wk7 - 1,536
Wk8 - 1,852
Wk9 - 2,233
Wk10 - 2,692
Wk11 - 3,246
Wk12 - 3,913
Wk13 - 4,718
Wk14 - 5,689
Wk15 - 6,859
Wk16 - 8,269
Wk17 - 9,970
Wk18 - 12,021
Wk19 - 14,493
Wk20 - 17,474
TOTAL - 100,000

The percentage growth % (what I'm looking for in a formula) is 20.6% which is applied to each week. for example:
WK1 - 500
Wk2 - (500 x (1+20.6%)) = 603.
Wk3 - (603 x (1+20.6%)) = 727. etc etc etc..

I need a formula to come up with the 20.6% figure.

Using your CAGR formula (which as you'll see is NOT what we need), the goals come out as such:
Wk1 - 500
Wk2 - 661
Wk3 - 873
Wk4 - 1,154
Wk5 - 1,525
Wk6 - 2,016
Wk7 - 2,664
Wk8 - 3,521
Wk9 - 4,654
Wk10 - 6,151
Wk11 - 8,129
Wk12 - 10,743
Wk13 - 14,199
Wk14 - 18,765
Wk15 - 24,801
Wk16 - 32,777
Wk17 - 43,319
Wk18 - 57,251
Wk19 - 75,665
Wk20 - 100,000
TOTAL - 409,371

Make sense?

 

[shg]

I'm sure it could be done with iteration and some helper cells.

 

[shg]

For example ...

<TABLE style="WIDTH: 397pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=530><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 5193" width=114><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 3254" width=71><COL style="WIDTH: 259pt; mso-width-source: userset; mso-width-alt: 15762" width=345><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Reset</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 width=71 align=right>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; WIDTH: 259pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32 width=345>Enter 1 to reset, set parameters, then clear to run</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Sum Ratio</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B2: Input</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Terms</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right>20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B3: Input</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Initial Term Ratio</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 align=right>1.037155044</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B4: =B2^(1/(B3-1))</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Term Ratio</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 align=right>1.037155044</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B5: =IF(B1=1, B4, B5 + SIGN(B2-B7) * B4/ 2 ^ (B6+1))</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Iteration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 align=right>0</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B6: =IF(B1=1, 0, B6+1)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3; WIDTH: 85pt; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl31 height=16 width=114>Sum</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #e7e7e7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 align=right>28.91424583</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #909090; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl32>B7: {=SUM(B5^(ROW(INDIRECT("1:" & B3)) -1))}</TD></TR></TBODY></TABLE>

Enable iterations and set to 50.

 

[rogerthat]

I want so badly to say this is awesome because it looks frickin awesome but i have no idea how to use it or what numbers to use and where. I've enabled iterations and opened a fresh worksheet and copied/pasted everything in place.

Any chance you could set it up with our numbers from before? The 100,000 number for overall goal, 20 for total weeks, and 2,500 for week 1 starting goal?