Table of Contents

How to Create a Standard Weekly Report

Please note: there is an example standard weekly report from March 2017 attached to this wiki page that can be used as an example.

History of the Standard Weekly Report:
The Standard Weekly Report was a custom report in DonorPerfect that was run by DevComm every Friday. However, when DonorPerfect switched from desktop to online, the report did not follow. We now have to combine a series of different reports in order to recreate the Standard Weekly Report. If you go into the drive under membership - reports - standard weekly reports, any of the 2015 reports will give you an idea of what this thing looked like in its original form. As of April 2017, for us to recreate this exact report, it would cost around $1,000 - 2,000 and would need to be done by one of their programmers in Smart Analytics.

The following How-To is the only way known to create this report. There may be an easier way to do this in the future, especially with reporting changes in Donor Perfect - if that does happen, please update the how-to accordingly.

Please do this report in Google Docs and follow all of the steps in the order they’re listed!!!

Also, make sure to save it onto the membership drive/ reports/ standard weekly reports/ year.

Step 1: Pull all of the Solicitation Codes for the year
  1. Log into DonorPerfect - go to report center - click on the financial reports folder - solicitation analysis
  2. In the left hand side bar, scroll to the bottom and underneath “Gift/ Pledge,” click on the binoculars located next to the “Solicitation code” box
  3. Go through and select all of the solicitation codes you want to show on the report. You may need to click “view inactive codes” for solicitation codes that have been hid.
  4. In the left hand side bar, scroll to the bottom and underneath “Gift/ Pledge,” click on the “date of gift” box and change the range to 01/01/1980 - the date of the report
  5. Click “run report” in the bottom left side bar and download the report as an Excel sheet.

Step 2: Get the report formatted in Excel (or Google Sheets)
  1. I usually take a previous Standard Weekly Report, make a copy of it and then clear all of the fields and work out of that sheet - that way you already have a template of the fields you need.
  2. It’s a bit easier if you reorganize the fields in the correct order of that you need it in the spreadsheet you downloaded from DonorPerfect before you copy and paste it into the sheet you’re going to be working in.
  3. You are going to need to organize the sheet in order of the most recent solicitations first. Beneath all of the solicitations that we actually mailed out, put all the other codes that have been used throughout the entire year (and we did NOT mail).

Example:
7/22/2016 N-2-16
7/11/2016 PRO16
6/22/2016 VSDM16
……………………………
1/1/2016 UNS16
1/1/2016 UNSON16
1/1/2016 WEB16
1/1/2016 WEBN16

Step 3: Pull all of the Solicitation Codes for the week
Your goal is to get the weekly gift amounts and $ of donations.
  1. Log into DonorPerfect - go to report center - click on the financial reports folder - solicitation analysis
  2. In the left hand side bar, scroll to the bottom and underneath “Gift/ Pledge,” click on the “date of gift” box and change the range to the week the report is for. I use the Monday - Sunday prior to the current week I am on.
  3. Ensure that the Solicitation box is empty
  4. Click “run report” in the bottom left side bar and download the report as an Excel sheet.
  5. You are now going to take the “# of Gifts” and “Total Revenue” column of this report and place it into the report you already have.
  6. Just copy and paste these values in for each solicitation code that is listed on the report.
  7. You may have some solicitation codes that are not going to be on this report - just highlight those cells in gray.

