Planning, reporting, and consolidations are all unified inside the single SAP Business Planning and Consolidation (BPC) application. That means you don't need different tools or technologies to plan or report on your data, nor do you need another tool if you also want to leverage BPC for performing your financial consolidations as well doing your annual budget or monthly forecasts. That provides higher user acceptance and adoption, lower total cost of ownership, and less time spent reconciling different sources of data.

We explained the reporting features of BPC in “An Introduction to Business Planning and Consolidation: The Reporting Features.” Now we'll focus on the planning and consolidation features of BPC. We'll examine input schedules for submitting data, work status for controlling and locking regions of data, posting journal entries, script logic files to manipulate data, as well as the delivered consolidation functions. The focus of this article is to introduce the various functionalities offered.

Input Schedules

In our previous article, we covered the fundamentals of reporting in BPC. However, because it is a unified solution, when a report is built, users can also choose for a report to act as an input schedule. An input schedule is a Microsoft Excel workbook with planning enabled. This allows the users responsible for planning to read BPC data from the server to Excel, then modify it in Excel, and ultimately save these changes back to the server. You can build input schedules similarly to the way you build reports. You can use a number of options including automatically via a dynamic wizard template and then customizing them using the control panel.

Because reports and input schedules are all built entirely in the BPC for Excel client, it means business users no longer have to go to their IT department to request a new or changed report. The user interface is something they know and understand, so they can create self-service reports and input schedules. Typically, a few designated power users in the business have these capabilities and can create the different report and input schedule templates for all the end users in their line of business.

Numerous functions are available within the BPC for Excel client to assist in planning, including functions such as spread, trend, and weight. The spread function spreads a value among selected cells. The trend function grows a value by a factor among selected cells. The weight function allocates a total amount to the selected cells, weighted by the values already found in the selected cells. The benefit to having these functions actually run on the user's machine, and not the server, is that you can execute them quickly.

Figure 1 shows an example of a spread formula. In this figure, item 1 shows a simple grid of data and a user selecting the spread function from the action pane on the right side. Item 2 shows the pop-up screen to perform the spread. In this example, you can see that we want to spread the value of 200 into cells G14 to I14 based on the weighting of cells already in G15 to I15. Item 3 shows the resulting figures updated in the grid. While this example is rather simple, you can see that these functions look and behave in a familiar way, so end users find them quick and easy to use.

Figure 1

Spread formula with BPC planning

After entering the planning data, the user can save the changes back to the server. The option to submit the data is in the action pane and menu in Excel, but a user can also right-click and choose Send and Refresh Schedules from the context menu (Figure 2).

Figure 2

Submit data via an input schedule

As mentioned in part 1 of this article series, BPC uses Excel formulas for sending and retrieving data. We discussed the EVDRE formula, which is the recommended way of sending and retrieving a whole grid of data all at once. This is the optimal approach because it is much faster to send a grid at once instead of sending each cell's contents over the network one at a time. This applies both to reports and input schedules because EVDRE supports both read and write operations. However, in some situations, such as a highly formatted workbook in which you want to control the exact placement of each cell of data, you might not want to use the EVDRE function. In this case, when building input schedules, you can write data back using the EVSND() function. EVSND stands for send value, and you can only use it in input schedules. This is an Excel formula that references the value of another cell, and sends that value to the server when the user chooses to submit his or her data. An example of an EVSND formula is:

=EVSND(D16, "Finance", "Revenue", "Store1", "Budget", "2009.JAN")

This formula submits the value of cell D16 back to the finance application. The other values in the displayed formula correspond to different dimension members for the record. In this case, revenue would be the account to which the user is posting data. You can see the value applies only to Store1, for the budget of January 2009. When data is submitted, the system performs a number of different checks and validations on the server to make sure the data is valid and error free. The end user receives a dialog that shows the number of records sent to the server and, of those, how many were written back successfully or were rejected. For any records that were not written back, the system produces a log to the user describing why the records were not written.

Work Status

As a planning cycle progresses, organizations want to set different statuses for different sets of data. For example, after data (e.g., a budget) has been submitted and finally approved, nobody should be able to go back and change the figures. Therefore, the budget for a certain year should be set to Approved so the system prevents anyone from changing it further. This type of functionality is provided by the Work Status capabilities within BPC. Figure 3 shows the options for building work states.

Figure 3

Work states within BPC

A work state is a status that users can assign to a region of data, such as Submitted, Approved, or Unlocked. Note that these are just examples and you can completely define your own work states. In addition to designating a region of data as locked down, a work state also allows you to get more granular by actually defining who can write data to a region of an application by module. For example, data that is Submitted may now only be changed by that user's manager (Mgr).

