The three toughest items in the SAP GL for finance users

SAP GL is notoriously difficult to learn, but once users become adept, they begin to appreciate the data model and consistency of transactions. With that said, the learning curve is still quite steep. When preparing a PowerPoint deck for an informative presentation on profit center derivation, I realized how much consultants ask of their users. With all of that said, I see three areas that finance users consistently struggle to understand.

Currency types

Many ERP systems do not have a disciplined model around handling foreign currency transactions. SAP’s GL is different in that it handles multi-currency scenarios with ease, but many users are not accustom to thinking in more than one currency at a time. Thus explaining differences between document, local, and group currencies has been a consistent point in my career.

Intercompany

Intercompany entries tend to give users fits. SAP’s concept of company code and trading partner, along with profit center and partner profit center, is an elegant way to enable eliminations on complex environments that need to support both legal and management eliminations. At the same time, explaining this concept to user ts is challenging, especially when the legacy system only uses GL accounts to segregate intercompany relations.

A second challenge is that intercompany is a tough topic in general. Many seasoned accountants struggle with eliminations. Trying to get both the accounting reality correct while enabling eliminations becomes as challenging as baking a souffle while stir-frying pad thai.

Profit Center Derivation

Perhaps it’s my monotoned explanations, but my users’ eyes glaze over whenever I begin to discuss how profit center is derived. I’ve tried explaining it in terms of the business process. I’ve tried explaining active split vs passive split vs inheritance, and that certainly doesn’t help. My finance users are top-notch, and I like to think that I’m a decent consultant. So I suspect that this area is really that difficult.

Each of these areas in the GL are quite challenging for both consultants and end-users to understand. So it’s critical to have learning materials prepared and to take the time to explain the in-depth details of these topics. In that way, the user will have the information they need to be successful.

 

Performing GL Conversions (part III)

In the previous two articles on SAP GL conversions, we discussed the process of GL conversions, journal entry flows, and how to handle reconciliation accounts. In this final article, we’ll look at a couple pain points and keys to success that arise in SAP GL conversions.

Currencies

In the GL, there is usually more than one currency type – say document, local, and group being tracked. See here for more information. While performing conversions, it’s incredibly critical to properly populate each currency type. Usually the local currency is the most critical to have correct as it is feeding consolidations, but other types should be considered.

The group currency tends to create the most trouble. If this value is tracked in the legacy system, then it can be populated in SAP. If the group currency isn’t being tracked, then a useful estimate should be created by populating a blended exchange rate

Account and Management Rollups

As previously mentioned, it’s critical to ensure that how accounts are classified and rolled up in legacy matches that of SAP. For instance, if the legacy system has an account “Rent Income” that is classified in the “Non Operating” section of the P&L, there would be a problem if the Rent Income account is mapped to the “Revenue” section of the P&L in SAP.

A similar issue occurs if profit centers and cost centers are not mapped correctly or if the hierarchies are not properly constructed. For instance, if a profit center A is mapped to division 1 in legacy, then it’s critical to ensure the activity is converted to a profit center in division 1 in SAP.

Correct Cost Objects on Correct Accounts

When the GL is designed, a structured approach is taken to what type of cost objects are valid for each account. For instance, revenue accounts require COPA segments or profit centers, expense accounts require cost centers, and balance sheet accounts require a profit center. It’s important that the conversion mapping respect the GL design and map the correct cost object to the correct account type, otherwise data corruption may result.

Convert Often

Just like a business process or custom development must be tested, likewise, a conversion should be heavily tested. Executing a conversion multiple times into a test environment enables finding errors, mistakes, and bugs prior to the conversion being finalized. That makes the final, production conversion much easier and much more likely to succeed in the given time frame.

Many Eyes

Having more than one person validate conversions will help ensure that the data is correct. Also, be sure to feed the converted data into the consolidation system prior to go-live. Otherwise, unexpected problems will crop up when the data is fed to the consolidation program.

In this series, we covered the process, journal entry flow, and keys to success for performing GL conversion. I hope that you found this series helpful and informative.

House Bank Accounts in SAP FICO

