AnsweredAssumed Answered

Financial Database Design

Question asked by pdoak_1 on Sep 6, 2013
Latest reply on Sep 10, 2013 by philmodjunk


Financial Database Design


System Information

  •           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]

     etc .....

     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:


     Company:CompID, CompName





     Company:CompID =>Data:CompID

     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:

  1.           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
  3.           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.

     Many thanks