BPC (Business Planning and Consolidation) Consulting

Many companies wonder that SAP strategy is used for business planning and consolidation with the recent trend in business objects and outlook soft. Let's see how SAP has been incorporating the outlook soft technology into business planning and consolidation. And let also see how these application can be compared to the existing SAP and business application.

Business Planning and Consolidation – BPC is a tool and is a part of SAP enterprise performance management. BPC Consulting is one of the components of SAP that is focused on financial performance management. It is used to help the organization to manage their financial planning, consolidation and strategic reporting needs. The current business planning and consolidation comes in two version. One is to integrate the SAP net weaver and the other is a standard Microsoft version that is used to accommodate the current outlook soft technology.

EPM – Enterprise Performance Management is a new module that improves the integration among the financial performance management tool and the other SAP applications like net weaver, BI, and GRC (SAP Governance, Risk and Compliance). As a part of this module SAP introduces BPC consulting. This application can be used as a choice to move forward in budgeting, planning, forecasting, and financial consolidation.

After the implementation of business objects and outlook soft, SAP found an overlap of products and made a decision to place the duplicate product in maintenance mode. This is made possible with a clear articulated maintenance rules, migration kits, and declaration of functional parity.

BCP Consulting expertize in working with multinational corporate managers and executives in order to meet their organizational and professional goals. With the help of BPC, a team can be made more effective and efficient by raising their profile among the high competitive corporate environment.

EPM Add-in report with multiple Categories in SAP BPC, version for Microsoft and NetWeaver 10

EPM Add-in report with multiple Categories in SAP BPC, version for Microsoft and NetWeaver 10

With the new user interface in BPC 10, one might wonder how to create a report rendering two different Categories: Actual data for closed months and Plan data for the outlying months.  Follow the below instructions and you will impress your client in no time. 

First, we need to create a property informing the system which months are closed or what the beginning Plan month is.   One way is to create a property on the Category dimension named OPENING_MTH and the administrator updates this monthly.  One advantage of this is your Categories can have different beginning months.  Another was is to create a property on the Time dimension (CLOSED) with valid values of “Y” or blank.  Either way will work and you should base your decision on the businesses requirements.  For this blog, we focus on the later and create a property on the Time dimension. 

Create CLOSED Property on the Time Dimension

Login to BPC 10 Portal and click Planning and Consolidation Administration.

When the Administration tab opens, click Dimensions.

Highlight the Time Dimension and click Edit Structure (at the top).  Be careful to not double-click on the Time dimension as that will take you to the Time dimension members. 

Once you are in the Structure of the Time dimension, click Add and then select New Property.

Type in CLOSED for ID and Name will default with CLOSED.  You can change the Name to something else that is more informative like Closed Actual Months or Actual Months.  Enter 1 (one) for the Number of Characters as this will be either a “Y” or blank.  Click OK to when finished.

Save the dimension by clicking Save.

Click Close when the dimension has finished saving.

Either double-click on the Time dimension to open it or single click and choose Edit Members.

Find the newly created CLOSED property when the Time dimension opens.

Enter a “Y” in all months, quarters, and Years that are closed.  In the below, I have enter a Y for month January, February, March, April, May and Quarter 1 for 2010.

Save and Process the dimension.

When the splash screen asking to take the environment offline, click No.

When the Time dimension has finished process, click Close.

Now it is time to start configuring the actual report.  To do this, click on the EPM Office Add-in for Excel.

Choose the correct model (in BPC 7.x, this was an application) and click OK.

At the prompt, supply a valid user ID and password and click Logon.

Since we are going to be creating formulas above the report, place your cursor in cell G7 and click New Report under the EPM ribbon.

Drag the Account dimension to the Row axis and the Time and Category dimensions to the Column axis.

Click the Category dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose Actual from the Dimension Members pane.  Ensure Member Only is selected for the expansion (at the bottom), and then click the right arrow.  Click OK when done.

Click the Time dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose a single month and select Member Only in the Selection Relationship dropdown (bottom).  Next, click the right arrow to bring the selection into the Selected Members pane.  When finished, click OK.

Click on the Account dimension and choose accounts that you have Actual data for.  In this example, I chose Context (Net Income) and to show the Member and Children. 

Now that the report layout is complete, click OK.

The report should look like below, assuming you used the same dimension members.

In cell F1, type in the formula =EPMContextMember(,”Time”).  Since there is only one active connection, the first parameter can be left blank.  The function EPMContextMember is the new version of EVCVW from BPC 7.x.  Click OK when finished to save the formula.

In cell G1, type in the formula =LEFT(F1,4)&”.01”.  In my environment, the months are represented by numbers (01, 02, 03, etc), but if you can just as easily have .JAN, .FEB, .MAR, etc.  This will be taking the current year in cell F1 and adding a suffix of .01.  Basically, our report will always start with the first month of the year, in this case January.

In cell G2, type in the formula =EPMMemberProperty(,G1,”CLOSED”) where G1 is the first month of the year and CLOSED is the property you defined in the Time dimension.  EPMMemberProperty is the new version of EVPRO.  Again, since we are only using one connection, the first parameter can be blank.

In cell G3, type in the following formula: =IF(G2=”Y”,”Actual”,”Plan”).

In cell H1, type in the formula =EPMMemberOffset(,G1,1).  EPMMemberOffset() is the new version of EVTIM().

Next, copy the contents in cells G2 and G3 to H2 and H3, respectively.

Copy the contents in cells H1, H2, and H3 out 10 (ten) columns to column R.

Next, we need to manufacture the time dimension.  In order to do this, click in cell G5 and enter EPMMemberDesc(G1) for the first parameter.  Click OK when done.

We are going to do the same thing for the Category dimension.  In cell G6, type in EPMMemberDesc(G3) to display the correct Category Member.

Technorati Tags:

Now, copy cells G5 and G6 out to column R.

Click the Refresh Report and two different Category dimensional data will be rendered in the same report.

Bonus:

Now that you have created this, it takes one formula to create a Rolling 12 report.  In cell G1, enter the following formula, change the Time Current Context to 2010.Q2, and click Refresh:

=IF(EPMMemberProperty(,F1,"Level")="Year",LEFT(F1,4)&".01",IF(RIGHT(F1,2)="Q1",LEFT(F1,4)&".01",IF(RIGHT(F1,2)="Q2",LEFT(F1,4)&".04",IF(RIGHT(F1,2)="Q3",LEFT(F1,4)&".07",IF(RIGHT(F1,2)="Q4",LEFT(F1,4)&".10",F1)))))

Robert Marshall   is a member of the BPC CSA team

sap bpc tutorials pdf, sap bpc tutorials, sap bpc tutorials download, sap bpc interview, questions, sap bpc interview questions pdf, sap bpc interview questions free download, sap bpc interview questions and answers

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

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 seventh blog!

Presentation of the business case

Year 2013

P7 owns 100% interests of subsidiary PS7 and 100% interest of subsidiary PS71

A fair value adjustment has been accounted for in PS71:

(USD6 000 – 33% deferred tax = USD4 000)

A goodwill of USD6 000 has been posted on PS71

Year 2014

PS71 net income = USD15 000

At the end of Year 2014, PS71 is merged into PS7

PS7 increase its share capital with 2 000 shares (nominal value of USD10) à USD20 000

PS7 accounts for a share premium of USD65 000 (total equity USD85 000 – increase in share capital USD20 000)

P7 individual accounts in 2014 (including intercompany accounts)

PS71 individual accounts in 2014 (with intercompany accounts):

Detailed merger of PS71 into PS7:

PS7 individual accounts (with intercompany accounts):

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