Financial Management Blogs by SAP
Get financial management insights from blog posts by SAP experts. Find and share tips on how to increase efficiency, reduce risk, and optimize working capital.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member706059
Participant

Hello there! Welcome back to Workbook Calculation Adapter Blog Posts Series. On the first blog post (Workbook Calculation Adapter Overview), we talked about the overview and how to configure Workbook Calculation Adapter. As promised, in the next blog posts, you’ll learn four examples on how to properly use and execute Workbook Calculation function in PaPM.




  1. Workbook Calculation (with Additional Created Field)

  2. Workbook Calculation (with Parameter)

  3. Workbook Calculation (with Look Up)

  4. Workbook Calculation (with Import)


In this second blog post, I will explain the first example we have. What if we want to have an additional Field which will contain a formula capturing some fields from your Input function? Sounds easy right? But this is a very interesting scenario and can be used on a daily basis.


This scenario shows how the Workbook Calculation processes the data from the Input Model Table and allows an additional field to be included in the result being calculated through a formula maintained directly in a workbook sheet.


Input


This is the table that we use as an input function:


Input Model Table






















































Customer Product Quantity Amount
CUST01 PROD01 1 10
CUST02 PROD02 2 20
CUST03 PROD03 3 30
CUST04 PROD04 4 40
CUST05 PROD05 5 50
CUST05 PROD0X 50 500
CUST06 PROD06 60 600

In the Signature tab, we maintain the following:


































Signature
Granularity Selection Action
Customer Customer Total Amount
Product Product
Quantity
Amount

By maintaining the above fields, we are:




  1. Putting the fields Customer and Product in the Granularity as they contain unique data records and that we are making sure that the system processes data records from the input data in a grouped manner and that it considers all records from the input function for processing.

  2. Making sure that the fields Customer, Product, Quantity and Amount (which can also be seen in our Input Mode Table) will be included in the result table.

  3. Adding another field (make sure that the field is created in the environment, otherwise an error will be displayed). We use it to create a formula which will eventually be included in the final result.


In the Calculation Rules (worksheet), we see two tabs (Input and Result). They contain the fields maintained in the Signature section.




















Customer Product Quantity Amount Total Amount
0 0 0

Both Input and Result tabs contain the table above. The tabs will not show the data records from the input table. We recommend adding dummy data in the Excel tabs to test the Excel formula logic. These data are only used for modeling purposes and are not considered in the calculation during system runtime.


Dummy Data provided below can be maintained.


Input tab




















Customer Product Quantity Amount Total Amount
ARR21 SHOES 143 300 0

Note


Even though dummy data is maintained in the Input tab, it is still capturing the data from the Input Model table.


Formulas shown below will be used for the Result tab.


Result tab




















Customer Product Quantity Amount Total Amount
=Input!A2 =Input!B2 =Input!C2 =Input!D2 =C2*D2

By maintaining these formulas, we are cross-referencing the value which will be captured by the first four fields, to the Input tab. As for the additional field, we maintained a formula which will multiply the value of the Quantity and Amount fields.


Final Output






























































Customer Product Quantity Amount Total Amount
CUST01 PROD01 1 10                                    10,00
CUST02 PROD02 2 20                                    40,00
CUST03 PROD03 3 30                                    90,00
CUST04 PROD04 4 40                                  160,00
CUST05 PROD05 5 50                                  250,00
CUST05 PROD0X 50 500                             25.000,00
CUST06 PROD06 60 600                             36.000,00

The first four fields (Customer, Product, Quantity and Amount) just captured the data from the Input Model table because we just created a formula referencing to the input. The additional field (Total Amount) multiplied the value from the Quantity and Amount accordingly.


We only just created a one liner of formula. Anyway, the function considered all records from the input function for processing because the fields have been maintained in the Granularity fields from the Signature section.


I believe that upon reading this blog post, you are now able to understand how to set up/configure a Workbook Calculation function with an additional Field which was not present from the Input function.


What if we create a formula wherein, we use a Parameter with a specified Value? Will the process be as easy as setting up a simple formula? How can we declare the value of the Parameter? These are just some of the questions that we will be answering on the third blog post of this Workbook Calculation Adapter Blog Posts Series focusing on the second example.


For other SAP Profitability and Performance Management related inquiries, you can post your questions through https://answers.sap.com/questions/ask.html and use primary tag: SAP Profitability and Performance Management.


You can also read other SAP Profitability and Performance Management posts via https://community.sap.com/topics/profitability-and-performance-management.