CNFS IQ - Reports: Produce a 5-Year Pledge Projection Report

Objective

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.

Training Steps

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:

  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
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.