This 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.
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 Connect Now 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.
Appendix: Creating 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:
- Pledges Pledge DUID
- Pledges Family DUID
- Pledges Total Pledge Amount
- Pledges Pledge Date
- Pledges Start Date
- Pledges Months Amortized
- Pledges_Down Payment Down Payment Amount
- Pledges Total Paid
- Pledges Balance
- Pledges Installment Amount (Unfiltered)
- Pledges Billing Frequency ID
- Pledges Billing Frequency
- Pledges Fund DUID
- Funds Fund Name
- Funds Description
- Pledges Jan
- Pledges Feb
- Pledges Mar
- Pledges Apr
- Pledges May
- Pledges Jun
- Pledges Jul
- Pledges Aug
- Pledges Sep
- Pledges Oct
- Pledges Nov
- Pledges Dec
- Pledges Total Adjustment Amount
- Pledges Last Adjustment Date
- Pledges Installment Amount (or Balance)
- Pledges is Paid In Full
- Pledges Latest Payment Amount
- Pledges Latest Payment Date
- Pledges Months Remaining
- Pledges Months Since Pledge Inception
- Pledges Number of Months Billed
- Pledges Original Pledge Amount
- Pledges Payment Count
- Pledges Percent of Pledge Fulfilled
- Pledges Fulfillment Method
- Pledges End Date