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

[nathskie28]

 

Hello there! last year, a series of blog posts were released by my colleague starting from the Workbook Calculation Adapter Overview down to the 5^th 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!