YoY Change % to forecast a line item from actuals

Assume, I have the following line items using which I am trying to build a prediction/forecast for "Revenue". 

 

Revenue --> Actuals are imported for the actuals period, and this should be the forecast for the forecast period.

YoY Chg% --> Imported input data that tells the % change in Revenue YoY for that month, for the forecast period alone.

TimeControl Module.IsActuals ---> My system time control module's line item called "IsActuals" is a 1 or 0 flag, that tells whether a particular month is 1 for "actuals" or 0 for "forecast" and is calculated based on the time switchover setting. 

TimeControl.PreviousYear --> System time control line item called "PreviousYear" that gives the exact date 1 year prior to START(). 

 

I want the Revenue line item to behave as follows:

 

Revenue = IF TimeControl.IsActuals = 1 THEN <Do nothing> ELSE Revenue[LOOKUP: TimeControl.PreviousYear] * ( 1 + YoY Chg%) 

 

For the do nothing time period, I would want to be able to import the Actuals. 

 

How do I achieve the above? I have traditionally done this by using anotehr line item called "Revenue Actuals" where I upload the actuals, and the Revenue (Forecast) will be Revenue = IF Revenue Actuals <> 0 THEN Revenue Actuals ELSE Revenue Actuals[LOOKUP: TimeControl.PreviousYear] * (1 + YoY Chg%).

 

I am looking for advice on what is the best way to achieve the above, and if it is possible to do using one line item for Revenue instead of using another Revenue Actuals line. 

 

 

Answers

  • Hi @visivasa 

     

    They way you currently doing things is very close to the solution using the formula method.

     

    The best way to achieve your condition for 'Do Nothing' and import actuals instead is too:

    1. Create a line item that has the actuals in it and then import the actuals into that line item. Essentially always having them preloaded in the background.This is already the revenue actuals line item you have created so could be reused. 

    2. In the formula you suggest for the 'do nothing' part then reference the line item created above which is the revenue actuals. 

     

    The other method which is more advisesable but again depends on your architecture and use of native anaplan versions. 

    Is to use Anaplan native switchover functionality, which essentialy switches between an actual version and budget or forecast version. Then you're able to load in the actuals up to the switchover date and then from this point onwards the revenue is forecasted using your calculation method.

     

    Switchover explained here:

     

    "Select the checkbox if you want the switchover date defined for model versions to be applied to a line item. A line item must have versions applied for the checkbox to be enabled. The list of available time periods depends on other settings in your model; see Time Period Selection.

    You can set the switchover date for versions in Model Settings > Versions.

    Switchover does not apply to the Actual version."

     

    Some way down the page but link: 

    https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Modules.html

    https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Blueprint.html#UseSwitchover

     

    I hope this helps and if you need any further explanation please do ask!

     

    Thanks,

     

    Usman

  • @usman.zia -- Thanks for your response! 

    For several modeling reasons, certain modules and line items are not leveraging the Anaplan native versioning as it does not serve our internal purposes. The example I gave you was not for Revenue but something else actually - I just called it Revenue for discussion purposes.  I agree that for Revenue or something as straight-forward as YoY Change that it does makes sense to use the internal versioning.

     

     

  • Hi @visivasa 

     

    I see, it is quite common to not always use the native versions in Anaplan and instead create a dummy versions list. 

    So the best option for the formula described above is to have another line which stores the actuals and then to have the fomula which switches based on the condition of the timing or the actual cell <> 0. 

     

    I hope this helps to support your solution.

     

    Kind Regards,

     

    Usman