Example: Planning with Integrated Excel 
Example: Planning with Integrated Excel
This section describes how you can best use integrated Excel for your sales and profit planning.
The following scenario, for example, is supported very effectively by integrated Excel:
A planning coordinator is responsible for sales planning in a corporate group. He or she creates templates for the individual sales organizations, which then plan the sales figures before sending them back to the central planning for the group. The planning coordinator then consolidates all the planning data for the group and processes it.
Use of integrated Excel means that only the planning coordinator in this scenario is working in the SAP System. He or she sets up Customizing by formatting an Excel template, saving copies of it locally and sending those copies as simple Excel files to the sales organizations. In the sales organizations, planning occurs in Excel without any connection to the SAP System and the files are then returned to the head office, where the planning coordinator imports the planning data into the SAP System using the Excel uploading function.
For this scenario, it makes sense to have as small an amount of technical responsibility as possible placed with the local sales organizations. You should ensure in Customizing that the local planners are only allowed to plan the data that is relevant to them. Moreover, the local planners should be affected as little as possible by the technical restrictions brought about by using integrated Excel (see the sections
Setting up Integrated Excel and How To Plan Using Integrated Excel).These aims can be attained by observing the following:
Since the data is uploaded from the first spreadsheet, this is the one that should be set aside as the "SAP sheet". It should not be possible to process data in this sheet. In a second spreadsheet, data can be copied to any desired item and processed. Macros should be used to ensure that the data is consistent in both spreadsheets.
Practical Conversion
First Spreadsheet
The first spreadsheet is used to provide an overview. The data is organized in the same way as when it was downloaded from SAP System. The individual profitability segments are listed in rows. To separate the planning data from the header data, the planning data area is placed just two lines lower (using the function in SAP System). For purposes of illustration, the cells have been stored in colors.
The first spreadsheet is protected as a whole.
This procedure has the advantage that the data can be imported safely from the first spreadsheet. Spreadsheet protection eliminates the danger of data being moved with the Excel function and the danger of losing the assignment to the SAP System using
file description . Furthermore, there is no way of adding data that could be misinterpreted as a profitability segment during the upload. The formatting - which could theoretically pose problems during upload - are simply discarded when the file is converted into the ".TXT" format. The central planner only needs to delete the totals row before uploading to ensure the import is carried out correctly. See also Planning Offline and Uploading into the SAP System.Second Spreadsheet
The second spreadsheet is structured so that the profitability segments can be imported and processed individually. To switch between profitability segments, you can use pushbuttons set with macros.
Spreadsheet protection can be set to be more flexible in the second spreadsheet than for the first one. It is possible to remove the protection from just the cells into which the planning data is to be entered. You can also remove protection from the entire spreadsheet to make additional calculations and so forth possible.
In the present example, entries can only be made in the value field for volume, whereby the other value fields for the profitability segment (cells B13 to B17) remain protected. Under additional calculations, it is possible to include information about the negotiated price, the average portion of sales discount and the average portion of product costs.
All other values for the profitability segments are calculated using the following formulas, stored in the respective cells:
Revenue = quantity * price (cell content:
=B13*F27 )Sales deductions = revenue * average portion of sales discount / 100 (cell content:
=B14*F28/100 )Cost of goods sold = revenue * average portion of product costs / 100 (cell content: =
B14*F29/100 )Contribution margin I = revenue - sales deductions - cost of goods sold (cell content:
=B14-B15-B16 )Macros
Work with visual basics macros is restricted when working with Excel in the SAP interface due to the connection to OLE (Object Linking and Embedding). For example, macros cannot be recorded. Moreover, no control elements (that usually start the macros) are available. Instead, you have to draw graphic objects in Excel and use them to store the macros (the gray rectangles in the above diagram).
The above example contains the following macros:
Definition of global variables:
The macro initialize is performed as soon as the second spreadsheet customer is activated. First, variable assignments are used as the initial line to determine the number 14 (the first line of data in the first spreadsheet). Then, the macro get_item is called up and used to read and copy the data in this line. The total number of lines of data is also calculated. The system assumes that the final line is the totals line and that it therefore does not contain any data records to be processed. Hence the final line is not included in the calculation.
The macro get_item reads the data from a line in the first spreadsheet and copies it first of all to the auxiliary cells in the second spreadsheet. These are cells which, in coding, have 100 as a second value (in the above spreadsheet, cells CV13 to CV17). They can be placed anywhere on the spreadsheet or made invisible. Only the quantity value is placed into that cell for the profitability segment that can accept entries. If the other data were also to be transferred directly into the value fields of the profitability segment, the formulas stored in the cells (mentioned above) would be overwritten. In order that these cells also contain data, the three cells necessary for the formulas (those cells used for additional calculations) must have those values entered into them that are used in the formula to arrive at the corresponding values from the first spreadsheet. For this, the result from each inverted formula is written to the cells for additional calculations (see the last lines of the macro).
The macro update_item copies any data, changed or otherwise, for the profitability object in the second spreadsheet and transfers it back to the first spreadsheet.
The macro first_item is called up by selecting First.
The macro next_item is called up by selecting Next.
The macro previous_item is called up by selecting Previous.
The macro last_item is called up by selecting Last.
It is recommended to create macros first in Excel on its own and then to use Customizing to copy them into the spreadsheet template. In this way, you can always go back to a version of your macros. Otherwise, if a macro contains an error or if an instruction cannot be performed with OLE, it could happen that the spreadsheet template can no longer be opened after being saved in integrated Excel.