For each work state, you can control which parts of the system can be allowed to change the data. For example, even if some data is Approved, you might not want end users to change it, but you might want to allow them to continue posting comments and documents to that cell. The administrator sets the data-locking behavior of each work state in the BPC Administration client. As shown in Figure 3, columns correspond to each module of the system, so that you can configure in detail what is and is not permitted for each work state. The columns shown in the figure are:

• DM: Data manager
• JRN: Journals
• MAN: Manual entries (i.e., data submissions via the BPC for Excel client)
• COMM: Comments
• DOC: Supporting documents

For each of these different modules, you can set the values to four settings (Figure 4):

• All: Everyone has access
• Locked: No one has access
• Mgr: The owner's manager can use the interface
• Owner: The owner can use the interface

Figure 4

Work state settings

By setting these values, you can control who can change the data (e.g., comments or documents) when the data is in the specified work state. The ControlledBy column at the end determines who can set the data to the specified status. For example, maybe the Owner of a cost center can set his data to Submitted, but only his Manager can set it to Approved.

While there are many other configuration options available for Work Status in SAP BPC, it should be evident that this functionality allows you to control the status and behavior of your data. You can use these types of configuration options for planning as well as for consolidation.

Journals

A basic consolidation requirement is the ability to post journal entries. Put simply, journals are another way to write data to a BPC application. However, journal entries are more sophisticated than normal data being submitted (such as in the case of planning). Journal entries have debit and credit postings, can be posted or unposted to the actual application, and are completely audited. To use the journals feature, the application administrator first sets up a journal entry template for each application. Then end users can post journal entries through the BPC for Excel client.

Journal entry templates have the following structure:

• Header dimensions
• Additional header items
• Detail dimensions

You can see how a journal entry is posted in Figure 5.

Figure 5

Journal entry

The users create journal entries by filling out required information and then posting changes to the application. The administrator has set up a journal entry template and defined which dimensions appear on the header and which appear in the detail area. You can see in this example that the Entity and Account dimensions appear in the detail, but Time and Category are in the header. Therefore, when posting the specific debit or credit amount, you can only vary the Entity and Account to which it is being posted. If you want to post to a different time period, you can create a new journal entry.

A user can save a journal entry at any time. When the user is satisfied that the journal entry is accurate and complete, he or she can post the entry. Posting is a significant step, and at posting time you should keep five considerations in mind:

1. The journal entry must be validated for completeness and accuracy. If balanced entries are required, debits must equal credits.
2. The journal entry is locked and cannot be edited.
3. Posting a journal entry inserts the records into the InfoCube, so application data is being changed. One important difference is that unlike using input schedules to submit data, journal entries are appended to the application. For example, take the situation that a particular cell has a value of 90, and I decide it should be 100. In an input schedule, I enter 100 for the cell and submit the change. However, for journal entries, I might post a debit or credit (depending on the account type) of 10 so the total of 90 and 10 equal the desired value of 100.
4. The journal entry is updated to record the user who posted the entry, which you can use later for reporting and auditing.
5. After posting, the status of the journal entry changes to Posted. You then need to unpost a journal entry before it can be changed again.

If your administrator has granted the proper access rights, you can unpost a journal entry to make subsequent changes. Unposting:

• Reverses the changes to the application. In the above example in which +10 was posted, the system now posts -10 to reverse the change.
• Changes the journal entry's status back to Unposted.
• Makes the journal entry read-only to ensure appropriate audit trails remain in place. However, to make a change, you can open it and choose to save it as a new journal entry ID so all the data does not need to be rekeyed. You can then edit the new journal entry and post the new journal entry again to the application.

Script Logic

Script logic is a simple language used in BPC for the creation of functions to achieve a business goal. For example, you might create a script logic file to perform a currency conversion or an allocation. You can use script logic files for planning requirements and executing consolidation functions. We'll describe the consolidation functions more below.

You can create script logic within the BPC Administration client (Figure 6).

Figure 6

Example script logic file (calling the consolidation function CURR_CONVERSION)

When creating script logic, you are creating an LGF file (logic file), which is a plain text ASCII file containing your instructions. Inside a logic file, you generally perform scoping operations (e.g., specifying on which section of data your script logic file should operate), and then the actual calculations or changes you want to perform on the data. BPC supports many keywords as part of script logic.

