# Workbook Calculation using an HR Expenses Budgeting worksheet as Input to calculation

Hello there! last year, a series of blog posts were released by my colleague starting from the Workbook Calculation Adapter Overview down to the 5th example of the Workbook Calculation Adapter wherein it uses a locally saved Excel and is imported to be used for calculation. Now, I would like to share with you our experience with using an excel file for HR expense Calculation and make it a part of SAP Profitability and Performance Management calculation.

This HR Expenses Budgeting worksheet is basically an excel planning for HR-related expenses which is shown below.

The worksheet will consider different variables for calculation like compensation, Employee Type, factors, etc. that are also coming from a different tab, and once everything is calculated it is allocated to different accounts within a cost center.  I will not drill down with how the calculation and formula work within the excel sheet but how we can use the HR Expense Budgeting data as an input to our workbook calculation adapter

Input (Model Table)
We will be having Salaries per cost center, which has the salary data for the fiscal year. It will serve as our input to the workbook calculation adapter.

Input Data

 Fiscal Year Cost Center Salary Level 1 Salary Level 2.1 Salary Level 2.2 Vacation Level 1 Vacation Level 2 Overtime per FTE 2021 CC4 4000.00 8000.00 12000.00 4.00 4.00 4.00 2021 CC3 3000.00 5000.00 7000.00 3.00 3.00 3.00 2021 CC2 2000.00 4000.00 6000.00 2.00 2.00 2.00 2021 CC1 1000.00 3000.00 5000.00 1.00 1.00 1.00

Configuring Workbook Calculation (Personnel Expenses Calculation)

The Signature.

The Cost Center was assigned in the Granularity section as it contains unique data records and the system considers all the data records for processing.  Selection fields are also defined to ensure that the fields will also be included in the result table. And since we will be getting the values for Account and Allocation (amounts) we added them in the action fields so we could define the formula and it will be also included in the result.

 Signature Granularity Selection Action Cost Center Cost Center Account Salary Level 1 Amount Salary Level 2.1 Currency Salary Level 2.2 Vacation Level 1 Vacation Level 2 Overtime per FTE

Configuring the Rules

Once you have connected the Input (Model Table) and defined the signature fields, the Input and Result tab will become available in the rules and will be containing the fields maintained in the Signature Section.

The input tab will not contain any data but can be used by adding dummy data to test the excel formula used in the result and is not considered in the calculation during system runtime.

Result Tab will basically layout/present the output that we wanted, and we use formulas by cross-referencing the value which will be captured in the Input tab. As our main goal is we use a workbook calculation adapter to get the Accounts and Allocation within the Cost Center

To calculate allocation per Account Number, we need to import the HR Expense Budgeting as it calculates the allocation based on the variable for salary, vacation, overtime, etc.

To Import, Follow the steps below to import an Excel or CSV file:

1. On the expanded workbook calculation rules, choose the File button located at the upper left of the workbook.
2. Select Import.
3. Depending on the type of file to be imported, choose either Import Excel File or Import CSV File.
4. Select Import Excel file.

5. Then select the HR Expenses Budgeting.

or you may read thru the Workbook Calculation (with Import) blog to know more.

After the import, the calculation rules will be filled with the data from the HR Expenses Budgeting shown below highlighted in orange boxes. The Input and Result tabs will still be intact.

The Workbook Calculation Adapter will read dynamically the values from our Input tab (coming from Input -Model Table) by using the formula “=Input!A2” in Cell I4 of the calculation tab to get the value of the cost center. Look at how we did it below at least for the Cost center.

We did the same thing for the Fiscal year, we used the formula “=Input!B2” in Cell I3 and for other variables that are highlighted in yellow which are Salary per FTE, Vacation factor, and Overtime.

As mentioned previously, In the result tab we can calculate the allocations (amount).  By cross-referencing the value or reading dynamically in the HR Expenses Budgeting tabs we imported. We set the formula as shown below;

 COST_CENTER FISCAL_YEAR SALARY_LEVEL_1 SALARY_LEVEL_2_1 SALARY_LEVEL_2_2 VACATION_LEVEL_1 VACATION_LEVEL_2 OVERTIME_PER_FTE ACCOUNT AMOUNT CURRENCY =Calculation!I\$4 =Calculation!I\$3 =Calculation!F\$16 =Calculation!F\$17 =Calculation!F\$18 =Calculation!H\$21 =Calculation!H\$22 =Calculation!H\$31 10001 =ROUND(SUMIF(Calculation!\$J\$56:\$J\$87,Result!I2,Calculation!\$L\$56:\$L\$87),0) EUR

