Calculate unit sales with recurring billing AND growth rate.

[Art]

Hi folks,

(I posted this a few days ago to the worksheetfunctions group, but it
appears to be pretty quiet over there...)

I'm building a worksheet to forecast unit sales. Our business does
recurring billing monthly (on a subscription), so I need to be able to
plug in a growth rate and have each month show a sum that includes in
the growth while also adding in all existing subscriptions. In other
words, I want to be able to show, say, a 2% growth rate each month for
new subscriptions, while still adding in existing ones into the total.

So, for instance, if I had a 10% monthly growth rate, and I started in
month 1 with 25 subscriptions, my monthly totals would look like:

1 2 3 4 5 6 <-- Month
-------------------------------
25 53 83 116 153 193 <-- Subscription sales (rounded)

I can do this using two cells for each month (one to calculate growth,
the other to add all the months, including the current month,
together), but I'd like to find a formula that will allow me to
calculate and show each month's total in a single cell.

I'm sure there's a nice easy formula/function, and I'm just missing
it.

Thanks!

Art

 

[duane]

previous months sales * (1 + growth rate) = new months sales

am i missing something

 

[Sandy Mann]

I don't know about you missing something but I deffnitely am.

With the OP's 25 in A1, =ROUND(A1+A1*1.1,0) will give the second value of 53
and =ROUND(A1+B1*1.1,0) will give the 83 third value but extending the logic
to =ROUND(A1+B1+C1*1.1,0) returns 169 instead of the 116 required value?

To the OP:
What formulas have you been using in the two cell for each month?

Regards

Sandy

 

[Art]

Hi Sandy and Duane,

Thanks very much for your responses. I was originally calculating
things the way you are, then realized that this meant that growth was
being calculated on both new sales and existing (recurring) sales. I
need the growth to be based only on the previous month's NEW sales,
without including the existing sales in the growth calculation. Once
I've done that, I can then add in the recurring sales. I'm just
wondering if there's a way to do all this in a single cell
calculation. Currently, I'm doing it in two cells. Here's another,
clearer example (again assuming a 10% growth on for new sales):

1 2 3 4 5 6 <-- Month
-------------------------------
25 28 30 33 37 40 <-- New Sales (based on 10% growth)
0 25 53 83 116 153 <-- Recurring sales (total of all
------------------------------- previous sales)
25 53 83 116 153 193 <-- Total New & Recurring Sales

Hope this makes more sense, and explains how I'm arriving at my
numbers.

Thanks again for your help!

Art

 

[Sandy Mann]

Art,

With your starting value of 25 in cell B21, try

=ROUND(B21+$B$21*1.1^(COLUMN()-COLUMN($B$21)),0)

in C1 and copy across.

By the way the way that I think your groth rate is 110% not 10%

HTH

Sandy

 

[Art]

Thanks, Sandy! I'll give your solution a go.

I'm pretty sure what I've got is a 10% growth rate, not 110%. At 110%,
you'd see the new sales more than double each month, right? Now, we
could say that this month's new sales are 110% of last month's new
sales, which means we grew by 10%. I think it's a semantics thing
(unless I'm very confused, here!).

Art