In the Controlling (CO) module, you create planning data in the SAP system and compare it to actual cost data so you can analyze the variances. SAP provides separate transaction codes for planning for cost objects, such as KP26 for cost center/activity type planning or CP26 for business process planning. With the configuration I’ll show you, you can integrate Microsoft Excel functionality with your SAP system. You then can use data in Excel and upload it directly into your SAP system using these transaction codes after you maintain the required configuration. You could activate Excel integration for any of the transactions, but you should adjust the planning layouts for them to note that you intend to gather data via Excel.

You must maintain the planning transactions in CO manually on a periodic basis. Manual maintenance is time consuming and prone to errors when users enter the planning data into the SAP system. Most organizations use Excel as the primary tool for planning. Users manually carry out planning in Excel and then the planning data undergoes various approval processes to ensure that it is accurate and correct. Excel-based planning builds on the data in the Excel workbook during the planning exercise for direct upload into the SAP system. Direct Excel upload saves time and is also less error prone because it eliminates the step of manually entering data.

I will take you through the necessary steps to maintain the required configuration for Excel integration in your SAP system in two sections: define the layout and define the planning profile. The layout provides a structure in which you can store planning data in the Excel sheet. After you’ve defined it, you assign it to the planning profile so you can upload Excel files directly into your SAP system. I’m using cost center/activity type planning to demonstrate planning using Excel because this transaction is widely used to maintain activity rates in the manufacturing and maintenance environment.

There is little or no information available related to Excel integration functionality in CO and the information in this article is based on my experience. My example uses screenprints and examples from SAP ERP Central Component (ECC) 6.0 but the functionality works for any SAP release.

Configuration Step 1. Define the Layout

The layout definition determines the selection screen and the subsequent screen for the planning transaction code in which users enter the planning data into the SAP system. Figure 1 shows the initial screen and the subsequent screen used in transaction KP26 (cost center/activity type planning) when you select Layout 1-201. You can reach the Overview Screen by pressing F5 or by following menu path Goto > Overview Screen on the Initial Screen. There are two options for Entry and I selected Free entry on the selection screen. Either selection is fine with Excel integration and just depends on your company’s preference.

Figure 1

Select Layout 1-201 in transaction KP26

Figure 2 shows the layout definition for 1-201, which is the default layout provided in the system for use with activity type planning in transaction code KP26. You create a layout to define the sequence of fields on a screen. In Figure 1, these are the fields that you see on the overview screen. The fields on the screen are divided into lead columns (the value of which can’t be changed, such as cost center and activity type) and data entry fields (in which you can enter a planning value). Layout also has general selection parameters, which in conjunction with lead columns define the selection screen parameters. In Figure 1, these are the parameters on the initial screen.

Figure 2

Display SAP layout 1-201

Use transaction code KP77 to look at the layout. Follow menu path Edit > Gen data selection > Gen data selection in transaction KP77 to look at the parameters that appear in transaction KP26 on the selection screen. As you can see here, Version, Period, Fiscal Year, and Cost Center are parts of the General data selection in this layout. The layout is an arrangement of all the fields that appear in the period screen in transaction KP26. However, you can see an extra column for Activity Type circled in the layout. This field is defined as a lead column to differentiate and identify the activity type for which you are going to carry out planning. If you want to use the Excel integration functionality then all the fields that appear on the layout form a part of the Excel layout as well. This, however, means that for layout 1-201 you can only plan for all the activity types associated with one cost center at a time (because cost center is not a lead column in the layout).

You are limited if you use the 1-201 layout as shown in Figure 2 because you can only plan for one cost center at a time. In this example, you want to create a layout so that you can plan for all cost centers/activity types for a period in one Excel file only. To do this, you need to exclude Cost Center from general data selection and include it as one of the lead columns.

To create a new layout, use transaction code KP75. In this example, you only need to make a few changes to the standard layout 1-201, so create a new layout by copying layout 1-201 and making changes to suit your requirements.

Use transaction code KP75 to create a new layout for activity type planning (Figure 3). Provide the name and description of the new layout in the two fields next to Planning Layout. Then provide the name of the layout that you’re copying to the new layout in the Copy from area at the bottom of the screen.

Figure 3

Create a new Layout

Press Enter or click on the enter icon on the initial screen for transaction KP75 to bring up the detailed layout definition screen (Figure 4). The layout is a standard Report Painter layout and is a combination of key figures and characteristics. Characteristics are the criteria by which you can select data records and key figures are the value fields that you can maintain or analyze.

Figure 4

Layout details

You can include characteristics at the header level to form a part of the selection screen. You define such characteristics as general data selection, which you can do through menu path Edit > Gen data selection > Gen data selection (Figure 5). If you look at the characteristics in Figure 5 and compare them with the initial screen for transaction KP26 in Figure 1, you can see that both figures have the same characteristics, namely Version, Period, Fiscal Year, and Cost Center.

Figure 5

General data selection in the layout

The initial screen for transaction KP26 contains Activity Type as an additional field. However, Activity Type doesn’t appear under General data selection. The reason for this is that there is a second way to define characteristics in a layout by defining them as a lead column. Regardless of whether a characteristic has been defined under General data selection or as a lead column, it appears on the selection screen. However, a lead column is also included in the overview screen. For the purposes of this process, you need to have both Cost Center and Activity Type defined as lead columns. So, you first exclude Cost Center from General data selection by selecting Cost Center from the Selected characteristics pane in Figure 5 and then moving it the Available characteristics pane. Click on the Confirm button.

