Real-world manufacturing scenarios have large numbers of sender and receiver cost centers and cost elements. Moreover, there could be a complex matrix of cost flow between various cost centers, resulting in many dependent allocation cycles running one after the other. Thus, the cost planning for allocating actual costs during month end could be very tedious.

At a typical manufacturing site, it takes up to three days of a site finance manager’s time every period end to ensure that the costs are properly allocated so that they are ultimately reflected in the product. For a large corporation with 50 sites, this could mean 150 days of a site finance manager’s time during each period closing. Automation using statistical key figures (SKFs) as tracing factors saves the site finance manager’s time on the period closing.

Finance managers are experienced and comfortable in using Excel-based allocation formats to plan for the periodic cost allocation. After interviewing finance managers, I conclude that much productive time is wasted in ensuring the rules provided in Excel are captured correctly in the SAP system. This article presents a solution that uses SKFs as tracing factors to integrate SAP with Excel-based cost allocation rules, thereby automating time-consuming month-end activity such as keying data into a system.

Figure 1 shows manufacturing costs categorized in a cost center hierarchy, which can be created, changed, or displayed in the system via transaction codes KSH1, KSH2, and KSH3, respectively.

Figure 1

A typical cost center hierarchy for a manufacturing site

You can see the cost center hierarchy as a tree structure consisting of cost center groups. Cost centers within a group are characterized by certain characteristics that differentiate them from cost centers in another group. See “Assigning Cost Center Categories” for additional details.

To simplify the discussion, assume that the manufacturing company has just six sender cost centers, six receiver cost centers, and five cost elements preserving the nature of cost. For instance, utilities could consist of electricity, gas, coal, and petroleum that are allocated using different cost elements. Therefore, you need to validate 180 (6 × 6 × 5) parameters every time you revisit the cost center planning process. I have made the prototype for the solution in SAP ERP Central Component (SAP ECC) 6.0, but the solution is applicable for earlier versions as well. The solution design consists of two components:

            1. Create a tracing factor-based assessment cycle

            2. Create an Excel interface for updating the tracing factors

Note
I captured all the screenprints in this article after designing the solution in an SAP system.

Now you are ready to see how the updated tracing factors work with the designed allocation cycle to compute period-end actual allocation.

Create a Tracing Factor-Based Assessment Cycle

To design the tracing factor automation on an allocation solution, you create an assessment cycle for allocating support cost center charges to production and quality cost centers. You can use various plan or actual values in the system for SKFs, activities, costs, or consumption quantities as tracing factors. The actual assessment cycles can be created, changed, and displayed via transaction code KSU1, KSU2, and KSU3, respectively.

As shown in Figure 2, you create one segment per sending site support cost center. A segment is a group of senders and their respective receivers that are all processed according to the same allocation rule. You can access the segments using Goto > Overview Segment and clicking the overview segments icon  or using the Shift+F4 button. You see in detail how the segment is defined for the UTILITY (site support) cost center.

Figure 2

The assessment cycle

You can access a particular segment by double-clicking it or by selecting the segment and clicking the magnifier icon (Figure 2). In the segment definition, you either use an assessment cost element of cost element category 42 (Assessment) or use the allocation structure. Cost element category is the classification of cost elements according to their usage, such as overhead allocation, internal activity allocation, or settlement. Cost elements having cost element category 42 are used for allocation. Allocation Structure specifies which source cost elements should be allocated under which assessment cost elements. The allocation structure, accessed via transaction code KSES (Figure 3), consists of an assignment of source cost elements to assessment cost elements.

Figure 3

Allocation structure maintenance

In the assessment cycle display (transaction code KSU3), click the magnifier icon next to Allocation structure in the Segment Header tab to view the allocation structure shown in Figure 4.

Figure 4

Display the allocation structure in an assessment cycle

As you receive invoices from vendors during a period, costs such as electrical expense and gas expense are booked using separate primary cost elements 30000000 and 30000001 to Utilities cost center in the Site Support node in Figure 1. At the period end, you need to allocate these costs to the respective departments that consumed the electricity and gas. Allocation structure facilitates period-end allocation of electricity cost using cost element 31000000 (CE Cat: 42) that was booked during the period using cost element 30000000 (CE Cat: 1).

