Forum Discussion

NicoleBruno's avatar
NicoleBruno
Valued Contributor
3 years ago

Forecasting Adjustments

Hello!

I have a question about other customer experiences and best practices regarding forecasting adjustments. We have forecast scenarios set up and previously would copy the entire scenario as the base for additional “adjustment” scenarios. We used these to toggle on or off certain risk adjustments in future periods but in order to save space and stop replicating so much data, we’re trying to move to another method instead. In our application, we have our UD3 set up as a more detailed Origin dimension where specific layers of adjustments are added (journal entries are split into more detail for GAAP or non-GAAP for example). This is where we were planning to add the adjustments but I wanted to check in with everyone else first to see if anyone has experience.

Basically what we want is one scenario where I can pull a UD3 “base” of the expected forecast data then “base+adj1” which would be an alternate hierarchy of the base data plus adjustment 1. Then we’d replicate that alternate hierarchy as needed for specific adjustments.

Would love to hear how you’re accomplishing this - any feedback would be appreciated!

Metadata
Methodology
  • nblades's avatar
    nblades
    New Contributor III
    3 years ago

    Hi Nicole,

    I have seen this with many of our customers where we (or another implementation partner) leverage a User Defined Dimension to capture a more granular level of detail - normally called “Data Type” or something like that can provide a more detailed level of information for adjustments - GAAP, IFRS, etc. The approach you have described above should work great for layering on Base + X to get to a final plan/budget/forecast.

    Nick

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      Hi Nick,
      Thanks so much for the feedback! Is there any more efficient way that you know of? Just wanted to make sure I cover my bases with my brainstorming.

  • AEdwards's avatar
    AEdwards
    New Contributor II
    3 years ago

    Hi Nicole, how about using scenarios for adjustments and stacking them together. So you would have one Forecast scenario and layer on top M1adjustments, M2Adjustments, etc and then create a parent scenario to combine them together, so you can always pull the latest total.

    Forecast2WithAdj
    Forecast2
    M3Adj
    M4Adj
    Etc.

    • nblades's avatar
      nblades
      New Contributor III
      3 years ago

      Hi all,

      I once had this same thought. The Scenario dimension is a bit unique as it does not consolidate up to the parent. The following statement is pulled directly from the Design and Reference Guide: “Scenarios can be shown as a part of a hierarchy if desired, but this is only for organizational purposes. They do not consolidate.”

      The way our implementation teams have tackled capturing varying adjustments in the past is by using one of the available User Defined dimensions. Hope this helps! Nick

      • AEdwards's avatar
        AEdwards
        New Contributor II
        3 years ago

        Hi Nick, you are right that scenarios don't consolidate out of the box, but it can be done via a simple formula, this is how we handle combining 2-3 scenarios into one.

        Here is an example of the formula: api.data.calculate("S#[AOP.With.Hedge] = S#AOP + S#[AOP.Hedge]","A#IncStmt.Base,A#BalSheet.Base")

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      Hi Alona,
      Thanks so much - that’s a great idea. Similar to the UD3 stacking/hierarchy but cleaner because now I won’t have to worry about slotting in the adjustments against existing UD3s in the scenarios. Great suggestion!

  • jeffzamorski's avatar
    jeffzamorski
    New Contributor II
    3 years ago

    Do you find that the UD3 is necessary for adjustments versus the origin dimension? I’ve raised this in our design sessions and it seems like the origin dimension may work, but I’d rather set things up properly the first time rather than need to add the dimension later if we realize the origin dimension doesn’t suffice.

    We make so many forecast/budget adjustments in our current tool which I have a separate dimension for layering, so my thought was to include a separate dimension in OneStream as well.

    I was talking to our SI and talking through using a separate dimension, or the combination of a new scenario and the origin dimension.

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      Hi Jeff,
      I went with UD3 because we already had UD3 as our datatype dimension which is essentially a more built out origin dimension. The origin dimension is specific to OS itself and cannot be edited. And regarding scenario, to Nick’s point above, the scenario dimension doesn’t aggregate/consolidate. Hope that’s helpful.

      • jeffzamorski's avatar
        jeffzamorski
        New Contributor II
        3 years ago

        Thanks for the info! Definitely going to have to consider adding a UD for the data types since the origin dimension is not customizable.

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor
    3 years ago

    So in my application I have UD1 set up as a Data Type dimensions. All Forecast inputs go in at Local_GAAP. There was a need to create multiple version, but we didn’t want to change the original input Budget data. I created a shared Top member that rolls under the Simplification rollup, and I layer on the adjustments, so the original scenario if it’s Budget or a forecast version doesn’t change. I do a similar type layering for my Proforma data.

    Posted by Amy Funari

    • Mike_Sabourin's avatar
      Mike_Sabourin
      Contributor II
      3 years ago

      I’ll echo Amy’s input and say for one of my clients, we created Budget as an overall Data Source in UD1, but added BUD_Form, BUD_Adj, and BUD_CorpAdj as additional levels to see the different pieces that make up the total budget number.

  • coneill's avatar
    coneill
    New Contributor III
    3 years ago

    This approach is a great idea. We don't currently use this approach, so I don't have anything to say regarding that experience. But I did have something to add to the conversation.

     

    UD3 DIMENSION

    TotalForecast (member)

       Base Forecast (member)

       FcstAdj1 (member)

       FcstAdj2 (member)

       FcstAdj3 (member)

     

    You could use one of the UD3 text fields (say txt1) to toggle off an adjustment. In this way, you could remove it from the ending forecast (but have it still available to add back in) with a simple update to the txt1 field and then reconsolidate. 

    Also, you would have to update the consolidation formula to be something like:

    TotalForecast = Base Forecast

    If FcstAdj1 Txt1 <> "'exclude from forecast" Then TotalForecast = TotalForecast+FcstAdj1

    If FcstAdj2 Txt1 <> "'exclude from forecast" Then TotalForecast = TotalForecast+FcstAdj2

    If FcstAdj3 Txt1 <> "'exclude from forecast" Then TotalForecast = TotalForecast+FcstAdj3

     

    This could also be integrated with cube view reporting as well, using showing accounts in the rows and UD3 values in the columns along with the ud3 txt1 value in the first row of data. You could also hide columns that have txt1 values of "exclude from forecast", in the cube view if you prefer.

    • NicoleBruno's avatar
      NicoleBruno
      Valued Contributor
      3 years ago

      Hi - thanks for the suggestion. I understand the set up but that would seem to be more work (editing text fields which is an admin only function at my co) and also less transparent to the user as they would have to log into OS dimension library to see the set up and what's included in the TotalForecast when they pull it. Alt hierarchies in UD3 would seem to provide the same result in a more straightforward manner with less maintenance as far as I can tell.