Solved

Growth function in Excel

Posted on 2010-08-13
14
445 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:BBlu
  • 7
  • 7
14 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33433401
>>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 :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33433438
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/(N-1))-1
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33433599
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.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:BBlu
ID: 33433609
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)?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33433738
How would that be calculated?  Linear, or geometric (which is what my formula will do)?
0
 

Author Comment

by:BBlu
ID: 33434283
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?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33434368
See the attached example, which shows how to do assuming either geometric growth or linear growth.

Q-26401805.xls
0
 

Author Comment

by:BBlu
ID: 33434376
Perfect!! Are you a mathematician or something? :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33434396
Nope, just a finance major in a different life :)
0
 

Author Comment

by:BBlu
ID: 33434406
one question: shouldn't the .01 be in period 0?  how would the formula change if that was the case>
0
 

Author Comment

by:BBlu
ID: 33434409
LOL.  Wow, I have an MBA and couldn't get that one... boy, do I feel like I wasted my money.  LOL
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 225 total points
ID: 33434439
>>one question: shouldn't the .01 be in period 0? how would the formula change if that was the case>

Have a look here:
Q-26401805.xls
0
 

Author Comment

by:BBlu
ID: 33434703
Got it.  Thank you.
0
 

Author Closing Comment

by:BBlu
ID: 33434706
Thank you very much.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question