House Bank Accounts (HBAs) in SAP are often configured incorrectly during an implementation. This improper configuration causes problems later when additional sub modules such as Electronic Bank Statement (EBS), Cash Forecasting, and Check Reconciliation are brought into scope. In this article, we explore how House Bank Accounts connect to physical bank accounts, some key settings, and common pitfalls to avoid.

House Bank Account Structure

House Bank Account

A house bank account represents a bank account that the company has opened with a bank. There should not be a house bank account for customer, vendor, or competitor bank accounts. HBAs should only be setup for a company’s own bank accounts. Second, each HBA should represent one single bank account and one bank account should only be represented by one HBA. If there are multiple house bank accounts with the same account number, check clearing and EBS operations will be negatively affected.

It’s also important to note that when a bank account is opened, it is opened by a single legal entity. So if a company with fifty legal entities opens a bank account, the account is still owned by one entity inside that company. We see this relationship in SAP in that a single company code is assigned to the HBA.

House Banks

If a house bank account represents a company’s bank account, then the House Bank (HB) represents the bank that the account is maintained at. So suppose a company has account 523552 at Bank of America with routing number 52389593, then the house bank account will be tied to account 523552 and the house bank will be tied to routing number 52389593. Let’s suppose that you have multiple Bank of America accounts with different routing numbers. Should you have a different HB for each routing number? Yes – SAP only enables one routing number per house bank.

Naming House Bank Accounts

SAP gives us four characters to name each House Bank and each House Bank Account. That of course means that we must be pithy and smart about our naming convention. Most companies only have one or small number of banks that they have accounts with. At the same time, there may be a few routing numbers for the same bank. Thus, multiple house banks may be required for the same bank. With that information, a sensible naming scheme for house banks seems to be AAA# where AAA is an abbreviation for the bank name – such as BOA for Bank of America or FTB for Fifth Third Bank – and # is a sequential number for each routing number at that bank.

Similarly, we must be careful about naming house bank accounts. Best treasury practices suggest that accounts should be segregated to only have one type of activity. So disbursements should be executed out of one account and collections should be in a separate account. Thus, a reasonable practice would be to use an abbreviation for the type of activity  and a sequential number. I would do AA## with AA being the short hand for the activity type and ## being a sequential number.

GL Accounts for House Bank Accounts

Most flows in bank accounting revolve around recording a companies cash activity and then reconciling against how the bank recorded that activity. As part of a monthly close operation, bank statements and internal accounting records should be reconciled against one another.

The standard recipe in SAP for cash accounting flows is that say when a payment is issued, a clearing account tied to the house bank account is credited and the vendor account is debited. Then, when the payment shows up on the bank statement, the clearing account is debited and the G/L tied to the HBA is debited credited. That means that the account tied to the HBA should always represent the reconciled activity (and match the balance per the last statement date), while the clearing accounts represent unreconciled activity.

Usually, there are more than one clearing accounts used depending on how much activity is flowing through the account. If the HBA GL account is 444440, then clearing accounts should be numbered 444441,444442,…,444449. If the account has low volumes, then only a clearing account for incomings and another for outgoings may be required. If the account has high volumes, then a separate account should be used for each type of activity (lockbox, ACH payments, check disbursements, etc).

House Bank Account GL Flow

Configuring House Bank Accounts

First, the house bank must be setup as a bank in FI01. The bank really is just used so that SAP is aware that the bank’s routing number is valid and to have a consistent address for the bank. Often times, a file is prepared and mass loaded with all of the known banks in a country to avoid having do to this step every time.

FI01 Create Bank  ((FI01))

Next, the house bank is setup in FI12. First, we’re prompted for the company code of the house bank. Next, a code is assigned to the house bank as previously detailed. The country should be denoted as well. If there is a need for additional payment settings, these can be entered under the DME section.FI12 Create House Bank  ((FI12))

Once the house bank is created, the house bank account should be configured. Key information here is the Account id, the description, the account number, the GL account, and the currency.

FI12 House Bank Account  ((FI12))

Once all of this information is saved, then the house bank account is ready for use in other cash flows such as lockbox, EBS, cash forecasting, and so on.

Questions? Comments? Please leave feedback!

