Skip to Content
Technical Articles

Create a Profit & Loss Statement in SAP Analytics Cloud importing data from an Excel file

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.

  • 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:

Then you have to maintain the data across and leave empty the amount column in all records, except in the lowest level of the hierarchy. For example the ECC account is the lowest level, here it will accommodate the amount (defined as measure), you can see it clearly in the Excel file.

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!


You must be Logged on to comment or reply to a post.
  • Hi Mariajose,

    the explanation is very clear, however I don’t see how the end client could accept a PL formatted in this way. It may be OK when you are planning, but it’s not the best when you have to present data. Since you are forced to build the PL based on Account hierarchy and you can’t define multiple hierarchies in Account, I don’t see any other solution than using a user defined dimension to build the final presentation for PL (or any other financial statements).  Do you have any other hints on this point?

    Anyway, thanks a lot for your blog and looking forward to hear your comments



    • Hello Francesco,

      The proper way to consume P&L Data is from a backend system like an ERP. You can find here the business content already available for SAP Analytics Cloud regarding this topic with S/4HANA:

      SAC is built to consume P&L based on an Account Hierarchy considering that it is built to work natively with the transactions of S/4HANA. So, if you want to manually model the data as I did, it is important to structure the data with the proper hierarchy towards an Account Dimension (Parent-Child hierarchy).

      I recommend you to reach out to you SAP support center contact to discuss it further. I hope I'd helped you a little bit more.

    • Hello Elmar,

      I checked the link and I see it working fine. Could you please keep trying? Either way I updated it again just in case. You may need to be logged in with One Drive first.