Step 4: Fill in Missing Info
  1. To find the cost per piece, you will need to take the total cost of the mailing/ # mailed
    • Any solicitation code that we did NOT mail will have a cost per piece of 0.
    • You can use an excel formula for this part. Ex: =I5/D5. You are dividing column I (cost of the mailing) by column D (# of people mailed)
  2. To find the return on investment, you will need to take the net revenue/ mailing cost
    • You can use an excel formula for this part. Ex: =K5/I5 You are dividing column K (net revenue)/ column I (total cost of mailing).
    • A ROI of -30.00% or higher should be in green. Anything lower than -30.00% should be in red.
    • In excel go to format - conditional formatting and create the rule. `
  3. Below all of the solicitation codes, you will need to find the sum of the individual columns: # Mailed, # of Gifts, Total Revenue, # of Gifts, Total Revenue, Total Cost of Mailing, and Net revenue.
    • You can use an excel formula for this part. Ex: =SUM(D5:D20)
    • You will need to average the columns: “average gift” & “cost per piece,” excluding 0.00.
      • You can use an excel formula for this part. Ex: =AVERAGE(L5:L39)

Step 5: Fill in the Bottom Boxes
  1. Log into DonorPerfect - go to report center - click on the “financial reports” folder and select the “sub-solicitation analysis” report
    • The sub-solicitation analysis report will pull each sub solicitation code (R, N,D, API), for the given date range that you put in.
    • To put in the date range, go to the left hand side bar and select the date range right underneath “Options and Filters”
    • When you run your report, you will see the number of donors and dollar amount totaled out for you by each solicitation code, plus the totals - you will place this information into the standard weekly report bottom box section.
  2. First you need to find the “totals for the week”, compared with the prior year.
    • Run the query for the week for the current year
    • Run the query for the equivalent week for the prior year
  3. Now you need to find “totals for the year”, compared with the prior year.
    • Do it from the renewal date (early December) instead of the calendar year - it will be a more accurate representation of our donations.
    • Run the query for the renewal cycle for the current year
    • Run the query for the equivalent renewal cycle for the prior year


Historical Data from 2013
Overview:
The intention of the weekly report is to record activity up to date as of the date of the report. Thus the report should be about what has happened up to the date of that report. Weekly Reports are used to make financial decisions, decisions on future mailings, and to report to foundations and the Board. Failure to have these reports timely and accurately will negatively effect our grant application efforts, reputation and planning. It is therefore critical that you make every effort to be accurate, complete, prompt, and communicative regarding the reports.

Weekly reports are due to RK, AE, and the ND the Monday following the week being reported. If you are unable to submit the weekly report at that time, you must communicate to the three of them when it will be received and why. They should continue to receive updates (at least DAILY) about the status, what's wrong, and a reasonable best guess as to when it will be submitted up until the report is received.

Prior to Running a Report
We do not want to do reports unless the reports are complete and accurate. Doing them prematurely invites and has actually caused expensive mistakes down the road. If you think it best to hold off on running the report, you should run this and the reasoning by Richard. Here are some things to keep in mind:

-do not wait for final invoices from a vendor in order to run a report- this will delay the report unnecessarily
-if you do not have a final invoice, confirm ACTUAL drop dates, the ACTUAL number mailed, and and the FINAL total cost of the mailing with EACH VENDOR INVOLVED
-have two people doublecheck the calculations for total cost of mailing (including but not limited to: postage, printing, the costs of the prospecting list, and enclosures like newsletters)
-The weekly report should not be run unless all final invoices and mailing drop dates have been entered in to Donor Perfect; no preliminary/unverified information should be entered into DP
-When the actual invoice comes in check to make sure that the invoice is the same as the agreed to or known costs that you had collected
-ensure accurate data entry and checks of contributions
-check the safe for donations left over from the previous week that were not processed, and make sure they get processed prior to running the report
-enter any donations received via email over the weekend (including recurring donations)
-make sure that all solicitation codes (even those without a set drop date) have at least a year assigned to them. For those codes that do not have mail dates and only represent year-to-date (or week within) totals. So simply put the date as if the information begins 1/1/[year], which it presumably does. That way future reviewers see what is happening clearly.
-each january 1st- create new "yearly" solicitation codes and ensure those entering contributions know. Mailings with specific drop dates should continue to use the same codes, as these measure the response to that particular mailing, regardless of the year.

Understanding the Weekly Report
You will need to become very familiar with it so that you note when things are out of the ordinary, i.e., costs, timing or returns, total results, missing information etc. This report is what Richard, you or whomever is making investment decisions must go to bat on and must be both accurate and have any deviations spotted and understood. Our biggest losses have been due to errors in the reports from the Membership Department. First $7,000 in 1990 (undocumented check), then $40,000 in 1998 (lost and undocumented mailing) and roughly $20,000 in 2011 (due to bad calculations on the report).

-the # of members listed at the end of the report- should represent all of the people with a Member flag
-the totals at the end of the report- No matter what date those were actually put into Donor Perfect, the actual date we received the donation is what the report totals. One important thing is that for the totals (yearly and weekly), regardless of which code was used when they were entered, the date that it was received is how it factors into the yearly (and weekly) totals. Even if someone accidentally attributed a donation to a mailing from 3 years ago, the date it was received will ensure that it is counted in the totals for the current year and week. Similarly, if there was a donation that got here on Jan. 3 (for example) but it wasn't put into Donor Perfect until March 3, it would still get counted for the week of Jan. 3 if we were to run that specific week's report again.

Confirm the Accuracy and Completeness of the Weekly Report
-look at the end of the report to make sure that there are no undated solicitation codes. If this is this case, you will need to add the appropriate drop date to Donor Perfect and rerun the report.
-make sure all mailings have a cost assigned to them that makes sense
-make sure that you double check on any contributions that are assigned a very old solicitation code, to make sure that this was not assigned in error. If it needs to be changed, the report will need to be rerun.
-make sure that at the beginning of the year, there are are no "yearly" solicitation codes from the previous year being used
-delete any copies of reports deemed to be incomplete or inaccurate from our records and rerun them- otherwise, we may end up making poor decisions based on these inaccurate reports

Submit Report and Addendum
You will then need to email the report to Richard, Adelaide, National Director and anyone in membership

Again, weekly reports are due to RK, AE, and the ND the Monday following the week being reported. If you are unable to submit the weekly report at that time, you must communicate to the three of them when it will be received and why. They should continue to receive updates (at least DAILY) about the status, what's wrong, and a reasonable best guess as to when it will be submitted up until the report is received.

Include an addendum with your report pointing out:
-anything out of the ordinary (i.e. are the recent mailings performing above or below normal expectations?)
-possible reasons why something is out of the ordinary (for example, a large bequest inflating the numbers, or it simply being a poor return)
-any changes you have made to the report
-the source of new members via the web

Attachments
File Last modified Size
Standard Weekly Report 3_28_17.xls 2017-04-20 13:59 11Kb

There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki