7 Replies Latest reply on Nov 2, 2015 4:23 PM by bwoods

    Cashflow carried forward calculation

    bwoods

      I am trying to create a cashflow system for projects which all have varying time frames.  Each project will have its own project ID and a cashflow table, the cash flow table has  a record for each month of the project with the following fields:

      1)  Expenditure

      2) Income

      3) Profit for month (calculation of income - expenditure)

      4) Balance (carried forward from previous month with profit or loss added)

       

      I would like  this to work without having to run a script to recalculate the table if changes are made.  I cannot see a way to do this without a script though.  

       

      Does anyone have any suggestions?

       

      Thanks in advance

       

      Brenton

        • 1. Re: Cashflow carried forward calculation
          mikebeargie

          calculate a field in your cashflow table that makes a number positive of negative depending on what type of transaction it is. Then you'll just be able to summarize the whole table to get your balance.

           

          if ( cashflow::type = "expenditure" ; cashflow::amount * -1 ; cashflow::amount )

          • 2. Re: Cashflow carried forward calculation
            bwoods

            Thanks Mike

             

            I understand from your answer the summary field you suggest will provide a total for the balance for the overall period.  However I need to track these as a running balance on a month by month basis, so I can see which months drop into negative or overdraft. 

            I have thought of a script which will do this using a variable to carry forward the previous month balance to the next month, but my preference is to have this as a calculation.   

             

             

            Brenton

            • 3. Re: Cashflow carried forward calculation
              bigtom

              Seems you should be able to use an unstored calculated field for Balance forward.

               

              But if you have an unstored calc chasing down last months unstored calc and so on eventually the source needs to be stored or it will not work.

              • 4. Re: Cashflow carried forward calculation
                mikebeargie

                If a report is acceptable, then you could sub-summarize the report based on a month/year field.

                • 5. Re: Cashflow carried forward calculation
                  bwoods

                  Thanks Mike and Bigtom.

                   

                  I think from your answers there is not a way to do this the way I initially imagined.  I can see some issues if I did just use the previous record as part of the calculation.  For instance, if for some reason the records were not sorted correctly, the whole cashflow will be wrong.  (part of my thought process was the way I had developed similar models using excel, which makes it easy to reference the previous month.)

                   

                  So I have resigned myself to running a script to do this which will be cleaner.  I will play with some script triggers to see if I can make it work without user intervention.

                   

                  Cheers

                   

                  Brenton

                  • 6. Re: Cashflow carried forward calculation
                    mikebeargie

                    Just be careful about slowing down your system to a crawl. I mentioned reporting because 9/10 that gets any user where they need (click a button, get a PDF). Instantly available sub summarized data like that isn't optimal. And to display it in a portal, you need to have a record to display it against. While virtual list is an option, as noted performance could suffer (and decrease as time goes on and the data set gets larger).

                    • 7. Re: Cashflow carried forward calculation
                      bwoods

                      Thanks Mike

                       

                      I will keep that in mind.  I have already experienced problems with script triggers running when not intended and slowing the system down.   So I am now wary of how I use them, and monitor the database using the script debugger to make sure they are not firing on unintended events.

                       

                      Cheers

                       

                      Brenton