Help with creating cyclical data

[didia]

Hello,


I have a challenge in Excel, that I do not know how to solve.


I want to predict the growth rate of average selling prices of a product and I predict that the CAGR is equal to the long term GDP-growth of around 3,1%. The industry is cyclical with approx. 3 year product cycles. I want to model it such that I only need to adjust one or two numbers in order to determine the "heights" and "lows" of the swings.


I was thinking of using some kind of sin function, since it has the cyclical struture that I need, but I do not know how to incorporate it.


Any help would be much appreciated

 

[Luke M]

Welcome to the forums!


First, while GDP is a fairly common acronym, I'm unfamiliar with CAGR. Can you example what this stands for? Next, would it be possible for you to post a sample of what the data looks like, and what you would expect to see/change?

 

[Hui]

Dida


Firstly, Welcome to the Chandoo.org Forums


If you know the cyclicity or period (3 Years) and Growth you could simply use an Offset() function to retrieve the last value and then add 3.1%

eg:

Assume the data is Quarterly in A2:A30

Growth is in B1

In A31: You will use =(1+$B$1)*Offset(A30,-11,)

This will retrieve the value that is 11 quarters before the previous quarter (3 years before the current Quarter) and multiply it by 1+Growth rate


If this is unclear, try posting a sample set of your data:

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[didia]

Thank you very much for your replies, they are much appreciated.


With regards to CAGR it is compound annual growth rate, i.e. the internal interest rate. It is calculated as (ending value/starting value)^(1/number of years)-1


The data could look like;

[pre]

Growth rates:
N/a  4,50%    -1,00%     -3,00%	4,30%	-1,00%	-2,80%	4,10%	-1,00%	-2,60%
3,90%    -1,00%     -2,40%	3,70%	-1,00%	-2,20%	3,60%	-1,00%	-2,10%
3,50%    -1,00%     -2,00%

Values:
1	1,045	1,035	1,004	1,047	1,036	1,007	1,048	1,038
1,011	1,050	1,040	1,015	1,053	1,042	1,019	1,056	1,045
1,023	1,059	1,048	1,028

[/pre]
I want it to look like a sin-function, but with the height of the cycles converging to 0 around the CAGR of 3,1% growth.

 

[Hui]

You might want to have a look at a possible solution using the Trend() function

here: https://www.dropbox.com/s/q77slubjca05qdb/Trend%20Question.xlsx


This solution extrapolates each point forward based on every third point

eg

Points 1, 4, 7, 10, 13, 16 & 19 are used to calculate points 22, 25 & 28

Points 2, 5, 8, 11, 14, 17 & 20 are used to calculate points 23, 26 & 29

etc


It seams to work really well