Accounting prediction tool for wages with excel?

[Ojfm]

Dear All,

I have a series of employees (with different payrolls) and a series of
accounts (with money coming in and going out during the year). I
would like to develop a tool where I can assign employees over certain
periods on specific accounts, to forecast the evolution of the
corresponding accounts.

For example:

- Mr. X is paid from January to April from account A and from May to
December from account B, his monthly wage is xx.
- Mrs. Y is paid from January to June from account A and from July to
December from account C, her monthly wage is yy.
- Mr. Z is paid from January to August from account C and from
September to December from account B, his monthly wage is zz and he
gets a bonus of zzz in June
- At the beginning of the year, accounts A, B and C have given
balances and specific entries as the year proceeds.

In the end I would like to have a sheet per account and the
possibility to move the employees around to see the evolution of the
different accounts, depending who is paid by which account.

How can I do that with Excel? I am not looking for a very
sophisticated tool, but for something that can cope with about 20
collaborators and 10 accounts?

Thanks a lot,

Olivier.

 

[Tom Ogilvy]

I would see a single sheet with collaborators down the first column and
months across the top. You would the collaborator identify in column 1, the
salary in column 2 and then the months January through December.

For each month, collaborator intersection, you would put in an account
identifier.

You could set up the account sheets based on the amount of detail you want
to see. One way would be to duplicate the above sheet with formulas at the
intersection like

=if(collaborator!thiscell=thisaccountnumber,salary of collaborator,0)

You could add total costs at the bottom of the column and then subtract that
from the previous month's balance.

Alternately, you could put in a similar formula, but one that develops the
total cost at once.