Skip to content
Business, Operations & Fundraising·Lesson 36 of 49

Mini-Project 2: A Sponsor CRM in a Spreadsheet

Build a lightweight sponsor relationship tracker with stages, owners, follow-up dates, and a pipeline-value rollup so no prospect or renewal ever falls through the cracks.

Sign in to track progress, earn XP, and save lessons.

Sponsorship is a sales pipeline, and teams lose money every year because a warm prospect was forgotten between two meetings. You do not need Salesforce — a single well-structured sheet beats a shoebox of business cards.

Columns to build. Create a Sponsors tab with these headers: Organization, Contact name, Email, Phone, Stage, Tier, Amount, Probability, Weighted value, Owner, Last contact, Next action, Next action date, Notes.

Step 1 — Define stages. Use a data-validation dropdown on Stage with a real pipeline: Researching → Contacted → Meeting set → Proposal sent → Verbal yes → Committed → Declined. These mirror how money actually moves.

Step 2 — Weighted pipeline. Assign each stage a probability and compute expected value so you know what you can realistically count on. Map stages to probabilities with a lookup, then:

Weighted value (I2) = G2 * H2

where G2 is Amount and H2 is Probability (e.g., Proposal sent = 0.4, Verbal yes = 0.8, Committed = 1.0). Sum column I for a realistic forecast that you can drop straight into your budget's committed-vs-pending split.

Step 3 — Never-miss follow-ups. Conditional-format Next action date so anything due within 7 days turns yellow and anything overdue turns red:

Custom formula for red: =AND($M2<>"", $M2 < TODAY())

Then build a This Week view with a filter or =FILTER(Sponsors!A:M, Sponsors!M:M <= TODAY()+7).

Step 4 — Renewal automation. Returning sponsors are cheaper to keep than new ones to win. Add a Renewal due column and set it to the date you should re-approach last year's sponsors. A formula like =EDATE(LastGift, 11) flags them about a month before your next kickoff so the ask lands before budgets close.

Step 5 — Assign owners. Every row needs exactly one student or mentor in Owner. Diffuse responsibility is why prospects go cold. Sort by owner at your weekly business meeting and have each person report their two next actions.

This sheet is also your audit trail: when a sponsor asks "what did you do with our money," your Notes and Last contact history is the start of the stewardship report you will build in a later project.

Key takeaways

  • Treat sponsorship as a pipeline with explicit stages, not a list of names.
  • Weighted value (amount × probability) gives a realistic forecast you can feed straight into the budget.
  • Conditional formatting on next-action dates turns the sheet into a follow-up alarm so warm leads never go cold.
  • Tag every prospect with a single owner and review the pipeline weekly by owner.

Go deeper

Lesson quiz

Required

Answer all 3 questions correctly to complete this lesson.

1.In a sponsor CRM spreadsheet, why is it important to track a 'pledged amount' separately from a 'payment received' amount?

2.A core function of a CRM is making the 'next action' obvious. Which spreadsheet design best supports following up with sponsors at the right time?

3.When organizing sponsors into recognition tiers (e.g., by contribution level) in the CRM, what is the primary purpose of the tier field?

Answer every question to submit.