How to create a comparative analysis model using lists?
I was looking into ways to create a model that allows end user to perform whatif analysis and compare the results (end user can choose multiple scenarios to compare). The user can select which version to compared via a list (list may contain members: Budget, Forecast, Forecast 1, Forecast 2, Forecast 3). Is it possible to build such model without using versions? I thought about it over the weekend and I couldn't figure out how I should build it. Can someone please provide some insights on how it should look like and how I should build it?
I found another thread on similar ask but it did not go into details:
One way to create a WhatIf analysis is to take your final planned/forecasted amount and move it to a module (let's call it WhatIf module) with the same dimensions as the original forecast module with an additional dimension for scenarios (baseline, best case, worst cast, etc). You can then have a system module (with only scenario list as a dimension) that lets the end user input % changes for each scenario.
The formula in the WhatIf module that pulls in the original forecast data should be equal to the original amount + (original amount * % change from the scenarios system module). As you select different scenarios (best case, baseline, worst case, etc.) in that module you should see your numbers change. Attached are some screenshots for a visual.
P.S. - If this solution does not work with your needs, it is also possible to do something like this using a Line Item Subset
Re: How to create a comparative analysis model using lists?
@anirudh, so for my organization, they have to reforecast weekly based off new actuals, so if I use versions to track each new reforecast, then I would have 52 versions in one year. Someone did that before and it significantly slowed the performance of the model.
It is very much possible to create a custom version dimension. I have seen many projects using custom versions and be successful in what they wanted to achieve.
Version is a list/dimension in short, so you can replicate it yourself. But you need to be aware of what you are loosing - readily available version comparision formulas, switchover (anyways most of us are not big fan of switchover), formula scoping based on version, etc. But you can achieve all these by model building on the custom version, so its just additional time & efforts. Boils down to "why you do not want to use the standard version" as asked by @anirudh .
1. Create a custom version list
2. Create a SYSTEM module for your custom version management - Flags for Budget, current version etc, switchover Period of the version, etc
3. Add this custom version as a dimension in your modules where you would do comparision analysis
4. Create comparision report - This can be creative as per your needs. You could have the user dynamically select two versions and do a comparision on the go, or it could be pre-built (Budget Vs Current, etc)