Next you need to add Cost Center as a lead column. To do this, follow menu path Edit > Columns > New lead column. This creates a new column on the layout. Double-click on this new column to open a pop-up window (Figure 6). You need to select the relevant characteristic (in this case Cost Center) from the Selected characteristics pane and move it to the Available characteristics pane. Click on the Confirm button to bring up a second pop-up window in which you define the properties of the lead column. Select the property as a characteristic value to proceed further. If you compare the new layout of ZTEST in Figure 6 to that of the standard layout 1-201 in Figure 2, you can see that the system has introduced a new lead column for cost center (CCtr) as the left column in the layout ZTEST.

Figure 6

Lead column in the layout

Now that you’ve defined the layout, you can save it. If you’d prefer to build a layout from scratch rather than copy an existing layout, you can do so by using the definitions for key figures and characteristics (general data selection and lead column) that I mentioned earlier. Next you define a planner profile. Along with the planning area, the planner profile determines the layout you use for planning.

Configuration Step 2. Define the Planner Profile

A planner profile controls the way the system carries out planning. You use it to associate the planning layout with the planning area. Use transaction code KP34 to define a new planner profile. Transaction KP34 has many standard SAP planner profiles including all the planning areas. SAP recommends that you not change the standard SAP definitions, so you should create a new planner profile. Follow menu path Edit > New Entries to add a new planner profile. Provide a name and description to the new profile. I have named the profile ZTEST (Figure 7).

Figure 7

Create a new planner profile

Next you need to assign the planner profile to the planning area. A planning area is a representation of the application component. In my prototype, the application component is cost center/activity type prices. Select Planner profiles and double-click on General Controlling in the Dialog Structure. This brings up the assignment screen for the planning area. Look at possible values in a drop-down menu by pressing F4 on the planning area. Then select the appropriate planning area. I selected Cost centers: Activities/prices (Figure 8).

Figure 8

Assign the planning area to the planner profile

The planner profile is linked to the planning layout by means of the planning area. To assign the layout to the planning profile, select the planning area row and then double-click on Layouts for Controlling in the Dialog Structure. This brings up the assignment screen for the layout. I already created a custom layout called ZTEST in configuration step 1. I now assign this layout to the planning area. Select the Integrated Excel check box to activate the integration of Excel with transaction KP26 (Figure 9). You do not need to select the other two check boxes (Deflt denotes that default parameters have been defined and Overw denotes that the system can overwrite default parameters during planning) because the system selects them automatically after you define the default parameters.

Figure 9

Assign the layout to the planning area

Next assign default parameters to the layout. You need to maintain the default parameters to save the Excel layout that you can use later for the Excel-based upload in your system. Select the layout and then double-click on Default parameters in the Dialog Structure. This brings up a screen similar to the initial screen of transaction KP26. Provide a sample set of selection criteria similar to what I have done in the screen in Figure 10.

Figure 10

Assign default parameters to the layout

Follow menu path Goto > Overview Screen or press F5 to associate the Excel file with the planning area (Figure 11). A pop-up window asking you to enable or disable macros may come up depending on your security settings. Enable the macros and proceed further. This is similar to the transaction KP26 overview screen except that it has now opened up in an Excel sheet. Also note that Cost Center is included along with Activity Type in the layout. Now you can plan for all cost center/activity types in one Excel sheet. Click on the Generic file button or use menu path Edit > Generic File (Figure 11). The generic file name assigns the file description to the Excel file. The SAP system checks this Excel file name for a successful upload. You can use the wild character * while naming the generic file for more flexibility. Here you can use the generic file name KP26* to name an Excel file by suffixing any characters after KP26 in the file name. Do not provide a file path here or else there will be errors in recognizing the Excel file later on when you want to upload one.

Figure 11

Define the Excel file name

Save the settings from menu path File description > Save all and save the Excel file on your local drive from the menu path File > Save copy as. You use the Excel file later for planning purposes and uploading to the SAP system. Click on the back icon to navigate back to the overview screen (Figure 12). You can now see the File description associated with the layout. The File description is a combination of a two-digit version of the sequence number from the Itm column (01), technical abbreviation for planning area (1P2), name of the planner profile (ZTEST), position of the layout in the profile (001), and name of the layout (ZTEST).

Figure 12

The planner profile now includes the file description

Upload the Planning Data Excel File in the SAP System

Now that you’ve activated the Excel upload functionality, you can prepare the planning data and format it using the Excel file format you saved earlier. Save the Excel file as a tab-delimited text file (file format .txt). The file name has to follow the naming convention as defined in the generic file name in Figure 11. I have named the file KP26_1.TXT and the generic file name was KP26*.TXT (Figure 13). You can now see the use of the wild character * when I named the generic file name. Note that the screen is quite a bit wider than the others you’ve seen to this point. This is because you can see the full names of the fields in Figure 13.

Figure 13

Prepare file to upload

Use the planning transaction code KP26 and then from the menu path navigate to Extras > Excel Planning > Upload. Browse to the location of the file and select it (Figure 14). In this case you only want to upload a single file, so select Import single file. If you needed to upload multiple files from a directory folder, you’d want to choose the Import file directory option. Click on the execute icon or press F8 to upload the planning data into the SAP system.

Figure 14

Upload the file

After you upload the file into the SAP system, the subsequent screen shows the upload results. In this case you can see the results with a green check mark stating that everything went OK with the file upload (Figure 15).

Figure 15

Upload results

Akhilesh Mittal

Akhilesh Mittal is a lead SAP consultant at Infosys Technologies Ltd. with eight years of consulting and industry experience. He has experience in FI and CO along with exposure to SD. Akhilesh is currently a consultant in the SAP space for a leading organization in the high technology domain. He has a degree in electronics and communication engineering from IIT Guwahati and an MBA in finance and systems from IIM Lucknow.

You may contact the author at akhileshmittal@yahoo.com.

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