5 Replies Latest reply on Sep 23, 2014 11:05 AM by philmodjunk

    advice for a revenue table

    alekz

      Title

      advice for a revenue table

      Post

      I have a revenue table to keep track of projected income from small construction projects. Two projects are now actually under construction, and about a dozen are in various stages of planning/negotiations.

      The revenue table projects revenue via calculation fields, taking a total project value multiplied by percentage of probability the project comes to fruition (thus 100% means a contract is signed and under construction), a new field for every month with projections to the end of 2016. These calculations are also constrained by revenue start and end dates, so it doesn’t keep projecting revenue into infinity.

      The superiors would like to punch in their own values into revenue projection fields once the contracts are signed since the actual revenues vary from month to month based on whatever deals got hammered out with the clients.

      Since the fields are currently calculations, they cannot override them. 

      I think the easiest thing to do is to change the field type to Number and then use a Calculated Value with the same calculation and allow an override. A coworker thinks it’s best for the long term to create a new table for actual revenue and use a script to move records over to the Actual Revenue table when the contracts are signed so that they can have free range over the fields in this new table.

      What would be the best way to go forward?

        • 1. Re: advice for a revenue table
          philmodjunk

          Is there a value in being able to compare projected revenue against actual? That would seem the point of your Coworker's idea and that is as much a business decision as it is a database problem.

          And you might not want to use a series of fields all in one record in the first place. This sounds like a case where a table of related records might be the best way to set up your initial projections from the start.

          • 2. Re: advice for a revenue table
            alekz

            I believe they would like to keep projected and actual revenue separate but somehow be able combine them when they want to. How to keep them separate and yet combine them at will, I am not sure how to conceptualize that in terms of the database.

            When we created the table, I also felt unsure if using a series of fields all in one record was a good idea and I believe you may have said the same thing in response to one of my previous posts. But given the time constraints and a general lack of knowledge, we plowed ahead with creating fields for individual months for three years ahead. Presently, the revenue table has 60 fields and counting.
            Here are a couple of screenshots: http://imgur.com/1e51FWy,Ucpo8Fx  http://imgur.com/1e51FWy,Ucpo8Fx#1

            Here’s an example calculation for field "FY14 Apr”:

            If ( Date ( 4 ; 15 ; 2014 )  ≥ Project Revenue Start Date and Date ( 4 ; 15 ; 2014 ) ≤ Revenue End Date ; PWNV/12 ; 0 )+ If ( Date ( 4 ; 15 ; 2014 )  ≥ Projects::Act_Feasibility_Started and Date ( 4 ; 15 ; 2014 ) ≤ Feasibility End Date ; Projects::Feasibility_Value/3 ; 0 )

            This calculation is repeated in other fields, we simply change the dates accordingly.

            If I am to redo the revenue table as a one-to-many relationship where a project is related to multiple records representing monthly income, how am I to replicate this calculation field? While I feel a lot more comfortable with FileMaker than I did a year ago, I still find this issue hard to grasp.

            • 3. Re: advice for a revenue table
              philmodjunk

              With or without changing to related tables, the explicit dates shown in your example will be an ongoing source of trouble. As time passes what will you do to keep these set up as dates for the future without changing what is calculated in past records? Will you just keep adding more fields?

              Consider the following modification to the above calculation:

              PWNV/12 + Projects::Feasibility_Value/3

              That's it. You use a relationship based on __pkProjectID to match to only records specified for this project. A date field in that record would be used to identify the month that it represents. This assumes that you are using the same calculation to project revenue for each month. If there is some detail to this calculation that changes the result computed with each month, a slightly more sophisticated method would be used.

              You are able to limit the applicable months by only creating those records that are applicable to a given project. IN fact, a script can take the project start and end dates and generate one such record for each month starting with the start date and finishing with the end date.

              You'd then add a number field to these same records for recording the actual revenue and you now have a way to record the actual revenue to compare against that which was projected for that same month.

              • 4. Re: advice for a revenue table
                alekz

                I went ahead and created a new related table with projected revenue. I modified the calculation as follows:

                If ( Date ≥ Revenue::Project Revenue Start Date and Date ≤ Revenue::Revenue End Date ; Revenue::PWNV/12 ; 0 )
                 + 
                If ( Date ≥ Projects::Act_Feasibility_Started and Date ≤ Revenue::Feasibility End Date ; Projects::Feasibility_Value/3 ; 0 )

                I simply substituted the hard coded date with the Date field in the new table. My new data seems to be identical to the stuff in the existing tables, so that's great. My next step would be to automate record creation with a script.

                But now I am faced with a different challenge. The financial officer likes to export the data to his excel sheets the way the fields are currently designed: FY 14 Sep, FY 14 Oct, etc. 

                | ProjName | FY14_Sep | FY14_Oct | FY14_Nov ...
                | ProjA    | $x       | $y       | $z
                | ProjB    | $x       | $y       | $z
                
                 

                My new related table is now like this:

                | ProjName | Date    | $$$ |
                | ProjA    | FY14Sep | $x  |
                | ProjA    | FY14Oct | $y  |
                | ProjA    | FY14Nov | $z  |
                | ProjB    | FY14Sep | $x  |
                | ProjB    | FY14Oct | $y  |
                | ProjB    | FY14Nov | $z  |
                

                It looks great in a portal but leaves me with an unhappy financial officer who wants to see and chart the data the old way when he exports to excel. Perhaps this is now a question for an excel forum? Thanks for all the advice thus far.

                • 5. Re: advice for a revenue table
                  philmodjunk

                  Sorry, but I don't see the need for the calculation fields. I can't imagine revenue records related to a given project that do not fall within the start and end dates specified for that project.

                  Have you considered using FileMaker to produce your FO's chart?

                  It's still possible to set up your data for export to excel with the columns he wants, but if you can chart the data in FileMaker, life get's simpler all around.

                   To set up fields for export, you can set up a series of calculation fields for your project records that extract this data from the related data for exporting purposes. GetNthRecord, for example, can be used to pull up the nth related record from a set of related records.