Solved

Modeling lock budget

  • 12 February 2024
  • 11 replies
  • 162 views
E
Rank
Userlevel 4
Badge +7

Hello,

I have created the budget based on historical data (budget version based on actuals version up to a switchover date), but at each closing period and when we import the new data, the historicals can vary so it makes my whole budget move… The uploaded data for the actuals is imported into several transaction and dimension lists.

It is not optimal as the budget should be closed as of now and not moving. Has anyone encountered the same issue? The idea would be to be able to use the budget version in comparison tables so it needs to be fixed in the app.

Hope this makes sense!

Thanks,

Elodie

icon

Best answer by Stef 12 February 2024, 16:01

View original

11 replies

Stef
Rank
Userlevel 4
Badge +6

Hi Elodie,

There are several possibilities here. The easiest one in your case is to use snapshots.
If you have enabled scenario's in your application you can also show snapshots in the same table as your live scenario. Just note that a snapshot can never be edited after its created.

Alternatively, you can also use Metric to Metric imports to import your data into a metric where you archive/save your budget values and break the live calculation that way. With metric to metric imports you're a bit more flexible if you ever want to change something in the archived data. 

Another alternative would be to create an “Archive” version of your transaction lists. But as you already have quite some, I don't think that will be the easiest solution. 

Hope this answers your question, feel free to reach out if you need additional context

Kind Regards, Stef
E
Rank
Userlevel 4
Badge +7

Hi Stef,

Thank you for you reply.

Regarding the first option, could you please be a little more precise on how I could use the snapshot in comparison tables actuals vs budget as I have created versions for actuals, budget and forecast, and not scenarios?

Thanks,

Elodie

Stef
Rank
Userlevel 4
Badge +6

Hi Elodie,

 

Of course. If you choose the snapshot/scenario approach you can create a view similar to the example below by selecting 2 scenarios at the same time. As you see the downside is that a snapshot copies your whole app. So you also have 2 actual versions now (which actually could be usefull in some cases). You can also use calculated items to either calculate the variance from version to version or scenario to scenario.
 


If you go for the metric-to-metric approach you're a bit more flexible. You can create a new metric with the same dimensionality as your current metric(s) where your budget is calculated. Then by using metric-to-metric import only import the data into it that is relevant. You can do that by creating a “technical” metric inbetween which filters out only the data you want. Example formula: 

Metric_with_budget_data [FILTER: Version = Version."Budget"]

This intermediate metric would be the source for your metric-to-metric import. 
The final metric with your import results can be shown in a table right next to your other metrics.

Kind Regards, Stef
E
Rank
Userlevel 4
Badge +7

Thank you Steph!

I have 2 questions following this reply:

  • Would it be possible to select which version we want to see for each scenario (i.e. hiding the Budget version in the Default scenario and the Actuals version in the Snapshot scenario)?
  • If I go for the metric-to-metric approach, does it mean I would have to duplicate every metrics in my budget app with the indicated formula, then erase the formula to have only hard values which should not move?

Thanks!

Elodie

Stef
Rank
Userlevel 4
Badge +6

Hi Elodie,

 

On your first question. Yes there is. If you follow this approach it should work. Added a GIF for your reference. 

  1. Create a filter metric (“Show” in the example) dimensioned by Version
  2. At the moment you create your snapshot make sure that ony “Budget” is TRUE on the filter metric
  3. After the snapshot is taken set only “Actual” to TRUE on the default scenario
  4. Add the filter metric to your table
  5. Filter the table on that metric as you see in the GIF to hide columns without a TRUE boolean in the filter metric
  6. Apply “Hide empty rows and columns”
  7. Optional but recommended: hide the filter metric from the view

 

On your second question: I'm not aware of your current application set-up but I'm assuming you have a metric where everything comes together (its often called Nexus). If you don't, you can create it (or a few), for example, create a metric where your full P&L is available on the level and with the dimensions that you need. 

You don't have to set a formula in your “archive metric”, you can use the metric-to-metric import to import the data from your “live” metric to your “archive” metric. Your suggestion to use the “store as values” with a formula will work in theory but it will be hard to update the values for e.g. your next budget cycle as you will recompute all the data once you put the formula back in.

Kind Regards, Stef
E
Rank
Userlevel 4
Badge +7

Thank you for this answer Stef.

I think I will try the first one as I currently don’t have any Nexus metrics, only tables for the P&L, key figures, etc for example.

Let me test this option and I will come back here if I have any other question 😊

Thanks for your help!

Nathan
Rank
Userlevel 6
Badge +11

and to clarify: are you reloading the same periods of actual and modifying the values of the periods you used for the budget calculation ?

If not, with a switchover date and a load date, you should be able to ensure your new loads don’t impact the past one

E
Rank
Userlevel 4
Badge +7

Hi Nathan,

Yes I have a switchover date fixed on 31/12/2023 so the budget won’t take any actuals past that date. 

The problem comes from the historicals before this date which can vary from month to month (for example when an accounting adjustment is booked).

Thanks,

Elodie

Stef
Rank
Userlevel 4
Badge +6

Brings an idea to mind.. are those accounting adjustments identifiable by one more data points? If you can recognize them by a journal type or something similar you could exclude them in your actual data or from the base of your forecast. 

Kind Regards, Stef
Nathan
Rank
Userlevel 6
Badge +11

if your accounting system has a creation date for entries and is not modifying past one, you could exclude new entries, even if they are from past periods.

 

Finally, another way: depending how the transaction list is used, you could make a copy of it and change the downstream formulas to force budget to use the copy TL

 

ps: solution given by Stef works, just sharing all possibilities

E
Rank
Userlevel 4
Badge +7

Thanks Nathan, however this problem doesn’t only concerns P&L data, bu also number of clients for example, and it is not possible to use this method.

I think I will try the scenario / snapshot method and let you know if I have any problem :)

Thanks,

Elodie

Reply


Ask your question here!

Terms & ConditionsCookie settings