The data is presented in the Result tab by itemizing the accounts per cost center and the allocation or amounts are calculated accordingly

Once all the configurations are done, we save, activate, and run the functions, the result would look like the sample below.

 Cost Center Fiscal Year Salary Level 1 Salary Level 2.1 Salary Level 2.2 Vacation Level 1 Vacation Level 2 Overtime per FTE Account Amount Currency CC1 2021 1000 3000 5000 1 1 1 10001 42000 EUR CC1 2021 1000 3000 5000 1 1 1 10002 97200 EUR CC1 2021 1000 3000 5000 1 1 1 10003 0 EUR CC1 2021 1000 3000 5000 1 1 1 10004 367 EUR CC1 2021 1000 3000 5000 1 1 1 10005 0 EUR CC1 2021 1000 3000 5000 1 1 1 10006 5400 EUR CC1 2021 1000 3000 5000 1 1 1 10007 12000 EUR CC1 2021 1000 3000 5000 1 1 1 10008 2500 EUR CC1 2021 1000 3000 5000 1 1 1 10009 0 EUR CC1 2021 1000 3000 5000 1 1 1 10010 3 EUR CC1 2021 1000 3000 5000 1 1 1 10011 0 EUR CC1 2021 1000 3000 5000 1 1 1 10012 0 EUR CC1 2021 1000 3000 5000 1 1 1 10013 0 EUR CC1 2021 1000 3000 5000 1 1 1 10014 0 EUR CC1 2021 1000 3000 5000 1 1 1 10015 2667 EUR CC1 2021 1000 3000 5000 1 1 1 10016 1000 EUR CC1 2021 1000 3000 5000 1 1 1 10017 1000 EUR CC1 2021 1000 3000 5000 1 1 1 10018 3333 EUR CC1 2021 1000 3000 5000 1 1 1 10019 0 EUR CC1 2021 1000 3000 5000 1 1 1 10020 62228 EUR CC1 2021 1000 3000 5000 1 1 1 10021 49727 EUR CC1 2021 1000 3000 5000 1 1 1 10022 0 EUR CC1 2021 1000 3000 5000 1 1 1 10023 0 EUR CC1 2021 1000 3000 5000 1 1 1 10024 30624 EUR CC1 2021 1000 3000 5000 1 1 1 10025 0 EUR CC2 2021 2000 4000 6000 2 2 2 10001 82000 EUR CC2 2021 2000 4000 6000 2 2 2 10002 121200 EUR CC2 2021 2000 4000 6000 2 2 2 10003 0 EUR CC2 2021 2000 4000 6000 2 2 2 10004 367 EUR CC2 2021 2000 4000 6000 2 2 2 10005 0 EUR CC2 2021 2000 4000 6000 2 2 2 10006 7200 EUR CC2 2021 2000 4000 6000 2 2 2 10007 14400 EUR CC2 2021 2000 4000 6000 2 2 2 10008 2500 EUR CC2 2021 2000 4000 6000 2 2 2 10009 0 EUR CC2 2021 2000 4000 6000 2 2 2 10010 7 EUR CC2 2021 2000 4000 6000 2 2 2 10011 0 EUR CC2 2021 2000 4000 6000 2 2 2 10012 0 EUR CC2 2021 2000 4000 6000 2 2 2 10013 0 EUR CC2 2021 2000 4000 6000 2 2 2 10014 0 EUR CC2 2021 2000 4000 6000 2 2 2 10015 5333 EUR CC2 2021 2000 4000 6000 2 2 2 10016 2000 EUR CC2 2021 2000 4000 6000 2 2 2 10017 2000 EUR CC2 2021 2000 4000 6000 2 2 2 10018 6667 EUR CC2 2021 2000 4000 6000 2 2 2 10019 0 EUR CC2 2021 2000 4000 6000 2 2 2 10020 118225 EUR CC2 2021 2000 4000 6000 2 2 2 10021 49727 EUR CC2 2021 2000 4000 6000 2 2 2 10022 0 EUR CC2 2021 2000 4000 6000 2 2 2 10023 0 EUR CC2 2021 2000 4000 6000 2 2 2 10024 35017 EUR CC2 2021 2000 4000 6000 2 2 2 10025 0 EUR CC3 2021 3000 5000 7000 3 3 3 10001 122000 EUR CC3 2021 3000 5000 7000 3 3 3 10002 145200 EUR CC3 2021 3000 5000 7000 3 3 3 10003 0 EUR CC3 2021 3000 5000 7000 3 3 3 10004 367 EUR CC3 2021 3000 5000 7000 3 3 3 10005 0 EUR CC3 2021 3000 5000 7000 3 3 3 10006 9000 EUR CC3 2021 3000 5000 7000 3 3 3 10007 16800 EUR CC3 2021 3000 5000 7000 3 3 3 10008 2500 EUR CC3 2021 3000 5000 7000 3 3 3 10009 0 EUR CC3 2021 3000 5000 7000 3 3 3 10010 10 EUR CC3 2021 3000 5000 7000 3 3 3 10011 0 EUR CC3 2021 3000 5000 7000 3 3 3 10012 0 EUR CC3 2021 3000 5000 7000 3 3 3 10013 0 EUR CC3 2021 3000 5000 7000 3 3 3 10014 0 EUR CC3 2021 3000 5000 7000 3 3 3 10015 8000 EUR CC3 2021 3000 5000 7000 3 3 3 10016 3000 EUR CC3 2021 3000 5000 7000 3 3 3 10017 3000 EUR CC3 2021 3000 5000 7000 3 3 3 10018 10000 EUR CC3 2021 3000 5000 7000 3 3 3 10019 0 EUR CC3 2021 3000 5000 7000 3 3 3 10020 174222 EUR CC3 2021 3000 5000 7000 3 3 3 10021 49727 EUR CC3 2021 3000 5000 7000 3 3 3 10022 0 EUR CC3 2021 3000 5000 7000 3 3 3 10023 0 EUR CC3 2021 3000 5000 7000 3 3 3 10024 39411 EUR CC3 2021 3000 5000 7000 3 3 3 10025 0 EUR CC4 2021 4000 8000 12000 4 4 4 10001 162000 EUR CC4 2021 4000 8000 12000 4 4 4 10002 241200 EUR CC4 2021 4000 8000 12000 4 4 4 10003 0 EUR CC4 2021 4000 8000 12000 4 4 4 10004 367 EUR CC4 2021 4000 8000 12000 4 4 4 10005 0 EUR CC4 2021 4000 8000 12000 4 4 4 10006 14400 EUR CC4 2021 4000 8000 12000 4 4 4 10007 28800 EUR CC4 2021 4000 8000 12000 4 4 4 10008 2500 EUR CC4 2021 4000 8000 12000 4 4 4 10009 0 EUR CC4 2021 4000 8000 12000 4 4 4 10010 13 EUR CC4 2021 4000 8000 12000 4 4 4 10011 0 EUR CC4 2021 4000 8000 12000 4 4 4 10012 0 EUR CC4 2021 4000 8000 12000 4 4 4 10013 0 EUR CC4 2021 4000 8000 12000 4 4 4 10014 0 EUR CC4 2021 4000 8000 12000 4 4 4 10015 10667 EUR CC4 2021 4000 8000 12000 4 4 4 10016 4000 EUR CC4 2021 4000 8000 12000 4 4 4 10017 4000 EUR CC4 2021 4000 8000 12000 4 4 4 10018 13333 EUR CC4 2021 4000 8000 12000 4 4 4 10019 0 EUR CC4 2021 4000 8000 12000 4 4 4 10020 230219 EUR CC4 2021 4000 8000 12000 4 4 4 10021 49727 EUR CC4 2021 4000 8000 12000 4 4 4 10022 0 EUR CC4 2021 4000 8000 12000 4 4 4 10023 0 EUR CC4 2021 4000 8000 12000 4 4 4 10024 43804 EUR CC4 2021 4000 8000 12000 4 4 4 10025 0 EUR

There you go! You can now use your existing excel based calculation tool as an input to workbook calculation by importing the locally saved excel file. You can also use the workbook calculation as an input to a Query function (Results Reporting) for reporting purposes.

If you enjoyed this blog share it with your friends and colleagues and leave a comment below. I’d like to know what you think!