Configuring Lockbox for Cash Application in SAP FICO

The United States, unlike much of the world, still relies heavily on paper checks for payments. In order to facilitate internal control and to streamline processing, many companies utilize a lockbox service at a bank. In this article, we’ll examine the process flow for cash application via lockbox, discuss a few keys to success, and then examine the configuration and technical details in SAP.

Lockbox Process
  1. A customer orders products from a company. The company sends the products to the customer.
  2. The company invoices the customer. On the invoice, the vendor includes a voucher. The voucher includes the invoice number and customer number.
  3. The customer mails their check with the voucher to the bank that the lockbox is setup with.
  4. The bank deposits the cash. The bank’s staff will capture the information off the customer’s check, possibly the information on the voucher, and possibly an image of the check.
  5. The bank sends the company a file with all of the captured payment information.
  6. The company uses the payment file from the bank in order to apply payments against the customer invoices.
  7. Any errors during step 6 must be manually researched and resolved.
BAI2 Overview for SAP Lockbox

Setting up lockbox processing starts with getting a correctly formatted file. SAP requires the file to be formatted in what it calls the BAI2 format. This is not the BAI2 layout that the rest of the world considers the BAI2 format. It is the BAI2 format for lockboxes. SAP thankfully gives us the layout for the file, but not a lot of detail about what is going on. From the help documentation in FLB2, we see that the layout of the file is contained in tables FLB*

SAP FICO Lockbox record layout

You can view the fields in each of those tables to see what fields should be contained in the file. The lockbox file if a fixed width text file. Even if the field is not being used, spaces should be added to the field. All of the meat of the file is in record types 5, 6, and 4. Record type 5 contains the lockbox information, record type 6 contains the check information as well as any invoice numbers that are being cleared. Record type 4 is an overflow of record type 6 and is used for adding additional invoices if they do not all fit in record type 6.

An example of the layout from the SCN wiki is below. You can see that the record type is the first digit of each line.

BAI2 Lockbox Format Example

This workbook details what each field does in each record type. Note that I had to work this out by reading the ABAP of RFEBLB20, so be aware that it may not be exactly perfect, but it’s a very good start.

Some banks are able to actually send the file in the correct format. Other banks are not able to do so and instead a custom program will need to translate the file out of the bank’s format and into the BAI2 lockbox format that SAP expects.

SAP Lockbox Configuration

The first step in the configuration is to set the control parameters for lockbox processing. This step is usually already done by default in most ECC 6.0 installations, but you should double check the settings.

Lockbox control parameters for SAP FICO 1

The most important setting here control the postings in the lockbox process. The standard set of journal entries are:

Dr Bank Account

Cr Incoming Payments

Dr Incoming Payments

Cr Customer Account

The settings in this config screen will determine how and if the postings are made. Have a look at the help text for each for more in depth discussion. The next screen has the setup for each individual lockbox.

SAP FICO Lockbox Posting Parameters 2

Here, the origin and destination that is coded in the lockbox file is entered. Additional parameters such as GL accounts to use for the bank and clearing accounts, document types, and posting keys can be selected. If you choose to code this activity to a house bank account that you setup in FI12, then it can be connected here.

That should give us enough configuration to process a lockbox file. There are other configuration items such as reason codes in AR, default profit centers in the GL, house banks in cash accounting, and disputes in FSCM Dispute Management that integrate here as well, but these are a separate topic and will make a long post even longer.  Let’s look at the lockbox processing screen:

SAP Lockbox Posting FLB2 3

A couple of key items here that impact processing in a big way. The procedure and format should match what is configured in our first config screen. The field “Invoice Numbers” is incredibly important as it determines how the invoice numbers on the lockbox file will be matched to the FI document numbers.

Any items that do not match an invoice after FLB2 is run will show up in FEBA_LOCKBOX. This screen is used for post processing and fixing errors.

Keys to Success in SAP Lockbox Processing

