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.
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?
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.
Opening the dimension adding the three accounts as well as the formula.
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.
Here I get the option to auto create the Value Driver Tree from the model which I’ll do selecting all accounts.
Entering some numbers would give me the view of a booming Sunday’s business results.
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!
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.
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.
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.
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!
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.