One of the features that FP&A managers like to use a lot is the ability to create alternative scenarios, such as ‘best case/worst case’, or a scenario with the same data but with alternative FX rates. 

For variance reporting for example, you often want to eliminate the differences in FX to see what are the underlying variances in performance. 

Most EPM/CPM tools will allow you to create various data scenarios, populate them with data and use them in variance reporting. On this topic I’ve seen the following caveats in previous EPM tools: 

• You don’t want to rely on a system administrator to create new 'sandbox’ or experimental scenarios every time. 
• You don’t want to duplicate the same FX rates into several scenarios. 
• You don’t want to rely on a system administrator to keep copying data between scenarios. 

That Venus vs Mars thing again? 

In an earlier blog I described previous generation EPM tools tended to create a culture of “Consolidation consultants are from Venus, Planning consultants are from Mars”. Some vendors of EPM suites claim that this separation of tools (i.e. a separate disconnected consolidation application and a separate disconnected planning application) is still the way forward, and this particular topic of creating lots of plan versions or alternative scenarios is a good example of their reasoning:  In a disconnected and dedicated planning tool, you can go and experiment with scenarios, versions, FX rates, and be sure it’s not going to trigger audit issues on the financial close tool. 

However that’s no excuse for EPM vendors to evangelise for a suite of disconnected EPM products. With the freedoms that came with being able to experiment with a separate planning application, would only delay the inevitable, that you would have to perform variance reporting and then explain why your planning application shows different numbers than the consolidation tool, even if you frequently synchronize all the data between applications. 

I will explain later how in OneStream you can have the best of both worlds: that is the ability to create dynamic experimental scenarios without changing master data, and also maintain only one unified application and keep your data in one place.

Actual at Budget Rates, PY rates, and other Variance Scenarios 

You probably know already that with OneStream, when creating additional scenarios for constant rate analysis (e.g. Actual @ Budget Rates), you don’t have to copy the FX rates across the scenarios. 

One advantage of OneStream vs other EPM products, even vs some other ‘new’ Cloud EPM products, is that with OneStream you don’t have to maintain multiple copies of FX rates just because you create extra scenarios.
For example: 

With OneStream you don’t have to make copies of the same FX rates. 

Although OneStream is not unique in being able to share one copy of FX rates between several scenarios, there are other EPM suites out there that still do not provide this capability. 

Making your own variance scenarios through a dashboard 

I use the term ‘variance scenario’ to mean an alternative view of the same data, but mixing with different rates or time offsets. 

For variance scenarios in OneStream you can make a dashboard where FP&A managers can quickly experiment, for example seeing the effect of mixing a budget or forecast with different FX rates, or comparing with actuals at budget rates. After all, these additional scenarios are basically a combination of four parameters: 

1. a) Originating Data Scenario- e.g. Actual, Budget (AOP), Latest Forecast
   b) Data Year offset- e.g. Current Year, Prior Year
   c) FX Rate table to use- e.g. Actual Avg Rates,  Budget Rates
   d) FX Rate year offset- e.g. Current Year rates, Rates prior year 

The following simple dashboard shows how an FP&A manager can create their own experimental scenarios without requiring assistance from a system administrator. 

Here you can select any combination of originating scenario, year offset, rates to use, etc, then press a button and quickly see how your data consolidates with the different mix of rates. 

Notice also I have provided an “Active Y/N” flag, so you can quickly enable or disable sandbox scenarios. This prevents excessive buildup of items in drop-down lists, and also prevents excessive number of scenarios to have to re-consolidate when you press “process cube”. 

With this dynamic scenario setup, there are two options regarding how this impacts the master data. 

Dynamic Metadata on-the-fly 

OneStream can dynamically create metadata on-the-fly. Obviously this is fully audited and shows up in the metadata audit reports, and also you would not want to give end-users full control on managing metadata. However you can control what users can / cannot create through careful consideration on dashboards. In this example shown above the dashboard allows an end-user to create scenarios on-the-fly.  

However, you may not want to allow metadata to change at all during some critical periods in the close cycle. 

Sandbox Scenarios without Metadata Changes 

In this option, you still provide end-users the same sort of dashboard to create sandbox variances, based on a copy of data in an existing forecast, then use a mix of different FX rates, year offsets etc. The difference now is that we want to provide this without actually changing the master data/metadata. 

Instead of dynamically creating physical scenario members on the fly in the metadata, you create a pre-defined set of dummy scenarios, like so:

This begs the question, how can an end-user configure these sandbox scenarios through a dashboard, without changing metadata, and without being aware of these unfriendly, internal ‘SandboxXX’ names? 

For naming the scenarios, or giving them a meaningful name, like “latest forecast at prior year rates”, obviously if you update the description in the metadata then it triggers a metadata change. Keep doing this several times each month and you may have a lot of explaining to do in front of the auditors. So that’s not really an option. What you can do in OneStream, is setup a lookup table (much like Excel VLOOKUP function), where the application looks up the meaningful name to present to the users.  With dashboard parameters, you can tell OneStream to lookup a relational table, which links the ‘dummy’ SandboxXX name to the meaningful name. This relational lookup table is separate from the financial cube, so can be updated without triggering audit changes or making any changes to the metadata, after all it is just a description lookup. 

Now you can show meaningful names of sandbox scenarios in reports or drop-down boxes. The user can keep creating new names of new sandbox scenarios and not trigger a single metadata change. 

In the drop-down list shown above, the scenario “AOP at PY Actual Rates” is actually the scenario “Sandbox01”, but we’ve made the parameter display show the friendly lookup-name. If you want to get technical and look under the hood, here is the lookup table I have used in this example:

Dynamic FX rate assignment 

Now the last challenge. How do you create a new sandbox scenario, and tell it to use a different set of FX rates, without changing the metadata? 

This can be done using the underlying relational table, and a small business rule to tell OneStream to perform translation at the rates specified in this table, rather than the rate type specified in the metadata. 

With a translation business rule you can look-up the appropriate FX rates by referring first to this lookup table. This means you never have to configure the sandbox scenarios to FX rate types in the metadata. It’s all done on the fly. 

The above snippet shows how you can use a custom lookup table, in a function I have called GetRatesToUse, then simply pass-on those rates to the default currency translation engine that comes out of the box. 

Summary 

By being able to dynamically associate scenarios with different FX rates on the fly, and lookup user-friendly descriptions on the fly, you can give your FP&A users a lot of flexibilty in creating sandbox versions of plans, or other experiemental scenarios, without creating excessive demand on system administrators, and reducing the need to update metadata.