There are a few critical points in the process to ensure that lockbox cash application has a high hit rate.

  1. Bank Integration: The bank who is providing the lockbox file must be on board with providing enough information. Similarly, the treasury department must be willing to pay the fees to capture the information
  2. Cross CoCd Payments: If the lockbox belongs to one company code and the payments are being received in another, then enhancing a user exit will be necessary.
  3. Updates during post processing: Each time that a payment is applied manually, the customer’s bank information should be updated. This approach requires enhancement.
  4. Routine error handling: All lockbox issues and unapplied cash should be examined on a daily basis. If these problems back up, it can make for an ugly close at month end.

Hopefully this information gives you a decent sense of how to configure lockbox for SAP. Feel free to leave comments or questions.

 

  1. SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Control Parameters []
  2. SPRO->Financial Accounting->Bank Accounting->Business Transactions->Payment Transactions->Lockbox->Define Posting Data []
  3. FLB2 []

Performing GL Conversions Correctly (part II)

In my previous post, I discussed the basic process for performing GL conversion. In this post, we’ll turn it up a notch and explore the basic journal entry flow as well as examine a few cases of how to handle subledger accounts such as the AP and AR reconciliation accounts.

Journal Entry Flow

One of the defining features of modern accounting is double entry bookkeeping which gives us the core relationship that debits=credits. In order to convert the GL, we take the legacy GL balances and re-inflate them in the new system. Exploiting the fact that debits=credits, then in the new system we can place the original balance in the converted account and then offset that entry to a conversion account. Assuming that all of the balances are converted, then the conversion account should balance to zero.

Take the following legacy trial balance:

Legacy Trial Balance Detail

Pushing the balances into SAP GL, we see that original balances are inflated where they belong, but that the conversion account is zero.

Conversion flow for SAP GL

This flow works well for most accounts. A bit more care is required for GL accounts that are reconciliation accounts. Let’s consider AR and AP first.

Accounts Receivable and Payable

For AR and AP, a straight GL balance does not have enough detail to properly enable the subledger. At the same time, specifically excluding these accounts is a recipe for a mess which will lead to finger pointing and argumentation between the people executing and reconciling each of the conversions. In order to properly delineate the activity from both conversions, we can use the following flow.

From the legacy GL, the AR balance is mapped to an SAP AR conversion GL account. Assuming a debit balance, then our entry debits the AR conversion account and credits the GL conversion account. When the AR conversion occurs, it will zero out the AR conversion account and debit each of the customer accounts and flow through to the AR reconciliation account. Any balance left in the AR conversion account usually indicates unconverted activity or other shenanigans.

Here are the legacy invoices. These are the open invoices.

Legacy AR Invoices for GL Conversion

In the GL conversion, the balance of the AR account is posted into the AR conversion account and offset against the GL conversion account period by period (entries G5-G7). During the AR conversion, each individual invoice must be posted in the go live period to debit the customer account and zero out the AR conversion (entries 1-6).

Converted SAP AR

Accounts Payable is done the exact same way, except the debits and credits are opposite.

Fixed Assets

Fixed assets have a similar but more complicated problem than AR or AP. There are usually many fixed asset accounts that are set as reconciliation accounts. There are accounts possibly for each asset class for the acquisition value (APC) of the asset as well as the accumulated depreciation. In the simplest flow, two conversion accounts can be used: one for converting the APC values and a second for converting the accumulated depreciation on the assets. In a more complex environment, separate APC and accumulated depreciation conversion accounts are used for each asset class.

Using these flows should help ensure that a GL conversion is successful. Other flows will work, but like this one, the flow must be systematic and well thought out. In the next and final article, I’ll discuss some of the challenges that are commonly seen, where they originate, and how they can be overcome.

Performing GL Conversions Correctly (part I)

It amazes me that GL conversions go so wrong so often. The recipe is straightforward and if the legacy data is good enough, there is rarely an excuse to have trouble during GL conversions. Yet, I’ve seen quite a few go off track. So in this series of posts, I’d like to talk about the critical points to doing them correctly.

What we’re converting

In general, most GL conversions are done on a period by period basis to ensure that the trial balance, P&L, and balance sheet are correct for the periods in question. Usually line item detail is left behind in the legacy system. So at the end of the conversion process, I should have worksheets that tie out each new account balance drilled down to the relevant cost objects and profit center for each period.

