GL: MassAllocation


The basic concept of Mass Allocation is dividingcost on some factors.

If we take a simple example then consider 3 departments X, Y and Z using a single landline telephone. Each department has 2, 3 and 6 employees respectively. The bill for month of June-09 of landlines comes out to be Rs.1700. If you have to calculate the telephone usage for department Y. How will you do that? Is it Rs.1700? No. Is it 1700/3 (total divided by total number of employees in dept-Y)? No, because there were 8 more person using the same landline connection. The answer is that you will allocate the total cost into each department based on the number of employee it has. So in this case the allocation will be 1700*3/11 and you’ll get Rs.463.

Here is the department wise total of the telephone bill:

Department X: Rs.309.09 (2 employees usage of 1700)

Department Y: Rs.463.63 (3 employees usage of 1700)

Department Z: Rs.927.27 (6 employees usage of 1700)

This is the formula for MassAllocation A*B/C where,

A = Total Cost

B = Factor (Number of Employee of particular department)

C = Total Factor (Total Employees)

So the Allocation formula for Department Y will be

A = 1700 (Total Landline Bill for a particular month)

B = 3 (Total Number of Employees in Department Y)

C = 11 (Total Number of Employee in all three departments)

In Oracle General Ledger this facility is given to divide or allocate your expenses or revenue income on your selected distributing criteria which can be your number of departments, branches, head count, covered area, etc. In my above example I used Head Count as the basis of allocation.

Steps for generating a Mass Allocation Journal:

  1. Pass a Standard Journal – This will identify your “A”
  2. Pass a STAT Journal – This will identify your “B” and “C”
  3. Define Mass Allocation Formula Batch and Journal.
  4. Validate the Formula.
  5. Generate the formula for specific accounting period.

Let’s see how we can carry out the process of Mass Allocation in General Ledger.

First let me take a scenario. Consider an organization with 4 divisions or departments:

1. Enterprise Resource Planning (ERP)

2. Software Development (SD)

3. Software Support (SS)

4. Network Infrastructure (NI)

The COA Structure of this organization is Company-Branch-Department-Product-Account

The segment values of Department or the hierarchy of Department segment is

0000 – Common or No Department

1200- Information Technology (Parent) (Child Ranges: 1201 – 1299)

1201-ERP

1202-Software Development

1203-Software Support

1204-Network Infrastructure

Let’s allocate the telephone bill expense of Rs. 18950 for the month of June incurred at Karachi branch on the number of employee each department has. The allocation basis in this example is Head Count per Department.

The account code for Karachi branch is 101 and the natural Account for PTCL expense is 50201 and each department has 9, 11, 5, and 3 employees respectively. That is ERP has a head count of 9, SD has 11, SS has 5 and NI has a head count of 3.

Now the MassAllocation procedure steps starts.
STEP1: We will create a total cost or “A” of the formula. Pass a Standard JV in the period of JUNE with the following Lines

Line1: 1-101-0000-00-50201 18950(DR)

Line2: 1-101-0000-00-10122 18950(CR)

Line 1 Account Description: XYZ-Karachi-NoDeparment-NoProduct-PTCL Expense

Line 2 Account Description: XYZ-Karachi-NoDepartment-NoProduct-Bank

This journal entry is equivalent to paying your PTCL telephone bill. Ideally this expense entry should be coming from Oracle Payables. We are manually entering this actual journal so that we can created a Cost Pool “A” having an amount of Rs.18950.

STEP2: Now we will create the “B” and “C” or Usage Factor and “Total Usage”. Pass a STAT JV. STAT is short for Statistical and it can be used by changing the currency from PKR to STAT. The STAT journal doesn’t need to be balanced. But they do affect the account balances if we inquire on the currency type of TOTAL but let’s not get there, it is a different topic. Simply pass a STAT JV to create “B” and “C”. Remember the Period of the JV should be JUNE as the Standard JV.

The account code combination for the STAT journals in this scenario will be

Line1: 1-101-1201-00-50201 9(DR)

Account Description: XYZ-Karachi-ERP-NoProduct-PTCL Expense

Line2: 1-101-1202-00-50201 11(DR)

Line3: 1-101-1203-00-50201 5(DR)

Line4: 1-101-1204-00-50201 3(DR)

By passing or posting this STAT journal we are creating a basis for expense allocation. The line 1 tells that the XYZ organization has 9 employees at Karachi branch in ERP department incurring PTCL Expense. We can enable UOM on STAT journal by enabling the profile option JOURNAL:MIX STATISTICAL AND MONETARY to YES. Similarly so on and so forth. Now where are “B” and “C” in this journal? You can see 4 lines with changing Department codes, these four lines individually represent Usage Factor “B” which is 9, 11, 5 & 3 and collectively they represent Total Usage “C” which is equal to 9+11+5+3=28.

Now moving on with STEP3

Create a MassAllocation Batch and then a Journal. Name it Karachi PTCL Expense Formula.

When you open the formula entry form you will find the three constant of the Mass Allocation formula A, B, C and two other fields T and O. “T” stands for Target Account and “O” stands for Offset Account. I will explain these Accounts later. Let’s continue with the formula.

Now give the account of the “A” which is 1-101-0000-00-50201 having the value of Rs.18950. On the account entry form you will find that the system prompts or asks for Ledger, it is an optional field. This option of ledger set is used when we are allocating cost from multiple ledgers. And there is another LOV having the value as

C: Constant – The segment is constant and doesn’t need any Loop or Sum. And the balance should be picked against “A” as a constant

L: Looping – The segment needs to loop from first value to last value provided in STAT JV.

S: Summing – The segment needs to sum the value in provided in STAT JV.

Generally the account code in “A” doesn’t not need any kind of looping or summing. So every segment should be given the value of C. The value this account has for the particular period should be picked as a constant. Keep the currency as Entered.

