This blog has been inspired by the requirement of one of our customers to analyze in detail the impacts of price, quantity and exchanges rates impact on Stock Keeping Unit (SKU) reported by end users in different transaction currencies of their revenue.
The purpose of this blog is to give an example on how to configure the calculation of the detailed analysis of the different impacts.
It demonstrates the capabilites of the new model measures and data actions calculations (including currency conversion steps).
Illustrated example in SAP Analytics Cloud
The end user inputs per business unit per transaction currency the price and the quantity of the SKU.
The outcome converted in Reporting Currency (here Euro):
The challenge brought up by end users is to analyze the delta amount between two periods and to determinate in Reporting Currency what is the part of
- the quantity variance,
- the price variance,
- the exchange rate impacts on variances (price and quantity)?
Design requirements
This example shows the impacts of the quantity and price modifications between March and February for one SKU and one transaction currency.
Transaction Currency
The difference of 66 USD in the Revenue can be split in 3 parts: 30 USD due to the increase of the price of the SKU, 30 USD due to the increase of the quantity sold and 6 USD due to the price variance applied to the quantity variance between these two periods.
Reporting Currency
The table below identifies how to analyze and split the amount of 175,20 Euros between the different variations of the quantity, the currency exchange rate and the price.
SAP Analytics Cloud provides in one click in a story this sophisticated analysis, offering a basis for simulations on revenue for worldwide companies having a high exposure to currencies rates variations in different selling markets.
How ?
Configuration in SAP Analytics Cloud
This example has been developed using the New model measures plus Conversion and Visual Advanced Formulas in the Data Actions module.
Please note that another option of this configuration could be achieved in using conversion measures and formulas in stories.
This last option requires fewer efforts but considering our best practices, I decided to avoid on the fly calculations in the story and used Data Actions to store all calculated details in the database.
Combination of both options can be relevant, the decision has to be made in considering the performance aspects (number of SKUs and Transaction currencies involved in the calculations per end user).
Model configuration
Activate the currency conversion in the Preferences of the New model
Set up the dimensions
Create the Calculated measure to get the Revenue amount
Create all required measures to store the calculations done with the data actions
Three types of measures are requested, one based on quantity without currency conversion, one based on the Transaction Currency (Price input) and one based on the Reporting Currency (in our use case Euro).
Please note that this configuration example stores every detail of gaps analysis on dedicated measures, but this isn’t mandatory, it has to be adapted to the end-user requirements.
Data Actions configuration
For clarity purposes, I have detailed every calculation in a dedicated advanced formula but most of them could be set in one, even with variables.
Every calculation doesn’t have to be stored in the database, please consider performance issues and check with end users what is most relevant to them.
Every setting is a balance 🙂
Summary of the data actions
Details of this configuration can be found in this video: https://d.dam.sap.com/a/PrhYMjX?rc=10
(Please note that in this simplified example, the Context of the Data Action applies only to the Revenue account).
I hope this illustrated example will help our community to take advantages of our last releases features as the new model and the conversion steps in Data Actions.
Useful links
SAP Analytics Cloud for planning : our implementation best advices | SAP Blogs
