Create Exponential Growth Curve in Excel where the starting point is higher than zero

[SR1SR]

Create Exponential Growth Curve in Excel - 2335

Generate an exponential growth curve that will generate 600,000 units of sales in 70 days. Brandon sent in this question. It turns out that this is fairly easy to do using the GROWTH function in Excel. Table of Contents (0:00) Welcome & Problem (0:21) Need to create an exponential growth curve...

www.mrexcel.com

This solution is perfect!! What if I need the starting point of this curve to be higher to match an existing trend?
Current sales are averaging more than zero per week so I want to continue that trend with an exponential curve whose data points all add up to a stated goal.
Example below

Goal	2202
Weeks	13
Curve Total	2202
Formula	92.7397
Constant	23.74388
Territory 8 Week Average	120
2/11	99
2/18	189
2/25	103
3/4	102
3/11	112
3/18	111
3/25	134
4/1	114
1	44.22161		I don't want to drop from 114 to 44.22.
2	53.23391		Need to continue from the average of known sales above with an exponential growth curve whose data points all add up to the goal number
3	64.08292
4	77.14293
5	92.86457
6	111.7902
7	134.573
8	161.9988
9	195.0139
10	234.7575
11	282.6007
12	340.1944
13	409.5256

 

[BSALV]

growrate.xlsx
	A	B	C	D	E	F	G	H
1				check
2	growrate	5,516373%
3	period	amount			period	value	sum	∆
4	1	1		5,516373%	1	120,2887	2201,998	0,001965
5	2	1,05516373			2	126,9242
6					3	133,9259
7	sum after 14 periods	20,31577224	<<<< GOAL SEEK on value : 20,3157894736842 by changing B2		4	141,3137
8					5	149,1091
9	goal	2202			6	157,3345
10	start	114			7	166,0137
11	fraction	20,31578947			8	175,1716
12					9	184,8347
13					10	195,0309
14					11	205,7895
15					12	217,1416
16					13	229,1200
17
Blad1

Cell Formulas
Range	Formula
D4	D4	=+B2
E4:E16	E4	=SEQUENCE(13)
G4	G4	=SUM(F4:F16)
H4	H4	=+B9-G4
B5	B5	=+B4+B2
B7	B7	=SUM(GROWTH(B4:B5,A4:A5,SEQUENCE(14)))
C7	C7	="<<<< GOAL SEEK "& CHAR(10) & "on value : " & B11 & CHAR(10) & " by changing B2"
B11	B11	=+B9/B10+1
F4:F16	F4	=$B$10*POWER(1+$D$4,E4)
Dynamic array formulas.

 

[SR1SR]

growrate.xlsx
	A	B	C	D	E	F	G	H
1				check
2	growrate	5,516373%
3	period	amount			period	value	sum	∆
4	1	1		5,516373%	1	120,2887	2201,998	0,001965
5	2	1,05516373			2	126,9242
6					3	133,9259
7	sum after 14 periods	20,31577224	<<<< GOAL SEEK on value : 20,3157894736842 by changing B2		4	141,3137
8					5	149,1091
9	goal	2202			6	157,3345
10	start	114			7	166,0137
11	fraction	20,31578947			8	175,1716
12					9	184,8347
13					10	195,0309
14					11	205,7895
15					12	217,1416
16					13	229,1200
17
Blad1

Cell Formulas
Range	Formula
D4	D4	=+B2
E4:E16	E4	=SEQUENCE(13)
G4	G4	=SUM(F4:F16)
H4	H4	=+B9-G4
B5	B5	=+B4+B2
B7	B7	=SUM(GROWTH(B4:B5,A4:A5,SEQUENCE(14)))
C7	C7	="<<<< GOAL SEEK "& CHAR(10) & "on value : " & B11 & CHAR(10) & " by changing B2"
B11	B11	=+B9/B10+1
F4:F16	F4	=$B$10*POWER(1+$D$4,E4)
Dynamic array formulas.

BSALV - Can't thank you enough. I tested your solution and it was spot on. My challenge is your method as well as using goals seek on my own gave me the correct answer for an individual territory. Problem is I have 80+ territories and using Goal Seek is too time consuming especially when the goal numbers will change quarterly for the 80+ territories. Was looking for a calculation using the GROWTH function as in the video but being able to start at a value greater than zero where, as you did, all of the values of the curve add up to a predetermined value or goal. If I had a formula, I could then copy it into a table across the 80+ territories. Again, very much appreciate your help and time. Any ideas are welcome

 

[KRice]

Is there some rationale for requiring exponential growth? The GROWTH function requires that you have sufficient information (known) to support regression to determine fitting parameters for an exponential model, and then those parameters are used for extrapolation purposes. In the example shown in post #1, there is little reason to expect that the growth will be exponential (the known data do not exhibit that behavior), so I do not understand why GROWTH would or should be used.

Regarding the latest post that mentions 80+ territories, is something known about historical growth trends for each of these territories? Are their growth trends different? Without some historical basis, there is no way to know what fitting parameters would be appropriate for each territory. Or are you only interested in specifying a starting y value, a target ending y value, and the time interval between the two to establish the exponential model parameters, without regard for whether real data support the application of the model?

 

[SR1SR]

Each territory is provided a quarterly goal. The growth is exponential based on the type of business we are in. It is also a better motivational tool for the rep to see that they don't have to have astronomical numbers right in the beginning of the quarter which is what would result in a linear trend. I can achieve this through an exponential growth curve and based on using GOAL SEEK last quarter, I was very accurate in what each of my 9 reps achieved, not just in the real numbers, but also in their ability to see that they could achieve what on the surface seemed like some very lofty quarterly goals. The challenge here is I want to roll this out to a larger group which includes 80+ territories with users who are very excel basic. By creating a table where they can enter basic information like 8 week territory sales average leading up to a new quarter, the territory goal, and the assumption of 13 weeks in a quarter, the project will help others who have reps who feel they are unable to achieve on day one of a new quarter. The need is a formula that, as in the video, produces a curve where all data points add up to the territory goal. I just need it to start at what each territory's 8 week average is, which of course, is a point higher than zero.

 

[BSALV]

KRice is right with his opinion. So this solution is perhaps mathematically correct, but is not the "real thing", thus a "motivational tool"
sr1sr
on the tab "VBA" in yellow the 3 numbers for making a serie. The resulting serie can be horizontal or vertical, your choice.

 

[SR1SR]

BSALV - you are over the target.
Ideally a non-VBA solution is preferred.
Best scenario is a growth formula (that doesn't start at zero but at a value inputted by the user) that could be copied and pasted across a table for the 80+ territories.
I appreciate your time and understand if you have had enough with trying to assist.
Do know your time and effort is much appreciated.

 

[BSALV]

i can't help you in a non-VBA way, sorry.
You have 80+ places in your workbook, where you want such a serie.
That 'll be a difficult job to copy exactly 80+ times a bunch of formulas ... .

With my solution, you point 80+ times the 3 parameters and the range for the output of that serie, no chance to make errors.

I hope a 365-formula-specialist 'll help you ...

 

[SR1SR]

Thanks BSALV. Your assistance is much appreciated