Let’s suppose that we have a GL conversion from a legacy system to our SAP GL that is supposed to span three years of data – 2011, 2012, and 2013 (YTD). We cannot just start with 2011 though. The end balance sheet for 2010 must be loaded to ensure that the starting position is correct. The P&L for 2010 does not necessarily need to be loaded as long as retained earnings is loaded correctly.

The GL conversion process

GL Conversion Process (ETL)

The GL conversion process is usually done in three steps that follows the acronym ETL. First, the existing GL balances are extracted from the legacy system. The balances data is placed into a staging location. This location could be a simple as a flat text file on a file server or as complex as complete database system with a web front end.

Next, the data in the staging table is transformed from the legacy GL characteristics to the new SAP GL characteristics. This step is accomplished with mapping tables that take the legacy value onto the SAP value.  These mapping tables can become exceedingly complex if the logic is ugly.

Third, the transformed data is loaded into the new SAP system. This step is usually done with an LSMW that injects the standard GL posting program, but I’ve also seen other methods such as Idocs and recordings. At this point, all of the GL data should be loaded into the SAP system.

Key checks in a GL conversion

Throughout the GL conversion process, it’s important to ensure that each step succeeds. At the absolute bare minimum, when the data is loaded into the new SAP system, it’s critical to ensure that legacy balances match the new SAP balances per each costing object and period and in each relevant currency. It’s much better though to make checks throughout the process. Often times, a few heuristics will detect problems much earlier. A couple suggestions:

  1. Debits=Credits. If your trial balance doesn’t balance at each step, something is obviously wrong.
  2. P&L matches. The P&L should always match throughout the process. If it doesn’t, something went wrong.
  3. Assets, Liabilities, and Equities match. Each major class should
  4. All values mapped. Once the transformation step is complete, all accounts, company codes, cost objects, etc should be mapped. If any aren’t, the mapping table isn’t complete or there is a bug in the transform logic.

Finally, it’s incredibly important to ensure that the roll-up of the legacy accounts and cost objects matches that of the new SAP system. That means ensuring that an account that is mapped as an asset in the legacy system is still mapped as an asset in the new SAP system. Even more complicated, it means ensuring that a profit center in a management entity is mapped to the same management entity in the new SAP system. Usually the consolidations team will have a lot to say here.

Each mapping of GL accounts should preserve the account group

In our next installment of Performing GL Conversions Correctly, we’ll explore a standard journal entry flow and how to handle sub-ledger accounts.

SAP Fixed Assets Organization Structure

The SAP fixed assets (FI-FA) module has a unique organization structure to enable accurate reporting for multiple purposes. First, each asset must be valued properly for financial reporting purposes. If multiple standards are being used, such as US GAAP and IFRS, then each of these values must be tracked. Second, assets must be valued properly for tax purposes. In the United States, depreciation can be calculated differently for standard US income tax, for the AMT, and for state income tax. So, let’s examine how the organization structure in fixed assets helps with meeting these requirements.

Chart of Depreciation

A chart of depreciation represents are used to enable a common standard of asset valuation requirements across more than one company code. So for instance, all US entities will usually have the same base set of requirements. We don’t want to have to set up asset accounting for each new US company code, so it makes more sense to have a common standard. That’s what the chart of depreciation provides.

Usually, a single chart of depreciation is used for one country. So all US entities are in one chart, all Mexico entities in a second. The reason for this breakdown is that tax rules vary wildly by country, and so having multiple countries covered in one chart is usually a bad idea.

Chart of Depreciation assigned to multiple company codes1

Depreciation Area

Each depreciation area enables an asset to have an alternative valuation. That means that each transaction can be booked against one or more depreciation areas. Even more, each asset can be depreciated differently with different calculations, life, and so on.  Each depreciation area can then be either linked to a ledger in the GL (to enable proper financial reporting) or left standalone (for tax purposes).

For US audiences, book depreciation is usually done differently than tax deprecation. Tax requires the use of MACRS which is an accelerated form of depreciation that is usually not appropriate for book purposes. Thus for US based entities, there will be (at a minimum) one depreciation area for book and a second for the MACRS value. If AMT must be tracked as well, then yet another depreciation area is required.

