Creating a Template from Scratch

A script is developed that is delivering two special files to N:\My Documents location. One file supports the graph functionality in standard templates that are used throughout this course. Another file is needed for the VBA hooks exercise. That location is not accessible with the student logon, but running the script is essential to have graphs displaying correctly for the rest of the exercises.

1. In your WTS session, select Start → All programs → → Initialize the course as shown below.

2. Double-click on the script for the IBP110 class you are taking.

   

3. Wait while the VB script runs to copy the files.

4. Choose Yes to allow the script to execute.

5. Choose OK when the process completes.

In Microsoft Excel, create an SAP IBP connection and log on to the system using the credentials provided to you.

1. Open Microsoft Excel and choose the SAP IBP tab.

2. On the Connection section of the ribbon, choose Log On. The Logon dialog box appears.

3. Choose the ... (Ellipsis) button to the right of the Connection field.

4. Choose Create Connection to create the connection with the login credentials provided to you.

5. In the Connection Name field, enter SAP110.

6. In the Server URL field, enter the server address provided to you and choose the ... button to the right of the Planning Area field.

7. Enter the ID assigned to you in the e-mail field and the password, and then choose planning area ZIBPADDON in the dropdown when all available PAs are listed.

8. Choose OK in the Create Connection window.

9. Choose OK and then Log On.

Create a new view that displays the sales forecast and place the data so that you have room enough to create a chart at the top. Save your planning view as a favorite called Sales Forecast##.

1. On an empty sheet, select cell A1 and choose New View → Without template on current sheet....

2. In the time settings, choose the next 12 months.

3. On the Attributes tab, select Product ID.

4. On the Key Figures tab, select the Actuals Qty, Sales Fcst Qty, and Consensus Demand key figures.

   You can expand the Key Figure Groups side panel to validate that these key figures are not assigned to any group.

5. Select Unit of Measure = EA.

6. As the data had been preloaded for all students, you will need to create a filter for products that only you will be using. On the Filter tab, select the Product ID attribute and then choose all products with your ##.

7. Select Add and name your filter My Group Products##.

8. Additionally filter for Product Sub-Family to include only Series 2, then choose OK.

9. Choose OK to display your planning view.

10. In your new planning view, choose the Template Admin tab, and then choose Report Editor.

11. Use the arrow icons to position the data at cell G10.

12. Check that the shift fields have a value of 1 to leave one row between the headers and the data.

13. Choose OK.

14. Add your planning view as a Favorite called Sales Forecast## by choosing the Favorites section of the SAP IBP ribbon and selecting Add.

Finish your planning view created from scratch. You have already completed the initial steps by adding the data using New View and positioning the data on the sheet. In this step, you complete the headers.

1. Select the cell in the header row above the Key Figures column.

   This should be cell F9 if you started your data in G10.

2. Enter =SOP_Heading1.

   This is provided by the SAP IBP add-In and can be used to create your column headings.

3. Select the cell in the header row above the Product ID column and enter =SOP_Heading2.

4. Move cell by cell to the left in the header row, repeating this procedure (step c), increasing the entry by one each time until you have filled all header rows to the end of the worksheet.

   This is done to ensure flexibility in your planning view.

5. Create a header section on your planning view by selecting row by row, the cells above the data table (G1 to R8), and, on the Home tab in the Alignment section, choosing Merge & Center → Merge Across.

6. Also do the merge for cells A1 to F8, and select the merged columns A-F.

7. On the Home tab in the Cells section, choose Format → Column Width and enter 2 to work around an issue with the auto-width feature for merged cells.

8. With the merged columns still selected, double-click one cell border to auto-size the columns.

Create header information using SAP IBP provided names. The SAP IBP add-in provides a range of names. You can use the Microsoft Excel AutoComplete feature or the Name Manager feature for an overview.

1. Select the top merged cell above the data table in your planning view.

2. Enter =IFERROR(SOP_Planning_Area, " ").

3. Select the cell below and use the statements outlined in the table to enhance your planning view.

   Some names depend on the implementation (all conversions, such as currency) or on the state of the worksheet (SOP_Favorite_Name, SOP_Template_Name are only created when the template is uploaded to the server). All expressions in the table are regular Microsoft functionalities.

Format your planning view using IBP Formatting.

1. On the SAP IBP tab, choose Edit View → View Formats. Notice the new worksheet SAP IBP Formatting Sheet had generated in your workbook. If it is not activated automatically, select the tab to activate it.

2. On the SAP IBP Formatting sheet in the Default Formatting section, select the data cell for the Default Format. Set it to a light gray background and a dark gray font color.

3. Repeat this for both the Column and Row subsections.

   You can use the format painter to copy the format from one cell to another in the SAP IBP Formatting Sheet.

4. Format the Labels, for example, give the column label (which will apply to the time bucket headers) a blue background, bold white font, and right alignment.

5. Check that the font size is 11 for both column and row headers.

6. Make sure that Apply check marks are selected.

Give a special format to editable numbers so that the values are red and bold, as opposed to the standard font of the default. Then, observe the difference in formatting

1. In the SAP IBP Formatting Sheet, scroll to the Member/Property Formatting section.

2. Select the checkbox in the section header (Apply).

3. Choose Priority to Row Format.

4. In the Row subsection, select Apply for the Default Format for Editable Cells row.

5. Format the Data as stated.

6. Double-click the right Use cell next to the Data field.

7. In the dialog box, deselect All and select FontColorIndex.

   This overrides the black font color and background from the default with the red font color and bold font, while leaving font size, and so on, of the default format defined previously unchanged.

8. Return to the sheet with your planning view. Notice that the system flashes a reminder (in the top left corner) for you to refresh your planning view to apply the formatting the first two times you do this.

   The next time the planning view is rendered, the formatting is applied.

9. To add a calculated key figure, select Edit View → Edit Planning View, and on the Key Figures tab, select Sales Fcst Rev. key figure.

   Note

   You will need to select currency (choose USD) as this key figure is convertible by the exchange rate and, therefore, needs currency to be specified for displaying in planning views.

10. Choose OK to return to the planning view and observe the difference in formatting between editable and non-editable key figures.

    Result

    Your template created from scratch should look like the one in the figure. The format should be the same but the data might differ as per your selection.

Eliminate error markers and improve your planning view.

1. Choose File → Options → Formulas → Error Checking and deselect Enable background error checking.

   Note that this setting applies to a Microsoft Excel installation, not to the workbook. This means that users have to do this step individually, and the setting is not part of a template.

2. Choose OK.

3. To eliminate grid lines, choose View → Show, and deselect Gridlines.

   You can also freeze the header sections above and left. To do so, select the first cell of the data grid and choose View → Freeze Panes → Freeze Panes.

4. To control whether the IBP add-in optimizes the columns occupied by the planning view with the AutoFit Column Width option, in the SAP IBP tab go to the About section, choose Settings → Options → Display.

5. Choose OK.

6. Update your planning favorite by selecting the Favorites section of the SAP IBP ribbon and specifying Update.

7. Choose Update.

8. To add your planning view as a template, select Templates → Add in the Templates Admin section and type the name of the template, for example, Template from scratch ##.

   Result

   Notice that the template is saved without data. This is done for security purposes. You can refresh the view and key figure values will come back. You will also see that header where you entered the formula for the template name, now displays the name that you had given to the template.

If you have more time, try to improve your template even more.