Allocation Methodology

[jstevens]

I have constructed an allocation table which I need to determine the order in which to sequence various allocations. The sequencing is where I need assistance.

Column A represents departments taking the credit whose corresponding debit is a department in Row 1. There are instances where a department in Column A also receives an allocation from other departments. Overall the table is 180 Columns x 220 Rows.

untitled.png

The table represents numberous journal entries; Debits to a departments in Row 1 and Credits to departments in Column A.

Any words of wisdom would truly be appreciated.
John

[Goshute]

John, I think you need to explain what kind of result you are looking for, otherwise I'm not sure you'll get any helpful replies. It would also help other Loungers if you posted a simplified and self-censored version of the spreadsheet, as proposing formulas on a graphic attachment is pretty much going to be wild speculation.

[jstevens]

Goshute,

I have attached an Excel file with an small sample of the allocation table and journals.

Any assistance would be truly appreciated,
John

WL_Sample.xls

[sdckapr]

Are you trying to create "sheet1" based on the "journal entries" sheet? If not what do you start with and what do you want it to look like at the end?

Steve

[HansV]

Have you already filled row 1 and column A of the target sheet with the department numbers, or should that be done by VBA code?

[jstevens]

Hans,

Row 1 represents those department receiving an allocation from a department or departments in Column A. My challenge is determining the sequencing order in which the allocations are to take place.

I already have code that allocates to the target sheets so that is not an issue. My code takes three passes before the allocations settle down (amounts are what I expect to see in the target department). I believe my challenge to be the sequencing of the allocations; my intent is to have the allocation process or sequence to run the first time and have the target departmental amounts correct.

Regards,
John

[HansV]

You haven't really answered the questions from Goshute, sdckapr and me. I don't understand what you mean by "allocation". Could you explain clearly and unambiguously what you want to accomplish?

[jstevens]

Hans,

Think of an allocation as an amount charged from one department to another department via a series of journal entries.

In this example let's assume department 1234 (source department) would like to allocated $1,000 to department 5678 (target department). The corresponding journal entry would look like:

Dept	Account	Debit	Credit
5678	Allocated from another dept	1,000	-
1234	Allocated to another dept	-	1,000

What I tried to portray in the sample file of a previous post are the departments receiving the debit (Row1) and the allocating departments (Column A). Remember that I have a few thousand journal entries to contend with.

In cross-referencing the journal entry above, department 5678 would be listed in Row1 while department 1234 would be found in Column A. The accounts/amounts can be taken out of the equation as the point I'm trying to convey is that there are a large number of departments allocating or charging one or more departments. My objective is to determine the order or sequence one or more departments are allocated to.

Here is another way to look at it: Source department to Target department

Source Dept	Target Dept
1234	5678
2345	6789
3456	1234

Note that 3456 is allocating to 1234 and 1234 is allocating to 5678


One would have to assume that the allocation sequence to take place first is from 3456 to 1234 and then from 1234 to 5678.

Hopefully this adds a little more clarity.

Regards,
John

[HansV]

This could get hairy with 180 columns x 220 rows - the number of combinations to check is daunting, and if there are circular reference (A allocates to B, B allocates to C and C allocates to A) you could get stuck in an endless loop. I'm not sure there is an easy solution - I'll have to think about it, and look forward to contributions by others.

[Don Wells]

Hello John
    Hans is way ahead of me. I cannot understand what knowledge the reader is to take from Sheet1.

• If it is a list of all transactions by all departments, it can be accomplished by filtering the Journal Entries worksheet
• If it is a summary of all disbursements and all receipts by each department, it can be easily accomplished by listing all departments in Col A and Row 1, then adding the amounts to the appropriate intersection of the two departments. The total of a given row will show the total of all allocations by a given department, and the total of a given column will show the total of all allocations received by a given department.

Perhaps identifying where I have misunderstood your requirement will clarify what your need.

[Goshute]

John, I work in a sort of accounting role, and there's a difference between charging a specific expense to a specific account (whether it's a GL account, or a product line, or a department, or some other sub-entity), and allocating a specific expense or bundle of expenses across a set of accounts. If you are allocating an expense, say general IT costs, across a number of accounts, you need a basis of allocation, which could be a number of things: employee count by department, revenue by product line, number of widgets produced per department, number of customers, etc, the list goes on. And if there are expenses that cascade through departments, there needs to be an order to the cascade.

I don't see that order and allocation basis guidance in your model and explanation.