Best method for Scenario Planning?

Fletch

Hi All,

I am currently in the process of investigating the best methodologies for building in scenario planning into one of my models. After some internal conversations and a little community hunting, I thought it would be best to write a new post.

For context the model i am building scenarios into will require several modules to be modified to allow users the ability to flex volumes or rates but what is the best way to allow users to interact against the base plan to then see comparative analysis dashboards of base plan vs scenario? Very conscious to not add complexity, duplication or increase model size drastically.

Currently I thought I could build additional modules that will house base plan with a version list to allow multiple scenario versions to be created. Adding in additional override line items to calculate through to a new total (if Boolean ticked take scenario volume else take base plan). OR would adding a new version into the model be best using a new list built into the existing modules be a better option?

Any advice would be really appreciated

Thanks Mark

AlejandroGomez

Hi @Fletch

As rule of thumb I think you are on the right track, I would suggest the following:

1. Use a regular list as opposed to the built-in “Version” dimension for the scenarios / versions: in this way you can create subsets (among other benefits such being able to use SUM / LOOKUP) to reduce space in case not all scenarios are applicable to a module(s).
2. Then the easisest way forward would be to copy the modules containing the data (baseline) so it remains unchanged and safe, and then add the scenario dimension to the copy modules. The original one will hold the “baseline” and the copy can be dimensioned by Scenarios and calculate the impact of the different drivers on your baseline for each scenario (these new copied modules will include the override line items, too).

Cheers

ALex

AlejandroGomez

additionally, not all Line Items (or parts of a formula) are impacted by the Drivers or Assumptions of the scenarios.

To use the old example of price * volume = sales, it could be that the type of scenarios you want to create will only impact the price of your goods, so you could create the new module with just “price” and the “scenarios”.

Then you can create a final reporting module (without scenarios) where “volume” comes from the baseline module (alwyas the same value, assuming that is not impacted by your scenarios) and the “price" is retrieved from the module mentioned in the paraprapgh above using a LOOKUP pointing to a LI dimensioned as “scenarios”.

The shortcoming is that only one scneario can be dispalyd at a time depending on the scenario chsosen in the LI mentioned above. The benefit is that you are not using a single cell more than you really need in order to calculate the impact of your scenarios.

Fletch

Thanks for your reply Alex. This is the theory that I was working with originally and have build conceptually. The challenge that I face is that the process that I have is quiet long with alot of underlying detail so duplicating the required modules for the process will be increase the module size considerably. But I may just have to see how this one plays out when building.