Hello! Hopefully this blog post will help you creating your own P&L Statements in SAP Analytics Cloud. For this exercise I used this Excel file with dummy data.
First you have to check that the different types of cost, expenses and sales are defined as dimensions and not as measures. You need only one measure that is going to be the amount for each of those dimensions (fixed and variable costs, expenses and sales, etc.)
Then you have to develop the hierarchy, that is only supported as Parent-Child and you have to put them in 3 columns.
See here how it looks like in the Data Model:
Depending on the structure of the Profit & Loss Statement that you require to see, it is highly probable that SAC will tell you that you have duplicate IDs, because one parent can have many children, but a child can only have one parent. In this case what I would recommend is to create unique IDs for chains of greatgrandfather-grandfather-parent-child. I concatenated those ID to have unique IDs in my hierarchy, you can see it clearly at the Excel file.
Don’t forget to enable planning in the data model before creating it. Also, make sure that the version is defined and mapped (actual, budget, planning, etc.) and the amount is defined as a measure.
To enrich the model and story to build, you can create product/category hierarchy and a location dimension based in coordinates.
After validating the data and creating the model (buttons are at the right bottom), you have to put manually the names of the highest level of the hierarchy (because you started only with the IDs with this dummy data) and put the formulas to calculate the net profit, total cost, total expenses, operating income, etc.
For this step, go to the Account ID and type down the description to the sales, costs and expenses level of your top P&L hierarchy (see second tab of the Excel file for more info).
In this case using this dummy data, you'll need to create the highest level of the hierarchy like: total sales, cost of goods sold and total expense to calculate the gross and operating profit (don't forget to specify the account type):
Later when finishing the model, and creating the story, by inserting a table you'll be able to see the hierarchy in a clearer way.
Even you can create a Value Driver Tree consuming the same data model.
To know how to create a VDT, see this video.
Now you have it.
This is an example of how you can build your own Profit & Loss Statement starting with a excel file and modeling it into SAP Analytics Cloud, but this is not all, thanks to the predictive capabilities embedded in SAP Analytics Cloud you can also enable what if scenarios which gives you more insights to make agile business decisions with anticipated outcomes!
First you have to check that the different types of cost, expenses and sales are defined as dimensions and not as measures. You need only one measure that is going to be the amount for each of those dimensions (fixed and variable costs, expenses and sales, etc.)
Then you have to develop the hierarchy, that is only supported as Parent-Child and you have to put them in 3 columns.
- The 1st column is the ID of the account defined in the data model as Account Dimension
- The 2nd column is the account name of that ID (acting as child) assigned as Description of the Account Dimention
- The 3rd column is the ID of the Parent account assigned as Parent of the Account Dimension
See here how it looks like in the Data Model:
Depending on the structure of the Profit & Loss Statement that you require to see, it is highly probable that SAC will tell you that you have duplicate IDs, because one parent can have many children, but a child can only have one parent. In this case what I would recommend is to create unique IDs for chains of greatgrandfather-grandfather-parent-child. I concatenated those ID to have unique IDs in my hierarchy, you can see it clearly at the Excel file.
Don’t forget to enable planning in the data model before creating it. Also, make sure that the version is defined and mapped (actual, budget, planning, etc.) and the amount is defined as a measure.
To enrich the model and story to build, you can create product/category hierarchy and a location dimension based in coordinates.
After validating the data and creating the model (buttons are at the right bottom), you have to put manually the names of the highest level of the hierarchy (because you started only with the IDs with this dummy data) and put the formulas to calculate the net profit, total cost, total expenses, operating income, etc.
For this step, go to the Account ID and type down the description to the sales, costs and expenses level of your top P&L hierarchy (see second tab of the Excel file for more info).
In this case using this dummy data, you'll need to create the highest level of the hierarchy like: total sales, cost of goods sold and total expense to calculate the gross and operating profit (don't forget to specify the account type):
Later when finishing the model, and creating the story, by inserting a table you'll be able to see the hierarchy in a clearer way.
Even you can create a Value Driver Tree consuming the same data model.
To know how to create a VDT, see this video.
Now you have it.
This is an example of how you can build your own Profit & Loss Statement starting with a excel file and modeling it into SAP Analytics Cloud, but this is not all, thanks to the predictive capabilities embedded in SAP Analytics Cloud you can also enable what if scenarios which gives you more insights to make agile business decisions with anticipated outcomes!
- SAP Managed Tags:
23 Comments
