1 2 3 Previous Next 44 Replies Latest reply on Aug 9, 2014 6:18 AM by hllp

    Calculate Weekly amount in Planned-Giving Database

    hllp

      Title

      Calculate Weekly amount in Planned-Giving Database

      Post

           I am creating a database for Planned-Giving in my parish. I need some guidance on how to calculate an amount base on 2 fields:

           Frequency = Weekly, Fortnightly, Monthly, Quarterly, Half Yearly and Yearly

           Amount Pledged = (Amount base on the Frequency)

           The other field is a Calculation field (Weekly Amount). I want to calculation the weekly amount base on the Frequency.

           I am new to Filemaker Pro therefore any help would be very grateful.

        • 1. Re: Calculate Weekly amount in Planned-Giving Database
          philmodjunk

               And from what kind of table of data will you be computing this value?

               Do you have a table where each gift is logged, recording the date, the amount and the ID of the donor?

               Do you have a second table where you have one record for each parish member (or at least each donor)? With a field for recording their pledge amount?

          • 2. Re: Calculate Weekly amount in Planned-Giving Database
            philmodjunk

                 And in the table where each gift is logged, is each such donation logged as a separate record?

            • 3. Re: Calculate Weekly amount in Planned-Giving Database
              hllp

                   Thanks for responding so quicly.

                   I have got the following:

                   Household; Members; Planned-Giving and Weekly Record

                   The calculation will be done in Planned-Giving.

                   Yes, I have a weekly record to lodge the amount each week.

                   In the fields in the Planned-Giving Database are:

                   Member ID; Pledged_ID; Envelope#; Financial Year; Frequency; Amount Pledged; Weekly Amount; Status (Active or Inactive); !st Quarter Total; 2nd Quarter Total; 3rd Quarter Total; 4th Quarter Total; Address to (Mr & Mrs  etc); Last Name (Lookup from Members.)

              Another Question?

                   How to start a new Fiscal Year but continue to use the Current Planned-Giving Database until the end of the current Fiscal Year eg 1st July 2013 to 30th June 2014?

                    

              • 4. Re: Calculate Weekly amount in Planned-Giving Database
                hllp

                     Another field is Amount received to Date. This and 1st, 2nd, 3rd and 4th Quarter total will be sum from the weekly record.

                     Yes. donations are logged in a separate record in Weekly Record:

                     Envelope#; Week (1 to 52/53); Date (Base on the Sunday) & Amount

                • 5. Re: Calculate Weekly amount in Planned-Giving Database
                  philmodjunk

                       Is it possible even if very unlikely that you might receive more than one gift from the same donor in the same week?

                       What version of FileMaker are you using?

                  • 6. Re: Calculate Weekly amount in Planned-Giving Database
                    hllp

                         Unlikely Phil.

                         We also have Direct Debit and we would normally record them into the nearest Week. If they put in couple of Envelopes from different weeks we just add them up and record them in that particular week. Collection are taken at our weekend Mass so it is unlikely that will happen.

                         FileMaker Pro Advance 13 is my version.

                    • 7. Re: Calculate Weekly amount in Planned-Giving Database
                      philmodjunk

                           Unlikely means that it's possible and that your database would be better designed if it were set up to handle even such unlikely events and a simple change to your design can make that very simple to do.

                           

                                we just add them up and record them in that particular week.

                           That takes time and introduces a possibility of data entry errors.

                           Instead of one record for each week, I recommend that you set up your table as one record for each gift. This allows you to record each gift as a separate data entry no matter when it is given. You simply log the date the gift was received. From there, Filemaker can compute totals for you for each week, month, quarter and fiscal year. (You don't need to start a new table or a new file for each fiscal year.)

                           

                                I want to calculation the weekly amount base on the Frequency.

                           If I understand that request correctly, if a member gives on a monthly frequency, you want to take that monthly amount received and divide by the number of weeks to compute an equivalent weekly amount. Is that correct? But if your monthly schedule of giving is based on calendar months, there isn't an exact number of weeks to each months as months vary from 28 to 31 days to each month. So what formula do you have in mind for computing this value? Simply dividing by 4 each time? or Multiple by 12 and divide by 52? or ???

                           The basic approach that I have in mind:

                           There are calculation fields that you can define in the table of gifts received that can take the date received and compute a common date for all gifts of the same week, month, fiscal year or fiscal year based quarter.

                           example:

                           cMonth: DateReceived - Day ( DateReceived ) + 1 // computes the date for the first day of the month for all dates in that month
                           cWeek: DateReceived - DayOfWeek ( DateReceived ) + 1 // computes the date for Sunday of the same week.

                           Fiscal year and quarter based calcs are similar

                           These values in turn allow you to either construct summary reports based on your Gifts Received table that compute totals received for each such time period, or set up calculation field in a table of members table that compute the same totals. (and allow for computing a total where frequency is used to estimate a weekly amount.)

                      • 8. Re: Calculate Weekly amount in Planned-Giving Database
                        hllp

                             Thanks Phil

                             I am just learning about FileMaker Pro and all the calculation formulas so you will have to be patient with my ignorance - I will try to work out what you recommend. It is a learning in progress - I can see you logic in your feedback and suggestion.

                        If I understand that request correctly, if a member gives on a monthly frequency, you want to take that monthly amount received and divide by the number of weeks to compute an equivalent weekly amount. Is that correct?

                             No, we want to calculate the Frequency (Monthly, Quarterly etc) the amount for each week on what they pledged not on what we received. I know it is a complicated way of doing it! I don't have a particular formula - I suppose I will have to use FileMaker to calculate for me. So if it is monthly my head is telling me I would use the formula *12 / 52 or 53 weeks = per week; Quarterly would be *4 / 52 or 53 weeks = per week!

                             There are a few issues that I need to address and you might be able to give advise on:

                             1) Every year some contributors will no longer be contributing and we re-allocate their number to another contributor (to save on wastage - otherwise there would be gaps in between the numbers) how would you avoid the conflict here?

                             2) This Database is intended to be used by other parishes as well who might start their Planned-Giving Fiscal year on a different date (how would you compute the Week? e.g. our Planned-Giving starts on 1st July 2014 and FileMaker calculation is Week 27.  Since our Envelope show Week 1 as 1st July 2014!)

                             I really appreciate your trouble and suggestions. I will take them on board and hopefully I can design something that is workable based on what you recommend.

                             Warm regards

                        • 9. Re: Calculate Weekly amount in Planned-Giving Database
                          philmodjunk
                               

                                    No, we want to calculate the Frequency (Monthly, Quarterly etc) the amount for each week on what they pledged not on what we received.

                               I'm not sure that I understand that. Isn't that something specifically indicated by the donor when they pledge a giving amount? Such as pledging to give 100 dollars a month, or 20 dollars a week or .... were "per month" or "per week" is the Frequency?

                               It doesn't seem like the frequency is calculated here, but if your donor does not specify a weekly pledge, you'd need to compute a weekly equivalent of the pledged amount.

                               If I am correct, than a "weekly pledge" can be easily computed as a calculation field in your members or donors table (whatever you choose to name that table).

                               Case ( Frequency = "Weekly" ; PledgeAmount ;
                                          Frequency = "Monthly" ; PledgeAmount * 3 / 13  ; // 12 to 52 simplifies down to 3 to 13
                                          Frequency = "Yearly" ; PledgeAmount / 52
                                        )

                               Note: since there are 52 weeks to a year, I'm not sure why you are using the 53 week figure in your examples

                               1) I would not do that, there is no "wastage" as issuing additional donor ID's does not cost you anything. Accounting errors due to recording gifts from two different donors that have the same Donor ID, on the other hand could cost you quite a bit.

                               2) You can enter the date of the first day of your Fiscal Year (Such as July first) in a record of a table of your database. The calculations for week and for fiscal year can use that information to help compute the correct value when such is needed. But please note that my previous examples are not used to produce a "week 1" type of value, they only produce a value that will be the same value for all dates in the same week as this is all that is needed for the uses I was describing for those particular fields.

                               To calculate a week number for a particular date based on a Fiscal rather than calendar year:

                               Let ( [ D = YourTable::YourDateField;
                                         FY1 = FiscalYear::YearStartDate
                                       ] ;
                                         52 * ( Month ( D )  <  Month ( FY1 ) ) + WeekOfYear ( D ) - WeekOfYear ( FY1 ) + 1
                                     )

                               Note: I haven't tested this calculation extensively, but a few quick tests--including some with a January date appear to work as expected.

                               Note 2: The above calcualtion assumes that YearStartDate is a field of type date. The actual year entered, however, is ignored. A date for the first day of the month is assumed and only the month is then used to compute a value. This calculationwill only work if there is a relationship matching the single record in FiscalYear to all records in YourTable. This can be set up using the X operator instead of the default = operator in a relationship linking the two tables. An alternative approach is to set up YearStartDate as a global field, but in cases where you host the database over a network, you will need to use a script to load the global field with this value each time that the field is opened. That can be a very simple script copying data from a nonglobal field set up for that purpose into the global field.

                               

                                    I am new to Filemaker Pro

                               

                                    This Database is intended to be used by other parishes

                               I strongly recommend that you do not rush out and distribute copies of your database to other parishes until you have used it successfully for an extended period of time at one location so that you can minimize the chance that a problem uncovered in the design of your database requires you to fix that problem at multiple sites and to give you more time to learn about FileMaker and relational database design.

                                

                          • 10. Re: Calculate Weekly amount in Planned-Giving Database
                            hllp

                                 Phil

                                 Thanks a million for all the work you outlined for me - I will sleep on it tonight. I will let you know once I have put all the instruction and suggestion into the database in the next few days. My day (24hrs) job is a Catholic Priest - this is just an add-on to help my parish!

                                 God bless you

                                 Pat

                            • 11. Re: Calculate Weekly amount in Planned-Giving Database
                              hllp

                                   Every few year we have 53 envelopes in our Pledged when it falls on a leap year - so the last quarter would be 14 weeks. It is not important but our estimate in our report will be out - I found that out in the Access database I help to design. I saw some posting on how to calculate but I will need some guidance here.

                                   Kind regards

                                   Pat

                              • 12. Re: Calculate Weekly amount in Planned-Giving Database
                                philmodjunk

                                     Yes, there are 52 and a part weeks to a year, but there are not 53 full weeks. Your 53rd envelope, technically, will overlap into the start of the next fiscal year.

                                     A leap year has 52 weeks and 2 days. A non leap year has 51 weeks and 1 day. "week number" calculations can return values up to 54 in some cases, depending on the starting date specified for your fiscal year as you can have a partial week at the beginning of the year, 52 full weeks and a partial week at the end of the year.

                                     But none of this justifies dividing a yearly pledge (or 12 times a monthly pledge) by 53 instead of 52 as far as I can see here.

                                     

                                          My day (24hrs) job is a Catholic Priest - this is just an add-on to help my parish!

                                     I'm protestant so my experience may be different, but the pastors that I know put in very long hours caring for their congregation. Tackling this database project on top of such hours seems pretty crazy--unless this is an activity you really enjoy that helps you relieve some stress. Perhaps you need to search out a parish member willing to donate some time helping you put together your database...

                                      

                                • 13. Re: Calculate Weekly amount in Planned-Giving Database
                                  hllp

                                       Thanks again


                                       Unfortunately, the printer in the printing of the Planned-Giving envelopes will always issue 53 envelopes for the leap year. A simple way would be to create another field "Number of Weeks" to enter the number of weeks and use it to calculate -  will this complicate things?


                                       Using the formulas you suggested is working fine:

                                       Case ( Frequency = "Weekly" ; Pledged Amount ;

                                       Frequency = "Forthnightly" ; Pledged Amount /2;
          
                                       Frequency = "Monthly" ; Pledged Amount *3/13;
          
                                       Frequency = "Quarterly"; Pledged Amount *4/52;
          
                                       Frequency = "Half Yearly" ; Pledged Amount *2/52;
          
                                       Frequency = "Yearly" ; Pledged Amount /52

                                       
         )


                                       Yes, it is crazy taking on this task - I did ask but no taker so far! Due to past bad experiences I decided to do it myself ....  Anyway, it sharpens my mind - keeping me young at 64 - a stress relieve as you said!


                                       Since moving to this parish a year ago I found a program here but it is incomplete and confusing - the fields are all in one database - eg household 1, household 2, child 1, child 2 ... child 6, planned-giving, week 1, 2, 3 …. 53 ..... moreover I am an Apple Mac convert and my early program (Access 2) and what is here will not run on Apple Mac.  So the obvious choice is FileMaker!

                                  • 14. Re: Calculate Weekly amount in Planned-Giving Database
                                    philmodjunk

                                         It shouldn't be a problem. I'm just trying to make a distinction between how the envelopes are numbered and the number of full weeks in a year. Upon further reflection, if you are issuing 53 weekly envelopes, you may want to divide by 53 on leap years. Since this will basically be an "estimate" of the member's weekly giving based on a less frequent schedule, it's a bit of a judgement call as to which option works better.

                                    1 2 3 Previous Next