Users execute script logic files in the BPC for Excel client. All the processing for executing script logic happens on the back-end server. You can also have script logic triggered automatically under certain conditions. For example, an application in BPC can have default logic, which is a script logic file called DEFAULT.LGF. The logic in the file executes every time a record is saved back to the application. You can use this for many different reasons, such as instantly converting any changed records into all target reporting currencies.

Consolidation and Business Rules

BPC has a number of pre-defined functions to support requirements for financial consolidations, such as matching and elimination of inter-company balances, for example. Consolidation capabilities within BPC include both these predefined functions as well as business rules tables that are used to configure the behavior of those functions. For example, business rules can specify an account to which a calculated balance should be posted. While the configuration can get much more sophisticated than this simple example, you can maintain business rules without needing to learn any scripting or programming languages.

Consolidation in BPC has eight functions, each of which can have their behavior configured through one or more business rules table in the BPC Administration client. The functions are:

1. COPYOPENING (balance carry-forward): When opening a new year for processing, you generally want to populate the opening balances in the new year with the closing balances from the last period of the prior year (for the balance sheet accounts). This function performs this carry-forward process, and the business rule configuration allows various parameters to be changed to define how this process is executed.
2. CURR_CONVERSION (currency conversion): This is designed to convert local currency values into one or more group or reporting currencies. You can also configure it in accordance with major Generally Accepted Accounting Principles (GAAP). Currency conversions are often run each time data is posted to the application to ensure reporting in all currencies is always available. Figure 6 shows this function being called from a script logic file.
3. ICDATA (inter-company reconciliation): You can use this program to copy the declarations (e.g., reported inter-company balances) of all entities versus a given entity, by intercompany account. Essentially, it concentrates into each single entity the declarations of all other entities against a specified entity. This mechanism allows the owners of an entity to run a report matching all its declarations against what the rest of the world has declared against the entity, without the need to assign authorization for the owner of the entity to see data for all the other entities.
4. ICBOOKING (inter-company balance booking): You can use this procedure to automatically generate the bookings that make the inter-company declarations match.
5. US_ELIM (US eliminations): When reporting on a group of entities, users want to see the results for the group as a whole. The US elimination performs this function of intercompany eliminations in simpler scenarios in which a full legal consolidation application isn't required (if it is, see the CONSOLIDATION function).
6. CONSOLIDATION (automatic adjustments): This function generates a number of automatic adjustments, based on the results submitted from the various subsidiaries, to create consolidated financial statements that comply with GAAP such as International Financial Reporting Standards (IFRS) and US GAAP. The most important of these adjustments are related to the elimination of inter-company activity between the various reporting units, reclassifications, and supporting the application of the rules for accounting for long-term investments.
7. VALIDATION (validations): Validation rules allow you to check the integrity of entered values before signing off such data as approved. It does this by comparing the balance of one set of records against a second set of records, and writes any difference in the amounts to a set of error accounts. All these error accounts should have a value of 0 after you execute this function.
8. CALC_ACCOUNT (account transformation): This is used for account transformation rules, which read values posted to specific accounts, flow types, and data sources. It also aggregates these values and then posts the aggregated amount to specified destination account, flow, and data source combinations. You can use this to calculate and store amounts required for cash flow reporting, such as calculating opening balances.

Ryan Leask

Ryan Leask currently runs the SAP BusinessObjects Planning and Consolidation solution management team for SAP, based out of Palo Alto, CA. Prior to this position, he led the EPM solution architecture team with a main focus on the design of SAP BusinessObjects Planning and Consolidation 7.0, version for SAP NetWeaver. Ryan has also worked on SAP xApp Analytics, SAP NetWeaver Visual Composer, SAP NetWeaver BW, SAP SEM, ABAP, SAP CRM, analytics/data mining, and whatever else seemed interesting. He has also co-authored SAP xApp Analytics (SAP PRESS, 2006), written many articles, and presented at numerous conferences.

You may contact the author at ryan.leask@sap.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.

Prakash Darji

Prakash Darji is an experienced professional with more than 10 years of end-to-end experience in enterprise software. He has a broad depth of experience including corporate strategy, sales, product management, architecture, and development. He has experience in product launch activities, including positioning, packaging, and pricing. He has delivered numerous product releases in a variety of capacities through his career. He thrives on building high-performing, scalable teams to achieve strategic deliverables, whether they close strategic sales deals, roll in product features, or roll out new releases. He is a recurring author for several publications and a speaker at SAP conferences around the world. Prakash is on LinkedIn at https://www.linkedin.com/in/prakashdarji.

You may contact the author at editor@BIexpertOnline.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.