Technical Articles
The beauty of Inverse formulas and the Value Driver Tree to perform “What if” analysis
Have you ever wondered what if? What if I would have taken that job offer, what if I would have said yes… I know I sure have. Personally, I haven’t found a solution yet, but in professional life there is a way to make this contemplation effective in business decision making using so called “What if” analysis.
In this blogpost I’d like to take you through two powerful functionalities in SAP Analytics Cloud that help you answer the question of what if: the Value Driver Tree and Inverse calculations. The Value Driver Tree is a clear visualization of the flow of data in your model and the Inverse calculation let’s you manipulate the flow also across calculated fields. Let us delve deeper into this functionality using a personal case.
The case
I wouldn’t dare call myself an entrepreneur as I’ve worked for an employer all of my life, but thinking back to my youth I have spent some weekends selling plants from my parents’ driveway. This was an ingenious business model where I was able to have no procurement cost due to my grandmother’s garden providing an ample supply of plants, and no labor cost as my parents slaved away making sure inventory was nicely trimmed. I just needed to take care of product management and sales which might have been a preamble to my future but that’s another story.
What we’ll do is take this case of marvelous business ingenuity, and now start asking my 8 year old self, what if?
Modelling
Taking the case as is would be relatively simple to model in SAP Analytics Cloud. Therefore, we’ll create a model with only 3 accounts indicating the simplicity: Price, Volume and Revenue. As these three held all the business metrics I ought to be relevant back then.
Start with a blank model
Add a generic dimension
Choose the Account dimension
Opening the dimension adding the three accounts as well as the formula.
Put in the Account information
Now let’s save this model and see what it looks like.
Value Driver Tree
To see what it looks like I will create a story with a canvas page and add a Value Driver Tree widget referring to the just saved model.
Create a Story
Add a Canvas page
Add the Value Driver Tree
Here I get the option to auto create the Value Driver Tree from the model which I’ll do selecting all accounts.
Auto-create Value Driver Tree
Select all accounts
Value Driver Tree created
Entering some numbers would give me the view of a booming Sunday’s business results.
Entering values
Here I can ask myself the first question: What if I’d increase my price with 10%, what would this mean for revenue? Using the Value Driver tree, the answer is delivered to me immediately! Back then I had to ask my father – my chief analyst at the time – for these insights, so this would’ve been a true time saver!
Percentage increase
Review results
Now let’s challenge Nick jr. a bit more, what if my grandmother got fed up with her garden shrinking and started asking a fee per plant? If we’ll add some accounts here and update the Value Driver Tree we’ll find out!
To accommodate this major shift in supplier relations we’ll add three more accounts and corresponding formulas: Cost price, Procurement cost and Gross Profit.
Adjusted accounts
Generating another Value Driver Tree will now answer this question to see if the time invested still warrants the projected profits. Given this setback some extra volumes would be needed to reach the target of at least €20 of Profits per day. Given the Gross profit node is a formula, it cannot directly be edited. This is where the Inverse calculation comes in, where we can make the non-editable, editable.
Newly created Value Driver Tree
New values
Inverse your problem to a solution
In the formula section of the Account dimension in our model we can add Inverse functionality. The Inverse functionality allows entry on formulas, because in the Inverse selection we explain how to make changes with the entry to the underlying accounts of the calculation. In below example you see that I’ve added Inverse functionality to the nodes that I’d like to be editable. As Grandmother won’t budge, I’ve chosen to only make Revenue and Gross profit editable, by increasing either node, Volume will now increase accordingly – or decrease naturally –.
Adding Inverse is simple as a tooltip explaining the syntax and providing options will automatically appear. Note that I’ve added the full formula in Gross profit to make sure everything can be specifically assigned.
Inverse Tooltip
Final formula view
Final What if questions I can now ask are: What do I need to do to get my desired level of €20 Gross Profit? How do I get to €30? The Value Driver Tree answers, and Nick jr. is ready to do business!
€20 Scenario
€30 Scenario
Conclusion
Above example is naturally lacking in complexity, it however does show the beauty of the Inverse functionality combined with the Value Driver tree. I’d encourage you to explore this further for your own business scenario, adding more complex formulas with INVERSEIF and OR statements to capture the full complexity of the real world as best you can. You’d be amazed what excitement this sparks in the business user to be able to answer “What if” scenarios in such a flexible and visual manner.
Unless you fix locking issue for VDTs (locked actuals are updated when flexing yearly figures) - that part of SAC is pretty much useless for planning projects where top-down planning needs to take place
Hi Krzysztof, thank you for your critical point, I would agree locking should be respected also by the Value Driver Tree. I would argue through that for forecasting and planning in the process the Value Driver Tree already has its value for What if analysis, as I've seen it in use and spark enthusiasm.
Make sure you vote on the SAP customer influencer portal to help the development team prioritize. Thanks again!
Hello Nick,
I have already submitted an enhancement and people can vote for it here:
https://influence.sap.com/sap/ino/#/idea/262330
IMHO, this is a bug, not an enhancement. Locks should be enforeced at the model level. VDTs are just a different way to run an allocation/make a data entry into the model. There is also a workaround for that based on inverse formulas with lots of dummy accounts but the maintenance effort involved doesn't make much sense to go that way.
I would agree with you that VDTs are used and spark enthusiasm but mostly during presales activity. Locking is just one issue, there is also limited way to influence VDTs with APIs in apps.
Planning panel + direct data entry is the best approach based on my experience. VDTs only in a limited way.
Regards,
Hi Krzysztof,
I'm pleased to share that the upcoming QRC with data entry disaggregation respecting data locks
will also support the Value Driver Tree: Roadmap item here
Nick
Hi Nick Verhoeven
if you had more than one dimension in your modell how would you adress the question of exception aggregation in regards to the price for instance? If you had a product dimension and different prices for each product you would need to define an exception aggregation of type "sum" to make sure that the formulas are calculated on the lowest level.
But then you would loose input-readiness in your VDT and tables on the gross profit?
Best,
Fabian
Hi Fabian,
to have calculations happen at the lowest level I would imagine data actions would be the best fit.
Nick