How to model exponential growth for revenue forecasts

[benduflot]

Hi,

I am doing financial forecasts for a new company.

In 2023, this company plans to earn $50K. In 2024, $500K and in 2025, $4M.

I must compute the monthly revenues for these 3 years.

To do so, I would like to model an exponential curve which goes through these 3 points, i.e meaning that if I sum all the months of 2023, I must find $50K ; and if I sum all the months of 2024, I must find $500K, etc.

The idea here is to get a smooth exponential curve going through each month for the 3 years.

I have tried to use the "Data analysis toolpak" add-in in Excel, to do exponential smoothing, but I got lost quite quickly. I am not used to this add-in. And I am not even sure it is the right tool to achieve my goal.

Can you help me on this?

 

[KRice]

You can't do that with a constant rate exponential model. Since you have equal time intervals, you can look at the ratio of earnings to determine if the rate would be constant. We don't know what the time 0 value is, but we know the target after 1 year is 5E4, and after another year the target is 5E5. This establishes a growth rate. And if we maintain that same growth rate, then after another year the value would be 5E6 (or 5 million, not 4 million)...so we have overshot the target. And projecting back to time 0 at the 1st growth rate, we see that the time 0 value would be 5E3.

The general form for the exponential model is F=a*b^x, which can be recast in terms of a "compounding" rate, r, and expressed as F=a*(1+r)^x. So this is the familiar financial compounding formula relating present value to future value. Rather than rely on those functions, you can solve for the rate analytically, but first, you mentioned looking at the earnings each month, so the effective yearly rate and compounding intervals will be accounted for:
F = P * (1 + r/m)^(n*m)
where F is the future value, r is the annual growth rate, m the number of compounding intervals per year, n the number of years, and P is the starting value.
...then r = m*(exp(ln(F/P)/(n*m))-1)
You could split the interval into three sections and compute a rate for each, but you mentioned that a smooth curve was desired. So you are left with determining some average growth rate that comes close to the target points, but it will not go through all of them.

The formula above (and used in the worksheet) computes the growing balance (cumulative earnings), so there is no summing of months. You could extract that monthly information by either reformulating the equation above or simply taking the difference between consecutive cumulative balances.

Book1
	A	B	C	D
1				exponential model
2	P	5000	assumed	a*(1+r/m)^(n*m)
3	n	3	yrs
4	m	12	pds/yr
5	F	4550000
6	r	250%	annual rate	2.500295
7
8	Time (mo)	Value	Yearly targets	Cumulative
9	0	5000	5000	5000
10	1	6041.79
11	2	7300.644
12	3	8821.791
13	4	10659.88
14	5	12880.95
15	6	15564.8
16	7	18807.85
17	8	22726.62
18	9	27461.89
19	10	33183.79
20	11	40097.89
21	12	48452.61	50000	55000
22	13	58548.09
23	14	70747.05
24	15	85487.76
25	16	103299.8
26	17	124823.1
27	18	150831
28	19	182257.9
29	20	220232.7
30	21	266120
31	22	321568.2
32	23	388569.5
33	24	469531	500000	555000
34	25	567361.5
35	26	685575.8
36	27	828420.9
37	28	1001029
38	29	1209601
39	30	1461631
40	31	1766174
41	32	2134170
42	33	2578841
43	34	3116163
44	35	3765441
45	36	4550000	4000000	4555000
Sheet1

Cell Formulas
Range	Formula
B5	B5	=4000000+500000+50000
B6	B6	=D6
D6	D6	=B4*(EXP(LN(B5/B2)/(B3*B4))-1)
A9:A45	A9	=SEQUENCE(B3*B4+1,,0)
D9	D9	=C9
D21	D21	=SUM(C9,C21)
D33	D33	=SUM(C9:C33)
B9:B45	B9	=$B$2*(1+$B$6/$B$4)^$A9
D45	D45	=SUM(C9:C45)
Dynamic array formulas.

 

[benduflot]

Thank you so much KRice for this crystal-clear explanation!

That is really really helpful!

However, in this model, I am forced to use 5000 as a starting value. Ideally, I would like this starting value to be 0 (or a very small number like 50 if we cannot use 0).
I have tried to modify the starting value to 50. But then, the sum of the whole first year is 8140€, not 50K€ anymore. Therefore, I would like to know if it is possible to build a slightly different curve, which would be quadratic rather than exponentially. I guess it would fit better to my target numbers:

Do you know how to transform an exponential growth to a quadratic growth?

 

[KRice]

