Skip to Content
Summary
This document describes how BPC 10 can be used to automate and control the collection of text based data using the distribution, collection and comment functions. In a consolidation scenario this could be particularly useful for disclosure management and collecting information required to produce notes in the annual report.

Authors Biography

Peter Warren is a senior SAP BI-BPC Consultant with a background in accounting. He has managed financial planning and reporting systems for multinational companies for 20 years and has worked with SAP since 2003.

Since leaving his last position managing the BI team of a major energy company in 2013 he has worked as an independent contractor in London.

https://uk.linkedin.com/in/peterwarrensapconsultant

Business Process

At the end of each financial year it is necessary to publish an annual report which includes a full set of financial statements and notes.

The majority of the financial statements and tables for the notes will be produced from reports run from the BPC consolidation system.

There are however a few notes to the accounts that require text based data from various sources to be collected, summarised and analysed in order to produce a paragraph in the annual report . Examples of this kind of note are

Lease payment commitments

Capital commitments

Provisions & contingent liabilities

Insurance claims

A manual process for collecting this data might be to create Excel sheets to capture the required information and email them out to multiple recipients to complete.

Each one of these sheets has to be opened when it is received back and the data from it is copied into a summary sheet.

Once all returns are completed – the data in the summary sheet is analysed and if a disclosure note is required the wording of that note is drafted based on the summarised results..

The process needs to be monitored and controlled to make sure that each entity has either sent in a nil return or a completed return by the due date. The person running the collection process therefore needs to create some monitoring spreadsheet to tick off those that have completed on time and those who they need to chase up.

This document describes how BPC can be used to automate and control this process using the distribution, collection and comments functionalities

Step by Step Guide

Step 1: Create dimensions for the model

Dimension – FORM_NAME

Dim Type – Category

ID

Description

FORM01

FORM02

FORM03

FORM04

Minimum lease payments

Capital Commitments

Provisions & Contingent Liabilities

Insurance Claims

Dimension – FORM_TIME

Dim Type – Time

ID

Description

YEAR2015

YEAR2016

Year to 31 March 2015

Year to 31 March 2016

Dimension – FORM_ENTITY

Dim Type – Entity

ID

Description

AFGHANISTAN

ALBANIA

ALGERIA

ARGENTINA

ARMENIA

AUSTRALIA

AUSTRIA

AZERBAIJAN

BAHRAIN

BANGLADESH

Afghanistan

Albania

Algeria

Argentina

Armenia

Australia

Austria

Azerbaijan

Bahrain

Bangladesh

Dimension – FORM_ACCOUNT

Dim Type – Account

ID

Description

Account type

STATUS

ADD_COMMENT

ITEM01

ITEM02

ITEM03

ITEM04

Line to capture the declaration statement

Line to store text entered in additional comment box

Line 1

Line 2

Line 3

Line 4

AST

AST

AST

AST

AST

AST

Step 2: Create a new model

I have created this as a financial model and linked it to the rates model in case there is a need to perform currency translation

on the data but it could also be created as a simple standard model using just Category, Account, Time and Entity dimensions.

Set security on dimensions as required, I have set entity and time in order to be able to lock down previous periods

/wp-content/uploads/2015/11/image003_830708.png

/wp-content/uploads/2015/11/image005_830709.png/wp-content/uploads/2015/11/image007_830710.png

Step 3: Create data collection forms

Create the basic form in Excel. Add any validation using standard Excel data validation formula.

/wp-content/uploads/2015/11/image009_830712.png

In this form there is a declaration statement at cell C10 to allow the recipient to submit a Nil return

/wp-content/uploads/2015/11/image013_830713.png

Data in the cell range B14:K18 will be saved as a single data string against the Account dimension using the EPMSaveComment() formula

In order to create the single data string add a formula to column L as shown here

I have chosen the pipe | character to delimit the fields, you can choose comma or other character as required

/wp-content/uploads/2015/11/image015_830714.png

In column A enter the Account dimension names against the rows that contain the data you wish to capture

