allocation to keep totals to 100%

akprasa1
akprasa1
in Modeling

Hi Ana planners,

Is there any way to keep the totals at 100% despite applying CAGR to set of percentages.

Image 1 shows the initial percentages and image 2 shows the new percentages after applying CAGR.

FY18 % are my base in image 1 on which CAGR is applied for an occasion called Party for subsequent years.

But the idea is to keep the totals to 100% despite the CAGR increases.

Can some one suggest a way to do it?

 

 

 

Tagged:

Best Answer

  • JaredDolich
    JaredDolich
    Answer ✓

    @akprasa1 ,

    Great question. 

    Because you are trying to get percentages to add to 100% that would suggest you are creating a mix% to total.

    Here are some suggestions with an example of how to accomplish this.

     

    First create a system module (See DISCO methodology) that contains your CAGR rates by Occasion.

    See @DavidSmith and @HollyRieke excellent post on this.

     

    You didn't provide these, but I think I'm pretty close.

    CAGR Rates.png

    Next you'll need to create a comparison to the compounded value to the total of all the compounded values. This will ensure your percentages add up to 100% as  you can see below.

    - Use a boolean to check for the first year since we don't want to compound the first year's base.

    - The boolean should always check for the most likely scenario first, making your calculations more efficient.

    - Next create a calculation on the percentages that uses the Occasion parent (use the SELECT function)

    - Your final percentages add up to 100% and in this example it's pretty close to what you had in your second image.

    - Note: In my example you could also put the base values in the system module instead of repeating them every year like I did.

     

    CAGR Grid.png

    Finally, here are the formulas to accomplish this.

    The compounded value is a little long so I show it in the formula bar.

     

    CAGR Formulas.png

Answers

Categories