MassAllocation Examples

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.

Usage-Based Allocation Example

To allocate the monthly rent expense for company 01, define the following MassAllocation formula:

Co

Dept

Acct

BalanceType

RelativePeriod

Currency

AmountType

C

C

C

A

01

100

5740

A

Current Period

USD

PTD

*

C

L

C

B

01

999

SQFT

A

Current Period

STAT

YTD

/

C

S

C

C

01

999

SQFT

A

Current Period

STAT

YTD

C

L

C

T

01

999

5740

A

Current Period

USD

PTD

C

C

C

O

01

100

5740

A

Current Period

USD

PTD

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.

Assume there are three other departments besides 100 in the company, 101, 102 and 103 that occupy 45%, 30% and 25% of the company's floor space, respectively. These departments are children to the parent department 999. When you run this MassAllocation formula for an accounting period with $100,000 of rent expense, you produce a journal entry that looks 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

You can use more than one looping segment in your formula. For example, you can perform the previous allocation for all companies in your organization. First, define a parent Company segment value (for example, 99) that is associated with all detail company values. Then use Company value 99 instead of 01 in all five rows of the formula above. Finally, use the Looping segment type for company 99 in each row.

Incremental MassAllocation Example

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:

Co

Dept

Acct

BalanceType

RelativePeriod

Currency

AmountType

C

C

C

A

01

100

5740

A

Current Period

USD

PTD

*

C

L

C

B

01

999

SQFT

A

Current Period

STAT

YTD

/

C

S

C

C

01

999

SQFT

A

Current Period

STAT

YTD

C

L

C

T

01

999

5740

A

Current Period

USD

PTD

C

C

C

O

01

100

5740

A

Current Period

USD

PTD

This is the same MassAllocation as in the previous example, except the cost pool is different from the offset account. When you run this MassAllocation formula for an accounting period with a rent cost pool of $100,000, you produce a journal entry that looks 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.