At the same time, gas expense booked during the period via primary cost element 30000001 (CE Cat: 1) is allocated at period end using separate cost element 31000001 (CE Cat: 42). In the absence of an allocation structure, you can design an allocation cycle using multiple segments for gas and electricity expenses. Thus, you can reduce the number of segments in an allocation cycle via use of an allocation structure. Using an allocation structure, you can design an efficient assessment cycle that leads to an efficient allocation run. SAP recommends having fewer segments in an assessment cycle (approximately 50 segments per cycle) for optimal system performance in running allocations. (See SAP Note 79224 for more details.)

In Figure 2 under the Receiver Tracing Factor tab, select the Variable portions receiver rule with Plan Stat. Key Figures as the variable portion type. These selections allow you to use SKFs as the tracing factor. Using the plan SKFs rather than the actual SKFs as the variable portion type provides you with additional flexibility to use the SKF as a tracing factor for closed period plan allocation using the same allocation cycle. Thus, you can plan in advance for future periods in the year for which you do not see any changes as well as do what-if analysis for completed closed periods.

For each sending support cost center, you create, change, and display an SKF in the system using transactions KK01, KK02, and KK03, respectively (Figure 5). The SKF category contains fixed values because you want the last updated SKF values to carry forward to subsequent periods. The unit of measure should be percent.

Figure 5

Display SKF (transaction code KK01)

You can access the SKF hierarchy shown in Figure 6 via transaction code KBH3. Note the one-to-one correspondence between the SKF hierarchy and the Site Support cost center hierarchy in Figure 1.

Figure 6

Display the SKF group

To get to Figures 7 and 8, click the Senders/Receivers and Receiver Tracing Factor tabs, respectively, in Figure 4. In the Senders/Receivers tab (Figure 7), update the Cost Center listed under Sender as UTILITY, and under the Receiver heading, update the Group field for the Cost Center as DIRECT for Direct Cost Centers (see Figure 1 for the cost center hierarchy). In the Receiver Tracing Factor tab under the Selection Criteria section (Figure 8), update Stat. key fig. as UTIL for Utility tracing factor.

Figure 7

Assessment cycle — Senders/Receivers tab

Figure 8

Assessment cycle — Receiver Tracing Factor tab

With these settings, the assessment cycle setup is complete. Now you build an Excel interface to upload the tracing factor into your SAP system. As shown in the Tracing Factor section in Figure 8, the Var.portion type is set to Plan Stat. Key Figures. This setting makes the system accept plan SKFs as the tracing factor for cost allocation.

Create an Excel Interface for Updating the Tracing Factors

You now create a planning layout that facilitates Excel integration of the tracing factors obtained from various plant finance managers. Using transaction code KP86, you create the planning layout for an SKF upload. Planning layout specifies the contents that appear in the header, rows, and columns in the planning screen. Here, you prepare the layout for handling the automation in the planning screen for an SKF. You access this screen via transaction code KP46.

Create a new layout using a name in the allowed namespace (e.g., A1-101EXCEL: Excel SKF TF Upload) by copying a standard layout provided by SAP, 1-303C: Stat. KF: central. KP86 comes with standard layouts that SAP provides. Now, from the header of the KP86 screen, create a new layout. Click Edit > General data selection or press Ctrl + F5 to arrive at the screen shown in Figure 9. Select the row Stat. key fig. and click the single arrow button to move it under the Available characteristics section as shown in the right. Thus, the selection screen of the layout only has Version, Period, and Fiscal Year, and you have the characteristic Stat. key fig. available for making a lead column in the layout for Excel-based input.  

Figure 9

Planning layout — general data selection

Using Edit > Columns > New lead column or by pressing Shift + F11, you create lead columns for the SKF and cost center. The key figure tracing factor is created for capturing the allocation percentages. With the assessment cycle designed to read SKF (Figure 9) as the tracing factor, it uses the tracing factor or SKF values captured using the Excel interface as the basis of cost allocation. After removing additional columns such as activity type, current plan value, and maximum plan value from Figure 10, you arrive at a simplified layout.

Figure 10

Planning layout for tracing factor upload

In Figure 10, select the column header Tracing Factor and click the Number format button to arrive at the screen shown in Figure 11. Select the Factor and Decimal places to ensure that the layout records percentages up to two decimal places.

Figure 11

Planning layout — Number format setting for tracing factor percentages

