How to manage scope changes with SAP BusinessObjects Planning and Consolidation 10.0, Version for SAP Netweaver Starter Kit for IFRS? Part 6

This series of seven blogs is dedicated to handling scope changes using “SAP® BusinessObjectsTM Planning and Consolidation 10.0, Version for SAP Netweaver Starter Kit for IFRS". The objective is to illustrate in the BPC Starter kit for IFRS some of the most frequent scope changes.

Part #1: Acquisition of a subsidiary (full goodwill method)

Part #2: Loss of control without any retained interest

Part #3: Acquisition of further equity interests from Non Controlling Interests

Part #4: Partial disposal of an investment in a subsidiary while control is retained

Part #5: Step acquisition

Part #6: Loss of control while retaining an interest - this blog

Part #7: Internal merger between two subsidiaries

Each blog introduces a practical guide that deals with the following questions:

-          What are the regulation requirements that applies to the business case

-          How to handle the business case in the starter kit for IFRS

-          What are the impacts on the financial statements

The business cases presented in these blogs are included in the set of data provided with BPC NW 10.0 Starter kit for IFRS. You can consult them in the database. Please, refer to the operating guide delivered along with the starter kit for further detail on the consolidation process.

These blogs have been written by members of the SAP EPM (Enterprise Performance Management) Starter Kits & Innovations team that develops starter kits on top of SAP financial consolidation products, Financial Consolidation (FC) and Business Planning and Consolidation (BPC). The starter kits are preconfigured contents created to deliver business logic, to speed-up the application deployment and to provide guidance to help maximize advantages of the product. The contents provided in the starter kits consist of reports, controls and rules for performing, validating and publishing a legal consolidation in accordance with IFRS. SAP starter kits for IFRS are provided to BPC/FC customers at no additional charge; they can be downloaded from SAP service market place at http://help.sap.com/.

Now to the sixth blog!

Presentation of the business case

Year 2012

P6 (USD) purchased a 100% interest in subsidiary PS6 for USD125 000

PS6 Fair value of net assets is USD100 000

A goodwill of USD25 000 was recognized.

Year 2013

PS6 Profit for the year = USD20 000

Year 2014

P6 disposes 75% of its equity interest in PS6 for USD115 000

P6 resulting 25% on PS6 is classified as an associate under IAS28 and has a fair value of USD38 000

P6 individual accounts in 2014:

PS6 individual accounts in 2014:

Calculation of the gain on sale of 75% of PS6:

  Fair value adjustment on 25% retained interest of PS6:

  Goodwill on the 25% of PS6

Practical guide

Please click here to access the practical guide

Acknowledgements to Laetitia Lamoureux, Caroline Verrier and Jean-François Bouillon from the EPM SK&I team for their high contribution to the "Consolidation Practical guide".

Your comments about the contents are very welcome. Let us know what you wish to write about.
http://forums.sdn.sap.com/forum.jspa?forumID=400
Facebook http://www.facebook.com/sapifrs
Twitter http://twitter.com/SAP_IFRS_XBRL

EVDRE Memberset Selector in Treeview

In SAP BPC 7.x EVDRE you may want to filter members according to both attribute values and/or member selections. You can find these complex selection examples in "Usage and Considerations of EVDRE" document like "SELF,DEP and ACCTYPE="INC",ID=Account:SalesKorea". For some of end users it may be complicated to hardcode this selection string into EVDRE Dimension Memberset field.

For one of our clients we decided to leverage MS Excel VBA functionality to achieve easy selection of dimension members in an EVCVW functionality fashion. Of course you can argue we may achieve this functionality with defining hierarchies in dimension but I personally do not like to have many many hierarchies, and prefer flat dimension structure.

Lets start with a EVDRE(1x1) in "Sheet1" which has material dimension in row expansion. As you can see Memberset is by default SELF,DEP.

In Sheet2 prepare a EVDRE for only ROW expansion listing Material dimension and place properties which you want to construct hierarchy based on these properties. In BPC MS you can also use EVLST function but since in NW version you do not have this functionality it is better to use EVDRE.

In Excel VBA Editor start with inserting a UserForm.

You can use default controls like combobox, listbox, checkboxes but if you want to use Treeview you have to add Treeview control from "Additional Controls..."

From "Additional Controls" list you can add Microsoft TreeView Control, version 6.0

Place Treeview and two command buttons into your UserForm.

First command button will construct treeview from a selected region in Sheet2.  VBA coding will be similar to the below one:

