AnsweredAssumed Answered

Financial Database Design

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

Title

Financial Database Design

Post

System Information

         
  •           Product: FM Pro v12
  •      
  •           OS: Mac OS X 10.8.4
  •      
  •           Experience:Intermediate/Advanced with 3 years experience

      

Question

     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:

Tables

     Company:CompID, CompName

     LineItem:LineItemID,name

     Data:CompID,LineItemID,year,value

     Adjustments:CompID,LineItemID,year,value

Relationships

     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
  2.      
  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

Outcomes