Friday, June 27, 2014

Mass Allocation in Oracle General Ledger R12

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 8 divisions or departments:

1. Finance dep (010)
2. Sales dep (020)
3. Marketing Dep (030)
4. Delivery Dep (040)
5. Resourcing dep (050)
6. HR Dep (060)
7. Production dep (070)
8. Management Team (080)

The COA Structure of this organization is Company -Department-Account-Intercompany-Product
100- Total department (Parent) (Child Ranges: 001 – 099)
Let’s allocate the telephone bill expense of Rs. 38950 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 01 and the natural Account for expense burden is 7050 and each department has 19, 7, 15,6,24,11,9 and 3 employees respectively.

1. Finance dep (010) >19
2. Sales dep (020) >7
3. Marketing Dep (030) >15
4. Delivery Dep (040) >6
5. Resourcing dep (050) >24
6. HR Dep (060) >11
7. Production dep (070) >9
8. Management Team (080) >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-2014 with the following Lines
Line1: 01-000-7050-00-0000 38950 (DR) (Expenses Burden a/c)
Line2: 01-000-1110-00-0000 38950(CR) (Cash Account)

Line Description-1: KS US Operations-Default-Expenses Burden Allocated-Default-Default
Line Description-2: KS US Operations-Default-Cash-Default-Default

This journal entry is equivalent to paying your 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.38950.



 
STEP2:

Now we will create the “B” and “C” or Usage Factor and “Total Usage”. Pass a STAT JV. STAT is short for Statisticaland it can be used by changing the currency from USD 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: 01-010-7050-00-0000 19(DR)
Account Description: KS US Operations-Finance department-Expenses Burden Allocated-Default-Default
Line2: 01-020-7050-00-0000 7(DR)
Account Description: KS US Operations-Sales department-Expenses Burden Allocated-Default-Default
Line3: 01-030-7050-00-0000 15(DR)
KS US Operations-Marketing department-Expenses Burden Allocated-Default-Default
Line4: 01-040-7050-00-0000 6(DR)
KS US Operations-Delivery-Expenses Burden Allocated-Default-Default
Line5: 01-050-7050-00-0000 24(DR)
KS US Operations-Resourcing-Expenses Burden Allocated-Default-Default
Line6: 01-060-7050-00-0000 11(DR)
KS US Operations-Human resource-Expenses Burden Allocated-Default-Default
Line7: 01-070-7050-00-0000 9(DR)
KS US Operations-Production Department-Expenses Burden Allocated-Default-Default
Line8: 01-080-7050-00-0000 3(DR)
KS US Operations-Management Team-Expenses Burden Allocated-Default-Default




By passing or posting this STAT journal we are creating a basis for expense allocation. 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 8 lines with changing Department codes, these four lines individually represent Usage Factor “B” which is 19,7,15,6,24, 11,9, & 3 and collectively they represent Total Usage “C” which is equal to 19+7+15+6+24+11+3=94.


Now moving on with STEP3

STEP: 3

Create a MassAllocation Batch and then a Journal.
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 01-000-7050-00-0000 having the value of Rs.38950. 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
01-100-7050-00-0000. Note that I have given the department code as 100 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 19,7,15,6,24, 11, 9 and 3.

REMEMBER: looping is only done on Parent Value of the Segment. In this example 100 is the Parent department which has the child departments 010, 020, 030,040,050,060,070 and 080.
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 100 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 94.



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 01-100-7050-00-0000 with 100 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 01-000-7050-00-0000 with every segment as Constant.



Now allocation formula has been completed.


Validation is pending, so click validate all (B)


Concurrent completed successfully



 
Generate the Mass allocation, click on submit.
Select the ledger, balancing segment value and period then click on submit




Review the journal once complete the concurrent program




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


That it.

16 comments:

  1. Nicely explained. Good Job.

    ReplyDelete
  2. Excellent ...
    Got clear idea about allocation.

    ReplyDelete
  3. GOOD JOB..HAVE A QUESTION;;let s assume that I have created 3 STAT journals for the same combinations..which one the system will grab while generating the allocation?

    ReplyDelete
  4. Hello Suresh,
    Greetings! Thanks for nice post. I want to know, if I have the following segment:

    com-location-dept-business-account-employee-product-f1-f2 where common Location is 101 with many Dept;

    101.101.Engg.999.5236562.999.9999.999.999 Debit 50000
    101.101.IT.999.5236562.RF001.9999.999.999 Debit 20000
    101.132.Engg.999.5236562.999.9999.999.999 Debit 30000
    101.101.Finance.999.5436562.999.9999.999.999 Debit 60000 etc. and I want to allocate them to a Costed Location and Dept, should I make allocation journals for all line levels?

    ReplyDelete
  5. I can use mass allocation to perform a reclassification in the following way:

    From various combinations

    01-010-7050-00-0000 19(DR)
    01-020-7050-00-0000 3(DR)

    To a combination

    01-000-7050-00-0000 22(CR)

    ReplyDelete
  6. Hey.

    Guys I have a requirement to formula with parent account of "Account segment" with looping.

    Purpose:
    The user want to define single STAT journal with multiple "account segment values" with in a period and single formula with "account segment values" with looping and expecting that the system allocates the desired departments according to their concern accounts. When I use to define the above scenario, System is generating wrong Journal Voucher with wrong code combination and amount. Your assistance will be highly appreciable.

    Thanks

    ReplyDelete
  7. Great Post. Please create such articles, definitely a big help for all Oracle learners.

    ReplyDelete
  8. Such a very great & helpful.

    Thanks,
    Regards,

    ReplyDelete
  9. It's a nice blog. Very helpful

    ReplyDelete
  10. https://www.youtube.com/watch?v=fOiIVdVMvWw

    ReplyDelete
  11. Vivid and how the training manuals should actually be written.

    ReplyDelete