Formula change in Opening and Closing line items.

RISINGSTAR
RISINGSTAR
in Modeling

Hi Team,

 

I needed small help to optimize the formula from Half-Year to Full-year.

 

Current working System:   If Jan 2021 (Budget) H1 Opening HC we were referring to Closing HC of Dec 2020(Actual) and IF July 2021(H2) we were referring to Jun 2021(Actual)

 

Proposed System: If Jan 2021 (Budget) H1 Opening HC we were referring to Closing HC of Dec 2020(Actual) and IF July 2021(H2) we were referring to Jun 2021 Closing HC (Budget)

 

attaching the Screenshots of the Formula and model.

 

IF MONTH(START()) <= MONTH('Basis Month -W') AND ITEM(Customized Versions) = Customized Versions.Forecast

THEN

Derive Actual Fully Loaded Cost.FTE HC

ELSE

IF

ITEM(Customized Versions) = Customized Versions.'Budget 1' AND 'Department L5'.'Exception of Edit(for CE-POPS)'

THEN

CE Summary Report Module.'FTE(Agent/Suppot-Existing)'[SELECT: 'MAPA/PAPL'.MAPA, LOOKUP: 'City L4'.'Parent (Default City)']

ELSE

IF (START() = 'H1/H2 Control'.Yearvalue Budget Start Date[SELECT: 'H1/H2'.'H1'] OR START() = 'H1/H2 Control'.Yearvalue Budget Start Date[SELECT: 'H1/H2'.'H2']) AND ITEM(Customized Versions) = Customized Versions.'Budget 1' AND NOT 'Department L5'.'Exception of Edit(for CE-POPS)'

THEN

PREVIOUS(Closing HC[SELECT: Customized Versions.Forecast])

ELSE PREVIOUS(Closing HC)

 

 

 

Thanks,

Uday

 

 

Tagged:

Answers

  • AjayM
    AjayM

    Hi Uday,
    From the shared formula, I couldn't notice that you were fetching 'Actual' version values from previous month. I see you are using 'Forecast' version in the select clause. If that portion has no problem, the solution to your query lies in trimming the last If condition - just remove the H2 value filter condition, and it should give you the desired result.

    ….

    ELSE

    IF START() = 'H1/H2Control'.Yearvalue Budget Start Date[SELECT:'H1/H2'.'H1'] AND ITEM(Customized Versions) = CustomizedVersions.'Budget 1' AND NOT 'Department L5'.'Exception ofEdit(for CE-POPS)'

    THEN

    PREVIOUS(Closing HC[SELECT: Customized Versions.Forecast])

    ELSE PREVIOUS(Closing HC)

    Cheers!

    PS: Noticing that this is too old post, I am sure you have progressed with your Anaplan journey and been solving bigger issues :)

Categories