Formula for calculating 30% growth

[HCRSnyder]

I currently have YTD operating Income which I've annualized. So I've taken that figure and multiplied it by 1.3 to get a 30% growth figure. Now I need to put into a spreadsheet and graph how much each division needs to gain in order to achieve 30% growth on a monthly basis over the next 16 months. How do I write the formula that will give me 30% growth on a monthly basis for the next 16 months compounded?

JRS

 

[grd]

I just need to clarify this.

Whilst I'm no expert on financial reporting, I can cope with compound interest, etc.

Are you asking how to convert a 30% annual growth back to a monthly percentage which, when compounded over the 12 months comes to 30% which is then taken over 16 months?

 

[HCRSnyder]

Not sure! Say I have 10 divisions. Each is supposed to bring in 1 million a month. However, say they are not at a million. How do I project how much they'd have to bring in more than they are currently bringing in each month to reach 30% growth by the end of 2007

JRS

 

[grd]

Sorry, but another question or two.

So the 30% is for the 16 months, is it?

When you say they are supposed to bring in 1m per month is that plus the 30% growth or excluding? In other words are you expecting 16m or 20.8m at the end of 2007 - assuming 16 months?

 

[HCRSnyder]

ok -- I have monthly figures through August 2006 (actual figures). I am then projecting based on those figures what Sept 06 through Dec 2007 will be. I've figured my overall 30% growth figure by the end of Dec 2007 to be 98 million. What $ figure in sales does each division need to reach in order to achieve that figure? So we'd be looking at doing 30% more each month. -- so I guess in my previous example it would be the 20.8 m by the end of 6 months.

Hope that helps. Thanks for your help also.

 

[xcdave]

Is 98 million a monthly figure, or an annual figure?

 

[HCRSnyder]

Annual by the end of 2007

 

[xcdave]

I would use goal seek to solve this problem:

book4
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	FY 06	FY 07
3	1,000	1,000	1,040	1,081	1,124	1,169	1,215	1,263	1,314	1,366	1,420	1,477	1,535	1,596	12,000	15,600
4
5		Rate	3.97%													Growth Rate
6		07 Sum	15,600													30%
7
Sheet1

Set cell = N6
To value = 15600 (cell AA3)
By changing cell = N5