Intake of Opening Balance

There have been a number of questions being asked on the correct process of taking in Opening Balances in an ERP Implementation. As mentioned in some of my previous posts, the Opening balances fall into the following items.
1. Inventory Quantity
2 Inventory Cost
3. Supplier Balances
4. Customer Balances
5. GL Balances
All the items above have accounting implications. How do we go about taking in OB? The key challenge is that while all the above creates accounting transactions in the subledger, GL Balances also creates balances for the same accounts. This creates double balances in GL.
Different consultants follow different practices to handle the above scenario. The methodology falls into four types.
1. Do not tranfer the subledger accounting entries to GL for the Opening Balances
2. Transfer the subledger balances to GL, but map the same account to both sides of the subledger accounting entry.
3. Transfer the subledger balances to GL and then pass a reverse journal in GL
All these approaches have merits (except 1 perhaps), but I have found the following process to be very effective.
1. Create three separate OB intake acconts for the three modules. Map these accounts as balancing accounts for Creditors, Debtors and Inventory Accounts. The OB intake accounts should be Balance sheet accounts.
2 Take in Opening Balance and Post the accounting entries to GL. The GL will now look as follows.
Debtors A/c Dr
Creditors OB A/c Dr
Inv A/c Dr
Creditors A/c Cr
Debtors OB A/c Cr
Inv OB A/c Cr
3. Modify the configurations and replace the OB accounts with the correct expense accounts in the respective modules.
4. Enter the GL OB as GL transactions in the same period. You can use web adi to upload the transactions.When you upload the OB, ensure to replace the Creditors, Debtors and Inventory accounts with the corresponding OB Accounts. This means that while the OB accounts get one side of the accounting entry from the transactions entered in the respecitive modules , the other side of the accounting entry will come from GL transactions. So after posting the GL tranactions, these accounts will have a zero balance.Verify the same and end date these three accounts. (so that people do not enter transactions in these accounts by mistake)
5. The retained earnings account will automatically adjust to the correct value.
Of course, the above is a broad strategy for data conversion and there are many devils in the details as you do a deep dive into the process. Some of the challenges include, how to handle partially received POs which are yet to be paid, how to handle TDS etc.

What is the advantage of this method? By making sure that the OB Intake accounts have zero balances at the end of the process, it ensures complete integrity of the process. For example, any balance in the intake account will mean that the total balances in the subledger did not tally with the balances in the GL. This means that either subledger figures are wrong or GL figures are wrong - both of which could point to a loophole in the customer's current accounting system.

Analytical V/s Transactional Information

Based on the type of decisions they help to make, information can be divided into two types. One, analytical information and two, transactional information. Analytical informations helps the organization to analyse the data. For example, a trend of purchase price of a raw material is an example of analytical information. Normally you do not take a transaction decision (like increasing the selling price) based on a single jump in the procurement price. Analytical information might ultimately end up generating a transaction decision (organization deciding to increase the selling price) but the main purpose of the analytical information is to help the organization analyse the data.
Transactional information on the other hand is an information which help to take a decision in the current transaction. For example, checking of quality samples is a transaction information. Based on a bad quality sample, the organization may decide to discard a production run altogether. The decisions based on transaction information is short term and immediate and alter the flow of the transaction. In jewellery industry for example, any change in the procurement price is reflected in a change in selling price. In this example, the procurement price of gold is a transaction information.
What is the relevance of the above to ERP implementation? Since analytical information is used to analyse the information, it need not impact the transactional flow. For example, if fluctuation in Procurement price is an analytical information, every single fluctuation need not be captured in the item cost. You can design a report that will provide the analytical data that you want from the above information.
Unfortunately this does not happen in real life implementations. A consultant who creates a GL flex field to capture the customer master is converting an analytical information into a transactional purpose. Another example could be that of a consultant increasing the number of balancing segment because client wanted 'Product wise trial balance'. Or that of a consultant designing an elaborate procedure to provide accurate 'average' price based on every little fluctuation in Procurement price.
Transaction information, as mentioned above, impacts decisions relating to the flow of transactions. Normally, the system should be designed to capture this type of information. Remember that the transaction information will most likely be used for analytical purposes, but the chance of analytical information being used for transaction purposes is very rare.
As a consultant, it is very important for you to differentiate the information requirement into the above categories. This will help you in creating an optimum solution.
If the information is analytical in nature, try to look for a reporting solution. If the information required is transaction in nature, you will need to capture this information in the application and configure the application to handle the flow of this information.

