Forum Discussion
Unique problem...can I use Excel to solve it?
So I'm trying to automate a task for work...it seems simple on the surface. I feel like automation would save a lot of time, but I'm not sure Excel is the right tool.
As part of my job, I review financial statements of various companies, with particular focus on the balance sheet. The companies I am reviewing also provide me a trial balance (i.e. list of 'sub accounts' that provides additional detail for the balance sheet items), but they don't detail how the sub accounts feed into the items on the balance sheet; typically, they are not sequential and there is no regulation of how many sub accounts are created by each company (although typically probably under 200 or so). The balance sheet accounts are the same for every company since they're in a standardized format; only the values change.
What I want to do is make it so that I can type in the values on the balance sheet in one column, type in the values from the trial balance in another, and have excel provide a 'best fit' of what sub accounts comprise each account listed on the balance sheet. I feel like this would be a task well suited to a computer (I imagine the background process would be that the computer takes balance sheet 'item A' from one column, and then takes every combination of the sub account figures in the second column until it finds the correct amounts that add up to balance sheet account, within a few dollar margin of error)
I tried to think through it logically, but I just don't have the technical know-how to perform this task. I imagine I have to assign variables to each number somehow? So that the computer sees, for example, 'accounts receivable' and then proceeds to try all the numbers from the trial balance until it finds, say 'B' 'C' and 'F' add up to the number in accounts receivable, providing that as my output so that I can now see how the sub accounts are mapped.
So does anyone have suggestions on how to tackle this? Is it too complicated for excel? Doing it manually is a relatively simple, but often time consuming project.
- Shouldn't those companies be able to deliver the missing information you need: the relation between the accounts on the trial balance and your main balance categories? After all, they somehow did sum things up?
You'd think so, in theory. However, for many of them the accounts were set up and mapped years ago by previous office staff. If I ask for that information, some of them know how to provide it but many don't.
- Problem with this is that there may be more than one solution to a combination of numbers and you'd have no way of knowing which is the correct answer.