You are not forced to use 5000 as a starting value, but you need to choose something other than 0 (because 0 will never increase in an exponential model). The rationale behind 5000 is based on your statement that a smooth exponential curve is needed. "Smooth" implies that there are no inflection points, which suggests a constant growth rate. But given 5E4, 5E5, and 4E6 as the benchmark points for the earnings during years 1, 2, and 3, respectively, we can determine that the annual growth rates over the interval 1-2 and 2-3 are approximately 265 % and 231 %. So I chose a rate in the neighborhood to estimate what the starting point would be to ensure a smooth curve and come reasonably close to hitting the intermediate benchmark points. And since the model was applied over the entire three year period, the endpoints match (but the interior ones not necessarily).

I don't think a quadratic (or any other polynomial) model will work well, as you will get inflections (which you don't want) with higher order models; and for a quadratic, you will have a negative trend before transitioning to positive growth.

With all of that said, I think you may want to consider splicing segments of exponential models together, taking each segment one year at a time, applying the relevant growth rate for that year and then ensuring that month 12 of one year is taken as month 0 for the next year. Here is what that looks like (see red curve) with benchmarks (black circles) for the cumulative value based on summing yearly targets. I've also compared it to the fixed growth rate 3-year model described earlier, except the starting value was set at 50 (light blue curve)...this is the curve you did not like because, as expected, it does not increase very quickly initially. Also shown is the 3-year model described earlier beginning at 5000, so that the yearly growth rates are fairly close across all three yearly intervals (green curve)...I believe you were probably okay with the closeness of that curve to the interior target points, but not pleased with the starting point. The segmented model seems to be the best approach to allow flexibility in starting lower (and using a significantly larger growth rate to hit the year 1 target) while ensuring that subsequent segments also hit the interior benchmarks. My only concern with the segmented model is whether inflection points might be obvious where the growth rates transition, but that does not appear to be an issue with the segmented exponential model (red curve).

Mrexcel_20220921.xlsx
	E	F	G	H	I
2		n	1	yrs
3		m	12	pds/yr
4			Cumul	Per Year
5		P=F0	50	50	Annual Rate, r
6		F1	50050	50000	934.1%
7		F2	550050	500000	265.3%
8		F3	4550050	4000000	231.0%
9
10	Sequential Months	Yearly Segments (mo)	Value	Yearly targets	Cumulative
11	0	0	50
12	1	1	88.92138
13	2	2	158.1402
14	3	3	281.2409
15	4	4	500.1666
16	5	5	889.5101
17	6	6	1581.929
18	7	7	2813.346
19	8	8	5003.333
20	9	9	8898.065
21	10	10	15824.56
22	11	11	28142.84
23	12	12	50050
24	13	1	61115.86
25	14	2	74628.34
26	15	3	91128.37
27	16	4	111276.5
28	17	5	135879.3
29	18	6	165921.7
30	19	7	202606.3
31	20	8	247401.8
32	21	9	302101.3
33	22	10	368894.8
34	23	11	450456
35	24	12	550050
36	25	1	655948.9
37	26	2	782236.1
38	27	3	932836.8
39	28	4	1112432
40	29	5	1326604
41	30	6	1582010
42	31	7	1886588
43	32	8	2249805
44	33	9	2682951
45	34	10	3199488
46	35	11	3815473
47	36	12	4550050
Sheet1

Cell Formulas
Range	Formula
G5:G8	G5	=SUM(H$5:H5)
I6:I8	I6	=G$3*(EXP(LN(G6/G5)/(G$2*G$3))-1)
E11:E47	E11	=SEQUENCE(37,,0)
F11:F23	F11	=SEQUENCE(13,,0)
F24:F47	F24	=SEQUENCE(12)
G11:G23	G11	=$G$5*(1+$I$6/$G$3)^$F11
G24:G35	G24	=$G$6*(1+$I$7/$G$3)^$F24
G36:G47	G36	=$G$7*(1+$I$8/$G$3)^$F36
Dynamic array formulas.

 

[benduflot]

That is AMAZING !! Thank you so much!

I wish I had you as an Excel teacher when I was at the uni.

 

[KRice]

You're welcome. A quick follow-up: If you zoom in on the plot to examine the intersections of the segments, you can see evidence of the growth rate changes, as they will create inflection points. The question then becomes whether the inflection points are objectionable or noticeable. The large difference in exponential growth rates between the 0-1 and 1-2 segments is much more noticeable (when zoomed in around 12 months) than the intersection at 24 months (because the growth rates for the 1-2 and 2-3 segments are fairly close). Cells I6:I8 show the growth rates. The reason for such a large disparity in growth rate between the 0-1 and 1-2 segments is due to the very low starting point (50). If you want to investigate alternative approaches that can avoid these issues, then I would consider spline interpolation...not an exponential model, but a segmented polynomial. So you would have 3 polynomials, one for each segment, and each polynomial would have 4 coefficients. That's messier, but an option, nonetheless.