Determine values with mix change

[DaveWagner_]

I have (2) dimensions with values in (2) years, which sum to a total value for each year. The mix of those values from year 1 to year 2 changes. I want to input a hypothetical value total and the formula to return the "should be" mix for each of the (2) dimensions.

Example:

Year 1
Consulting is 14,000 (30% of total)
Distribution is 33,000 (70% of total)
Total is 47,000

Year 2
Consulting is 15,710 (18% of total)
Distribution is 69,290 (82% of total)
Total is 85,000

Hypothetical
Consulting is XX,XXX (XX% of total)
Distribution is XX,XXX (XX% of total)
Total is 72,500 (I enter this value and the formulas provide the XX results above)

 

[dreid1011]

Does this look like results you expect?

Book1
	A	B	C
1	Year 1
2	Consulting is	14,000	30%
3	Distribution is	33,000	70%
4	Total is	47,000
5
6	Year 2
7	Consulting is	15,710	18%
8	Distribution is	69,290	82%
9	Total is	85,000
10
11	Hypothetical
12	Consulting is	17,498	24%
13	Distribution is	55,002	76%
14	Total is	72,500
Sheet4

Cell Formulas
Range	Formula
C2,C7	C2	=B2/B4
C3,C8	C3	=B3/B4
B12	B12	=ROUND(B14*C12,0)
C12:C13	C12	=(C2+C7)/2
B13	B13	=ROUND(B14*C13,0)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculate values based on changing mix
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[DaveWagner_]

That's a correct representation if the Hypothetical value is 66,000 (exactly halfway between Year 1 of 47,000 and Year 2 of 85,000). Thanks for helping me to continue to refine the request.

I need the formula(s) to account for scaling based on where the Hypothetical value corresponds with the Delta. In other words, the mix would shift closer to the Year 2 values as the Hypothetical total gets close to the Year 2 total.

 

[DaveWagner_]

Calculate values based on changing mix

I have (2) dimensions with values in (2) years, which sum to a total value for each year. The mix of those values from year 1 to year 2 changes. I want to input a hypothetical value total and the formula to return the "should be" mix for each of the (2) dimensions. Example: Year 1...

www.excelforum.com

 

[DaveWagner_]

Solved on my own

 

[Joe4]

DaveWagner,
Please do not mark a post as the solution unless you actually post the solution. I have removed it from your last post.
If you post exactly how you solved it, then you can mark that post as the solution.

 

[DaveWagner_]

38,000 potential solutions - the delta from the Year 1 total (47,000) to the Year 2 total (85,000).

Utilizing the Hypothetical total, I calculated the percentage realized along that delta scale, then applied that ratio to the Year 1 to Year 2 growth rates of each dimension.

Consulting
Year 1 = 14,000 (30% of Total)
Year 2 = 15,710 (18% of Total)
Y-O-Y Growth Rate = 12.21% [IFERROR((Year 2 - Year 1)/ABS(Year 1),0)]

Distribution
Year 1 = 33,000 (70% of Total)
Year 2 = 69,290 (82% of Total)
Y-O-Y Growth Rate = 109.97% [IFERROR((Year 2 - Year 1)/ABS(Year 1),0)]

Total
Year 1 = 47,000
Year 2 = 85,000
Y-O-Y Growth Rate = 80.85% [IFERROR((Year 2 - Year 1)/ABS(Year 1),0)]

Hypothetical Total = 80,817
Percentage Realized = (Hypothetical Total - Year 1 Total) / (Year 2 Total - Year 1 Total)
Percentage Realized = (80,817 - 47,000) / (85,000 - 47,000)
Percentage Realized = 33,817 / 38,000
Percentage Realized = 88.99%
>>> This is required to scale the delta range (47,000 to 85,000) to comprehend where/how the Hypothetical value relates to it

Adjusted Y-O-Y Growth Rate
Adjusted the Y-O-Y Growth Rates for both Consulting and Distribution based on the Percentage Realized
Consulting Adjusted YOY Growth Rate = 12.21% * 88.99% = 10.87%
Distribution Adjusted YOY Growth Rate = 109.97% * 88.99% = 97.87%

Adjusted Hypothetical Values
Applied the Adjusted YOY Growth Rate to the Year 1 Values
Consulting Adjusted Value = 14,000 * (1 + 10.87%) = 15,522
Distribution Adjusted Value = 33,000 * (1 + 97.87%) = 65,296

Hypothetical Total
Consulting = 15,522 (19.21% of Total)
Distribution = 65,296 (80.79% of Total)

Implemented as a single formula each for Consulting and Distribution, which auto-checks by summing their results.

=Year 1 Value *(1 + ( ( (Hypothetical Total - Year 1 Total) / (Year 2 Total - Year 1 Total) ) * IFERROR( (Year 2 Value - Year 1 Value) / ABS(Year 1 Value), 0) ) )

 

[DaveWagner_]

 

[felixstraube]

Would this work for you?

Book1.xlsx
	A	B	C	D
1		Year 1	Year 2	Hypothetical
2	Consulting	14,000	15,710	15147.5
3	Distribution	33,000	69,290	57352.5
4	Total	47,000	85,000	72,500
5
6	Consulting	29.79%	18.48%	20.89%
7	Distribution	70.21%	81.52%	79.11%
Sheet1

Cell Formulas
Range	Formula
D2:D3	D2	=(C2-B2)/($C$4-$B$4)*($D$4-$B$4)+B2
B4	B4	=SUM(B2:B3)
B6:D6	B6	=B2/B4
B7:D7	B7	=B3/B4