3 Replies Latest reply on Oct 1, 2012 9:18 AM by philmodjunk

    Cash Flow Layout

    TylerNelson

      Title

      Cash Flow Layout

      Post

           I have a data table of financial Commitments listing pending payments to various entities with Due Dates for each payment. Each Commitment record has a serialized CommitmentID, Amount Due, Due Date, etc. I would like to make a layout that would give me a CASH FLOW chart based on these Commitments. So if I have a commitment due for $100 on Oct 1st, a commitment due on October 15th for $100 and a commitment due on Oct 30th for $100, it will show me in some kind of list view (or ideally some kind of Calendar view) that from October 1-14 I will have spent $100, by the 15th and until the 29th I will have spend $200 and by the 30th I will have spent $300. 

           I'm not sure how to approach this. I created a table called CashFlow with 3 fields: Date, CommitmentsToDate, zcConstant1 (a calc field that equals 1). But I'm not sure how to relate it to properly relate it to my Commitments table in order to calculate the CommitmentsToDate. I made the CashFlow layout a list view and created about 30 records for each day in the month. I have Commitment dates in the Commitments table with due dates from that month. In my CommitmentsToDate field I would like to execute a sum() function through a relationship from CashFlow to Commitments, where 2 zcConstant1 fields in each table are related and the date fields are related  as such: CashFlowDate ≤ CommitmentDate... Needless to say my approach is failing and I can't seem to get the calc field to display anything. Any suggestions!???

        • 1. Re: Cash Flow Layout
          philmodjunk

               I'm not sure what the "rules" are for the date intervals you describe here:

               

                    So if I have a commitment due for $100 on Oct 1st, a commitment due on October 15th for $100 and a commitment due on Oct 30th for $100, it will show me in some kind of list view (or ideally some kind of Calendar view) that from October 1-14 I will have spent $100, by the 15th and until the 29th I will have spend $200 and by the 30th I will have spent $300.

               You describe a 2 week interval (1-14) followed by a single day (the 15th) followed by another 2 week interval ( 15-29th) and then another single day (30th) and that opens up questions for February and months that are 31 days in length.

               Can you provide just a bit more detail about exactly what you have in mind?

          • 2. Re: Cash Flow Layout
            TylerNelson

                 In my hypothetical scenario, if I only have 3 commitments in January for $100 each, by February my CommitmentsToDate should be a fixed $300. Should I have a commitment on any particular day in February, for say $100, on and after that day my CommitmentsToDate should be $400. The date intervals don't matter. So if I had more commitments spread out in October, each commitment would increase the CommitmentsToDate total from that day on. 

                 This layout is part of a Film Production Management solution and the whole point of this Cash Flow chart is to assess how quickly I will deplete the funds in the bank. So for example, if I have $100,000 in the bank and next week I have many Commitments due that total $80,000 and the following week I have $20,000 due, I can deduce that I will need additional funding in two weeks, but I should be covered for next week.

                 Thanks!!!!

            • 3. Re: Cash Flow Layout
              philmodjunk

                   Sounds like all you need is a summary field that totals your amount field with a running total.