Performing Matrix Consolidation

Objective

After completing this lesson, you will be able to perform matrix consolidation

Management Consolidation Use Case

Matrix consolidation key points

In the corporate close process, we've run the intercompany elimination tasks and now we can report on the results. We need reports that show the performance from a management perspective as well as from a legal perspective. For example, legal reports are by consolidation unit but management reports are by profit center or segment.

In addition, we have a business requirement to view income statement data with a consolidation view as compared to a contribution view:

  • The consolidation view will show total revenue by entity along with an elimination entity (for each consolidation unit hierarchy node).
  • The contribution view shows revenue net of eliminations by entity.

Note

The term matrix consolidation refers to the concept of preparing legal and management consolidated data simultaneously. Group reporting provides multiple views of the data to answer many different business questions. There's only one set of data, therefore there's no data redundancy.

In the Consolidation unit consolidation and contribution view example, we're reporting on consolidation unit revenue with the contribution view and the consolidation view. The same hierarchy is being used for both views. The consolidation view dynamically determines the elimination entity based on the first common parent in the legal hierarchy.

Each view uses a different dimension:

  • Contribution view uses the Consolidation Unit dimension.
  • Consolidation view uses the Consolidation Unit for Elimination dimension.
The image shows two tables comparing the contribution and consolidation views by consolidation unit for the sale of goods.The left table displays the contribution view, while the right table shows the consolidation view for elimination.

Matrix Report Examples

In the following content, you’ll see an example of how to create multiple reports on a single set of data to answer multiple business questions.

In the Profit center - consolidation and contribution views example, we're reporting on profit center revenue with the contribution view and the consolidation view. The same hierarchy is being used for both views. The consolidation view dynamically determines the elimination entity based on the first common parent in the line-of-business hierarchy.

Each view uses a different dimension:

  • Contribution view - uses the Profit Center dimension.
  • Consolidation view - uses the Profit Center for Elimination dimension.
The image shows two tables comparing the Contribution View and Consolidation View by Profit Center for the sale of goods. The tables display the financial amounts for various profit centers, including adjustments for eliminations in the Consolidation View.

When analyzing management entity data, you can use either the Profit Center dimension for a contribution view or the Profit Center for Elimination dimension for a consolidated view. In order to do this, the relevant profit center hierarchy must be selected via the report prompts.

In addition, Segment and Partner Segment can also be used for evaluation of management consolidation data.

In the Elimination entity determination with two different hierarchies example, the consolidation view (total revenue by entity along with an elimination entity for each consolidation unit hierarchy node) is provided by using two different hierarchies:

  • Legal hierarchy: Elimination entities are dynamically determined via the parent nodes in the legal hierarchy.
  • Line-of-business (LOB) hierarchy: Elimination entities are dynamically determined via the parent nodes in the line-of-business hierarchy.
The image shows two tables comparing the Legal Hierarchy by Consolidation Unit and the Line-of-Business Hierarchy by Consolidation Unit, detailing amounts for the sale of goods.

In summary, legal consolidation data can be evaluated with a legal-oriented consolidation unit hierarchy. An alternative hierarchy by line-of- business can also be used to provide a management perspective. In either case, the first common parent in each hierarchy will display the elimination values dynamically. And, because the calculation is on-the-fly, there’s no duplication of data.

Elimination Entity Documents

When reporting on data by consolidation unit for elimination or profit center for elimination, you can jump to the underlying documents to view the dynamically determined elimination entities.

The image shows an SAP interface displaying group journal entries with various financial details such as amounts, currencies, and consolidation units.

You can also view the elimination entity values by running the Display Group Journal Entries with Reporting Logic app. In either case, hierarchies must be selected for the consolidation unit and profit center/segment dimensions.

Analyze Intercompany Eliminations with Microsoft Excel

Business Scenario

Intercompany revenue and cost for the consolidation units as well as the profit centers have been eliminated. Now, you need to run reports from a legal reporting perspective by consolidation unit and from a management reporting perspective by profit center. The business requirement is to display revenue data by entity from both a contribution and consolidation perspective.