Now move on to enter the code for “B”. The account code for Usage Factor in our example will be

1-101-1200-00-50201. Note that I have given the department code as 1200 which is parent of the departments we selected for allocation basis. Give every segment a Constant C but the segment of Department will be having the value as Looping L. Why? Because we need to pick the individual values of 9, 11, 5 and 3.

REMEMBER: looping is only done on Parent Value of the Segment. In this example 1200 is the Parent department which has the child departments 1201, 1202, 1203 and 1204.

The system will automatically pick the allocation basis by matching the natural account and the looping segment.

REMEMBER: The currency for “B” and “C” should be STAT.

Now give the account code for the Total Usage “C”. The account code will remain the same as “B” with 1200 as the department code. The only difference this time is that instead of Looping we will give the Department segment the value of Summing S. so that we can have the sum of head count which is 28.

It’s time to give the “T” account. No, it’s not the T Account as we see in Ledger. It is the Target Account of the cost pool or these are the Debit Accounts which should hold the allocated expense. In our example these account are the accounts we gave in “B”. Yes the account code combination 1-101-1200-00-50201 with 1200 as Looping. IN FACT, usually the accounts given in “B” are repeated in “T” and account given in “A” is repeated in “O”

Let’s proceed further by entering the “O” or the Offset account. This account is same as the account we gave in “A”. This is the credit account. The account code combination given here will 1-101-0000-00-50201 with every segment as Constant.

With this step we have completed our allocation formula. The final Journal generated with this formula should be

Line 1 1-101-1201-00-50201 6091.071
Line2 1-101-1202-00-50201 7444.643
Line3 1-101-1203-00-50201 3383.929
Line4 1-101-1204-00-50201 2030.357
Line5 1-101-0000-00-50201 18950

If you enable the Full Cost Pool Allocation option then the system will post the rounding difference to the account with highest value. In this case the all the rounding will be given to line2 account. The first four accounts are the accounts we mentioned in Target field and the last account is the one we mentioned in Offset field. The accounting done here is that the PTCL Expense posted on a Common department was credited and distributed to four other departments on the basis we defined in STAT journal in Step 2.

If the concurrent request ends with an error then check the Output and Log file for error details.

That’s All:)

About these ads

23 Responses to “GL: MassAllocation”

  1. Madhan Says:

    Mind Blowing…….I couldnt find anywhere such a detailed explanation of Mass Allocations and your illustration is phenominal

    Thanks,
    Madhan

  2. Excellent explanation and very useful for Oracle functional community. Says:

    Excellent explanation, and very useful for me. Keep the good work.
    Thanks,
    Ramaiah

  3. omkaram Says:

    This Artical is very excelent to easly understand the real senario process

    Thanks
    Omkaram

  4. MKK Says:

    All these days i was having a fear to touch mass allocation, but now i can conduct a training, Too nice one, excellent one, great work, keep it up.

  5. Nishanth Says:

    Hi.. Please clarify my doubt
    Department X: Rs.309.09 (2 employees usage of 1700)
    Department Y: Rs.463.63 (3 employees usage of 1700)
    Department Z: Rs.927.27 (6 employees usage of 1700)
    In this where we need to mention the number of employees(like 2, 3 and 6 in this case) in mass allocation form in order to use for summing and looping??? I really don’t understand. Please help me

  6. anupam Says:

    beautiful article…

  7. Kranthi Says:

    Really Very Very Useful Post here, Thanks Atif Siddiqui and Thanks Pakistan .

  8. Findy Nurtantio Says:

    It’s very helpful, thanks a lot

  9. Rahul Says:

    I tried the above steps to re-allocate expenses. However having issues with not able to generate any JE. The formula was validated correctly. I have two scenarios –
    Divide parent account of department 100 into 3 departments 250, 260, 270.
    Further Divide Department 250 into departments 250, 251, 252. Another Question, how would the system know the parent account as I have not specified that in STAT journal?
    Thanks.

  10. aaa Says:

    its excellent ,grt wrk.

  11. Bhanu Pratap Singh Says:

    very good

  12. Bhanu Pratap Singh Says:

    This is the very simple way to understand maas allocation. iam very great full to you . pl send me more example for Roll up group.
    Thanks
    Bhanu

  13. Muhammed Ariff Says:

    helpful

  14. Nageswara Rao Says:

    Its really an analytical example…
    Thank you very much

  15. mohammed Says:

    Salaam,
    Thanx alot its really good and simple example.

    Regards

  16. Anand Says:

    Hi Atif,
    This is an excellent article on how Mass Allocations feature should be used in Oracle General Ledger. Thanks for all your effort and sharing it with others.

    All the best!

    Thanks,
    Anand

  17. Subashini Says:

    Hi,

    This article is really lovely.It gave me a high level understanding.

    Note: It has really changed my perception on Pakistanis.

  18. Oracle Training Says:

    Nice and precise article on Oracle Mass Allocation.

  19. P Pusp Says:

    Good abd Great , Easy learning document .

    Regards
    PP

  20. Kesavan Says:

    It’s an excellent document. Since last 2 weeks we were trying to get in web. At last we found yours.

    Now, I expect more from you on Revaluation, Transation and Considation process.

    Once again thanks for Sharing your knowledge with others.

  21. Nasir Khan Says:

    AOA Atif
    You cleared many confusions about mass allocation. Keep it continued
    Thanks a lot

    Nasir Khan

  22. Soegih Says:

    Hi Atif,

    Thanks a lot, this is very helpfully

  23. Parveen Says:

    Hi
    The document is very good and very helpful it would be great if you can post more such examples.

    Thanks,
    Parveen


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 397 other followers

%d bloggers like this: