Help- Formula/method to calculate individual CAGR components

[saiff]

Hello All,

I am struggling with a certain piece within my sheet.

I have the final revenue numbers for multiple years available. This annual revenue is representing a recurring stream of business.
Example: Q1 = $10000 | Q2 = $10000 + 5000(new) | Q3 = 15000 + 9000(new).....So old customers retained and new ones added each quarter.

So I only have the total revenue from the recurring stream across the whole year ---> 2018 = $500K AND I have the rate at which new accounts are being added per quarter = 10%.

I am unable to figure how to use these two data points to come up with actual revenue splits across each quarter that match the given growth rate and sum up to the final annual revenue number that has been given to me.

I am able to get by Goal seek, but i Need to be dynamic so that we can play around with the annual revenue numbers.

Please help. Thank a ton!

 

[StephenCrump]

Welcome to the Forum!

I'm not clear how all your numbers relate, so the solution below is only a guess about what you are trying to do:

Growth.xlsx
	A	B	C	D	E	F	G	H
1	g	10%
2	N	4
3	Base	107,735.40
4
5	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
6	107,735	107,735	107,735	107,735	107,735	107,735	107,735	107,735
7		10,774	10,774	10,774	10,774	10,774	10,774	10,774
8			11,851	11,851	11,851	11,851	11,851	11,851
9				13,036	13,036	13,036	13,036	13,036
10					14,340	14,340	14,340	14,340
11						15,774	15,774	15,774
12							17,351	17,351
13								19,086
14
15	107,735	118,509	130,360	143,396	157,735	173,509	190,860	209,946
16
17	107,735	226,244	356,604	500,000	657,735	831,244	1,022,104	1,232,050
18	107,735	226,244	356,604	500,000	657,735	831,244	1,022,104	1,232,050
1

Cell Formulas
Range	Formula
B3	B3	=500000*g/((1+g)^N-1)
A6:H6	A6	=Base
B7,H7:H13,G7:G12,F7:F11,E7:E10,D7:D9,C7:C8	B7	=g*SUM(B$6:B6)
A15:H15	A15	=SUM(A6:A13)
A17	A17	=A15
B17:H17	B17	=A17+B15
A18:H18	A18	=Base*((1+g)^COLUMNS($A18:A18)-1)/g

Named Ranges
Name	Refers To	Cells
Base	='1'!$B$3	A18:H18, A6:H6
g	='1'!$B$1	B7, B3, A18:H18, C7:C8, D7:D9, E7:E10, F7:F11, G7:G12, H7:H13
N	='1'!$B$2	B3