Advice needed: Comparing Committed vs Actual Donations in Excel

Question

&nbsp;&nbsp;I would like to compare committed donations versus actual donations received for an NGO.&nbsp;&nbsp;Scenario&nbsp;Every month, quarter, half-year, and year, different groups of people commit to donating a specific amount at different intervals.&nbsp;For example:&nbsp;A number of people donate monthlyB number&nbsp; people donate quarterlyC number of&nbsp; people donate half-yearlyD number&nbsp; people donate yearly&nbsp;The number of donors and the committed amount may vary from person to person.&nbsp;Requirement&nbsp;I want to compare Committed Amount vs Actual Amount Received and analyse the difference for:&nbsp;1)&nbsp; Monthly2) Quarterly3) Half-yearly4)&nbsp; Yearly&nbsp;I would like the results to be presented both as tables and line graphs.&nbsp;For example:&nbsp;Monthly:&nbsp;&nbsp;X-axis: Month (Jan, Feb, Mar, etc.)&nbsp;Y-axis: Committed Amount and Actual Amount&nbsp;Quarterly:&nbsp;&nbsp;X-axis: 1Q, 2Q, 3Q, 4Q&nbsp;Y-axis: Committed Amount and Actual Amount&nbsp;Half-yearly:&nbsp;X-axis: 1H, 2HY-axis: Committed Amount and Actual Amount&nbsp;I would also like to see the&nbsp; difference between committed and actual amounts, both in absolute amount and, if possible, as a percentage.&nbsp;My question&nbsp;I need advice on how best to organise the base data in Excel.&nbsp;Specifically:&nbsp;1. What fields/columns should I maintain in the base data?2. How should I record donors who donate monthly, quarterly, half-yearly, and yearly?3. How should I record the committed amount and the actual amount received?4. How should I handle cases where a donor pays less, more, or does not pay?5. How can I generate monthly, quarterly, half-yearly, and yearly summary tables from the base data?6. How can I create the corresponding line graphs?7. Ideally, I would like the reports to update automatically when new data is added.&nbsp;I am familiar with Excel, but I am not familiar with Power BI or Power Query. Therefore, I would prefer an Excel-only solution, using tables, formulas, PivotTables/PivotCharts, etc., if possible.&nbsp;I would appreciate suggestions for the best base-data structure and reporting approach.&nbsp;

m_tarler · Answer

That is a lot but what i absolutely LOVE is that you were smart enough to ask how to set it up instead of doing something and then asking how to make it work using a setup that may be poorly thought through. That said, you may receive various opinions on the best set up so I will put forth mine and we'll see what others say...

I would have at least 2 input tables (I have include a table 'name' in the parentheses for reference later):

a) Donor Information (t_DonorInfo) - this would include all their basic information (some sort of donorID, name, contact info, etc...) and the donation committed and schedule type

b) Donation Log (t_Log) - this would have each donation received with date, donor, amount

that said you could break a) into a couple of tables:

a1) Donor Basic Info (t_Donors)

a2) Donor Commitments (t_Commitments) - in this case you could break this apart from the Donor Info table and include year, donor, commitment amount, commitment schedule. In this way you could make this track multiple years and track changes over the year in their committments

Then it is a matter of building the 'reports' but you now have it organized so you can FILTER the data accordingly. For example on a 'monthly' tab you could:

a) have a table: Monthly Donor List this could be a whole list like this:

=FILTER(t_DonorInfo, t_DonorInfo[schedule]="Monthly") or if you broke the table up then =CHOOSEROWS(t_Donors, XMATCH(FILTER(t_Commitments[ID], t_Commitments[schedule]="Monthly"), t_Donors[ID]))

or just the Donor IDs (which is what you really need for the next part) like this:

=FILTER(t_DonorInfo[ID], t_DonorInfo[schedule]="Monthly") or if you broke the table up then =FILTER(t_Commitments[ID], t_Commitments[schedule]="Monthly")

either way lets call the column of Monthly contributor's IDs = mDonors

b) you can then create a donation table for the 'Monthly' contributors by filtering the Donation Log:

=FILTER(t_Log, MMULT(--(t_Log[ID}=TRANSPOSE(mDonors)),SEQUENCE(ROWS(mDonors),,,0)))

Another option that might be easier is to add a column to the t_Log table that will automatically look up the commitment amount and schedule from the other tables:

=XLOOKUP([@ID], t_Commitments[ID], t_Commitments[Amount])

=XLOOKUP([@ID], t_Commitments[ID], t_Commitments[Schedule])

then just do a Pivot Table from this expanded t_Log table and filter by the corresponding schedule type

I hope this has been helpful and not just confusing. If I have time I will try to append a sample file..

Donor_List.xlsx32 KB

terio · Answer

FIrst of all, you must set the information as follow:
Donor
Committed amount
Date on which he committed to donate the sum
Effective amount
Donation data

The monthly, quarterly, half-yearly, and yearly you can retrieve from date, and/or group in a pivot table,
bye.

olufemi7 · Answer

Hello shavira9​,&nbsp;I would keep the commitments and actual donations in separate Excel Tables. This will make the reporting much easier and will also handle partial or missed payments correctly.For example:DonorsDonorIDDonorNameOther donor detailsCommitmentsCommitmentIDDonorIDStartDateEndDateFrequencyCommittedAmountFrequency could be Monthly, Quarterly, Half-Yearly or Yearly.DonationsDonationIDDonorIDDonationDateActualAmountFor example, if a donor commits ₦100,000 monthly but pays ₦70,000 in March, the commitment remains ₦100,000 and the actual donation is ₦70,000.You can then calculate:Difference = Actual - CommittedDifference % = (Actual - Committed) / CommittedI would also keep the donation date as an actual Excel date rather than creating separate columns for Jan, Feb, Mar, etc. A PivotTable can then group the dates by month, quarter and year.The main point to clarify before building the report is what the committed amount means for quarterly, half-yearly and yearly donors.For example, does ₦300,000 mean ₦300,000 per quarter, or ₦300,000 for the entire year?Once that is defined, the same source tables can support the monthly, quarterly, half-yearly and yearly summary tables and PivotCharts.Keeping the source data in Excel Tables will also make it easier to add new records and refresh the reports.

Forum Discussion

Advice needed: Comparing Committed vs Actual Donations in Excel

3 Replies