6 Replies Latest reply on Jan 22, 2014 8:02 AM by philmodjunk

    Cash Flow Statement



      Cash Flow Statement


           Hello Ladies and Gentlemen, 

           First time posting here. I am an amateur filemaker user that is trying to make a cash flow statement file for my company,  I started out by making two tables, one for the names of the types of inflows and other for the names of the types outflows, then I made two other tables to input and report the inflows and outflows separately. Finally, I made an overview table, to get an overview of the amounts of inflows and outflows and here is where things get messy, I made a ton of portals to be able to get the totals of the categories for every month, but I cant, for the love of life, get filemaker to add the totals of the inflows and outflows for a particular month. I attached a picture of my overview layout and of my relationships. The portal rows on the left are for the inflows and the ones on the right are for the outflows. I really hope you guys can help me out.

           I thank you in advance for your help and please don't make fun of me :)



        • 1. Re: Cash Flow Statement

               I would suggest a combined table for "in" and "out" transactions that use a debit and credit field for the two kinds of transactions. That then produces a unified table of transactions that you can use for your report. Here's a demo file: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12

          • 2. Re: Cash Flow Statement

                 Thank you very much!

            • 3. Re: Cash Flow Statement

                   Dear @PhilModJunk, 

                   I thank you again for your response, as you can see from the attached image, I have changed your solution to fit my particular situation, my only problem, is to forward the balance from the end of one month to the next.

                   I thank you again for all of your help!

              • 4. Re: Cash Flow Statement

                     You can use a calculation field with a relationship or a script that computes the balance forward that then sets a global number field with the balance forward. You can then put the balance forward in the header and replace the running balance field on your layout with a calculation field that adds the running balance field to the balance forward.

                • 5. Re: Cash Flow Statement

                       I tried using a calculation field, but I cant reference the sBalance(Final balance) field from the previous month

                  • 6. Re: Cash Flow Statement

                         Correct, but you can use a relationship that matches to all records from previous months and then a Sum function can compute the sum of cBal and this will be your previous balance.

                         Note: I'm making up names as I go here so they may not exactly match the names in the demo file.

                         You might define a cMonth calculation field with Date selected as the result type: Date ( Month ( YourDateFIeld ) ; 1 ; Year ( YourDateFIeld ) ) to give all transactions from the same date, the date for the first of the month. Then a relationship such as:

                         Ledger::cMonth > Ledger|Previous::cMonth

                         Where "Ledger|Previous" is the name of a new Table occurrence of Ledger. Then, Sum ( Ledger|Previous::cBal ) will return the previous balance.