How to Apply two dimensions

SPratt01
SPratt01
in Modeling

I'm creating a scenario model which is based on two measures.  I'd like my model to only apply a specific forecasted % to a row, if the measure is "total company" and profit share measure "Construction".  However, I cant use the NESTED IF function which I would use in excel as its not letting me use multiple items on the same dimensation.  This is the syntax I have so far:

 

IF ITEM(Profit Share Measures) = Profit Share Measures.Story Contracting Company Total THEN '(E1) Scenario Model'.Company % to Apply[SELECT: Total Company.CONSTRUCTION] ELSE '(E1) Scenario Model'.Profit % To Apply[AVERAGE: '(E1) Scenario Model'.Profit Share Measures]

 

To explain I want to bring through the forecasted % for each division of the company, however, in the total company I want to apply the company % forecasted for that specific division which may differ for each division.

 

 

 

 

Tagged:

Best Answer

  • Jcorlett
    Jcorlett
    Answer ✓

    Hello @SPratt01,

     

    Not sure if I have correctly interpreted your question or the required functionality correctly but here goes...

     

    I would suggest that rather than use a nested if approach, which could require a multiple IF statements to capture the various Divisions of the Company (assuming Construction is not the only one), that you consider the following approach...

     

    Create Lists:  

    1. Profit Share Measures; Company Level, Division Level 
    2. L1 Companies; Company A
    3. L2 Divisions; Construction, Division B, Division C [**  Add a list property 'Company' with formula = PARENT(ITEM('L2 Division'))  **]

     

    Create Modules:

    1. Co Profit Share - input area for Co. Profit Share % (see top module in screenshot, could be calculated based upon Division Average / Weighted Average by Company Size etc.)
    2. Division Profit Share - input area for Division Profit Share % (see 2nd module in screenshot)
    3. Calculation Module / Example 1 (see 'Scenario Model Example 1' blueprint in screenshot)
      • Applies To: L2 Division
      • Time Scale: YEAR

     

    Essentially, I have defined a % at both a Company AND a Division level. I have then created a 'Co. Level Profit Share' Line item and used LOOKUP (**with reference to L2 Division.Company**) within the Calculation Module to return the Company Level % * Profit to Share. Additionally, I have created a 'Div Level Profit Share' which is much simpler as the module is dimensioned by Division.

     

    Alternatively, in Example 2 I created an additional module (see 'Scenario Model Example 1' blueprint in screenshot) to approach this in a slightly different way,

    • Applies To: L2 Division + Profit Share Measures
    • Time Scale: YEAR
    • 'Profit Share' Formula = IF ITEM(Profit Share Measures) = Profit Share Measures.Division Level THEN 'EBITDA / Profit to Share' * Div Profit Share.Div Profit Share % ELSE 'EBITDA / Profit to Share' * Co Profit Share.Company Profit Share %[LOOKUP: 'L2 Division'.Company]

     

    Screenshot 2019-01-29 at 11.56.10.png

    Screenshot 2019-01-29 at 12.18.02.png

    Screenshot 2019-01-29 at 12.18.12.png

     

    Hope this helps,

Categories