Using a IF THEN ELSE formula and LOOKUP formula to solve a Customer Buying Group mapping question

gcaldis
gcaldis
in Modeling

Hi there

 

My organisation's Anaplan model was originally built to cater for a "Bottom Up" sales plan , and I am amending it to also cater for a "Top Down" sales plan, when required.

 

We have 2 modules in this model, TOP DOWN and TOTAL SALES.

 

TOP DOWN - Only has one Customer Buying Group code called "TDB" - short for Top Down Budgeting.

TOTAL SALES - Has multiple Customer Buying Group codes (e.g. "KO3", "M04"  etc)

 

Once particular problem I have is how to write the expression which says.....

 

IF the "TOP DOWN" module's line item "Customer Buying Group" code of "TDB" is = 0 (i.e. $0 sales),

THEN show the "TOTAL SALES" Customer Buying Group code (e.g. K03, M04) Sales $ amounts,

ELSE take the Sales $ amount  from Customer Buying Group "TDB" (in which case all other codes e.g. K03, M04 etc should show $0 Sales).

 

Would I need to reference an ITEM or LOOKUP formula within a normal IF THEN ELSE formula to achieve this?

 

Thanks

George

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @gcaldis George,

    Awesome use case, and nicely written up. Here's an idea:

    • Create a system module for the buying groups.
    • Create a BOOLEAN line item called "TBD Related?"
    • Create a list formatted line item called "Buying Group" using the buying group list.
    • Go into grid mode and set the TBD? to TRUE for the all the buying groups that you want to sum up if TBD is zero.
    • Add another line item in TOTAL SALES called "Sales Replace" = IF TBD Related? = TRUE then Sales ELSE 0. This will sum up only the buying groups you want.
    • We already know the TOP DOWN module is only for TBD so we don't have to do anything special to find it
    • In the TOPS DOWN module add a line item "New Sales" = If Sales = 0 then TOTAL SALES.Sales Replace[SUM: System Module.Buying Group] ELSE Sales

    This is a little rough. If you run into any issues, send a note back here and I'll create an example for you in Anaplan. 

    Sounds like a really neat use case. Let's get this!

  • gcaldis
    gcaldis

    Hi Jared

     

    Apologies for the late response to your message.  I've had other organisational priorities over the past 5 months to attend to, but am now back working on Anaplan, in particular integrating our new Top-Down planning process into our main Anaplan FP&A model.

     

    Many thanks for your response. I've read through it but am still unclear on how to incorporate some parts of it.

     

    Please see the attached EXCEL file with the use case and Anaplan line item formula I am trying to write to achieve the desired outcome.

     

    Would appreciate your expert assistance here.


    Regards

    George

     

Categories