How to produce a 5-year pledge projection report

This article will give you the steps to follow to create a 5-year pledge projection report in Excel. The report will provide a projection of income from a pledge for the next 5 years.

This report may already exist. To check, click the My Queries option and view a list of existing queries. If you don't find it, continue with the following instructions.

To produce the report, you will create and run a query in IQ and paste the data from the query into an Excel spreadsheet that will produce the report. This article assumes that you understand how to create and edit IQ queries. If you are not familiar with IQ, start with the video training session IQ Basics.

There are 4 steps to the process of creating the pledge projection report.

1. Open MS Excel Template "PledgeProjectionCN_Master"
   • A link to the template is at the bottom of this document. 
2. Run Query in IQ
   • Open ParishSOFT and choose the IQ module.
   • Open the IQ query "PledgeProjectionPull."
   • If you need to create the query, see instructions at the bottom of the article
   • Choose the funds you want to report on. If appropriate, set a condition for Pledge Start Date to limit the report to pledges that were given in a specific date range.
     

3. Execute and Save Query Results
   • Click Execute Query to produce the list of query results. It may take some time for the query to run.
     
     When the query runs, select Query Results and choose Export to CSV. A file will download to your computer. The specific way to open the file depends on your web browser and computer setting. In Chrome and Internet Explorer, you will see a grey box with the filename in the lower left corner of the IQ window.

      

4. Copy Results to Spreadsheet and View
   • Open the CSV export file.
   • Highlight and copy all the cells in the CSV export file.
   • Paste the cells into the Data tab of the PledgeProjectionCN_Master spreadsheet.
   • Select the Report worksheet.
   • Review and/or Print as desired.

How to create the Pledge Production Data Pull Query in IQ

The IQ query "PledgeProjectionPull" needs to produce exactly the right result columns in order to be read by the spreadsheet. To create the query, go through the following steps:

For Query Condition, use Pledges: Fund DUID. This should include the Fund ID for the fund you want to get projections for. You can use multiple funds or limit the results to pledges given during a specific time period.

For Result Columns, enter the following columns in the exact order specified below:

1. Pledges Pledge DUID
2. Pledges Family DUID
3. Pledges Total Pledge Amount
4. Pledges Pledge Date
5. Pledges Start Date
6. Pledges Months Amortized
7. Pledges_Down Payment Down Payment Amount
8. Pledges Total Paid
9. Pledges Balance
10. Pledges Installment Amount (Unfiltered)
11. Pledges Billing Frequency ID
12. Pledges Billing Frequency
13. Pledges Fund DUID
14. Funds Fund Name
15. Funds Description
16. Pledges Jan
17. Pledges Feb
18. Pledges Mar
19. Pledges Apr
20. Pledges May
21. Pledges Jun
22. Pledges Jul
23. Pledges Aug
24. Pledges Sep
25. Pledges Oct
26. Pledges Nov
27. Pledges Dec
28. Pledges Total Adjustment Amount
29. Pledges Last Adjustment Date
30. Pledges Installment Amount (or Balance)
31. Pledges is Paid In Full
32. Pledges Latest Payment Amount
33. Pledges Latest Payment Date
34. Pledges Months Remaining
35. Pledges Months Since Pledge Inception
36. Pledges Number of Months Billed
37. Pledges Original Pledge Amount
38. Pledges Payment Count
39. Pledges Percent of Pledge Fulfilled
40. Pledges Fulfillment Method
41. Pledges End Date

Related Articles

How to export query results to a .csv file

How to open and view a saved query

Top

• PledgeProjectionCN_Master.xlsx7 MB

April 17, 2023 18:44