Advice needed: Comparing Committed vs Actual Donations in Excel

Hello shavira9​, 

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:

1. Donors
   DonorID
   DonorName
   Other donor details
2. Commitments
   CommitmentID
   DonorID
   StartDate
   EndDate
   Frequency
   CommittedAmount

Frequency could be Monthly, Quarterly, Half-Yearly or Yearly.

1. Donations
   DonationID
   DonorID
   DonationDate
   ActualAmount

For 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 - Committed

Difference % = (Actual - Committed) / Committed

I 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.