Sound Advice!!

While creating a Chart of Account segments in Oracle, give the line numbers as 1,2,3,4.... etc. Sometimes the practice is to give the numbers as 10,20,30... etc which could cause problems when intgrating with certain applications.

Requirement gathering: The pain in a consultant's life

"How do you prepare tea?"

"Well, we boil some water, put some sugar, add some milk, add some tea, put this powder and boil it all till it becomes tea".

"What is that powder?"

"We don't know."

"Then why are you adding it?"

"Because our mom used to add it?"

"Why did your mom add it?"

"We don't know."

In the world of requirement gathering, this is a scene one comes up so often. Here is an example.

"How do you you create a purchased order?",

"Well first we note down the details of the requirement, take a printout, get it approved and then enter the PO in the legacy system",

"Why do you get the PO approved and then enter in the system?"

"I think it is because....... well I don't know, I have always done it this way"

Or another,

"What are you expecting from the system?"

"We have many warehouses in the city. We need warehouse wise trial balance"

"Why do you need warehouse wise trial balance? do you track assets at each warehouse"

"No, but we are currently doing it this way and we want the system to provide that"

Requirement gathering can be one of the most frustrating part of an ERP Consultant's life. Especially if you are sitting with users who 'add the powder while making tea' without knowing why he / she is doing that way. They follow a process because they have been doing it that way since they started working. Can the system meet their requirements?

Unfortunately many consultants also end up providing whatever the user asks for without probing further without knowing the real reason for these requirements. Many a times it is due to the lack of business knowledge on the part of the consultants. Most of the ERP functional consultants join the IT industry immediately after graduation without having a feel of how the business funtions. Through practice they become very comfortable with the application and end up providing whatever the client asks for in a bid to please the customer. For instance, in the 'Trial Balance for warehouse' scenario mentioned above, one of the consultants came out with the idea of creating each warehouse as a 'company' segment value in Oracle. On deeper probing it was found that what the user wanted was 'warehouse wise profitability' which he had confused with 'trial balance'

He was making tea without knowing why he was adding the 'special powder'

PS: On deeper probing it was found that their mom used to add the special powder because they were living in a cold weather and the special powder had a way of invigorating the body.

Oracle Apps Financials: Some Interview questions

I am giving below some of the interview questions relating to Oracle Financials that I have come across.
1. Oracle financials talk of Set of Books. However at the beginning of an implementation, when you talk of set of books to the CFO, he will most likely tell you that 'He has just an accounting book for the organization'. So he feels that he does not need 'Set of Books'. How can you reconcile the above two statements?
2. What is meant by 'Top Line' and 'Bottom Line'?
3. In P&L, what is 'Gross Profit', What is 'EBIDTA', What is 'cash profit'?
4. What is the concept of 'Bill in Advance' and 'Bill in arrears' in AR?
6. What is profitability? How do you map 'segment wise profitability' in Oracle?
7. Explain the accounting flow in P2P Cycle, O2C cycle etc
8. Foreign currency accounting: Explain conversion, revaluation, translation and remeasurement
9. How do you get the account description in FSG reports?
10. What is mass allocation?
11. Suppose I am paying rent regularly on the premises that I occupy. The amount and the account is the same for each month. What are the two ways in which I can map this requirement in Oracle so that the data entry is simplified. Which approach would you prefer? Why?
12. What are the different ways in which you can map intercompany transactions in apps? which one you prefer and why?
13. What is a ledger in R12? What are the four Cs of a ledger?
14. Is profit an 'Asset' or 'Liability'?
15. What is the difference between 'Periodic' accounting and 'Perpetual' accounting?
ney

Service Tax Flow in Oracle