Next, you modify the planning layout so that you are able to call this layout for the SKF planning transaction KP46. Call up the layout via planner profile settings that can be made using transaction code KP34. The default planner profile is SAPALL. SAP recommends that you copy this layout and make any changes to the copy. In the copied Planner profile field enter ASMALL, select the Planning Area CCtr: Statistical key figures, and create a new entry for layout A1-101EXCEL that you created earlier. To facilitate Excel-based planning, click the check box under the Integrated Excel column shown in Figure 12.

Figure 12

Planner profile settings for tracing factor Excel integration

Select the layout A1-101EXCEL and double-click the Default parameters. This step takes you to the KP46 screen using the layout A1-101EXCEL. Enter default values in the selection screen and click the Overview Screen button, or click F5 to get to the Excel integrated screen (Figure 13). Click Save file description and Save Excel layout to create the file description and layout for an Excel-based tracing factor upload. A message such as File description 01_1P3_ASMALL_001A1-101EXCEL was saved provides you with the description that can be used during an Excel upload.

Figure 13

An Excel integrated screen for a tracing factor upload

In the layout in Figure 13, the UTILITY cost center allocates support costs to various direct (production and quality) cost centers based on the UTIL SKF tracing factor input to the system using the shown layout.

Actual Allocation Processing at Period End

You can save the Excel layout shown in Figure 13 and circulate it to various site finance managers to record their inputs periodically. The inputs from various managers can be consolidated, validated, and uploaded into the system in one round using resources at shared service centers for the organization.

You can access the SKF planning via transaction KP46 (Figure 14) to upload the tracing factor. Using Setting > Set Planner Profile, you can select the planner profile ASMALL to access the Excel integrated layout A1-101EXCEL: Stat. key figures: central.

Figure 14

SKF planning (transaction code KP46)

You can directly copy and paste the consolidated tracing factors from the Excel sheet recorded by plant managers earlier. Another way to get the tracing factor data into the SAP system is by using the Flexible Upload functionality (Figure 15). You can access the Flexible Upload screen by selecting Extras > Excel Planning > Upload... from the KP46 selection screen.

Figure 15

SKF planning — Flexible Upload functionality

Now you provide the file location and the file description that you generated earlier while configuring the layout. The tracing factor data obtained in Excel can be saved to a local workstation drive in comma-separated values (CSVs). CSV is one of the formats in which Microsoft Excel can save spreadsheets or a tab-delimited .txt file. The Flexible Upload program can read these file formats to populate the tracing factor in the system. You can generate the upload file template by saving a local copy of the layout that appears in KP46 (Figure 14). Figure 16 shows a sample upload file in a tab-delimited format.

Figure 16

A tab-delimited file format for Flexible Upload

Any file format inconsistency leads to an error message that can be used in validating and updating the file format. A successful upload generates the message shown in Figure 17.

Figure 17

A log indicating a successful upload via a Flexible Upload program

After the tracing factors are uploaded, you can use the allocation cycle for an allocation run. You execute the allocation cycle via transaction code KSU5. Input the period of execution, allocation cycle name, and start date. Now execute the transaction, which displays the allocation results (Figure 18). You see that the utility support cost center’s costs are charged to the direct (production and quality) cost centers using the tracing factors that are uploaded via the Excel interface.

Figure 18

Results of an allocation run using uploaded SKF tracing factors

Now you know how this solution design can automate the period-end support cost allocation. With every change in an allocation rule, you do not need to revisit the assessment cycle. Simply updating the revised rule via KP46 meets the requirement. However, you need to take the following key considerations into account:

1. The design requires creation of a segment per sender. The number of senders could be high, thus requiring much one-time initial effort for long-term benefits. You can use the Legacy System Migration Workbench (LSMW) tool for creating such assessment cycles during cutover.
2. The solution uses portions in place of percentage for the tracing factor. The design is functional even when the allocation does not add up to 100.
3. From a business process perspective, additional manual validations can be mandated in Excel before uploading the tracing factor to the SAP system to ensure that the percentages add up to 100.

Ashim A. Nanda

Ashim A. Nanda is a managing consultant in SAP FI/CO working with IBM Global Business Services. He has more than eight years of experience with implementation, global rollout, and maintenance projects for clients in the pharmaceutical, telecommunication, automotive, and high-tech industries. Ashim holds an MBA in finance and systems from Xavier Institute of Management, India, and a degree in electrical engineering from University College of Engineering, India.

 

You may contact the author at ashim.nanda2001@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.