What skills will you develop in this practice exercise?

  • Open SAP Analysis for Office
  • Report on the eliminated data from a legal reporting perspective by consolidation unit
  • Report on the eliminated data from a management reporting perspective by profit center
ExerciseStart Exercise
Business Scenario

Intercompany revenue and cost for the consolidation units as well as intracompany revenue and cost for the profit centers have been eliminated. Now, you need to run reports from a legal reporting perspective by consolidation unit and from a management reporting perspective by profit center. The business requirement is to display revenue data by entity from both a contribution and consolidation perspective.

Note

If you’re a learning access student, you must do the Analyze matrix revenue eliminations with SAP Fiori exercise instead of this one.

The main steps in this exercise are:

  • Open SAP Analysis for Office.
  • Report on the eliminated data from a legal reporting perspective by consolidation unit.
  • Report on the eliminated data from a management reporting perspective by profit center.

Note

In this exercise, when you see ##, replace the characters with your group number.

Task 1: Open SAP Analysis for Microsoft Excel

Steps

  1. Launch the SAP Analysis for Microsoft Excel app. If there’s an error when opening SAP Analysis for Office from the app, close Microsoft Excel. Go to the training desktop's search. Search for Analysis for Microsoft Excel and open it.

    1. Close all Excel files in your training desktop if needed.

    2. In the training desktop, search for SAP Analysis for Microsoft Excel.

    3. If an information dialog opens, choose OK.

    4. If needed, choose Excel near the bottom of your training desktop.

    Result

    Microsoft Excel should now be open. You should have an Analysis tab.

  2. Create a connection for SAP S/4HANA Cloud. Use the data in the following table. Skip the SAP BusinessObjects prompt.

    FieldValue
    Logon URLThis will be provided by your instructor.
    API URL(This defaults in)
    DescriptionS4HC

    1. If needed, click in cell A1.

    2. Go to the Analysis tab.

    3. On the upper left, use the drop-down next to Insert Data Source → Select Select Data Source for Analysis.

    4. Skip the SAP BusinessObjects prompt.

    5. In the Select Data Source dialog, right-click and select Create New SAP S/4HANA Cloud Connection.

    6. Enter the data as provided in the table above.

    7. Choose OK → Choose Next.

    8. If prompted to login, your user is CO### or A0### and the password is Welcome1.

    9. If needed, choose Excel near the bottom of your training desktop.

    Result

    The Select Data Source - S4HC dialog should be open.

Task 2: Analyze revenue eliminations between consolidation units

