Planning in Microsoft Excel 

Purpose

As of Mobile Sales Release 4.0 SP01, Microsoft Excel has been integrated with Mobile Sales so that account managers can perform offline planning using a Microsoft Excel interface and the same key figures they have loaded from the SAP SEM and SAP BW systems. This functionality supports account managers in making planning decisions by giving them flexibility and control in planning the figures for which they are held accountable. The option to plan in Microsoft Excel enables key account managers to

·        Analyze data based on their own simulations and calculations

·        Run a series of calculations to arrive at planning figures

·        Work directly in Microsoft Excel with planning figures from SAP SEM and SAP BW

·        Maintain their own copies of their planning calculations locally

·        Perform basic customizing of the Microsoft Excel worksheet

·        Transfer plan data from Mobile Sales to Microsoft Excel

·        Save customized Microsoft Excel sheets as templates for future use

·        Transfer planned values back to Mobile Sales

Prerequisites

You have created a campaign, trade promotion, account plan and entered the required details.

You have set up Microsoft Excel to run in Mobile Sales. To do this, in the Microsoft Windows Explorer, navigate to Tools ® Folder Options. Choose the File Type tab and select the file type Microsoft Excel Worksheet. Choose the Advanced button. Make sure Browse in same window is selected.

In Microsoft Excel, the security level needs to be set to Medium or Low. To make this setting, choose Tools ® Macro ® Security. If you are using Windows XP, select Trust access to Visual Basic Project on the Trusted Sources tab.

Process Flow

...

       1.      You generate a DHTML file in Mobile Sales by selecting a planning customer and opening a planning tile set, such as

·         Marketing/Key Figure Planning

·         Marketing/Volume/Trade Spend Planning

·         Marketing/Cost Planning

·         Marketing/Success Key Measure Planning

·         Account Planning/Volume/Trade Spend Planning

·         Account Planning/Key Figure Planning

For more information, see Planning in Mobile Sales for Campaigns and Trade Promotions or Offline Account Planning.

Generating the DHTML file is a prerequisite for launching the Microsoft Excel interface.

       2.      You choose the Plan in MS Excel button. The system displays a dialog box from which you can select the planning template you want to use. The Microsoft Excel planning template is based on the SEM planning layout. You can also create your own planning templates by customizing already existing templates and saving your settings. In future planning sessions, you can access these saved templates by selecting Other Templates from the options displayed in the dialog box.

After you make your selection, the system transfers the customizing data for the planning layout and the planning figures from Mobile Sales and displays them as a Microsoft Excel workbook within the mobile application.

The Microsoft Excel workbook that is displayed comprises two sheets:

·         Customizing – Displays the basic SEM planning layout

·         Planning – Displays the plan data, including the header information, products information, and planned values for the key figures

On the Planning sheet, you can edit the key figures. 

On the Customizing sheet, you can make basic modifications to the layout that is displayed, however you cannot change the fundamental SEM layout, such as the column headings. You can change the customizing of the plan sheet by adding rows, which will result in additional columns or rows displayed on the Planning sheet. You can also apply Microsoft Excel formulas to display calculated values on the Planning sheet.

If you add columns directly on the Planning sheet, these columns will not be transferred to the Customizing sheet and cannot be saved to the template file.

 

On the Customizing sheet, are the following column headings:

Some columns headings appear with faded out, or grayed, text. These columns are provided by SAP and cannot be edited.

·         Name – Displays the name of the InfoObject in the SEM layout. This can also be the heading for additional columns. If the short text is not maintained for the InfoObject in the SEM layout definition, then the system displays this technical name on the Planning sheet.

·         Allow Edit – Set to YES for key figures and for data that can be edited, otherwise set to NO

·         Convert to Columns – Set to YES for key figures that are displayed in columns on the Planning sheet

·         InfoObject – Set to YES if the column is part of the SEM layout; set to NO if the column contains calculated values

·         Formula – Contains the formula that is applicable. (If the formula is available for a column that has the InfoObject setting YES and is a key figure, then the formula is applied for aggregation across the lead columns for all the period values; if the InfoObject setting is NO, then the formula is used for the calculated column)

·         Range Name – Contains the range definition of the period column

·         Header – Set to YES if the column should be used as a heading

·         Column – Refers to the column number for the corresponding data

·         Dependent On – Refers to the time period column on which the key figures are dependent

·         Color – Applies the pattern in this cell to the corresponding data cells. This is applicable to only the first InfoObject on the Customizing sheet. The system uses the color pattern for displaying the calculated key figures and for the InfoObjects that have aggregate values.

·         Apply formula for all key figures? – Contains X if the formula in the Formula column should be applied to all key figures. You can leave this column blank and in the following columns enter the key figures for which the formula applies.

The range Period contains the time definition for the planning data.

You construct formulas in the Customizing sheet using the conventions of Microsoft Excel. For example, for a calculated column that displays first quarter totals, you would apply the formula JAN+FEB+MAR, using JAN, FEB, and MAR as the values under the column Heading in the Period range.

       3.      In the Microsoft Excel planning sheet, you make your changes to the customizing settings and planning data. You then save your changes.

Once the application is closed, you cannot read the planning data from the saved Microsoft Excel workbooks. However, before closing the application, you can save a copy of the Microsoft Excel planning sheet to your local directory for your records. You can also save the changes you made to the template on the Customizing sheet for future use.

The updated planned values are transferred back to Mobile Sales and appear in the DHTML interface.