Skip to main content

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.


Comments

Post a Comment

Popular posts from this blog

General Ledger FAQ

1.  What responsibility should I use when doing the set up for General Ledger? Use a seeded responsibility like 'Oracle General Ledger Super User'. You may also need to use the System Administrator responsibility. 2.  What are the pre-requisites required to define a new calendar? According to your business needs you need to decide the calendar type required i.e. monthly, weekly or biweekly, the number of periods, adjusting periods and the maximum number of periods within a fiscal year. 3. What are the pre-requisites required to define a new Ledger? Define a Calendar, Chart of Accounts and enable the functional Currency  and convention of subledger accounting method. 4. Why must I check the calendar definition before assigning to a Ledger? The calendar definition cannot be changed once it is assigned to a set of books so it is very important to check that the calendar definition is suitable to the specific business needs, has been d

Difference Between MTS, ATO, MTO ,PTO ,CTO and ETO.

 Make-to-stock (MTS) In MTS environments, products are created before receipt of a customer order. Customer orders are then filled from existing stock, and then those stocks are replenished through production orders. MTS environments have the advantage of decoupling manufacturing processes from customer orders. Theoretically, this enables customer orders to be filled immediately from readily available stock. It also allows the manufacturer to organize production in ways that minimize costly changeovers and other disruptions. However, there are risks associated with placing finished goods into inventory without having a firm customer order or an established need. These risks tend to limit MTS environments to simple, low-variety, or commodity products whose demand can be forecasted readily.  Assemble-to-order (ATO) In ATO environments, products are assembled from components after the receipt of a customer order. The key components in the assembly or finishing process are pla

Accounting entries in Oracle Purchasing and Payables

This document gives in detail different accounts used and the accounting impact of various transactions that take place in Oracle Purchasing and Oracle Payables. Both Standard costing and Average costing methods are considered. The accounts are Oracle Applications specific and might differ from the conventional accounting names. Examples are given wherever required for better understanding of the concept. The sources of these accounts are given. PURCHASING:  Receiving – For Accrual Process for perpetual Accruals Receipts for inventory purchases are always accrued upon receipt. And also use perpetual accruals for expense purchases you want to record uninvoiced purchase liabilities immediately upon the receipt of the expense goods. Receiving Account (Receiving Account) To record the current balance of the material in receiving and inspection. Where to define in Apps: Define Organization                                          Define Receiving Options