Skip to Content
Technical Articles
Author's profile photo Nick Verhoeven

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%20with%20a%20blank%20model

Start with a blank model

Add%20a%20generic%20dimension

Add a generic dimension

Choose%20the%20Account%20dimension

Choose the Account dimension

Opening the dimension adding the three accounts as well as the formula.

Put%20in%20the%20Account%20information

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%20a%20Story

Create a Story

Add%20a%20Canvas%20page

Add a Canvas page

Add%20the%20Value%20Driver%20Tree

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%20generate%20the%20Value%20Driver%20Tree

Auto-create Value Driver Tree

Select%20all%20accounts

Select all accounts

Value%20Driver%20Tree%20created

Value Driver Tree created

Entering some numbers would give me the view of a booming Sunday’s business results.

Entering%20values

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%20increase

Percentage increase

Review%20results

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%20accounts

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%20created%20Value%20Driver%20Tree

Newly created Value Driver Tree

New%20values

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%20Tooltip

Inverse Tooltip

Final%20formula%20view

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!

%u20AC20%20Scenario

€20 Scenario

%u20AC30%20Scenario

€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.

 

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Krzysztof Grzymala
      Krzysztof Grzymala

      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

      Author's profile photo Nick Verhoeven
      Nick Verhoeven
      Blog Post Author

      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!

      Author's profile photo Krzysztof Grzymala
      Krzysztof Grzymala

      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,

      Author's profile photo Nick Verhoeven
      Nick Verhoeven
      Blog Post Author

      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

      Author's profile photo Fabian Runge
      Fabian Runge

      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

      Author's profile photo Nick Verhoeven
      Nick Verhoeven
      Blog Post Author

      Hi Fabian,

       

      to have calculations happen at the lowest level I would imagine data actions would be the best fit.

      Nick