4 Replies Latest reply on Sep 10, 2013 11:20 AM by philmodjunk

    Financial Database Design

    pdoak_1

      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

        • 1. Re: Financial Database Design
          philmodjunk

               I suggest researching "cross tab" reports and "horizontal portals" in connection with FileMaker database design. Each column of data can be a single row portal (often a filtered portal) on a list view and then the number or relationships needed become much simpler.

          • 2. Re: Financial Database Design
            davidanders

                 Google "crosstab report filemaker"

            https://www.google.com/search?q=crosstab+report+filemaker

            • 3. Re: Financial Database Design
              pdoak_1

                   Thank you. 

                   Is this database design that I presented the best solution or do you think there are better designs for this type of data. If this is the best solution, I am right that for each calculated field a new relationship will have to be built along the lines of:

                   Data:gLineItemID = Data 2:LineItemID AND Data:year = Data 2:year - this relationship will identify the correct line item

                   Then a calculation field in Data will perform the following calculation: Data:GrossProfit = Data 2:Value/Data 3:Value where

                   Data2:Value = Revenue for the correct year; and

                   Data3:Value = CoS for the correct year.

                    

                   In terms of the cross tab report, it looks like repeating fields solution may work.

                   Many thanks

              • 4. Re: Financial Database Design
                philmodjunk

                     There's "best"--a very fuzzy criterion and "easy".

                     "Easy" is to set up a summary report where the data is not arranged in this grid.

                     2008
                        Revenue
                        Cos
                        Gross Profit

                     2009
                         Revenue
                         Cos
                     etc...

                     That can be produced with summary fields and sub summary layout parts.

                     But that makes comparing values across the years more difficult so I wouldn't call it "best".

                     And please note that the correct structure for your tables and their relationships is critical for either format.