Help: Table to show progressive income growth

CaliforniaLori

New Member
Joined
Sep 26, 2010
Messages
3
Hi! Just learned about this site and really hoping someone can help me! This is for real project - and I need to provide these numbers to prospective investors soon. Let me know if anyone can help me calculate this!

I need to create a table that shows 5 years (60 months) projected revenue for a start-up company, for a subscription-based consumer software product. Many thanks in advance for any help!

Potential market size 5,000,000 people (rough estimate)
Subscription fee per month $11.99 (rough estimate)
Assume annual doubling of market penetration

Year #1: Start at 0, end at 0.5% of market
(i.e. in 12th month would have grown to 25,000 members [which is 5,000,000 * .05])
To describe further, in the 12th month revenue would be 25,000 * $11.99 = $299,750.
My question: What would monthly revenue be for each month leading up to the 12th month, assuming steady rate of growth each month?
NOTE: The first month of year #1 will be offered for free (30 day free trial) - so revenue doesn't start until beginning of Month #2 in the first year (only).

Year 2, end at 1%
Year 3, end at 2%
Year 4, end at 4%
Yera 5, end at 8%

So far I've been able to create a chart that shows a "flat" rate of increase (see below). BUT I don't like this approach, as it shows the company doubling it's rate of growth from end of one year to beginning of the next. (e.g., a company wouldn't generally double it's rate of growth from .17% to .34% fom the last month of year 4 to the first month of year 5.)
Year 1 = .04% growth per month (from 0 to .5%)
Year 2 = .04% growth per month (from .5% to 1%)
Year 3 = .08% growth per month (from 1% to 2%)
Year 4 = .17% growth per month (from 2% to 4%)
Year 5 = .34% growth per month (from 4% to 8%)

Anyone know a formulat I could build that would have a steadily increasing rate of growth from one month to the next, that would result in a doubling every 12 months, ending at 8% at the end of the 60th month?

Again - many thanks!

California Lori
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Lori and welcome to the board.

One possible solution would be to use Linear Interpolation. There is an article here which describes its use: http://support.microsoft.com/kb/214096

The basic formula is: =(end-start)/(ROW(end)-ROW(start))

In the sample below the end row is B16 and the start row is B4.


Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 43px"><COL style="WIDTH: 142px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD style="FONT-WEIGHT: bold">Potentail Market size</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">5000000</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD style="FONT-WEIGHT: bold">Subscription</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">11.99</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Month</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">0</TD><TD>Income</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">2083</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">4167</TD><TD style="TEXT-ALIGN: right">49,958</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">6250</TD><TD style="TEXT-ALIGN: right">74,938</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">8333</TD><TD style="TEXT-ALIGN: right">99,917</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">10417</TD><TD style="TEXT-ALIGN: right">124,896</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">12500</TD><TD style="TEXT-ALIGN: right">149,875</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">14583</TD><TD style="TEXT-ALIGN: right">174,854</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">16667</TD><TD style="TEXT-ALIGN: right">199,833</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">18750</TD><TD style="TEXT-ALIGN: right">224,813</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">20833</TD><TD style="TEXT-ALIGN: right">249,792</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">22917</TD><TD style="TEXT-ALIGN: right">274,771</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">25000</TD><TD style="TEXT-ALIGN: right">299,750</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B5</TD><TD>=+B4+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>B6</TD><TD>=+B5+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C6</TD><TD>=+B6*$C$2</TD></TR><TR><TD>B7</TD><TD>=+B6+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C7</TD><TD>=+B7*$C$2</TD></TR><TR><TD>B8</TD><TD>=+B7+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C8</TD><TD>=+B8*$C$2</TD></TR><TR><TD>B9</TD><TD>=+B8+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C9</TD><TD>=+B9*$C$2</TD></TR><TR><TD>B10</TD><TD>=+B9+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C10</TD><TD>=+B10*$C$2</TD></TR><TR><TD>B11</TD><TD>=+B10+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C11</TD><TD>=+B11*$C$2</TD></TR><TR><TD>B12</TD><TD>=+B11+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C12</TD><TD>=+B12*$C$2</TD></TR><TR><TD>B13</TD><TD>=+B12+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C13</TD><TD>=+B13*$C$2</TD></TR><TR><TD>B14</TD><TD>=+B13+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C14</TD><TD>=+B14*$C$2</TD></TR><TR><TD>B15</TD><TD>=+B14+($B$16-$B$4)/(ROW($B$16)-ROW($B$4))</TD></TR><TR><TD>C15</TD><TD>=+B15*$C$2</TD></TR><TR><TD>B16</TD><TD>=+C1*0.005</TD></TR><TR><TD>C16</TD><TD>=+B16*$C$2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>




