How To: Replacing the Account Column with a Descriptive Revenue Type Column in the Workbooks for SAP BPC for S/4HANA
SAP BPC optimized for S/4HANA
In this blog I describe how you can eliminate the account column and replace it with a more meaningful “Revenue Type” in the Analysis for Office Excel workbooks provided for SAP BPC optimized for S/4HANA. I describe the procedure for this based on the workbook for sales planning (technical name: /ERP/SFIN_A51_WB02), which is available as of release 1610.
Sales-oriented quantity and price planning in companies is often executed without reference to the revenue accounts of financial accounting. Nevertheless, the connection to the revenue accounts must be created in later reconciliation steps with the departments of Management Accounting to facilitate a comprehensive financial planning.
SAP BPC optimized for S/4HANA also contains content for sales planning. However, as each company uses its own chart of accounts, the planning workbooks and queries for sales planning contain the account in the column drilldown. Although it is generally known that in practice the assignment to revenue accounts is not relevant for the sales employee responsible for planning.
Based on the analysis for Office Excel workbooks for sales planning (technical name: /ERP/SFIN_A51_WB02), I will describe how you can prevent the column for the revenue account appearing in your own planning workbooks and replace it with a more meaningful “Revenue Type” for the sales employee without the assignment to a specific revenue account getting lost. With this workbook, a sales employee, for example within the context of sales revenue planning, can plan quantities and prices for each customer group, product group and revenue account.
In general, customers can of course use the workbooks of the content provided as a template for their own planning workbooks and then adjust them to suit their own planning process.
When characteristics or key figures are added or deleted, the detail level of the planning can be changed.
If more detail is wanted for the sales planning (for example, customer instead of customer group), you can achieve this by replacing the “Customer Group” characteristic with the “Customer” characteristic in the corresponding objects of the content (workbook, query, aggregation-level) or a copy of this. The necessary adjustments for this are not part of this How-To. For more information about adjusting the content to meet company-specific requirements, have a look at our Adaptation Guide (see http://go.sap.com/documents/2016/06/14e6eeba-757c-0010-82c7-eda71af511fa.html).
It is best to follow the recommendations in the Adaptation Guide and make the necessary adjustments in a copy of the workbook or a copy of the BW query that is embedded in the workbook.
Start with the BW query.
- Open the Excel workbook /ERP/SFIN_A51_WB02 in Analysis for Office.
- Navigate to the BEx Query Designer.
- Copy the BW query that the workbook is based on.
- Switch to the Rows/Columns pane and delete the Account characteristic from the query (drag and drop).
- Add a structure to the rows.
- Rename the structure to “Revenue Type“.
- Add selections for the account dimension to the structure you just created.
- Adjust the order of the columns by moving the “Revenue Type“ column from the first to the third column.
- Switch back to the Filter pane and delete the restrictions for the account characteristic (drag and drop).
Once you have copied the query and replaced the account column, you can turn your attention to the Excel workbook. Copy the workbook and embed it into your own query.
- Copy the workbook provided.
- Swap the query. Replace the original query with your own query.
In the workbook, only the planned figures for the accounts are now displayed from the structure selection in the query.
- Delete the empty header lines where before the account selection was.
- Save the workbook.
- You can now enter planned figures at the level of the revenue types defined this way.