Financial Management Blogs by Members
Dive into a treasure trove of SAP financial management wisdom shared by a vibrant community of bloggers. Submit a blog post of your own to share knowledge.
cancel
Showing results for 
Search instead for 
Did you mean: 
peter_warren_uk
Explorer
5,988
















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




Step 3: Create data collection forms


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



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



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



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



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



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



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.



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.



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



Step 5: Test the input form and the report


Fill in some of the fields in the input form


Save the data



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



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.



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



Step 7: Configure Email access


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



 


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



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



2 Check the email addresses



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



4 Select when to run the distribution



5 Select email as the method



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



7 Select the template and section of the template



8 Check the summary



9 Process Log



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



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



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



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



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



3 Click the boxes to process the attachments



4 Dialog shows the progress of the process



5 Run the report to show the data collected



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




7 Run the status report


Troubleshooting and maintenance


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



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



Now use SE16 to view the data in the table




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.



1 Comment
Labels in this area
Top kudoed authors