Dim nodItem As Node
Dim rangeaddress As Range
Dim level1 As Range
Dim level1name, level1key, level1text As String
Dim level2 As Range
Dim level2name, level2key, level2text As String
Dim dimmember As Range
Dim dimmemberkey, dimmembertext As String
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
TreeView.CheckBoxes = True
TreeView.Nodes.Clear
Set nodItem = TreeView.Nodes.Add(, , "ROOT", "ALL")
nodItem.Expanded = True
level1name = "GRUPICI"
level2name = "YAYINEVITEXT"
Set rangeaddress = Worksheets("Sheet2").Range("B138").Cells
For Each c In Worksheets("Sheet2").Range(rangeaddress.Value).Cells
Set level1 = c.Offset(0, 1)
level1key = level1name & "=""" & level1.Value & ""","
level1text = level1.Value
Set level2 = c.Offset(0, 2)
level2key = level2name & "=""" & level2.Value & ""","
level2text = level2.Value
Set dimmember = c.Offset(0, 3)
dimmemberkey = "ID=""" & c.Value & ""","
dimmembertext = dimmember.Value
On Error Resume Next
TreeView.Nodes.Add "ROOT", tvwChild, level1key, level1text
TreeView.Nodes.Add level1key, tvwChild, level2key, level2text
TreeView.Nodes.Add level2key, tvwChild, dimmemberkey, dimmembertext
Next

Second Command Button enables user to write selected nodes into a cell seperated with commas. Code for second button will be like this:

Dim filterdestination As Range
Set filterdestination = Worksheets("Sheet1").Range("A2").Cells
Dim TreeView As TreeView
Set TreeView = UserForm1.TreeView1
Dim FilterString As String
For Each tnode In TreeView.Nodes
  If Not tnode Is tnode.Root Then
    If tnode.Checked = True Then
     FilterString = FilterString & tnode.Key
    End If
  End If
Next
If Len(FilterString) > 0 Then
  FilterString = Left(FilterString, Len(FilterString) - 1)
  filterdestination.Value = FilterString
  Unload UserForm1
Else
  MsgBox ("Please Select Material")
End If

Now our UserForm is ready for users to call, so place a Shape object in Sheet1 and assign a macro to this shape.

When you click on shape object you will get a hierarchical representation of Material dimension like this

After you select dimension members and attribute nodes and click on commandbutton2 as you can see comma seperated memberset is written to destination cell specified in code.

Now you can pass these selected values to EVDRE Memberset field. In case of no selection you may want to use EVCVW option, so using an IF formula for checking empty selection will be beneficial.

SAP BPC allows us to use flexibility of Excel, in this example I have tried to show a simple solution. I hope developers may add some simple but handy functionalities in coming service packs.

You may want to examine a simple macro example without EVDRE's, download here

Best Regards

BPC WebFolder maintenance program

Like any other solution BPC needs to be maintained and needs general housekeeping. Recently, I have been assisting customers with various performance related issue. One of the first things that strike me is that the general housekeeping of BPC is not being done.

A simply example would be the maintenance of the WebFolders. (Please Note: This blog is applicable to the MS Version)

The BPC WebFolders is like a file server, it still needs to adhere to best practises. In doing the performance troubleshooting, I often find that performing some basic maintenance on the WebFolders structure can go a long way in improving the overall performance of the SAP BPC solution. 

General best practise recommendations are:

• Remove any EVDRE debug files
• Remove any files in the PrivatePublications that are older than 6 months
• Remove *.TMP files older than 6 months (You could remove all of the files, it is a copy of your imported data, technically speaking that data is already in the database)
• Remove *.LOG files older than 6 months
• Remove any inactive / old / backup reports or input schedules that are not being used

This kind of housekeeping can be done manually at regular intervals, but it would be way better if this could be automated and scheduled. Hence the reason for this blog.

I wrote a simple C# console application which recursively would go through the WebFolders and remove the files which need to be removed. It is a simple application which can be expanded to do more tasks.

You can download the program from the following link:

http://db.tt/QT1wwlaM

You can download the source code from the following link:

http://db.tt/FKRlfFwp

How the program works..

Pre-Requites:  .NET 2.0 Framework

Essentially the core of the program is in the config file. It is called MaintainBPC.exe.config

In the file you will find the following configurable parameters which have to be amended to reflect your environment

• CleanWF_Log_File – This is the a log file where the operations of the program will get recorded to
• BPC_WebFolders_Path – This is the path of the BPC WebFolders
• Deletion_Period – This tells the program how old the files must be before deleting them
• Delete_Ext – This tells the program which files to delete, this is an important configuration parameter, as you don’t want to delete any input, reports, script logic files, etc
• Prod_Mode – this tells the program to move the files and not to delete the files (this provides an extra layer of security, as you will have to manually peruse the TobeDeleted folder before deleting the files
• ToBeDeleted_Path – this folder will be used when Prod_Mode is set to true

This program has been tested on BPC 7.0 and 7.5. I haven’t tested it on BPC 10 MS version yet.  I am not a programmer, so I am pretty sure that there are ways of improving the program.

Please Note: This program will not be supported by SAP product support and as not standard functionality nor delivered with SAP BPC.

Please test the program thoroughly before deploying and RULE 1; always ensure that you have working backups.

Hopefully this program will help out some of the customers in automating some of the maintenance around the BPC WebFolders.