Enter EPMSaveData() formulas in column M as shown. I have added an IF(NOT(ISBLANK  test around the data so that the comment

is only saved when some data has been entered in the first cell on each line. If you don’t do this you will get a lot of entries with |||||| on the

ITEM01 to ITEM04 lines if there is no data on the line

/wp-content/uploads/2015/11/image018_830715.jpg

Now add a simple input form to the sheet so that the worksheet can be used to save the comment data

/wp-content/uploads/2015/11/image020_830716.jpg

Now set the context options so that the form name currency and measures dimensions are fixed and the other dimensions are hidden

/wp-content/uploads/2015/11/image023_830717.png

The input form is now ready for distribution, repeat the process to create additional forms and either save each one as separate workbook

or put them in the same workbook as multiple tabs.

Step 4: Create summarised data reports

The data report will look similar to the input form but will contain the data from all of the returned input forms.

/wp-content/uploads/2015/11/image025_830718.png

Add another tab to the report to retrieve the data and prepare it for presentation in the front sheet. On the Data Sheet tab create a list of all the

entities with all of the accounts. Tip – use the EPMDimensionPropertyValues formula to get the values list.

/wp-content/uploads/2015/11/image028_830719.jpg

Enter the formula into column C that will retrieve the data back from the BPC comments table

/wp-content/uploads/2015/11/image029_830720.png

Step 5: Test the input form and the report

Fill in some of the fields in the input form

Save the data

/wp-content/uploads/2015/11/image032_830721.jpg

Change the Entity on the context options and enter some data for that entity too

Open the Report and refresh the data in the data sheet

/wp-content/uploads/2015/11/image036_830722.jpg

You should see the data saved into the return form

Step 5: Create process status report

Create an Excel sheet as shown with the Entity values down and the Category values across.

/wp-content/uploads/2015/11/image038_830723.jpg

I have then used an excel formula to interpret the values recorded from the Status declaration in the input form so that they present as either

Return Completed, Nil return or No Reply. I have then used Excel conditional formatting to show the No Reply as red and the others green

Step 6: Create a distribution template

The returns form will be distributed via email using the BPC distribution and collection process so it is necessary to create a distribution template.

In the Report Workbook section – select the data collection form created in step 3

The template will need to have a section for each of the Entity dimension values and an email address

/wp-content/uploads/2015/11/image039_830725.png

Step 7: Configure Email access

In the EPM User options – add in the server address , usually something like outlook.yourorganisation.com.

/wp-content/uploads/2015/11/image041_830726.png


You can probably get the server address by looking at Account Settings in the File tab in Outlook but you may need to ask the administrators

of your corporate email system

/wp-content/uploads/2015/11/image044_830727.jpg

The log in details will be the same as your Windows AD login and you will need to manually re-enter the password into this User Options screen

whenever your password changes

Running the Distribution Process

1 Open the distribution template to ensure that the correct email addresses are set for each of the entities

/wp-content/uploads/2015/11/image045_830729.png

2 Check the email addresses

/wp-content/uploads/2015/11/image047_830730.png

3 Run the distribution process to send an email including the Excel returns workbook to each of the recipients in the template.

/wp-content/uploads/2015/11/image049_830728.png

4 Select when to run the distribution

/wp-content/uploads/2015/11/image052_830731.jpg

5 Select email as the method

/wp-content/uploads/2015/11/image053_830732.png

6 Create a subject and text for the body of the email

/wp-content/uploads/2015/11/image056_830733.jpg

7 Select the template and section of the template

/wp-content/uploads/2015/11/image057_830734.png

8 Check the summary

/wp-content/uploads/2015/11/image059_830741.png

9 Process Log

/wp-content/uploads/2015/11/image062_830742.jpg

10 Email result

Since in this example I had selected my own email address for each of the 3 countries I have received a mail with 3 separate forms attached. Each Excel workbook has a different context setting for the Entity dimension and the time period set according to the value entered in the template

Ticking the copy to my inbox resulted in receiving 2 emails as I was the sender and also the recipient

/wp-content/uploads/2015/11/image063_830743.png

The email will be sent from the email address specified in figure 6 above returns.xxxxx.com

(as that’s not a real address the email is from me to me in this picture)

The recipients of the mail open the attachment and fill in the details. They do not need to be BPC users or have any connection

to BPC – they just type in the information then click Save and send as an attachment to send back the completed form

/wp-content/uploads/2015/11/image069_830747.png

For the purposes of demonstration I have filled in all 3 of the returns that I sent to myself and sent them back to myself

Now my inbox has the replies

/wp-content/uploads/2015/11/image071_830748.png

Create an outlook folder to store the replies outside of the Inbox. The reason for this is that the collection process will need

to scan all of the emails in the folder and therefore you want to limit the number it looks at to just those that are BPC replies

Running the Collection Process

1 Select Collection from the distribution menu item

/wp-content/uploads/2015/11/image074_830749.jpg

When you click the button to select the working folder it should open an Outlook dialog box but it doesn’t always appear in front

– you may see an outlook icon flashing on the task bar. If that happens – click on the icon to display the dialog

2 Select the folder containing the reply emails

/wp-content/uploads/2015/11/image075_830759.png

3 Click the boxes to process the attachments

/wp-content/uploads/2015/11/image078_830760.jpg

4 Dialog shows the progress of the process

/wp-content/uploads/2015/11/image080_830761.jpg

5 Run the report to show the data collected

/wp-content/uploads/2015/11/image082_830768.jpg

6 Alternatively you can use the web client to retrieve the comments data and download it to a file

/wp-content/uploads/2015/11/image083_830769.png

7 Run the status report

/wp-content/uploads/2015/11/image086_830770.jpg

Troubleshooting and maintenance

It is possible to clear comment data from the table using the standard data package /CPMB/CLEARCOMMENTS

/wp-content/uploads/2015/11/image088_830771.jpg

To see the data stored in the comments table – first find out the technical name of the table that stores the comments for your model

Use SE38 to run program UJ0_GET_GEN_TABNAME

/wp-content/uploads/2015/11/image089_830772.png

Now use SE16 to view the data in the table

/wp-content/uploads/2015/11/image091_830773.png

Conclusion

The comment function of BPC 10 combined with the distribution and collection process can be a powerful tool that saves time over

manual processes and provides control and status reporting. Even if this particular business scenario is not applicable for your organisation,

by reading this document you should have a good understanding of how to implement and manage these processes.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply