Relative growth factor within a fixed sum (Growth A 100%, Growth B 200%, sum remain X)

[Suze10]

Hi Everybody,

I would like to breack down expectations per Customers, knowing that Customer B will grow 200% more than A and C. The Total expectation is allready fixed by calculating CAGR and should stay as is.

I tried with different formulas but no matter how I did, the total "Expectation 2020" did not match the sum.
Please, see the Pictures attached.
Growth Factor.PNG


Any Idea how I can do this?

Many thanks for you help
Suzanne

[Suze10]

It Looks like the file attached cannot be open...

So
Turnover 2014
Customer A: 5
Customer B: 3
Customer C: 7
Total Turnover 2014: 15


Expectation 2020 in Total = CAGR 12,25% = 30

Customer B will grow 200% more than the other
How to breack down the expectations 2020 to get sales for Customer A, B and C?

[JeteMc]

If I am understanding the requirements correctly the increased turnover by Customer B will be twice the increased turnover of Customers A and C. That is, if A and C increase turnover by 2, B will increase turnover by 4. Is that correct and does the turnover for each customer need to be in whole numbers?

[joeu2004]

[.... deleted ....]

[Suze10]

Hi JeteMc,

You got it.
the goal for 2020 has been set on the total turnover! I need to simulate how each customer could grow. They will not all have the same grow over the next 6 years. It has to be numbers that sum up to the expected total...

Thanks

[Tony Valko]

Many members are unable to see images in the *.png format.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

[samba_ravi]

please attach a sample file with expected result

[MrShorty]

The heart of the problem seems to me like it will be a "solve a system of equations" type problem, but it is not clear to me what those equations are. (refresher course on solving systems of linear equations: http://www.purplemath.com/modules/systlin1.htm )

Based on JeteMc's post #3, the equations might be:

a) 5+A + 3+B + 7+C = 30
b) C=A
c) B=2*A

If those are the correct equations, then it should be as simple as 1) substitute for B and C into eqn a, then 2) solve the resulting expression for A, then 3) find B and C from A. If this is correct, then, once you have done the algebra, programming those resulting expressions for A, B, and C into the spreadsheet should be easy.

[JeteMc]

I hope that I understand correctly, let me know if not. The attached file's solution goes along the lines of Mr. Shorty's system of equations. When the file opens you should notice that D6 is selected (value at this point will not be correct), after you "Enable Editing" open the Data tab. Choose "What-If Analysis" from the Data tools pane then choose "Goal Seek". The "Set cell:" should already be D6, click in the "To value:" box and enter 30 as that is your expected value. Click in the "By changing cell:" box and type in F1 and click OK.
Relative Growth Factor.xlsx
Let me know if you have any questions.