I need an Excel Accounting Legder Modeller

  • Posted:
  • Proposals: 5
  • Remote
  • #663075
  • Awarded
Alex F.
Surya G.
Rundie D.Robert A.Ilyas A. have already sent a proposal.
  • 0

Description

Experience Level: Intermediate
Estimated Job duration: 1 day or less
Industry: Business
Description of excel work: I want to design an excel matrix for a complex inter- company structure. This should be done on MS Excel (2010 preferred)

Please note that we are looking at a 24 hours turnaround time.

We have 85 companies (A001 - A085) - Design should allow for up to 999 companies, within the group.

We have 157 fund within the 85 companies. We have few fund (in yellow in my spreadsheet - under company) that have 2 (and only two) companies (separate set of companies) using them each, apart from this funds are unique to particular companies. The group also uses a "class" group for reporting purposes. Each class is unique to 1 company and one fund.

Companies may have 1 or more than one fund. Companies may have 1 or more than one class.

Companies relates ( or transacts) with one another on the "fund" level.

Companies relates with one another based on transactions, eg loan relationship, loan related interest income and expenses, sales to related company ,purchases from related company, investment in subsidiary,shares from parent company,treasury and cash pooling,royalties,cost sharing,etc.

The transactions are 16 in total. However, each of the 16 transactions can have 5 "sub types". eg we may have loan type A, loan type B, loan type C etc.

Companies can have a 0 or many relationships with one another.

These transactional relationships are "dual in nature" - that is for example, if company A lends money to company B, company A open an account (with, say, account code 012778) - Loan to company B in their book/record. Company B also open an account (this may or may not be the same code as used by company A) in their book - Loan from company A. So from the group point of view every transactional relationship creates 2 set of records/account which may or may not be the same code.

I have account codes for each of the transaction relationships between each of the company fund within this group as explained above. I want an excel matrix (or any thing you should to design or call what you design) - it can be on one tab or many tabs within a spreadsheet, designed so that i can insert the nominal codes into each of the "matching cells"

We want to establish whether companies:

(1) use the same code for related transaction
(2) use codes in a pre-designed format or structure or just randomly

I have attached a spreadsheet of all the relevant tables.



I used a simply scenario between only two companies to illustrate.

Company A001 made a loan A (through its AAA fund) to fund ABT of company A042.

The attached spreadsheet shows a basic matrix showing that A001 recorded that transaction in its book using account code - 101789, while A042 recorded it in its book using account code - 123478

I want a matrix (or any design) for me to capture all the relationships for all the transactions. I have a list of all the relationships and the account codes used. I just want a means to show them on excel in a very presentable format.

Please let me know what you think.

Many Thanks,

Agu



Thank you.

Agu
Extra notes:

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.