Spreadsheet design help needed (accounting/design)

jsforza

New Member
Joined
Jan 25, 2004
Messages
2
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 :rolleyes:
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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. :LOL: )
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Jay Petrulis said:
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. :oops:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top