Skip to Content
Technical Articles
Author's profile photo Mariajose Martinez

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!

 

Assigned tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Francesco Gavarini
      Francesco Gavarini

      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

      Regards

      Francesco

      Author's profile photo Mariajose Martinez
      Mariajose Martinez
      Blog Post Author

      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: https://help.sap.com/viewer/42093f14b43c485fbe3adbbe81eff6c8/release/en-US/dcc9dd4daa3a4dafa4789febbc3953dc.html

      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.

      Author's profile photo Felix Steigmeier
      Felix Steigmeier

      Hi Mariajose,

      interesting post. It seems that the Link to the Excel expired. Could you please renew it?

      Many thanks!

       

      Regards

      Felix

      Author's profile photo Mariajose Martinez
      Mariajose Martinez
      Blog Post Author

      Thanks for letting me know Felix, it is already updated 🙂

      Author's profile photo Elmar Hutter
      Elmar Hutter

      Hi Mariajose

      I tried to open the excel file but it doesen't work properly. Can you send me the Excel?

      Regards

      Elmar

      Author's profile photo Mariajose Martinez
      Mariajose Martinez
      Blog Post Author

      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.

      Author's profile photo Krishna Porandla
      Krishna Porandla

      Can you please share the Excel please, the SharePoint link is not working for me.

      Author's profile photo Mariajose Martinez
      Mariajose Martinez
      Blog Post Author

      Hi Krishna!

      Thanks for letting me know. One Drive doesn't allow me to keep the link alive but I already updated it.

      Please try again.

      Author's profile photo Arvind Sharma
      Arvind Sharma

      Thanks its a good explanation, can the excel file be posted in English,

      Regards

      Arvind

      Author's profile photo RAFFAELLA BARONI
      RAFFAELLA BARONI

      Hi Mariajose

      I tried to open the excel file but it doesen't work properly. Can you send me the Excel?

      Regards

      Raffaella

      Author's profile photo Mariajose Martinez
      Mariajose Martinez
      Blog Post Author

      Hi Raffaella,

      Please try again, it's already updated.

      Author's profile photo RAFFAELLA BARONI
      RAFFAELLA BARONI

      Thanks a lot Mariajose, now is working 🙂

      Regards

      Raffaella