Calculating exponential growth to a known point. How?

Status
Not open for further replies.

Schuyler

New Member
Hiya,

I'm trying to project growth figures for my company over two years. I know where we are and I know where I want to get to, but I am struggling to work out a formula to calculate monthly targets over 24 months.

Current Yr: 382,135
In 2 yrs: 860,426
Monthly targets: ?

I've attached an example. cell A4 is where we are, Z4 is where we want to get to, and in between are the monthly targets I want to fill out.

Attachments

• 15 KB Views: 26

Last edited:

SirJB7

Excel Rōnin
Hi, Schuyler!

As a new user you might want (I'd say should and must) read this:

And regarding your issue, arithmetically speaking there're infinite functions that passes for 2 given points, being as simpler as both the degree of the function and its complexity, i.e.:
y=ax+b, linear (simpler)
and so on.

From the viewpoint of the progression concept, depending on if it's more suitable an arithmetic progression than a geometric one, the result (intermediate values) maybe different too.

So if you don't give us an idea of how those figures would vary, that's to say which function type/degree will they better follow, it'd be difficult to predict something close to what reality will show.

For the simpler case (linear), place this formula in B4 and copy across thru Y4:
=\$A\$4+(\$Z4-\$A4)/(COLUMNA(\$Z4)-COLUMNA(\$A4))*(COLUMNA()-COLUMNA(\$A4)) -----> in english: =\$A\$4+(\$Z4-\$A4)/(COLUMN(\$Z4)-COLUMN(\$A4))*(COLUMN()-COLUMN(\$A4))

Regards!

wchaug

New Member
There are two formulas in Excel that can model exponential growth: One is called LOGEST and the other is GROWTH.

For your situation, you want GROWTH. LOGEST gives you the exponential formula which I'm pretty sure you don't actually care about. GROWTH takes exponential data and predicts points based on an exponential trend. Since there are only 2 points on this chart, the fit is perfect, which I suppose is OK because these are target values, not actual values. I have it set up correctly on the attached spreadsheet.

One more thing I think is worth mentioning: Excel stores dates by assigning day 1 to January 1, 1900 and then counting the days afterwards. Because of this, I wouldn't recommend using the actual dates with the GROWTH function, or with any curve-fit or regression data. Instead I assigned 1 to the current month and just counted the months afterwards, since you wanted a monthly forecast. On the chart, I assigned the x-axis labels to the months.

Attachments

• 19.1 KB Views: 135

Schuyler

New Member
Many thanks for that - I'll have a look and see if that solves it.

Schuyler

New Member
bobhc & SirJB7 & wchaug - please do let me know if I've breached any etiquette here. I've reviewed the link you've provided. Thank you and updated my profile and posted my introduction. Also Many thanks for the replies, the issue I think is very much the infinite functions. In this instance I am going with the linear approach outlined - it at least avoids a large leap in targets between years 1& 2, which an annual target divided by 12 would produce.

thanks all.

SirJB7

Excel Rōnin
Hi, Schuyler!

You've not done anything that'd define you as an outlaw, take it easy. The idea is getting new members go thru those links so as to find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: The choose of linear method among the infinite functions is the 1st, basic, instinctive and easier way to do the job. But I insist that it's much more tied to how you assign resources (production factors like capital, work, etc.) than to an arbitrary arithmetic method.
If you sell lollipops (1 per child) your growth equation will be given by the population increase factor.
If your production schema is tied to capital resource only, and you have a 20% of profit and you distribute a 10% to the shareholders and reinvest the 10%, your growth equation will be of the form of X0*(1+.10)^n.
I'd go on with lots of different examples, but it's you who know your biz.

Last edited:

Schuyler

New Member
SirJB7,

Thanks. OK - lets try and fix this properly as I know I will use it again if we do.
I've set out the example excel file differently, and I'll try and explain my challenge again below:

1. I know the revenue total for year 1.
2. I have an annual target for years 2 & 3, which each of the 12 months must total.
3. I don't want there to be a big jump in the target between the last month of year 2 and the first month of year 3
4. I would like a smooth curve from the start of year 2 until the end of year 3 of increasing revenue targets.

How do I do this?

I appreciate your perseverance in helping me to solve this.

Best,

S

Attachments

• 15.2 KB Views: 25

SirJB7

Excel Rōnin
Hi, Schuyler!

If we only consider your 1) and 2) the problem seems to look a lot like the original one, thus the solutions (trivial as the linear case or a bit more elaborated as 2nd degree functions) enumerated earlier would be still easy to define an implement.

With 3) and 4) things are radically different. We should be moving towards Bezier curves to get the smoothness required and avoid the notable jump for the delta difference between last month of 1st year and 1st month of last year.

Start giving a look at these links for the main concept:
https://en.wikipedia.org/wiki/Bézier_curve

Then go on with this Excel / VBA links:
[url]http://www.mrexcel.com/forum/excel-questions/718515-cubic-spline-visual-basic-applications-code.html[/URL]

Hope it helps to get a more accurate view of what we're now dealing with, thanks to your 3&4.

Regards!

Schuyler

New Member
I think the math is a bit beyond my small brain and the time frame for my need, but I will continue to look into the links. What I've done is to find a % amount that I can increase each previous months target by: 103% in the first year and 105% in the second. By making a small adjustment to month 0 and month 13 I was able to get the right total amount for each year without any big jump. Thanks all for your help in trying to educate me to do this properly.

SirJB7

Excel Rōnin
Hi, Schuyler!
Regards!
PS: If I happen to imagine a simple way to do it (I thought about something alike your percentages) I'll get back to you and post it here.

DLee

New Member
Thank you for your examples. I have a similar issue. I have worked in linear decay into a proforma. I'm trying to compare exponential decay between two known beginning and ending percentages. When I use the examples above the exponential figures are almost identical to the linear. Any ideas on what is wrong here?

Attachments

• 13.9 KB Views: 3

vletm

Excel Ninja
DLee
As You're a new member, You have read Forum Rules ... yesterday:
• Start a new post every time you ask a question, even if the theme is similar.
The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

Peter Bartholomew

Well-Known Member
The attached uses exponential growth and hits the target for year 3 though it falls a shade shy for year 2.

Attachments

• 19.3 KB Views: 4
Status
Not open for further replies.