- SAP Managed Tags:
Financial planning - Overhead Costs and other Gains/Losses, Revenues and Cost of Goods Sold
This is a detailed blog post in the context of SAP Business ByDesign capabilities related to active budget control - see main blog post: Active Budget Control in SAP Business ByDesign
But the information here gives valuable insights to how to enter financial planning data in general.
Why do we need to talk about planning at all?
In SAP Business ByDesign the data entry of financial plan data is supported by using excel templates that integrate with the SAP Business ByDesign system. It is clear that we are not talking about supporting an entire planning process that orchestrates the phases and stages of planning itself. The capabilities allow financial or management accountants to enter the financial plan data into the SAP Business ByDesign system for operational reporting and monitoring in order to benchmark the actuals against the plan. In addition, with uploading deltas and changes there are capabilities to adjust the plan over time if required.
In this blog post I will focus on the planning of revenue and expenses and stick to the main aspects you need to know and keep in mind.
Access the work center ‘Cost and Revenue’ and work center view ‘Planning’. Plan Versions are created at a granularity of:
Creation of new plan versions is only possible by using the excel sheet. After they have been created certain properties can be maintained on the UI. I personally prefer to create an empty plan and step by enter data for the P&L areas via multiple uploads that add on top of each other. Keep in mind that the plan version captures the entire P&L. That means that a complete plan version has plan data for revenues, discounts, overhead costs and other gains/losses.
The entered plan data is can be mainly reported and be used in two areas:
After having switched on active budget control you need to download new planning templates with budget category entry enabled. Simply store it on your computer, file share or shared folder you can access and usually use for your financial planning.
Now open the excel template connect and logon to the SAP Business ByDesign system with a user authorized to do financial planning and logon.
Simply enter the mandatory header information. Unfortunately, you must enter minimum one record to create a new plan version like illustrated.
In the SAP Business ByDesign Plugin for MS Excel use the 'Save Data' option. I personally also would recommend to store the excel file itself to a storage location to document the plan data entry activities. As a result, you now will see in the system an instance of the plan version.
The next few screenshots illustrate the properties you can access and maintain on the UI.
Please take this paragraph as input for a thought process how to manage and orchestrate the plan data entry in a way that suite your needs. I will share my preference here. From a conceptional point of view I prefer grouping plan data entry in logical sections like in the P&L structure:
For sure if you are only interested for entering only plan data for overhead you only can maintain this data. A complete P&L analysis using this plan version would obviously not make sense .I always create a structure like this on a file share to keep organized and track the various excel sheet templates for initial load but also to document later on for delta adjustments in the respective area
I personally prefer to zip the folder structure on a regular basis and apply it at planning milestones to the plan version as a pure documentation of the overall planning process in the SAP Business ByDesign System!
In order to understand how to enter the data in the excel template I created a small overview table to illustrate this. Other critical settings or actions for a created plan version are available from the UI in order to assure that they are consciously applied and not by accident. To mention a few of these critical actions:
Format to enter data
As mentioned, I recommend splitting the upload into multiple planning areas as described before. Since we have one excel template format to input the data, I think it would be helpful to share in which format the data needs to be entered.
* Budget Category – Only relevant if budget check scope is switched on
** Profit Centers – Only relevant if accounting with profit centers is switched on
*** Functional Area – this is not an input field but will be derived / enriched during data load into SAP Business ByDesign. It is relevant if your P&L is structured by function of expense. See special chapter further below
A tip upfront: I first would create a kind of ‘cheat sheet’ for myself to keep an overview of the relevant subset of G/L accounts I consider relevant for plan data entry. It is also a good practice to map these G/L Accounts to the desired budget categories. Usually this should be similar to the settings that you had applied during the budget category definition. For example, the ‘cheat sheet’ could look like this:
Thereafter a filled plan data entry sheet for cost center planning could look like this:
After having saved the data to your SAP Business ByDesign system you can check the plan data entry by looking into the details of the plan version.
You can validate the overall values of the entire plan by:
In this example I’m illustrating how the excel template can be used to plan revenues, discounts and cost of goods sold.
Why is the topic ‘functional area derivation’ important in the context of cost center planning? Please keep in mind that planning overhead cost is also part of your P&L plan & actual reporting. Since many customers report their P&L by function of expense the functional area derivation is of key importance here. How is the functional area derived during upload of the plan data entry to the SAP Business ByDesign system?
The functional area derivation is part of your fine-tuning activities. So this area is carefully to be reviewed. I recommend checking the P&L impact by using the Financial Statement after initial uploads of the plan data for cost center planning to double check the correct setup of your functional area determination.
This special topic is only of interest if you are looking in parallel on the same plan version from a P&L perspective. I initially try to illustrate the problem with a very simplified example of a cost center collecting all costs for energy and distributing / allocating these costs at period end to receiving cost centers with distribution rules.
From a cost center budget planning perspective and cost center plan / actual comparison point of view the data entry could focus on the ‘input side’. Also, the cost center plan actual reports filter on the input side only. But if the area marked in ‘red’ is not considered, the effect in the P&L and income statement is that the costs are being doubled in this example.
If you are using the plan data entry capabilities for the first time you must know how SAP Business ByDesign handles multiple uploads using the excel sheet template. I would like to illustrate this with a simple example for delta upload mechanism and common mistakes you should avoid. This delta mechanism is handy in :
Here is the short story to explain the behavior:
Our financial accountant Julia starts planning cost centers. Shortly before lunch she saves the file and uploads it to SAP Business ByDesign.
After lunch she continues to plan the cost centers MC22000, MC23300, MC42000. She enters the data in the planning sheet like this and uploads to SAP Business ByDesign. Please note that Julia has NOT entered the cost center MC20000 again.
After 2 delta uploads Julia has a combined plan of all 4 cost centers. The second upload simply adds to the existing plan.
Avoid common mistake:
If Julia would have simply extended the planning sheet like this and uploads it again, some unwanted results will occur.
The planning for MC22000, MC23300, MC42000 looks o.k. But after the 2nd upload the amounts for cost center MC20000 doubled. Why? Please have a look at the first cell marked with the red dot on the screenshot above.
The need to adjust a plan occurs in real life on a regular basis. We all know that. This happens during the planning process but also while you are running in the middle of a fiscal year. Typical examples are:
Especially in these change scenarios the ‘delta upload’ behavior described in the previous paragraph becomes extremely handy. Envision you have planned 100 or more cost centers with ~20 G/L Accounts in multiple budget categories. Instead of reuploading the entire P&L plan with multiple thousands of lines only the delta needs to be entered. I would like to illustrate this with a tangible example we all might be familiar with.
In this example the change could look like this:
Julia checks the impact of that delta change in one of the planning reports and confirms the expected results
Please note:
Major plan changes – delta changes or a new plan version – what is the better approach?
If major structural adjustments or totally radical unforeseen changes happen it is a good practice to create a new plan version that accounts for all these changes and gets approved. If the changes are put into effect the accounting team can switch the new plan version to be the default version for reporting. In case active budget control is switches on and a related budget version exists adjustments are required here too.
I hope these tangible examples and recommendations give you guidance how the system handles plan data entry. The upload via excel plugin combined with excel capabilities for the accountant to model customer ‘side-calculations’ for specific needs for his company or type of business. This blog is fundamental also to understand the budget release in context of active budget control because it also uses the excel plug-in and a template to upload the released budget. Moreover, in the budget release scenario a good understanding of the delta mechanism is required.
Back to main blog post: Active Budget Control in SAP Business ByDesign
This is a detailed blog post in the context of SAP Business ByDesign capabilities related to active budget control - see main blog post: Active Budget Control in SAP Business ByDesign
But the information here gives valuable insights to how to enter financial planning data in general.
Overview planning
Why do we need to talk about planning at all?
- Financial planning is the prerequisite to release budget. Without plan data no released budget is possible.
- Released budget is the prerequisite to execute budget checks on the purchase order (PO) or supplier invoice creation for account assignments to cost centers and cost collector projects
- Released budget is related to the default plan version only
In SAP Business ByDesign the data entry of financial plan data is supported by using excel templates that integrate with the SAP Business ByDesign system. It is clear that we are not talking about supporting an entire planning process that orchestrates the phases and stages of planning itself. The capabilities allow financial or management accountants to enter the financial plan data into the SAP Business ByDesign system for operational reporting and monitoring in order to benchmark the actuals against the plan. In addition, with uploading deltas and changes there are capabilities to adjust the plan over time if required.
In this blog post I will focus on the planning of revenue and expenses and stick to the main aspects you need to know and keep in mind.
Access planning and create a new plan version
Access the work center ‘Cost and Revenue’ and work center view ‘Planning’. Plan Versions are created at a granularity of:
- Company
- Set of Books
- Fiscal Year
Creation of new plan versions is only possible by using the excel sheet. After they have been created certain properties can be maintained on the UI. I personally prefer to create an empty plan and step by enter data for the P&L areas via multiple uploads that add on top of each other. Keep in mind that the plan version captures the entire P&L. That means that a complete plan version has plan data for revenues, discounts, overhead costs and other gains/losses.
The entered plan data is can be mainly reported and be used in two areas:
- Financial Statement Plan / Actual
- Cost Center Plan / Actual and Cost Centers - Budget Monitoring
Question: How to access the excel template for financial planning?
After having switched on active budget control you need to download new planning templates with budget category entry enabled. Simply store it on your computer, file share or shared folder you can access and usually use for your financial planning.
Financial Planning - Access to Planning Templates
Download the Planning Template for Income Statement
Now open the excel template connect and logon to the SAP Business ByDesign system with a user authorized to do financial planning and logon.
Connect to SAP Business ByDesign using the SAP Business ByDesign Excel PlugIn
Question: How to create an ‘empty' plan version?
Simply enter the mandatory header information. Unfortunately, you must enter minimum one record to create a new plan version like illustrated.
Creation of an (Empty) Plan Version
In the SAP Business ByDesign Plugin for MS Excel use the 'Save Data' option. I personally also would recommend to store the excel file itself to a storage location to document the plan data entry activities. As a result, you now will see in the system an instance of the plan version.
New Plan Version Created
The next few screenshots illustrate the properties you can access and maintain on the UI.
Plan Version - General Data
Plan Version - View Plan Data (in this case with one record)
Proposal: How to structure the plan data entry from a business perspective?
Please take this paragraph as input for a thought process how to manage and orchestrate the plan data entry in a way that suite your needs. I will share my preference here. From a conceptional point of view I prefer grouping plan data entry in logical sections like in the P&L structure:
- Overhead Cost plan data entry
- Cost Centers
- Direct Cost Projects
- Allocation Adjustments
- Revenue and Cost of Goods Sold plan data entry
- Other Gains and Losses plan data entry
For sure if you are only interested for entering only plan data for overhead you only can maintain this data. A complete P&L analysis using this plan version would obviously not make sense .I always create a structure like this on a file share to keep organized and track the various excel sheet templates for initial load but also to document later on for delta adjustments in the respective area
Financial Planning - Folder Structure on a Fileshare
I personally prefer to zip the folder structure on a regular basis and apply it at planning milestones to the plan version as a pure documentation of the overall planning process in the SAP Business ByDesign System!
Plan Version Attachments - Documenting the Plan Data Entry Process
Question: How to enter data using the excel template?
In order to understand how to enter the data in the excel template I created a small overview table to illustrate this. Other critical settings or actions for a created plan version are available from the UI in order to assure that they are consciously applied and not by accident. To mention a few of these critical actions:
- Delete all plan data from plan version
- Set as default, reset default
- Delete entire plan version including plan data
Format to enter data
As mentioned, I recommend splitting the upload into multiple planning areas as described before. Since we have one excel template format to input the data, I think it would be helpful to share in which format the data needs to be entered.
| Area | Amount Input Format | G/L Account | Budget Category* | Cost Center | Direct Cost Project | Profit Center** | Functional Area *** |
| Overhead Cost Centers | Positive Input 200,00 | yes | Optional* | Yes | n/a | derived | derived |
| Overhead Direct Cost Projects | Positive Input 200,00 | yes | Optional* | n/a | yes | derived | derived |
| Other Gains | Negative Input -10,00 | yes | n/a | n/a | n/a | Manual Input | derived |
| Other Losses / Expenses | Positive Input 10,00 | yes | n/a | n/a | n/a | Manual Input | derived |
| Revenues | Negative Input -1000,00 | Yes | n/a | n/a | n/a | Manual Input | derived |
| Cost of Goods Sold | Positive Input 800,00 | Yes | n/a | n/a | n/a | Manual Input | derived |
* Budget Category – Only relevant if budget check scope is switched on
** Profit Centers – Only relevant if accounting with profit centers is switched on
*** Functional Area – this is not an input field but will be derived / enriched during data load into SAP Business ByDesign. It is relevant if your P&L is structured by function of expense. See special chapter further below
Example: Plan data entry for cost centers
A tip upfront: I first would create a kind of ‘cheat sheet’ for myself to keep an overview of the relevant subset of G/L accounts I consider relevant for plan data entry. It is also a good practice to map these G/L Accounts to the desired budget categories. Usually this should be similar to the settings that you had applied during the budget category definition. For example, the ‘cheat sheet’ could look like this:
| Budget Category | G/L Account | G/L Account Name |
| DEPREC | 515600 | Depr. Other Machinery, Furniture & Office Equipment |
| EQUIPMENT | 450230 | Material Consumptions |
| OTHERS | 517900 | Other Expense |
| SALARY | 510100 | Wages Expense |
| THIRDPARTY | 511000 | Insurance Expense |
| THIRDPARTY | 513300 | Rent or Lease Expense |
| TRAVEL | 500300 | Travel Expense |
Thereafter a filled plan data entry sheet for cost center planning could look like this:
Example: Plan Data Entry for Cost Centers
After having saved the data to your SAP Business ByDesign system you can check the plan data entry by looking into the details of the plan version.
You can validate the overall values of the entire plan by:
- Using the plan data display in the plan version UI itself
- Using a report ‘Financial Plan Details’
- Using a report ‘Financial Statements - Plan/Actual - Version Comparison’
Example Report: Financial Plan Details using a personalized view
Example: planning revenues, discounts and cost of goods sold
In this example I’m illustrating how the excel template can be used to plan revenues, discounts and cost of goods sold.
| G/L Account | G/L Account Name |
| 400000 | Domestic Sales |
| 401000 | International Sales |
| 450000 | Cost of Goods Sold |
| 409910 | Discounts |
Plan Data Entry - Revenue, Discounts and Cost of Goods Sold
Special topic: functional area derivation
Why is the topic ‘functional area derivation’ important in the context of cost center planning? Please keep in mind that planning overhead cost is also part of your P&L plan & actual reporting. Since many customers report their P&L by function of expense the functional area derivation is of key importance here. How is the functional area derived during upload of the plan data entry to the SAP Business ByDesign system?
| Area | G/L Account | Cost Center | Direct Cost Project | Functional Area Derivation Strategy |
| Revenues | yes | n/a | n/a | 1. From G/L Account & Sales Document Indicator 2. From G/L Account & Sales Document Indicator = space 3. Fallback = Default Functional Area if provided |
| Cost of Goods Sold | yes | n/a | n/a | 1. From G/L Account & Sales Document Indicator 2. From G/L Account & Sales Document Indicator = space 3. Fallback = Default Functional Area if provided |
| Overhead Cost Centers | yes | Yes | n/a | 1. From G/L Account and sales document indicator = space 2. From Cost Center Type 3. Fallback = Default Functional Area if provided |
| Overhead Direct Cost Projects | yes | n/a | yes | 1. From G/L Account and sales document indicator = space 2. From Cost Center Type 3. Fallback = Default Functional Area if provided |
| Other Gains | yes | n/a | n/a | 1. From G/L Account and sales document indicator = space 2. Fallback = Default Functional Area if provided |
| Other Losses / Expenses | yes | n/a | n/a | 1. From G/L Account and sales document indicator = space 2. Fallback = Default Functional Area if provided |
The functional area derivation is part of your fine-tuning activities. So this area is carefully to be reviewed. I recommend checking the P&L impact by using the Financial Statement after initial uploads of the plan data for cost center planning to double check the correct setup of your functional area determination.
Fine Tuning Activity - Functional Area Determination
Special topic: dealing with allocations and distributions in planning
This special topic is only of interest if you are looking in parallel on the same plan version from a P&L perspective. I initially try to illustrate the problem with a very simplified example of a cost center collecting all costs for energy and distributing / allocating these costs at period end to receiving cost centers with distribution rules.
Example: Allocations / Distributions
From a cost center budget planning perspective and cost center plan / actual comparison point of view the data entry could focus on the ‘input side’. Also, the cost center plan actual reports filter on the input side only. But if the area marked in ‘red’ is not considered, the effect in the P&L and income statement is that the costs are being doubled in this example.
Special topic: multiple uploads to the same plan version
If you are using the plan data entry capabilities for the first time you must know how SAP Business ByDesign handles multiple uploads using the excel sheet template. I would like to illustrate this with a simple example for delta upload mechanism and common mistakes you should avoid. This delta mechanism is handy in :
- Planning in multiple business ‘areas or purposes’
e.g., overhead, cost and revenue, others - Planning with multiple financial accountants
- Large organization to be planned
g. multiple 100 cost centers - Having to adjust the plan data over time
Here is the short story to explain the behavior:
Our financial accountant Julia starts planning cost centers. Shortly before lunch she saves the file and uploads it to SAP Business ByDesign.
Plan Data Entry for Cost Center MC20000
After lunch she continues to plan the cost centers MC22000, MC23300, MC42000. She enters the data in the planning sheet like this and uploads to SAP Business ByDesign. Please note that Julia has NOT entered the cost center MC20000 again.
Plan Data Entry for Cost Centers MC22000, MC23300, MC24000
After 2 delta uploads Julia has a combined plan of all 4 cost centers. The second upload simply adds to the existing plan.
Avoid common mistake:
If Julia would have simply extended the planning sheet like this and uploads it again, some unwanted results will occur.
Common Mistake - Extending an already uploaded plan
The planning for MC22000, MC23300, MC42000 looks o.k. But after the 2nd upload the amounts for cost center MC20000 doubled. Why? Please have a look at the first cell marked with the red dot on the screenshot above.
- EQUIPMENT 450230 MC20000 PERIOD 1
during the 1st upload (prior to lunch break) she uploaded 111,11 USD - Now with the 2nd upload the exact same record EQUIPMENT 450230 MC20000 PERIOD 1 another 111,11 USD is added totaling to a period plan of 222,22 = 111,11 + 111,11 USD.
Special topic: plan data adjustments and changes
The need to adjust a plan occurs in real life on a regular basis. We all know that. This happens during the planning process but also while you are running in the middle of a fiscal year. Typical examples are:
- Shift planned budget from one budget category to another
- Shift planned budget from one cost center to another cost center or merge/split cost centers
- Shift planned budget to later periods
- Increase or decrease budget or sales planning to adjust to a changed business environment
Especially in these change scenarios the ‘delta upload’ behavior described in the previous paragraph becomes extremely handy. Envision you have planned 100 or more cost centers with ~20 G/L Accounts in multiple budget categories. Instead of reuploading the entire P&L plan with multiple thousands of lines only the delta needs to be entered. I would like to illustrate this with a tangible example we all might be familiar with.
“Our accountant Julia has to update the plan locally for one cost center MC2000 because it has been decided to change the working environment with less face-to-face meetings in office to more online meetings. That means the required budget to TRAVEL is reduced and shifted to EQUIPMENT in the first quarter and increases THIRDPARTY expenses across the entire year for the Online Meeting Services contract.”
In this example the change could look like this:
Example: Plan Data Change - Shift between Budget Categories
Julia checks the impact of that delta change in one of the planning reports and confirms the expected results
Example: Delta Adjustment in Plan Data after Upload of Changes
Please note:
- you are planning the delta – not the total budget. After the upload you see the overall result in the planning reports.
- With these deltas you also can create ‘negative costs’ temporarily. This is not constrained by the system to give accountants the required flexibility.
- The rational is to give the planning accountant more flexibility is to allow complex changes in a way the accountant wants to apply them probably step by step using multiple uploads. This might in a short intermediate phase cause negative budgets that can be adjusted with the next delta upload.
- It is a good practice to upload these plan changes in the attachment folder of the plan version to document the deltas.
Major plan changes – delta changes or a new plan version – what is the better approach?
If major structural adjustments or totally radical unforeseen changes happen it is a good practice to create a new plan version that accounts for all these changes and gets approved. If the changes are put into effect the accounting team can switch the new plan version to be the default version for reporting. In case active budget control is switches on and a related budget version exists adjustments are required here too.
Summary:
I hope these tangible examples and recommendations give you guidance how the system handles plan data entry. The upload via excel plugin combined with excel capabilities for the accountant to model customer ‘side-calculations’ for specific needs for his company or type of business. This blog is fundamental also to understand the budget release in context of active budget control because it also uses the excel plug-in and a template to upload the released budget. Moreover, in the budget release scenario a good understanding of the delta mechanism is required.
Back to main blog post: Active Budget Control in SAP Business ByDesign
7 Comments
