Financial Database Design
- Product: FM Pro v12
- OS: Mac OS X 10.8.4
- Experience:Intermediate/Advanced with 3 years experience
I would like to desing a database to store company financial data i.e income statement, balance sheet etc. An example of the data that I would like to be able to produce is:
etc 2008 2009 2010 etc
revenue ... 5000 7500 10000 ...
Cos ... 4000 6000 8000 ...
Gross profit [Calculated number = Revenue - CoS]
In addition, I want to be able to adjust the raw data and keep track of those adjustments. For example, say CoS for 2009 should be 5000 istead of 6000, the database would record this adjustment and the corresponding calculated values so that either the adjusted or unadjusted values could be displayed along with the corresponding calculations.
My intital thoughts on design led me to this database structure:
LineItem:LineItemID => Data:LineItemID
Adjustments:CompID = Data:CompID AND Adjustments:LineItemID = Data:LineItemID AND Adjustments:year = Data:year
This structure appears to have two limitations:
- The calculated entries such as "gross profit" in my example above will be cumbersome to create as I think I would need to create separate relationships for each calculation. As there will eventually be over 100 this does not seem easy to adminster; and
- How can the cross tabulation of the data be achieved so that it is displayed with LineItems as rows and years as columns.
I would appreciate some help with getting this structure right from the beginning.