Growth function in Excel
I'm working on a revenue forecast and have several revenue streams. I'm predicting a different month for stabilization, after which I think a monthly or annual growth rate is appropriate. Until that point in time, though, I expect an exponential growth. For example, memberships are expected to reach 10k per month by month 6, with a 5% growth per month thereafter. I'd like to figure out, via a formula, a quick way to grow the revenue from $0 to $10k per month by month 6, with the ability to change the stabilization month (6 in this case) and Dollar figure ($10k in this case) at the stabilization point. Anyone have any ideas?
Patrick Matthews
>>I'd like to figure out, via a formula, a quick way to grow the revenue from $0 to $10k per month by month 6Arithmetically, you can't. There is no possible growth rate that gets from 0 to any other number, because you cannot divide by zero :)
What you can do is assume your starting point is 0.01. Now the growth rate to get you from 0.01 to GoalAmount by the end of N periods is:=(GoalAmount/0.01)^(1/(
BTW, the rate that that formula above calculates is a per period rate; if the periods you are using here are months (i.e., going from 0.01 to 10,000 by the end of Month 6), then that rate is a monthly growth rate.
ASKER
and how do I apply that to each month... for example we expect memberships to grow to a steady state of 28,633 by month 6. What might the function be for each month (1-5; 6 = 28,633)?
How would that be calculated? Linear, or geometric (which is what my formula will do)?
ASKER
I assume N is the total number of periods. Should there be a n for the current period. Using your formula, what would the first and second months formulas be, for example?
See the attached example, which shows how to do assuming either geometric growth or linear growth.
Q-26401805.xls
Q-26401805.xls
ASKER
Perfect!! Are you a mathematician or something? :)
Nope, just a finance major in a different life :)
ASKER
one question: shouldn't the .01 be in period 0? how would the formula change if that was the case>
ASKER
LOL. Wow, I have an MBA and couldn't get that one... boy, do I feel like I wasted my money. LOL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it. Thank you.
ASKER
Thank you very much.