Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2010, 06:59 AM
captain_hawkeye captain_hawkeye is offline Financial Data Organization Windows 7 Financial Data Organization Office 2007
Novice
Financial Data Organization
 
Join Date: Jun 2010
Posts: 1
captain_hawkeye is on a distinguished road
Default Financial Data Organization

Hello all. I'm working on a project to analyze financial data (income statements, balance sheets, cash flow statements) for many companies (>100).



I currently have the the data in a very inefficient format in Excel. I have three worksheets, one for each of the three financial statements above. In each of these worksheets, the data is broken down as follows:

Company Year Data1 Data2 Data3
Company1 Year1 x y z
Company1 Year2 x y z
Company1 Year3 x y z
Company2 Year1 x y z
Company2 Year2 x y z
Company3 Year3 x y z
...etc

The size of the file is around 100MB, and as you can imagine, Excel takes way too long to do any useful activity.

Can anyone help me think of a more efficient way of organizing the data? I need to be able to apply statistical formulas to the raw data and to calculated data between the worksheets. For example, I may want to look at the trend of Income_Statement.Company1.YearX.Data1 / Balance_Sheet.Company1.YearX.Data2. I may also want to look at the trend of Income_Statement.Company1.YearX.Data1 over the past 5 years versus the trend of Income_Statement.Company1.YearX.Data2 over the same period.

It seems like Access would be a better choice than Excel, but I don't know if it can handle the data analysis I need to do.

Thanks in advance for your help.
Reply With Quote
  #2  
Old 06-14-2010, 04:09 AM
Kimberly Kimberly is offline Financial Data Organization Windows 7 Financial Data Organization Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

In general, it is much better to place all of the data in a single range on a single worksheet when you can (whenever the structure is the same.)

I don't know what calculations you are doing, or what Data1, Data2, etc are, but by placing the data on a single sheet, you can easily take advantage of features like filtering and pivot tables. And of course, simple division is a bit easier too.

I don't know what Data1 Data2 Data3 are, and they are probably fine, but since I don't know what they are, I will mention that it is easy to fall into using for column labels what should be data in a column. For example, if I sold vegetables on the side of the highway, I would want a field for Item and another for Quantity. Not a field for Corn and a different field for Beans, etc. Again, I dont knowyour data and I really doubt you need to change this, but I thought I should mention it.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Financial Data Organization Federal financial rounding? markg2 Excel 6 05-26-2010 09:54 AM
Organization chart glendot Word 0 02-23-2010 08:15 AM
Formatting financial ratios jtrobinson Word 0 01-19-2010 06:46 AM


All times are GMT -7. The time now is 04:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft