Mass Allocation in GL

The basic concept of Mass Allocation is dividing a cost 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 Statisticaland 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 XYZorganization 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.