Mass
allocation is a facility in Oracle Financials through which one can create
journals that distribute and allocate revenues and expenses across a group of
cost centers, departments, divisions, and so on. It is a very useful tool to properly distribute amounts between accounts. For example rent for the entire premises can be allocated to different cost centers based on the area occupied by each department.
To define MassAllocation formulas, you create a MassAllocation batch that contains one or more MassAllocation formula entries. You can also copy an existing MassAllocation batch then modify it as needed for your new batch. By including parent values in allocation formulas, you can allocate to the child values referenced by the parent without having to enumerate each child separately. You can create MassAllocations in your functional currency, a foreign currency or statistical currency.
Note: When you create MassAllocation formulas, you can enter specific amounts or specify an account for lines A, B or C. If you do not choose Full Cost Allocation: you can enter an amount instead or an account in any of lines A,B or C. If you choose Full Cost Allocation: you can enter an amount in line A but lines B and C must contain accounts only. See also the Validation Business Rules for Allocation Formulas with Full Cost Pool enabled.
Note: The offset account must conform to the allocation formula rules for offsetting accounts. Be sure to also follow the account segment cross–validation rules. The validation program does not check for account cross–validation rule violations. If you enter an offset account that violates a cross–validation rule General Ledger creates invalid journal lines when you generate the formula. You must correct the result in
You can run the program to validate all previously unvalidated batches, or you can validate all unvalidated batches when you close the window.
For target and offset lines (lines T and O)
For the target line only (line T)
For the offset line only (line O)
1. Line B is account based.
If you rerun your incremental batches, General Ledger uses cumulative period–to–date, quarter–to–date, year–to–date or project–to–date balances for the accounting period you specify. The first amount type General Ledger encounters in the A*B/C formula is the amount type used for the target account when calculating the incremental allocation amount (A*B/C).
This is the formula for MassAllocation A*B/C where,
To allocate the monthly rent expense for company 01, define the following MassAllocation formula:
Row A represents the cost pool that you want to allocate to all departments. Rows B and C compute the relative amount of floor space occupied by each department. These rows access statistical accounts of the form 01–101–SQFT, 01–102–SQFT, and so on.
Row B loops through all department segment values. Row C computes the total of all floor space occupied. Now assume that you will want to reallocate an adjusted cost pool without reversing the posted journal batches created by the previous MassAllocations.
You define your MassAllocation with a different offset account from your cost pool:
debit 01 – 101 – 5740.......45,000 Rent Expense – Dept 101
Now, assume that later you want to reallocate a rent cost pool increased by $10,000 to a total of $110,000. When you run the same MassAllocation formula in incremental mode for an accounting period with a cost pool of $110,000, General Ledger only allocates the adjustment to the cost pool, or $10,000. This produces the following journal entry:
debit 01 – 101 – 5740.......4,500 Rent Expense – Dept 101
After you post this journal entry, the balances in your rent expense accounts are:
Now assume that later you want to reallocate a rent cost pool decreased by $30,000 to a total of $80,000. When you run the same MassAllocation formula in incremental mode for an accounting period with $80,000 of rent expense, General Ledger produces the following journal entry:
debit 01 – 100 – 5740.......30,000 Rent Expense – Dept 100
After you post this journal entry, the new balances in your rent expense accounts are:
01 – 101 – 5740.......36,000 Rent Expense – Dept 101
Warning: When using MassAllocations or MassBudgeting, use accounts that receive all of their activity solely from incremental and regular MassAllocations and MassBudgeting. This ensures that General Ledger uses an accurate target balance for the incremental allocation.
The account code for X 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.
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.
Create a MassAllocation Batch and then a Journal. Name it X PTCL Expense Formula.
Now give the account of the “A” which is 1-101-0000-00-50201 having the value of $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
Now move on to enter the code for “B”. The account code for Usage Factor in our example will be
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.
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 wills 1-101-0000-00-50201 with every segment as Constant.
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.
To define MassAllocation formulas, you create a MassAllocation batch that contains one or more MassAllocation formula entries. You can also copy an existing MassAllocation batch then modify it as needed for your new batch. By including parent values in allocation formulas, you can allocate to the child values referenced by the parent without having to enumerate each child separately. You can create MassAllocations in your functional currency, a foreign currency or statistical currency.
The Mass allocation journals in Oracle
are handled in a step by step manner. The following steps are involved in
created a Mass Allocation Journal Entry:
- Create MassAllocation Definition - First the user needs to define the mass allocation definition. The definition contains parameters as to how expenses etc would be distributed.
- Validate the Mass Allocation definition - The second step is to validate the mass allocation definitions with valid account segments to ensure that same are correct and accurate.
- Generate the Mass Allocation journals - Finally from journal entries already posted, mass allocation journals need to be passed so as to distribute or allocate the amounts to valid accounts based on proportion.
- Review and post the entries - Once the mass allocation journals are generated, the last step in the process is to review the journals and post the entries to the Oracle General Ledger.
Formula Lines
Operand Lines
All
MassAllocation formulas use the following equation to determine allocation
amounts:
COST POOL
* (USAGE FACTOR / TOTAL USAGE)
General
Ledger uses the following format to represent the equation. Each factor in this
equation relates to a separate formula line.
A * B / C
You can
enter any combination of fixed amounts and accounts in formula lines A, B, or
C.
Note: When you create MassAllocation formulas, you can enter specific amounts or specify an account for lines A, B or C. If you do not choose Full Cost Allocation: you can enter an amount instead or an account in any of lines A,B or C. If you choose Full Cost Allocation: you can enter an amount in line A but lines B and C must contain accounts only. See also the Validation Business Rules for Allocation Formulas with Full Cost Pool enabled.
Target Account
Enter
an account in the Target line to specify the destination for your allocations.
When
the result of your allocation formula is a positive number, the resulting
journal entry debits the target account and credits the offset account. When
the result of your allocation formula is a negative number, the resulting
journal entry credits the target account and debits the offset account.
Note:
The target account must conform to the allocation formula rules for target
accounts. Be sure to also follow the account segment cross–validation rules.
The validation program does not check for account cross–validation rule
violations. If you enter a target account that violates a cross–validation rule
General Ledger creates invalid journal lines when you generate the formula. You
must correct the resulting journals in the Enter Journals window before you
post.
Offsetting Account
Enter
an account in the Offset line to specify the account to use for the offsetting
debit or credit from your allocation. The Offset account is usually the same
account as formula line A to reduce the cost pool by the allocated amount. When
the result of your allocation formula is a positive number, the resulting
journal entry debits the target accounts and credits the offset account.
When the result of your allocation formula is a negative number, the resulting journal entry credits the target accounts and debits the offset account.
When the result of your allocation formula is a negative number, the resulting journal entry credits the target accounts and debits the offset account.
Note: The offset account must conform to the allocation formula rules for offsetting accounts. Be sure to also follow the account segment cross–validation rules. The validation program does not check for account cross–validation rule violations. If you enter an offset account that violates a cross–validation rule General Ledger creates invalid journal lines when you generate the formula. You must correct the result in
Validating MassAllocation Batches
After
you define a new allocation batch, or change an allocation formula, you must
validate the batch by running the MassAllocation/ MassBudget Validation
program. The program verifies that your allocation formulas conform to the
allocation formula definition rules.
You can run the program to validate all previously unvalidated batches, or you can validate all unvalidated batches when you close the window.
Allocation Formula Rules
Use
the following definition rules when creating your allocation formulas. The
allocation validation program checks that your formulas adhere to these rules:
For
formula lines A, B and C (operand lines):
·
You
can enter either an amount or an account in lines A, B and C.
·
If
you enter an account, child values must have a Constant segment type.
·
Parent
values may have a Constant, Looping or Summing segment type.
·
You
can use a Constant segment type with a parent value only if it references a
summary account.
·
If
you use a Looping segment type on the same segment in more than one of the
operand lines, you must use the same parent.
·
If
you use a Looping segment type in your Target line, you must use a Looping
segment type on the same segment using the same parent in lines A, B or C.
·
To
use summary accounts, all segments in your formula must be assigned a segment
type of Constant.
For target and offset lines (lines T and O)
·
You
must enter an account in the Target and Offset lines.
·
Detail
values must have a Constant segment type.
·
Parent
values must have a Looping segment type.
·
Your
Target account must be different from your Offset account.
For the target line only (line T)
·
If
you use a Looping segment type in lines A, B or C, you must use a Looping
segment type on the same segment using the same parent in your Target line.
For the offset line only (line O)
·
You
can only use a Looping segment type in your Offset line if the corresponding
segment type in your Target line is Looping.
·
If
you use a Looping segment type in your Offset line, you must use the same
parent as in your Target line.
Validation Business Rules
If you
choose to use Full Cost Pool Allocation, below are the business rules used to
validate your Allocation Formula Rules for lines A, B, and C. If your full cost
pool allocation contains violations of the business rules, the execution report
will detail the errors.
1. Line B is account based.
2.
Line B has at least one looping segment.
3.
Line C is account based and has the same segment values as line B.
4.
Line C uses Constant or Summing segment type if line B uses Constant or Summing
segment type.
5. At
least one Summing or Constant segment in line C corresponds to a looping
segment in line B.
Generating MassAllocation Journals
Generate
MassAllocations to create unposted journal batches based on your validated
MassAllocation formulas. The generated journal batch contains one entry for
each allocation formula in the batch.
Use
MassAllocation journals to reverse existing balances, post new allocation
amounts, or generate journals that increment the existing balances to match the
current allocation amount.
You
can generate MassAllocation journal batches for any range of open or future
enterable periods. If you are allocating encumbrances, all of the periods must
be in open encumbrance years. General Ledger creates one unposted journal batch
for each period in the range. If there is a closed period in the range of
periods you specify, General Ledger generates a batch that cannot be posted
until you open the period.
Choosing an Allocation Method
You
can generate journals from allocation formulas using a full or incremental
allocation method, depending on whether you want to replace or increment
existing account balances.
Generating Journals Using the Full Allocation Method
Choose
the Full allocation method to generate journals that reverse previous
allocations or to post new allocation amounts. We recommend that you use this
method only if you are allocating amounts for the first time, or only once.
To
replace a previous allocation requires two steps. First, reverse the original
allocation. Second, create a mass allocation for the new amounts.
Generating Journals Using the Incremental Allocation Method
Choose
the Incremental allocation method when you want to update allocated balances
without reversing the previous allocation batches. Using this method, you can
generate allocation journals as many times as you wish, provided there is no
activity against the target accounts between runs.
We
recommend that you do not use the incremental method the first time you
generate a MassAllocation entry. However, if you do generate your first
MassAllocation entry using this method, your target balance must be zero.
Before
generating incremental allocation journals, post all batches you previously
generated from the same formula batch.
If you rerun your incremental batches, General Ledger uses cumulative period–to–date, quarter–to–date, year–to–date or project–to–date balances for the accounting period you specify. The first amount type General Ledger encounters in the A*B/C formula is the amount type used for the target account when calculating the incremental allocation amount (A*B/C).
Scheduling Your Allocation or MassAllocation Batch
You
can generate your Allocation or MassAllocation Journal Batch according to
schedules in Oracle Applications; schedules you define in Oracle Applications,
or schedules you define in General Ledger.
MassAllocation Examples
The basic concept of Mass Allocation is dividing your cost on some factor. If we take a simple real life example then
consider 5 friends having a dinner at a restaurant. Each of them buys a meal
deal which costs $260 per person. Now if one of the friends pays the whole
amount which is $1300. How will he calculate per head cost? The answer is
simply by dividing 1300 by 5 or multiplying 1300 by 1/5. Mathematically
1300*1/5
This is the formula for MassAllocation A*B/C where,
A = Total Cost ($ 1300)
B = Factor (1deal per person)
C = Total Factor (Total Deals)
In Oracle General Ledger this facility is given to divide or allocate your expense
cost or revenue income on your
selected distributing criteria which can be your no. of departments, branches,
head count, covered area, etc.
Example 1:
Suppose
your account is composed of three segments: Company, Department and Account.
You want to redistribute your monthly rent expense from department 100 to each
department based on the amount of space each department occupies.
Department
999 is a parent that includes all departments except 100.
Department
100 is the department that stores all rent expenses.
Account
5740 is the rent expense account. SQFT is the statistical account used to
record square footage for each department.
To allocate the monthly rent expense for company 01, define the following MassAllocation formula:
Row A represents the cost pool that you want to allocate to all departments. Rows B and C compute the relative amount of floor space occupied by each department. These rows access statistical accounts of the form 01–101–SQFT, 01–102–SQFT, and so on.
Row B loops through all department segment values. Row C computes the total of all floor space occupied. Now assume that you will want to reallocate an adjusted cost pool without reversing the posted journal batches created by the previous MassAllocations.
You define your MassAllocation with a different offset account from your cost pool:
The
examples below will build upon the same MassAllocation as in the previous
example, except the allocation amount is the incremental change. The original
MassAllocation used a cost pool of $100,000, resulting in a journal entry like
the following:
debit 01 – 101 – 5740.......45,000 Rent Expense – Dept 101
debit 01 – 102 – 5740.......30,000 Rent
Expense – Dept 102
debit 01 – 103 – 5740.......25,000 Rent
Expense – Dept 103
credit 01 – 100 – 5740.......100,000
Rent Expense – Dept 100
Now, assume that later you want to reallocate a rent cost pool increased by $10,000 to a total of $110,000. When you run the same MassAllocation formula in incremental mode for an accounting period with a cost pool of $110,000, General Ledger only allocates the adjustment to the cost pool, or $10,000. This produces the following journal entry:
debit 01 – 101 – 5740.......4,500 Rent Expense – Dept 101
debit 01 – 102 – 5740.......3,000 Rent
Expense – Dept 102
debit 01 – 103 – 5740.......2,500 Rent
Expense – Dept 103
credit 01 – 100 – 5740.......10,000
Rent Expense – Dept 100
After you post this journal entry, the balances in your rent expense accounts are:
01 – 101 – 5740.......49,500 Rent
Expense – Dept 101
01 – 102 – 5740.......33,000 Rent
Expense – Dept 102
01 – 103 – 5740.......27,500 Rent
Expense – Dept 103
01 – 100 – 5740.......110,000 Rent
Expense – Dept 100
Now assume that later you want to reallocate a rent cost pool decreased by $30,000 to a total of $80,000. When you run the same MassAllocation formula in incremental mode for an accounting period with $80,000 of rent expense, General Ledger produces the following journal entry:
debit 01 – 100 – 5740.......30,000 Rent Expense – Dept 100
credit 01 – 101 – 5740.......13,500
Rent Expense – Dept 101
credit 01 – 102 – 5740.......9,000 Rent
Expense – Dept 102
credit 01 – 103 – 5740.......7,500 Rent
Expense – Dept 103
After you post this journal entry, the new balances in your rent expense accounts are:
01 – 101 – 5740.......36,000 Rent Expense – Dept 101
01 – 102 – 5740.......24,000 Rent
Expense – Dept 102
01 – 103 – 5740.......20,000 Rent Expense
– Dept 103
01 – 100 – 5740.......80,000 Rent
Expense – Dept 100
Posting the resulting incremental
MassAllocation journal entry has a net effect of replacing the existing target
balance with the allocated amounts from A*B/C.
Warning: When using MassAllocations or MassBudgeting, use accounts that receive all of their activity solely from incremental and regular MassAllocations and MassBudgeting. This ensures that General Ledger uses an accurate target balance for the incremental allocation.
Example 2
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 $18950 for the month of June incurred at X branch on the
number of employee each department has. The allocation basis in this example is
Head Count per Department.
The account code for X 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-X-NoDeparment-NoProduct-PTCL
Expense
Line 2 Account Description: XYZ-X-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 $18950.
Cost Pool “A” having an amount of $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-X-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 X 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. We
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 $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 wills 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.
我不要做白工
ReplyDelete