NB For the second month the end row and start row changes to B28 and B16 respectively. Note how these cells contain your forecast values for the years end.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 43px"><COL style="WIDTH: 142px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">25000</TD><TD style="TEXT-ALIGN: right">299,750</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">27083</TD><TD style="TEXT-ALIGN: right">324,729</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">29167</TD><TD style="TEXT-ALIGN: right">349,708</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">31250</TD><TD style="TEXT-ALIGN: right">374,688</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">33333</TD><TD style="TEXT-ALIGN: right">399,667</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">35417</TD><TD style="TEXT-ALIGN: right">424,646</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">37500</TD><TD style="TEXT-ALIGN: right">449,625</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">39583</TD><TD style="TEXT-ALIGN: right">474,604</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">41667</TD><TD style="TEXT-ALIGN: right">499,583</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">43750</TD><TD style="TEXT-ALIGN: right">524,563</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">45833</TD><TD style="TEXT-ALIGN: right">549,542</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #333333">47917</TD><TD style="TEXT-ALIGN: right">574,521</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">50000</TD><TD style="TEXT-ALIGN: right">599,500</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B16</TD><TD>=+C1*0.005</TD></TR><TR><TD>C16</TD><TD>=+B16*$C$2</TD></TR><TR><TD>B17</TD><TD>=+B16+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C17</TD><TD>=+B17*$C$2</TD></TR><TR><TD>B18</TD><TD>=+B17+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C18</TD><TD>=+B18*$C$2</TD></TR><TR><TD>B19</TD><TD>=+B18+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C19</TD><TD>=+B19*$C$2</TD></TR><TR><TD>B20</TD><TD>=+B19+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C20</TD><TD>=+B20*$C$2</TD></TR><TR><TD>B21</TD><TD>=+B20+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C21</TD><TD>=+B21*$C$2</TD></TR><TR><TD>B22</TD><TD>=+B21+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C22</TD><TD>=+B22*$C$2</TD></TR><TR><TD>B23</TD><TD>=+B22+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C23</TD><TD>=+B23*$C$2</TD></TR><TR><TD>B24</TD><TD>=+B23+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C24</TD><TD>=+B24*$C$2</TD></TR><TR><TD>B25</TD><TD>=+B24+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C25</TD><TD>=+B25*$C$2</TD></TR><TR><TD>B26</TD><TD>=+B25+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C26</TD><TD>=+B26*$C$2</TD></TR><TR><TD>B27</TD><TD>=+B26+($B$28-$B$16)/(ROW($B$28)-ROW($B$16))</TD></TR><TR><TD>C27</TD><TD>=+B27*$C$2</TD></TR><TR><TD>B28</TD><TD>=+C1*0.01</TD></TR><TR><TD>C28</TD><TD>=+B28*$C$2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Repeating this pattern for the remaining years:
B29=B28+($B$40-$B$28)/(ROW($B$40)-ROW($B$28))
B41=B40+($B$52-$B$40)/(ROW($B$52)-ROW($B$40))
B53=B52+($B$64-$B$52)/(ROW($B$64)-ROW($B$52))

And drag the formula down for the intervening months.

Hope this helps,
Bertie
 
Upvote 0
Thanks! I will look at this more in depth tonight.

Will be at a seminar all day with no access to email.

Have a wonderful day everyone!

Lori
 
Upvote 0
Hi Bertie,

I took a look at that formula and results. Unfortunately it gives me the same flat rate of growth that my original calculations gave me (e.g, 0.4% each month year 1, etc.)

FYI, I did get a reply from eAnswer - using monthly growth rate of 1.0594631, which yields an annual growth rate of 2. This solved my problem.

Many thanks for getting back to me. I'm glad to know the formula you offered, for other purposes too.

Cheers!

Lori
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top