Spreadsheet design help needed (accounting/design)

[jsforza]

Hello,

I'm a beginning accountant and I've been given as my first project to construct a spreadsheet that outlines our company's General Ledger Accounts by department and division. To be more specific, our company has various general ledger accounts (e.g. expenses, revenues, etc...)dedicated to each department. Our goal is to have people use this sheet as a reference and thus have transactions properly recorded in the appropriate department for budgeting purposes.

For Example: Our company has four divisions. Each division has various departments. Our departments consist of Customer service, Sales, M.I.S., IT Group, Human resources, Engineering, R&D, Finance & Accounting, etc... Each department has general ledger accounts that look like this:
e.g. 4250-320--2. The first number represents whether it is an expense, revenue, prepaid, asset, etc... The second number represents the department in which the first number goes into. The last number represents the divion in which the department is located in.

My goal is to put all this information into a spreadsheet table (for each division) that is concise, simple to read, easily searched, and with a creative touch. We've been having problems with people putting expenses (transactions) into the wrong department or division. My spreadsheat is to correct this problem, so that people can easily use the sheet as a reference for transactions.

Can anyone help me on a design for my spreadsheet? Does anyone have something similar that I can use? Can you please post a design.

Thanks for all your help,

Goodfella

 

[PaddyD]

general rule - differentiate between design for reporting & design for data collection / analysis. For the latter, google for info on normalisation & first normal form - they braodly define the design approach you should adopt (there are also a few things in the recommended links thread @ the top of the board that you should have a look at). Once you've got the design right from an analytical point of view, you should be able to get it in just about any format you need to readability / reporting. (The reverse is often not the case).

 

[nbrcrunch]

Another EXCELlent (pun intended) resource is your local bookstore. There are dozens of books on Excel for accountants (go figure) (another pun).

Seriously, I have looked through several and they offer a wealth of information on design. Your scenario is not a difficult one.

(Sorry, I get full of myself sometimes. )

 

[Jay Petrulis]

Hi,

Since you are starting this project, go to John Raffensperger's site (the link is in the recommendeds section of this board), and read it carefully.

I think the advice he offers is outstanding, for the most part. The main quibble I have is that I separate the data from the reporting/analysis of the data completely, which he recommends against.

My other suggestion, and it depends on the sophistication of the user, would be to allow the entries to be made in English (or native language). Use data validation or VBA to restrict the entries to what is allowable, and convert them. If not, make a CLEAR table which says an expense is this, revenue is that, etc.

I assume that you already know this, but pay careful attention to date typing. If you design this nicely, your pendings, payables/receivables will automatically adjust when completed. Also, make sure that you build into the report some way to adjust the date range of the report -- possibly someone would need to see a point in time that is not today or yesterday.

I hope that this vague advice can be put to good use. Not know exactly what you have precludes me from suggesting a specific structure.

 

[Jay Petrulis]

Hi,

snip

Not know exactly what you have precludes me from suggesting a specific structure.

"Not know" ?????!!!!!

I must have flunked basic grammar. That was atrocious.