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.

Creating a Blank model from scratch and adding only 1 dimension, the Account dimension

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.