Reporting Month over Month

PrevContributor
PrevContributor
in Modeling

All: Anyone have any suggestions or options for building a reporting that is MONTH versus MONTH, inside a specific version or across versions?  Our customer requires an ability to compare JUN FCST 2014 against MAY FCST 2014. or JUN FCST 2014 against JUN FCST 2013?   We have ideas, but are looking for external ideas from the larger community. Thanks in advance. Petar

Tagged:

Best Answers

  • PrevContributor
    PrevContributor
    Answer ✓
    Hi Petar,

    You could create a module that does not have Versions as a dimensions and use the folowing line items for the case of Jun 2014 FCST v May 2014 FCST:

    Line Item 1:     Module.Line Item[SELECT: Versions.Forecast]

    Line Item 2: Post('Line Item 1', -1)

    Line Item 3: Line Item 1 - Line Item 2

    For Line item 2, you can substitue the -1 for any number of periods you wish to go back, e.g. -12. The syntax is POST(Value, Number of Periods).  A negative number will return a period in the past whereas a positive will pull a period in the future.

    Thanks,
    Ryan
  • simon_ritchie
    simon_ritchie
    Answer ✓
    Hi Peter,
     
    If you need to allow the users complete flexibility to select any 2 months to compare then the below might be of interest.
     
    We had this requirement recently working with a CPG company who needed to analyse the changes between months/versions and perform a Volume, Price & Mix Variance calculation.
    Below is one of a few different ways you could model it in Anaplan.
     
    This is what my sample source data module looks like. It’s called “PL Summary”
     **See screenshot**
    image
     
    Create a new list, I called mine Months (Properties: Start Date, End Date)
    image
     **See screenshot**

    Create a new module, this will be used by the user to select the two months to compare
    Add 4 Line Items:
     
    These are for user selections
    [list=1]
    Base Month, Format: List.Months
    Comparative Month, Format: List.Months
     
    These are to lookup the selected months properties
    [list=1]
    Base Date, Format: Date, Formula: Months.Start Date[Lookup: Base Month]
    Comparative, Format: Date, Formula: Months.Start Date[Lookup: Comparative Month]
    **See screenshot**
     
    image
    Using the selections we can pull the data from the source module into a Comparison module.
    Create the comparison module with the same dimensions as the Source, don’t include Time or Version
    Add the following line items
    [list=1]
    Base
    Comparison
    Variance
    Variance %
    **See blueprint screenshots**
    Formula for the base Line Item is: PL Summary.Value[SELECT: VERSIONS.Actual, LOOKUP: Comparison Selection.Base Date]
    Note: In Anaplan time is built in, so by saying, Lookup a date, it will grab the number for the month that the date is in.
     
     
    image
    I added some variance formulas and some conditional formatting on the Variance %, then created a dashboard.
    The user can now compare any month, against any other month.
     **See screenshot**
     
    Notice that I published the Month Selection line items to the dashboard and placed them at the top of the dashboard to make the user experience nicer.
    image
    Note: This approach only allows 1 choice of months to compare, it works well in a single/low user scenario. Where you have many users that want to analyse the results and select different months to compare they will trip over each other. The was around this is to introduce another list into the Comparison Modules and use selective access so that each user has their own item in this list. Hope that I have explained it clearly.
     
    For simplicity, the above assumes you are only comparing Actual data only. If you need to allow the user to compare different version/month combinations then you would expand on the above approach to allowusers to select a Base and Comparative version also and tweak some of the formulas.
     
    Get in touch if you have questions.
     
    Hope it helps.
    Kind regards,
    Simon

Categories