Introduction
Service Tax is a form of indirect tax imposed on specified services called "taxable services". Service tax cannot be levied on any service which is not included in the list of taxable services. Over the past few years, service tax been expanded to cover new services. The intention of the government is to gradually increase the list of taxable services until most services fall within the scope of service tax.In the Union Budget of India for the year 2006-2007, service tax has been increased from 10% to 12%. For the purpose of levying service tax, the value of any taxable service should be the gross amount charged by the service provider for the service rendered by him
Readers, pl. update this section by providing your comments
Accounting for Service Tax
Service tax follows 'cash basis' of accounting. The actual service tax impact occurs on the event of the closure of the document. For example, the service tax recovery occurs at the time of payment of AP invoice and service tax liability occurs at the time of entering receipts in AR. To integrate the cash basis accounting with the 'accrual basis' of accounting, minimum of four new accounts are setup in Oracle. They are the Service Tax Interim Recovery account, the Service Tax Interim Liability Account, Service Tax Receivable A/c and Service Tax Payable A/c.

Accounting flow in Service tax is simple and intutive.

On PO Receipt

Service Tax Interim Recovery A/c Dr
AP Accrual A/c Cr

On Payment of receipt matched invoice

Service Tax Receivable A/c Dr
Service Tax Interim Recovery A/c Cr

On entry of AR Invoice

Receivables A/c Dr
Service Tax Interim Liability A/c Cr

On applying the receipt against the invoice

Service Tax Interim Liability A/c Dr
Service Tax Payable A/c Cr.

On settlement, if there is a net service tax payable, an invoice is generated on the tax authority

Service Tax Payable A/c Dr
Supplier Liability A/c Cr

Other Features

1. You can net off service tax payable against Cenvat (RG / PLA) accounts
2. You can distribute service tax from one OU to multiple OUs

Since Service tax is an evolving legislation there will be a lot of new knowledge being generated. I want this site to be a one stop shop for Service Tax setting up in Oracle. Pl. be active with your comments so that all the users and viewers to this site can benefit.

Foreign Currency Transaction Concepts

There are four concepts in foreign currency transactions. They are conversion, revaluation, translation and remeasurement.
1. Conversion.
Conversion takes place at the time of the transaction entry. Conversion converts the foreign currency value to the base currency at the days exchange rate. For example, if you have a payable liability of 100 USD and the exchange rate is 1 USD is 40 INR, the foreign currency payable is converted to the INR value of 4000.
2. Revaluation
This happens at the end of the period. The foreign currency account balances are revalued based on the period end exchange rate. Normally the impact goes into 'unrealized gains / losses' account. For example, if the exchange rate is 45 at the end of the period, the foreign currency payable is revalued to 4500 INR and the 'Unrealized Loss' in this case is Rs. 500. The accounting entry in the above case is:
Unrealized Loss Dr. 500
Liability Cr. 500
The above entry is either reversed at the beginning of the next period or carried forward. The actual realized gain / loss is booked at the time of closing the above transaction, by payment against the invoice in the above example.
3. Translation
Translation is used when the organization reports in a foreign currency which is different from its base currency. This process happens at the period end. In this case, the base currency trial balance is translated to the foreign currency trial balance. You can use different exchange rates to translate different account types. For example, you could use historical rates to translate owners equity and fixed assets, the period end rates to translate current assets and liabilities and period average rates to translate income statement accounts.
4. Remeasurement
This happens in situations where your base currency is an inflationary currency. In this case the parent will want to keep a close track of the transactions by remeasuring each and every transaction in the currency of parent company. For example if your base currency is Argentine Peso and the parent currency is USD, each and every transaction in Argentine Peso will be remeasured in USD in the day's exchange rate. This ensures a tight control by parent on the subsidiary's operations and provides the parent with a real time report of the subsidiary's financial performance. This helps effective intervention by the parent in case subsidiary's performance significantly deteriorates due to domestic inflation.

Checklist for initiating an offshore project

