Forum Discussion
Targeted Value with 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
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)
- 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.
[U]Consulting[/U]
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)]
[U]Distribution[/U]
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)]
[U]Total[/U]
Year 1 = 47,000
Year 2 = 85,000
Y-O-Y Growth Rate = 80.85% [IFERROR((Year 2 - Year 1)/ABS(Year 1),0)]
[B]Hypothetical Total[/B] = 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
[U]Adjusted Y-O-Y Growth Rate[/U]
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%
[U]Adjusted Hypothetical Values[/U]
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
[U]Hypothetical Total[/U]
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) ) )
You'll have to specify either one of the amounts or one of the percentages. Otherwise, there are infinitely many solutions. For example:
Thanks for your reply - appreciate your time and support.
Actually 38,000 potential solutions - the delta from the Year 1 total (47,000) to the Year 2 total (85,000).
I was able to solve on my own.
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.
Again, thanks much for your input.
- 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.
[U]Consulting[/U]
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)]
[U]Distribution[/U]
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)]
[U]Total[/U]
Year 1 = 47,000
Year 2 = 85,000
Y-O-Y Growth Rate = 80.85% [IFERROR((Year 2 - Year 1)/ABS(Year 1),0)]
[B]Hypothetical Total[/B] = 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
[U]Adjusted Y-O-Y Growth Rate[/U]
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%
[U]Adjusted Hypothetical Values[/U]
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
[U]Hypothetical Total[/U]
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) ) )