Multiple depreciation areas in a single chart of depreciation2

Asset valuation in two different depreciation areas3

Asset Class

The asset class is a grouping of like assets. Each chart of depreciation can have one or more asset classes activated in it. An asset class will share the same account determination, the same number range, and the same default depreciation parameters. The key though for asset class is to enable reporting for tax preparation and financial statement preparation. Thus if depreciation on vehicles must be reported individually for tax purposes, than vehicles should be an asset class. Similarly any assets that should be reported in the same GL account can be grouped together in the same asset class or at least in classes with the same account determination.

Configuration of multiple asset classes4

I can’t show a screenshot of the reporting coming off the asset classes since my sandbox is so out of date, but it looks roughly like this:

Asset Balances By Class (Excel)

Here ends our overview of the key org structure objects in asset accounting. This is meant to give a good flavour of the major elements. Certainly in complex environments and those spanning multiple countries, asset accounting can get ugly fast, but SAP provides the tools to properly account for assets for both financial accounting and tax purposes.

  1. SPRO->Financial Accounting New->Asset Accounting->Organizational Structures->Assign Chart of Depreciation to Company Codes []
  2. SPRO->Financial Accounting New->Fixed Assets->Valuation->Depreciation Areas->Define Depreciation Areas []
  3. AS03->Asset Values []
  4. SPRO->Financial Accounting New->Asset Accounting->Organizational Structures->Asset Classes->Define Asset Classes []

Currencies in SAP FI

Understanding currency types in SAP FI is critical for both users and consultants. If a user does not understand the currency types, then they will book entries wrong. If the consultant does not understand them, then serious valuation problems can result.

Consider the following:

We have a Mexico affiliate of a US corporation that is procuring supplies from Germany. The contract is denominated for 500 EUR, but the company reports tax locally in MXN, and operating results from all countries are consolidated into USD. 

What currency should the contract be tracked in? The answer, of course, is EUR, MXN, and USD. SAP gives us the flexibility to account in different currencies for different purposes.

Currency Types in SAP FI

Document Currency

The document currency is the currency of the transaction which depends on the structure of the transaction. In our example, we’re legally obligated to pay 500 EUR. Thus our document currency is EUR. This currency is actually set on the document at the time of the transaction. When we start FB60 to book our vendor invoice, we select the document currency of EUR.

Document Currency on FB601

Local Currency

The local currency is the currency of the company code which represents the legal entity in a ‘standard’ SAP configuration. This currency is used to comply with local tax reporting requirements as well as representing the functional currency as seen in FAS 52 or IAS 21.  In our example above, the functional currency for a Mexico entity is most likely MXN. That said, according to FAS 52 or IAS 21, if we suppose the primary business to be exporting ot the USA, then the functional currency might be USD. In any case, the currency is set on the company code as you can see below configuration screen.

Company Code Currency2

Group Currency

To enable reporting across all entities in an SAP environment – for doing comparative metrics and quick estimations – you can use the group currency. Since our example entity is based out of the USA, then the USD would almost certainly be the group currency. The group currency is set at the client and is consistent across all company codes. You can see the configuration below.

Currency for entire client3

Tying it all together

In the configuration, you specify which currencies are relevant for which company codes. Notice that you can have up to three currencies for the company code in addition to the document currency. That allows for inflation accounting and other currency types that we haven’t covered here. In this very standard setup, we have the first currency set as the company code currency (the local currency) and the second currency set as the group currency. That means, for any transaction, we’ll have a document currency, the currency of the legal entity, and the currency of the entire enterprise.

4

Now, when we post a document, we can see that each currency type is being calculated and tracked – even if we don’t explicitly enter it!

JE with multiple currencies5

 

  1. FB60 []
  2. Defined when you create the company code in SPRO->Enterprise Structure->Definition->Financial Accounting->Edit, Copy, etc Company Code []
  3. Basis can modify the client in SCC4 []
  4. SPRO->Financial Accounting New->Financial Accounting Global Settings New->Ledgers->Ledger->Define Currencies of Leading Ledger []
  5. FB60 []