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.
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
Allocation Methodology
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Allocation Methodology
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: Allocation Methodolgy
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.
Goshute
I float in liquid gardens
I float in liquid gardens
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Allocation Methodolgy
Goshute,
I have attached an Excel file with an small sample of the allocation table and journals.
Any assistance would be truly appreciated,
John
I have attached an Excel file with an small sample of the allocation table and journals.
Any assistance would be truly appreciated,
John
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Allocation Methodolgy
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
Steve
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Allocation Methodolgy
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?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Allocation Methodolgy
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
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
Regards,
John
John
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Allocation Methodolgy
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?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Allocation Methodology
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:
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
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
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 |
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 |
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
Last edited by jstevens on 18 Oct 2010, 18:48, edited 3 times in total.
Regards,
John
John
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Allocation Methodology
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Allocation Methodology
Hello John
Hans is way ahead of me. I cannot understand what knowledge the reader is to take from Sheet1.
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.
Regards
Don
Don
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: Allocation Methodology
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.
I don't see that order and allocation basis guidance in your model and explanation.
Goshute
I float in liquid gardens
I float in liquid gardens