1 2 Previous Next 18 Replies Latest reply on Apr 29, 2009 8:49 AM by DonorTracker

    a monthly financial report?

    DonorTracker

      Title

      a monthly financial report?

      Post

      How do I best go about creating an automated Monthly Financial Report that has the following information?
      I'm putting items in < > to indicate that their values change.

       

      I'd like to just enter the Month and Year on a layout to generate the report, thusly:

       

      <Month> <Year> Financial Report

      BankA
      Previous Balance <last day of previous month> <previous balance amount>
      Credits (from INCOME table)
       Memberships <amount>
       Donations <amount>
       Interest <amount>
      Total Credits: <sum>
      Credits Balance: <sum> + <previous balance amount>

      Debits (from CHECK_REGISTER table)
       item1-description <amount>
       item2-description <amount>
       ...
      Total Debits: <sum>

      Final Balance BankA: <credits_balance> - <debits_balance>

      Rinse and repeat for two more Bank Accounts (BankB and BankC).

      Then total the Final Balances of all the Accounts

      (Typically the credits would be on the left side of the report, and the debits on the right side, rather
      than above-and-below as shown above.)

       

      The two Tables (INCOME and CHECK_REGISTER) each have a date field for each record.

       

      -----

       

      I'm assuming that I'll need to create a new MONTHLY_REPORT table that has a month field and a year field that link to those values in INCOME and CHECK_REGISTER?  Is that the right approach?












        • 1. Re: a monthly financial report?
          Orlando
            

          Hi DonorTracker

           

          I think you may be able to do this all with the tables you have and a few relationships, summary fields and a sub summary report.

           

          Have you got any sub summary reports setup already for any part of this?

          • 2. Re: a monthly financial report?
            DonorTracker
              

            Orlando wrote:

            Hi DonorTracker

             

            I think you may be able to do this all with the tables you have and a few relationships, summary fields and a sub summary report.

             

            Have you got any sub summary reports setup already for any part of this?


            Hi Orlando,

             

            No, not yet. (Is there a way to put multiple sub summary reports onto a single sheet?)


            • 3. Re: a monthly financial report?
              Orlando
                

              Yes you can have as many sub-summary parts to your report layout, just need to sort by each of the fields the sub summary part is based on. Although looking at it again you have the Credits and Debits in two separate tables which may mean you will need to have two separate report layouts as this cant be easily done in one.

               

              Do you need to view these reports on screen or could you compile a PDF and view the final report in that?

               

              You could build on report for the Credits, a second for the Debits, and the Debits could hold the Total Debits: and Final Balance Bank#: lines as they are at the end of the report for the bank.

               

              Run the reports via a script for each bank individually, finding the appropriate records for the current bank and appending the PDF and then going on to the next bank until you have done all three banks.

               

              I think that will do the trick, if you want me to go into more detail about this let me know and i will be more than happy to help.

              • 4. Re: a monthly financial report?
                DonorTracker
                  

                Ultimately, I need a printed report. 

                 

                Like you I was thinking that the solution might require outputting to PDF and using a program like SwiftPublisher to combine the PDFs onto a single sheet.

                 

                Would it be possible, instead, to use scripted Finds to set temporary variables storing the summary totals for use in a third (Monthly Report) Table? (I've never used the variables and programming constructs of scripting in FM so far.)

                • 5. Re: a monthly financial report?
                  Orlando
                    

                  On the PDF thing, you have an option in the Save as PDF script step to 'Append to existing PDF' which will add what you are currently saving to another PDF as long as the file path you set is the same. and then you can open the PDF using the Send Event step.

                   

                  With regards to a Monthly Reports table, you can use scripted finds and set values in this third table no problem, and doing this will definitely get you familiar with ScriptMaker and Variables.

                   

                  Is your report just for the top line figures, or are you listing all the records for members for example? Do you see each banks figures, for both Credit and Debit being on the same line along with all the other figures?

                   

                  Also do you have the Summary fields setup for the totals? 


                  • 6. Re: a monthly financial report?
                    DonorTracker
                      

                    Orlando wrote:...

                     

                    With regards to a Monthly Reports table, you can use scripted finds and set values in this third table no problem, and doing this will definitely get you familiar with ScriptMaker and Variables.

                     

                    Is your report just for the top line figures, or are you listing all the records for members for example? Do you see each banks figures, for both Credit and Debit being on the same line along with all the other figures?

                     

                    Also do you have the Summary fields setup for the totals?


                    My INCOME Table records each item by date, source (person), purpose (membership, donation, interest). My monthly report Credits would have just three lines for these: total membership amount, total donation amount, and interest amount. And then total.

                     

                    My CHECK_REGISTER Table records both cheques written (debits) and deposits. I'm only interested in the debits portion. For my monthly report Debits, I need to list each cheque written for that month (with Payee, amount, description). And then total.

                     

                    Although I'd like the Credits on the left and the Debits on the right side of the report, no lines have to line up between Debits and Credits.

                     

                    Regarding your last question: I don't yet have Summary fields set up for totals.

                     

                    For conceptual purposes, it would be all right to just consider the case of a single Bank account. The first Bank is where most of the "action" happens.

                     

                    I should also explain my workflow, some of which has historic origins from when the bookkeeping was "paper-based": for each income item, I scan the paper documents and save them as a PDF. Then I enter the details into my INCOME Table, with a container field link to the PDF.

                     

                    Every few weeks, the collected cheques and money gets recorded in a DEPOSITS Table which creates a Deposit Tape for the Bank. That total deposit amount then gets entered into the CHECK_REGISTER Table.  Each income item (in INCOME table) records the Date of its Deposit. And that is the Date I use for determining which month an income item gets recorded into.

                    • 7. Re: a monthly financial report?
                      Orlando
                        

                      Well here goes...

                       

                      Ok I think I have it, have a go with the following on a backup and hopefully this will do the trick for you, this will place all the values in a single record for the specified month, one record for each bank account.

                       

                      In your INCOME table you need to setup a number of summary field in your Credits table, TotalMembership, TotalDonations and TotalInterest. To get these to work properly you also need a calculation to base the summary on.

                       

                      So for Memberships you will have a calculation called ValueMembership and the calculation for this field is:

                       

                      If ( purpose = "Membership" ; Amount )

                       

                      then you create a field called TotalMembership and set the type to Summary and in the options dialog you set it to 'Total of' and specify ValueMembership.

                       

                      And then do the same for Donations and Interest.

                       

                      Now in the CHECK_REGISTER table you just need a single summary field called TotalAmount, set to be summary and again set to 'Total of' but pointing to your Amount field.

                       

                      That should do the trick

                       

                      Now you need to create a new table called MONTHLY_REPORT with the following field:

                       

                      Date [ number ]

                      Account [ text ]

                      Income_Membership [ number ]

                      Income_Donations  [ number ]

                      Income_Interest  [ number ]

                      Income_Total [ calculation ; number =  Income_Membership + Income_Donations + Income_Interest ]

                      Debits_Total  [ number ]

                       

                      And then the following script.

                       

                      Set Variable [ $Date ; DateGlobal ]
                      Set Variable [ $SearchMonth ; "*/" & Month ( $Date ) & "/" & Year ( $Date ) ]
                      Set Variable [ $AccuntList ; "BankABankBBankC" ]
                      #
                      Loop
                      Set Variable [ $LoopCounter ;  $LoopCounter + 1 ]
                      Set Variable [ $Accunt ; GetValue ( $AccuntList ; $LoopCount ) ]
                      #
                      # CREDITS
                      Go to layout [ Credits ]
                      EnterFindMode [ ] // deselect Pause
                      Set Field [ Account ; $Account ]
                      Set Field [ Date ; $SearchMonth ]
                      Perform Find [ ]
                      #
                      If [ Get ( FoundCount ) > 0 ]

                      Set Variable [ $TotalMembership ; TotalMembership ]
                      Set Variable [ $TotalDonations ; TotalDonations ]
                      Set Variable [ $TotalInterest ; TotalInterest ]

                      End If
                      #
                      # DEBITS
                      Go to layout [ Debits ]
                      EnterFindMode [ ] // deselect Pause
                      Set Field [ Account ; $Account ]
                      Set Field [ Date ; $SearchMonth ]
                      Perform Find [ ]
                      #
                      If [ Get ( FoundCount ) > 0 ]

                      Set Variable [ $TotalDebits ; TotalAmount ]
                      End If
                      #
                      # CREATE REPORT LINE
                      Go to layout [ Report ]
                      New Record / Request
                      Set Field [ Date ; Year ( $Date ) & Month ( $Date ) ]
                      Set Field [ Account ; $Account ]
                      Set Field [ Income_Membership ; $TotalMembership ]
                      Set Field [ Income_Donations ; $TotalDonations ]
                      Set Field [ Income_Interest ; $TotalInterest ]
                      Set Field [ Debits_Total ; $TotalDebits ]
                      #
                      Exit Loop if [ $LoopCount = 3 ]
                      #

                      End Loop
                      #

                       

                      Let me know if I have missed anything.

                       

                      I hope this helps 

















                      • 8. Re: a monthly financial report?
                        DonorTracker
                          

                        Orlando, many many thanks!

                         

                        That looks like some seriously good code.  You'll probably not get any feedback about it from me until tomorrow.  But for now ... THANK YOU!!!

                        • 9. Re: a monthly financial report?
                          Orlando
                             No worries and I hope it works as you want it to, do let me know if you run into any difficulties.
                          • 10. Re: a monthly financial report?
                            DonorTracker
                              

                            Hi Orlando,

                             

                            I've rolled up my sleeves and am digging into this code -- though with some modifications.

                             

                            In the expression:

                             

                            If ( purpose = "Membership" ; Amount )

                             

                            how can I in the if-statement substitute for "Membership" a wildcard that would match any string beginning with Member

                            for example?

                            • 11. Re: a monthly financial report?
                              Orlando
                                

                              Yes, you could use 

                               

                              If ( Left ( purpose ; 1 ) = "M" ; Amount )

                               

                              The left function grabs the specified number of characters from the left of the string, in this case it will return the first letter of the word and if it equals 'M' it will return the amount.

                              • 12. Re: a monthly financial report?
                                DonorTracker
                                  

                                Orlando wrote:

                                Yes, you could use 

                                 

                                If ( Left ( purpose ; 1 ) = "M" ; Amount )

                                 

                                The left function grabs the specified number of characters from the left of the string, in this case it will return the first letter of the word and if it equals 'M' it will return the amount.


                                Thanks, Orlando.  It looks like the logical matching isn't quite as flexible as the wildcards available for searches.

                                 

                                Is there a function I could use to match any string that contained "new" anywhere in the string?  (The equivalent of *new* in a find.) Could the PatternCount function do this?

                                 

                                Alternatively, is there a way to phrase the "if-statement" so that I could match "foo" or "bar", for example?


                                • 13. Re: a monthly financial report?
                                  Orlando
                                    

                                  PatternCount ( purpose ; "New" ) will return true (1) if "new" is anywhere in the string. So to incorporate this into an If statement, along with "foo" or "bar" try:

                                   

                                  If ( PatternCount ( purpose ; "New" ) ; Amount )
                                  • 14. Re: a monthly financial report?
                                    DonorTracker
                                      

                                    PatternCount is working well. And solves my immediate issue.

                                     

                                    But for future reference,  could you clarify the syntax for  IF purpose EQUALS "foo" OR purpose EQUALS "bar" THEN SET TO Amount

                                     

                                    Many thanks!

                                    1 2 Previous Next