Many of the Indian Companies are successfully deploying the offshore delivery model nowadays. Advances in technology and network efficiency has enabled them to connect to the customer network, work on the customer environment, deploy the solution and test the same. I am putting together here a few of the check points that we must consider while initiating an offshore delivery project.
1. Do we have network connectivity?
The normal tool used is VPN and it is normally customer's responsibility to establish the same. Sometimes customer's go for leased lines from ISPs.
2. Has the network connectivity been tested?
This need to be tested for access to the network, access to the development environment and in case of Oracle Apps opening of forms and finally for the connectivity continuity and speed. You may need to test this at different points in time in a week till you are absolutely comfortable.
3. Details of Customer Contact: Who is the customer contact? What is his role - for example is he only going to coordinate or is he an active participant in the project team.
4. Knowledge of English: Does the custmer have a project team which is conversant in English?
5. Database connectivity: Does this project require database connectivity? If yes has the connectivity details been received from the customer and has this been tested satisfactorily.
6. Database connectivity tool: Does the offshore project team have a database connectivity tool like 'Toad' and has the connectivity been tested using this tool.
7. Customer Support Identifier: Almost all the technology vendors have an online support portal for which the customer is given a restricted user identifier. For example, in case of Oracle, the portal is the Metalink and the number is called CSI. You need to receive the CSI number from the customer and the access to the same has to be enabled for the offshore project team so that they can raise support request in case they run into any product related issues during the project phase. You will need to ensure that you have tested the connectivity to the portal using the CSI number that the customer has provided.
8. Does the customer has any specific requirements? Has that been understood well by the project team? Has it been documented?
9. Prerequisites: Does the project have any prerequisites? Have they been identified clearly and action taken against each action item?

These are a few of the points that immediately come to mind. If you have any further points, do let me know so that we can together prepare an excellent checklist.

Preparing for Mark

Mark was coming to our office.
Mark was the head of the IT arm of one of world's largest travel companies who were planning to implement Oracle Apps worldwide. Our organization was trying to get a slice of the pie.
The previous day we had an internal meeting.
"Mark want to meet the team here" informed our program manager, "we will have a team meeting with him tomorrow"
"What is the agenda?" one of us asked.
"We have to demonstrate our knowledge of the domain, the product, the technology and the implementation methodology (AIM)" replied the boss.
"When introducing ourselves we will mention our name, our Oracle Apps experience and our experience in using AIM" continued the boss.
All of us were ready for Mark.
The next day's team meeting went on expected lines. Each of us will get up, and like parrots, repeat 'Hi Mark, my name is ..........., I am a functional consultant with..... years of domain experience and ...... years in apps. I have worked on GL, AP......and have been a part of multiple implementations. I always ensure to follow AIM systematically and methodically to get the best results in our implementation....'
This went on and on. During all this Mark's face was unscrutable and our program manager's face was getting swollen with pride.
And finally it was Mark's turn.
"I see that all of you have done a lot of work using AIM" he observed.
All heads nodded in ascent.
"That is funny, because our organization do not believe in AIM. We feel that it is documentation intensive." He had a naughty smile which suggested that he knew about our team meeting the previous day.
Only sound that could be heard was that of our program manager picking up the broken pieces.

Setting up for Segment wise profitability in Oracle Apps

The Accounting standards in various countries expects the organization earning revenue from multiple product segments to show the segment wise profitability information as a part of the Financial Reporting.

Here is how you can set up the same.

1. Set up an item category as 'Product Segment'.
2. In the GL Set of books, create a chart of account segment as 'Product Segment'. Give validation as table, and link it to the product segment field in the item category table.
3. Set up the cost of goods sold account in such a way that for each item, the corresponding 'Product Segment' value is debited during a shipment.
4. In the item attributes for invoicing, change the 'Product Segment' value in the 'Revenue Account' field to the corresponding value for each item.
5. In AR, set up the auto accounting to pick the 'Product Segment' Value from the 'Standard Lines' for Revenue Account.
6. Create an FSG report to capture Total Revenue, Total Cost and the Profitability for each Product Segment.

And you are done.....

Note:
In R12 there is a feature called 'Supporting Reference' which can provide you with the same information. This means that you need not create a chart of account segment called 'Product Segment' in your ledger. However you will need to create a custom report to provide you with the profitability since segment wise revenue information is available in AR and segment wise cost information is available in 'Cost Management' for Discrete inventory and 'OPM Financials' for OPM.