Steps

  1. Run the 2CCCSMATRIXRPT10 Q query.

    1. In the Select Data Source dialog, search for 2CCCS.

    2. Select the 2CCCSMATRIXRPT10Q query.

    3. Choose OK.

    Result

    Note

    Don't select the 2CCCSMATRIXR01 Q query.

  2. Make the following selections:

    FieldSelection
    Consolidation GroupWorld
    VersionU##
    Fiscal Year2022
    Reporting Period12
    Period ModePER (Periodic)
    Consolidation COAY1
    FS Item HierarchyBS_PL (BS and PL | use the search help)
    Consolidation Unit HierarchyCS17/ALL_CU_Hierarchy (All Consolidation Units | use the search help)
    Segment Hierarchy$
    Profit Center HierarchyCS04/A000/U00_LOB (U00 Line of Business | use the search help)
    Key DateCurrent date

    1. On the Prompts for Local/Group Data Analysis Query dialog box, enter the data as shown in the table above.

    2. Choose OK.

    Result

    The query is inserted into Sheet 1.

  3. Insert 5 rows at the top of the sheet.

    1. Highlight rows 1 to 5.

    2. Right-click and choose Insert.

  4. Filter the measures to Amount in Group Currency only.

    1. Scroll to the right and locate Amount in Group Currency.

    2. Right-click on Amount in Group Currency → Choose Filter Members.

    3. Go back to the top left of Sheet1.

    Result

    The only measure in the columns is now Amount in Group Currency.

  5. Expand the FS Item hierarchy. Filter FS Items to 411100.

    1. In column A, right-click FS Item.

    2. Choose Expand to level → Level 09.

    3. Scroll down to FS Item 411100 (Sale of goods).

    4. Right-click on FS Item 411100 → Choose Filter Members.

    5. Scroll up to the top of Sheet1.

    Result

    Only FS Item 411100 is displayed in the rows.

    Note

    The business requirement is to display revenue data by entity from both a contribution and consolidation perspective.

  6. In the Analysis pane, move FS Items from the rows to the columns.

    1. On the right, right-click on FS Items (BS and PL).

    2. Choose Move to Columns.

  7. Remove Consolidation Unit for Elimination from the columns.

    1. On the top right, right-click on Consolidation Unit for Elimination.

    2. Choose Remove.

  8. Add Consolidation Unit to the rows along with the All Consolidation Units Hierarchy. Expand the hierarchy fully.

    1. On the right, drag Consolidation Unit into the Row axis (on the right).

    2. Under Data Source, expand Consolidation Unit → Expand the Hierarchies folder → Double-click the All Consolidation Units Hierarchy.

    3. In column A, right-click on Consolidation Unit → Choose Expand to → Select Level 04.

    Result

    The revenue by consolidation unit net of eliminations is displayed. This is the contribution view (each consolidation unit's revenue is net of intercompany transactions). This represents how much they contributed to the corporate third party revenue.

    Note

    See the Consolidation Unit Contribution View screenshot in the Exercise results figure. 

  9. In order to display the consolidation view, replace Consolidation Unit with Consolidation Unit for Elimination in the rows. Assign the All Consolidation Units Hierarchy. Expand the hierarchy fully.

    1. On the right, right-click on Consolidation Unit in the Rows → Choose Remove.

    2. Under Data Source, right-click on Consolidation Unit for Elimination → Select Add to Rows.

    3. Under Data Source, expand Consolidation Unit for Elimination → Expand the Hierarchies node → Double-click the All Consolidation Units Hierarchy.

    4. In column A, right-click on Consolidation Unit for Elimination → Choose Expand to → Select Level 04.

    Result

    The consolidation units display intercompany and external revenue but the intercompany revenue eliminations are displayed with the elimination entities based on the first common parent rule.

    Note

    See the Consolidation Unit Consolidation View screenshot in the Exercise results figure. 

Task 3: Analyze revenue eliminations between profit centers

Steps

  1. In order to display the consolidation view by profit center, replace Consolidation Unit for Elimination in the rows with Profit Center for Elimination. Assign the U00 Line of Business Hierarchy. Expand the hierarchy fully.

    1. On the right, right-click on Consolidation Unit for Elimination in the Rows → Choose Remove.

    2. Under Data Source, right-click on Profit Center for Elimination → Select Add to Rows.

    3. Under Data Source, expand Profit Center for Elimination → Expand the Hierarchies node → Double click the U00 Line of Business Hierarchy.

    4. In column A, right-click on Profit Center for Elimination → Choose Expand to → Select Level 03.

    Result

    The profit centers display both intercompany and external revenue but the intercompany revenue eliminations are displayed with elimination entities based on the first common parent rule.

    Note

    See the Profit Center Consolidation View screenshot in the Exercise results figure.
    The image displays three tables showing financial data in different views: Consolidation Unit Contribution View, Consolidation Unit Consolidation View, and Profit Center Consolidation View. Each table lists amounts in group currency for the sale of goods, categorized by various units and profit centers.

  2. Save the workbook to the training desktop with the following name:

    FieldValue
    File NameMatrix ##

    1. Go to File.

    2. Choose Save As.

    3. Choose Desktop.

    4. Enter the file name as shown in the table above.

    5. Choose Save.

  3. Close Microsoft Excel.

    Result

    You have completed this exercise.

Log in to track your progress & complete quizzes