Beyond Last-Year-Plus-Optimism: A Solver-Based Model for Fundraising Budgets

Planning period: FY2027-FY2029
Model type: Mixed-integer linear program (MILP)
Source: Synthetic Dataset
Audience: New users of Excel Solver — no prior optimization experience assumed
Purpose: Demonstrate a fundraising allocation problem where Excel Solver adds clear value beyond manually ranking opportunities by return, and show exactly how to build, populate, and run the workbook that solves it.

This guide covers three things in order: what the model is and why it needs Solver (Sections 1-9), how to build the source data it runs on, from Raiser’s Edge NXT export through to a populated workbook (Sections 10-13), and how to actually use the workbook and run Solver yourself (Sections 14-17). If you only need to run an already-built workbook, start at Section 13.

Quick Glossary

Use these definitions before working through the model. Full definitions are in Section 19.

1. Stream: a fundraising strategy bucket (Renewal, Reactivation, Acquisition, High-Mass Upgrade).
2. Tier: a spend segment within a stream with its own capacity and return rate.
3. Tier capacity: the maximum spend allowed in that tier before it is exhausted.
4. Strategic value: same-year net revenue plus acquisition future value plus terminal donor value — the figure Solver maximizes.
5. Same-year net revenue: revenue minus cost recognized within the active fiscal year alone.
6. Acquisition future value: a planning value assigned to newly acquired donors for what they’re expected to give after the acquisition year.
7. Terminal donor value: a planning value assigned to the donor pool still active at the end of the three-year horizon.
8. Donor cohort rollforward: the mechanism that carries an acquisition year’s donors into later years at a retention rate, so acquisition isn’t judged on first-year return alone.
9. Run decision: the binary (0 or 1) variable for whether an appeal is selected in a given year.
10. Binding constraint: a constraint with no slack left — it is actively limiting the solution.
11. Solver Controls: the one sheet in this workbook where Solver’s live model is actually configured and run from. Every other sheet is either an input, a calculation, or documentation.

1. What This Model Does

The model chooses a three-year portfolio of direct-mail fundraising activities. It decides:

• which appeals to run;
• how much to invest in each fundraising stream and return tier;
• how to meet annual acquisition targets;
• how to protect current-year net revenue;
• how to remain within budget, contact-volume, and package-capacity limits; and
• how much value to place on donors who are acquired now and renew in later years.

The model is intentionally narrower than the complete fundraising ecosystem. Monthly giving, major gifts, corporate giving, events, digital activity, suppression, and other modules remain in the underlying dataset, but they are not decision variables in this direct-mail optimization problem.

The source donor and gift datasets were not regenerated for this model. A separate strategic planning extension supplies the appeal choices, capacities, assumptions, and constraints — Sections 10-12 show you how to build that extension yourself from raw data.

2. Why Solver Is Needed

A simple fundraising allocation model can often be solved by inspection: rank activities by return and invest in the highest-return activity until its capacity is exhausted. That approach is insufficient here because decisions interact.

For example:

• Selecting an appeal creates a fixed cost before any variable investment is made.
• An appeal must be selected before its stream capacity becomes available.
• Acquisition produces a first-year loss but creates donors with future renewal value.
• Spending more on acquisition can violate the same-year cash floor.
• Spending too little on acquisition can violate the new-donor target.
• Selecting fewer appeals saves fixed costs but can violate cadence requirements or remove needed capacity.
• The highest-return tiers have limited capacity.
• Year-End activity is mandatory even when another appeal appears more profitable.

Solver evaluates combinations of appeal selections and tier investments simultaneously. The best feasible portfolio is therefore not visible from any single return column.

3. Enabling Solver in Excel

Do this once, before opening the workbook for the first time.

Excel Desktop (Windows)

1. Open Excel.
2. Go to File > Options > Add-ins.
3. At the bottom, next to Manage, choose Excel Add-ins and click Go.
4. Check Solver Add-in.
5. Click OK.
6. Confirm Solver appears on the Data ribbon, usually in the Analysis group.

If Solver does not appear:

1. Repeat the steps above and verify Solver Add-in remains checked.
2. Restart Excel.
3. Confirm the workbook is not in Protected View (if it opened from an email attachment or a downloads folder, click Enable Editing first).

Excel Desktop (Mac)

1. Open Excel.
2. Go to Tools > Excel Add-ins.
3. Check Solver Add-in.
4. Click OK.
5. Confirm Solver appears on the Data tab.

Excel for Microsoft 365 and Excel for the Web

If you are using the desktop Microsoft 365 app, use the Windows or Mac steps above — behavior is identical.

If you are using Excel on the web:

1. Go to Insert > Add-ins, search for Solver, and add it. It opens as a docked side panel rather than the classic dialog box, and its layout looks different.
2. Solver functionality and add-in availability can vary by tenant policy and environment — some organizations restrict which add-ins can be installed.

Recommended practice for this model: run Solver in desktop Excel (Windows or Mac) for full compatibility with the range constraints and Simplex LP settings this workbook uses. Verify in Excel for the web before relying on it for a live presentation.

4. Planning Horizon and Decisions

The model covers three fiscal years: 2027, 2028, and 2029.

4.1 Continuous decision variables

The Investment Tiers sheet contains 48 continuous spend decisions:

3 years x 4 fundraising streams x 4 return tiers = 48 decisions

Each decision represents the dollars allocated to one stream and tier in one year.

The four Solver streams are:

1. Renewal_Direct_Mail
2. Reactivation_Direct_Mail
3. Affiliate_Acquisition_Direct_Mail
4. High_Mass_Upgrade_Direct_Mail

4.2 Binary decision variables

The Appeal Selection sheet contains 18 binary decisions:

3 years x 6 appeal options = 18 decisions

A binary value must equal either:

• 1: run the appeal; or
• 0: do not run the appeal.

The six annual appeal options are:

• Spring Integrated
• Summer Integrated
• Fall Integrated
• Year-End Integrated
• Acquisition Test
• Lapsed Donor Blitz

4.3 Total decision variables

48 continuous spend decisions + 18 binary appeal decisions = 66 decisions

5. Objective Function

The objective is to maximize three-year strategic value.

Three-Year Strategic Value
    = cumulative same-year net revenue
    + acquisition future value
    + terminal donor value

The objective is calculated on Objective Bridge!G8 and is read into Solver through the workbook-level named range Solver_Objective.

5.1 Same-year net revenue

For each investment tier:

Same-Year Revenue
    = Tier Spend x Same-Year Return per Dollar

Variable Net Revenue
    = Same-Year Revenue - Tier Spend

At the annual level:

Same-Year Net Revenue
    = sum of variable net revenue
    - fixed costs of selected appeals

5.2 Acquisition future value

Expected new donors are calculated linearly:

Expected Contacts
    = Acquisition Spend / Unit Contact Cost

Expected New Donors
    = Expected Contacts x Acquisition Response Rate

Acquisition Future Value
    = Expected New Donors x Future Value per New Donor

The current planning value is CAD 42 per newly acquired donor. This value is an auditable teaching assumption, not a claim that every acquired donor has identical lifetime value — Section 11 shows how to replace it with your own figure.

5.3 Terminal donor value

The model assigns value to the managed active donor pool remaining at the end of FY2029:

Terminal Donor Value
    = FY2029 Managed Active Donors x Terminal Value per Donor

The current terminal value assumption is CAD 95 per donor.

Terminal value prevents the model from treating donors remaining after FY2029 as worthless merely because their later gifts occur outside the planning horizon.

6. Diminishing-Return Tiers

Each stream has four investment tiers. Tier 1 has the strongest expected marginal return; later tiers have lower returns.

Tier 1 return >= Tier 2 return >= Tier 3 return >= Tier 4 return

The tier structure approximates diminishing returns caused by:

• reaching less-responsive donor segments;
• increasing contact frequency;
• using lower-quality acquisition lists;
• exhausting the strongest upgrade candidates; and
• expanding beyond the most efficient package volumes.

Each tier also has a maximum capacity. Solver cannot allocate more than the documented tier capacity.

7. Appeal Activation and Fixed Costs

Appeal selection is separate from tier investment.

Selecting an appeal:

1. incurs its fixed creative, production, and operational cost; and
2. unlocks stream capacity associated with that appeal.

For an appeal with binary decision Run:

Selected Fixed Cost = Run x Appeal Fixed Cost

Selected Stream Capacity = Run x Available Stream Capacity

If Run = 0, the appeal contributes no capacity and no fixed cost. This linking logic is what makes the appeal decisions economically meaningful, and it’s the specific mechanism the Appeal Selection sheet’s Selected Fixed Cost, Selected Capacity, and the four Selected <Stream> columns implement — see Section 13.3.

8. Donor Cohort Rollforward

The Donor Pool Rollforward sheet connects acquisition decisions across years.

The current cohort assumptions are:

• first-year donor retention: 28%;
• subsequent repeat retention: 60%.

For example:

FY2028 retained FY2027 acquisition donors
    = FY2027 acquired donors x 28%

FY2029 retained FY2027 acquisition donors
    = FY2027 acquired donors x 28% x 60%

This structure means acquisition in FY2027 can contribute value in FY2028 and FY2029. Acquisition is therefore not judged solely on its first-year net revenue.

The rollforward is a planning representation of managed direct-mail donor cohorts. It is not intended to replace the complete organization-wide donor lifecycle tables in v12.

9. Constraints

The workbook contains 33 explicit constraint rows, shown on Constraint Matrix: 11 constraints for each of three years.

9.1 Annual budget

Tier Spend + Selected Appeal Fixed Costs <= Annual Budget

Current annual budgets are:

9.2 Minimum appeal cadence

Selected Appeals >= 4 per year

This prevents Solver from collapsing the program into one or two highly profitable mailings.

9.3 Mandatory Year-End appeal

Year-End Integrated Run Decision = 1

Year-End is treated as an organizational requirement rather than an optional package.

9.4 Minimum new donors

Expected New Donors >= Annual New-Donor Target

This constraint protects donor-pool replenishment even when acquisition is unattractive on first-year return.

9.5 Direct-mail volume

Expected Direct-Mail Contacts <= Annual Mail-Volume Capacity

The capacity represents production, operational, and donor-fatigue limits.

9.6 Same-year cash floor

Same-Year Net Revenue >= Minimum Same-Year Net Revenue

The cash floor prevents the strategic objective from over-investing in acquisition at the expense of current operations.

9.7 Stream-capacity links

For each stream:

Annual Stream Spend
    <= Stream Capacity Unlocked by Selected Appeals

Separate constraints exist for Renewal, Reactivation, Acquisition, and High-Mass Upgrade.

9.8 Acquisition appeal cadence

Selected Appeals with Acquisition Capacity >= 3 per year

This ensures acquisition is integrated across the annual calendar instead of being represented by one isolated mailing.

9.9 Tier capacity and non-negativity

For every spend decision:

0 <= Tier Spend <= Tier Capacity

9.10 Binary restrictions

For every appeal decision:

Run Decision is binary

10. What Data to Export from Raiser’s Edge NXT

Everything in Sections 4-9 depends on numbers that ultimately come from real gift and constituent history. This section and the next two show how to get from a Raiser’s Edge NXT export to a populated workbook.

To go from source system to optimization tiers, export enough data to reconstruct donor-cohort transitions and stream-level economics.

10.1 Gift-level export fields

1. Constituent ID
2. Gift Date
3. Gift Amount
4. Campaign
5. Appeal — map each historical appeal code to one of the six model appeals (Spring Integrated, Summer Integrated, Fall Integrated, Year-End Integrated, Acquisition Test, Lapsed Donor Blitz), or to “Other” if it predates this appeal structure
6. Package or Source Code (if available)
7. Gift Type
8. Payment Method

Recommended additional operational fields:

1. Gift ID (or transaction ID) for deduplication.
2. Appeal Channel (if not encoded in campaign or source code) — needed to assign a stream (see 10.3).
3. Revenue recognition date if different from gift date (i.e. Gift GL Post Date).

10.2 Constituent-level export fields

1. Constituent ID
2. First Gift Date
3. Last Gift Date
4. Lifetime Giving
5. Largest Gift
6. Segment or status fields used in your organization
7. Opt-in and contactability fields by channel

Minimum contactability fields to include explicitly:

1. Email opt-in or do-not-email.
2. Do-not-mail.
3. Do-not-call.
4. No-solicit.
5. Deceased.

10.3 Mapping gifts to the model’s four streams

Every gift needs to be assigned to exactly one of the four Solver streams before it can contribute to Historical Calibration. Use campaign, appeal, and package/source code together:

Document your exact mapping rule (which campaign/appeal/source-code combinations map to which stream) in a lookup table you keep with the source files — you will need to re-apply the same rule every planning cycle.

10.4 Time horizon

1. Preferred: 3 to 5 fiscal years.
2. Minimum: 1 full fiscal year plus current-year YTD.

More years give you more stable response-rate and return-per-dollar estimates for Historical Calibration, and let you compute an empirical first-year and repeat retention rate to check against the model’s 28% / 60% assumptions.

11. Building the Source Files: From Raw Gifts to Historical Calibration

This section turns the raw export from Section 10 into the inputs Historical Calibration and Investment Tiers need. Work through it once per planning cycle.

11.1 Step 1: Normalize raw records

1. Assign fiscal year consistently. If your fiscal year starts in July: =IF(MONTH(GiftDate)>=7,YEAR(GiftDate)+1,YEAR(GiftDate)).
2. Create a normalized donor key, for example DonorKey = UPPER(TRIM(ConstituentID)).
3. Create a unique transaction key, for example TxnKey = DonorKey + GiftID + GiftDate.
4. Remove exact duplicates by TxnKey.
5. Flag exclusions with an ExcludeFlag field and an explicit reason (INVALID_TYPE, SOFT_CREDIT, ANONYMOUS_UNMATCHED, REVERSAL). Keep excluded rows in an audit table; do not hard-delete without a trace.

Typical invalid gift types for annual-fund optimization (confirm with your finance policy):

1. Pledges not yet paid.
2. In-kind gifts.
3. Bequests.
4. Internal transfers.
5. Write-offs or reversals.

QA checks before continuing:

1. No null DonorKey values.
2. No future-dated gifts unless explicitly allowed.
3. Sum of included + excluded gross equals raw gross total.

11.2 Step 2: Build a donor-cohort table

This model needs less donor-state detail than a full lifecycle model — you’re tracking two things: whether a donor is new this year, and whether prior acquisition cohorts are still giving.

Build one row per donor per fiscal year with:

1. DonorKey, FiscalYear, Stream (from the mapping in 10.3)
2. IsNewThisYear — 1 if this is the donor’s first-ever included gift, else 0
3. AcquisitionYear — the fiscal year IsNewThisYear was last 1 for this donor (blank until then)
4. RespondedFlag — 1 if the donor gave (any stream) in this fiscal year, else 0

Execution checklist:

1. Aggregate gifts to one row per donor per fiscal year.
2. Set IsNewThisYear = 1 where this fiscal year is the donor’s earliest included gift year, else 0.
3. Carry AcquisitionYear forward for every subsequent row belonging to that donor.
4. Set RespondedFlag = 1 if the donor has any included gift that fiscal year.

Once this table exists, compute empirical retention rates to check against the model’s assumptions:

Empirical First-Year Retention
    = donors with AcquisitionYear = Y-1 and RespondedFlag = 1 in year Y
    / donors with AcquisitionYear = Y-1

Empirical Repeat Retention
    = donors retained in year Y (per above) who also have RespondedFlag = 1 in year Y+1
    / donors retained in year Y

Compare both to the model’s 28% and 60% before accepting them into Scenario Inputs (Section 12.1).

QA checks:

1. Exactly one row per (DonorKey, FiscalYear).
2. IsNewThisYear = 1 occurs at most once per donor across all years.
3. RespondedFlag = 1 always implies the donor has an included gift that year.

11.3 Step 3: Build the stream-tier performance summary

For each (Stream, FiscalYear), and then for each tier within a stream, calculate:

1. contacts,
2. responses,
3. response rate,
4. average response value,
5. gross revenue,
6. cost,
7. gross return per dollar.

Execution checklist:

1. Create one summary row per (FiscalYear, Stream) first, at the whole-stream level.
2. Compute Contacts from campaign activity or mail-file counts for that stream-year.
3. Compute Responses as distinct donors with a responded gift in that stream-year.
4. Compute ResponseRate = Responses / Contacts.
5. Compute AverageResponseValue = GrossRevenue / Responses.
6. Compute GrossReturnPerDollar = GrossRevenue / Cost.
7. Create 3-year trailing averages for each KPI (average of the prior three completed fiscal years for that same stream and KPI; do not include the current year in its own average; if fewer than 3 prior years exist, use what’s available and flag it as low-confidence).
8. Flag outliers where a stream-year KPI deviates from its trailing average beyond a tolerance you set in advance (for example plus or minus 20% relative difference; use an absolute threshold instead of a percentage where the denominator is near zero).

Recommended minimum output columns: FiscalYear, Stream, Contacts, Responses, ResponseRate, GrossRevenue, Cost, GrossReturnPerDollar, DataQualityFlag (suggested values: OK, LOW_VOLUME, MISSING_COST, OUTLIER_KPI, INCOMPLETE_YEAR, MANUAL_REVIEW).

11.4 Step 4: Split each stream into four tiers

1. Within each stream, define the segmentation units you’ll rank (audience bands, channel cohorts, recency bands, or list-quality groups).
2. Compute each unit’s historical GrossReturnPerDollar.
3. Sort units from highest to lowest productivity within the stream.
4. Assign units sequentially into Tier 1 through Tier 4 so each tier remains operationally executable (a tier should correspond to something you could actually mail as one segment).
5. Sum each tier’s reachable spend into TierCapacity — this becomes rounded_tier_capacity in Historical Calibration, and from there Tier Capacity in Investment Tiers.
6. Estimate the tier’s response rate and unit contact cost from weighted historical outcomes (weight each unit by its historical cost or contact volume, so Tier Estimate = SUM(UnitMetric x UnitWeight) / SUM(UnitWeight)) — these become anchor_response_rate and anchor_unit_contact_cost.
7. Estimate same_year_return_per_dollar the same way.
8. For the acquisition stream only, estimate future_value_per_new_donor (see 11.6).
9. Validate monotonicity: Tier 1 >= Tier 2 >= Tier 3 >= Tier 4 on return per dollar, within every stream.

If monotonicity fails: recheck segment boundaries, pool unstable low-volume segments, apply conservative smoothing, and document any manual override (who approved it, when, and why) in a companion audit table.

Ranking metric guidance, in priority order: GrossReturnPerDollar first; then response-rate stability; then larger reachable capacity; then lower year-to-year volatility, as tie-breakers.

11.5 Step 5: Populate Historical Calibration

Historical Calibration has 16 rows — four streams by four tiers — and these exact columns:

This sheet is a lookup table only — nothing on it is a formula. Everything downstream reads from it.

11.6 Estimating the two future-value assumptions

future_value_per_new_donor (currently CAD 42) and the terminal value per donor (currently CAD 95, entered on Scenario Inputs, not here) are the two hardest inputs to source directly from a gift export, because they describe value that hasn’t happened yet. Reasonable approaches:

1. Cohort-based: using the donor-cohort table from Section 11.2, take a past acquisition cohort with at least 2-3 years of history, and compute actual average giving per acquired donor in the years after acquisition. Use that as your future-value estimate.
2. Retention x average gift: multiply your empirical repeat retention rate (Section 11.2) by an average repeat-gift amount for that stream.
3. Document which method you used, the cohort or years it’s based on, and the date — this assumption needs revisiting more often than the response-rate and cost figures, since it’s inferred rather than directly observed.

12. From Historical Calibration to a Live Workbook: Populating Every Input Sheet

With Historical Calibration complete, populate the workbook’s other input sheets in this order.

12.1 Scenario Inputs

One row per fiscal year, ten columns, all manually entered:

12.2 Appeal Selection

One row per year per appeal (18 rows). For each, enter:

• Fixed Cost — historical or quoted cost to produce and mail that appeal
• Required Flag — 1 only for Year-End Integrated, 0 for every other appeal
• Renewal Capacity, Reactivation Capacity, Acquisition Capacity, Upgrade Capacity — the stream capacity that appeal makes available if selected, from your campaign planning data (an appeal can carry capacity in more than one stream)

Leave Run Decision at its seeded value (see Section 16) — this is a Solver changing cell, not something you hand-populate from data.

12.3 Investment Tiers

One row per year per stream per tier (48 rows). Unit Cost, Response Rate, and Same-Year Return / $ should be formulas that look up the matching row in Historical Calibration by stream and tier — not retyped values — so that recalibrating Historical Calibration next cycle automatically updates every year’s tiers. Tier Capacity should look up rounded_tier_capacity the same way.

Leave Spend Decision at its seeded value — this is the other Solver changing-cell block.

12.4 Donor Pool Rollforward

This sheet is almost entirely formulas reaching into Investment Tiers and Scenario Inputs (see the in-sheet layout note for the exact formulas). The one thing to verify manually is the opening figures for the very first modeled year — if FY2027 needs to reflect donors already retained from acquisition before the model’s horizon starts, add that as an explicit opening balance rather than assuming zero.

13. Workbook Structure: The Eleven Sheets and How They Connect

Every sheet in this workbook also has an extended note directly under its data table explaining what each column means and where its values come from — read that note before editing anything on a sheet you haven’t worked with before.

13.1 Why Solver Setup and Solver Controls are different sheets

This is the single most important structural fact in this workbook, and it exists because of a real, previously-encountered failure mode: when a workbook’s Solver-changing cells live on one sheet but the model is opened or solved from a different sheet, Excel can silently misbehave — writing results to the wrong place, or loading an incomplete model.

The fix used in this workbook is to put every single cell Solver touches — all 48 spend decisions, all 18 binary decisions, all 33 constraint slacks, and the objective — onto one dedicated sheet, Solver Controls, with nothing cross-referencing another sheet inside the live model itself. Solver Setup exists purely to document, in plain language, what that model is; it is not wired to Solver and editing it has no effect on what Solver does.

Practical consequence: always navigate to the Solver Controls tab before opening Data > Solver. A few other sheets in this workbook (Executive Summary, Solver Setup, and Constraint Matrix) still carry incomplete, leftover Solver sessions from earlier drafts of this file. If you ever open Solver while one of those three is active and the Objective, Variables, or Constraints fields look sparse, unfamiliar, or wrong, close the panel, click the Solver Controls tab, and reopen it.

13.2 Solver Controls layout

Solver Controls has two variable blocks side by side, sharing row numbers for convenience only — they are otherwise unrelated:

13.3 How the sheets connect (a worked trace)

To see the whole model work end to end, trace one dollar of acquisition spend:

1. You (or Solver) set a value in Investment Tiers!D (a Spend Decision cell) for the Acquisition stream — mirrored live to Solver Controls!B.
2. Investment Tiers divides that spend by Unit Cost to get Expected Contacts, multiplies by Response Rate to get Expected Donors, and computes Same-Year Revenue and Variable Net.
3. Because this row’s stream is the acquisition stream, Investment Tiers also computes Future Donor Value from Expected Donors.
4. Donor Pool Rollforward sums that year’s acquisition-stream Expected Donors into New Donors, and carries a retained share of it into future years via the 28% / 60% rates.
5. Objective Bridge pulls Variable Net (summed across all tiers) and Acquisition Future Value and Terminal Donor Value (from Donor Pool Rollforward) into that year’s Strategic Value.
6. Constraint Matrix separately checks that this same spend decision hasn’t broken the budget, the stream-capacity link, or the mail-volume cap.
7. Executive Summary and Solver Controls both display the final, rolled-up figures — the first for a human reader, the second for Solver.

14. Using the Workbook: A First-Time Walkthrough

If you’ve never opened this workbook before, work through the sheets in this order rather than jumping straight to Solver Controls:

1. Executive Summary — orients you to what the model does and what the seeded plan currently produces.
2. Scenario Inputs — shows you every policy lever in one place.
3. Historical Calibration — shows you where the tier economics come from.
4. Investment Tiers and Appeal Selection — the two sheets holding the actual decision variables.
5. Donor Pool Rollforward and Objective Bridge — shows you how spend becomes the objective.
6. Constraint Matrix — shows you every rule the model must obey.
7. Naive vs Solver — read this before running Solver, so you have a mental benchmark for why the optimized answer looks the way it does.
8. Solver Setup, then Solver Controls — read the documentation sheet first, then go to the sheet where you’ll actually work.

Every sheet’s under-the-table note is written to stand alone — you do not need this guide open to understand any single sheet once you’re inside the workbook.

15. Step-by-Step: Configuring and Running Solver on Solver Controls

15.1 Confirm the model before touching anything

1. Click the Solver Controls tab so it is the active sheet.
2. Open Solver from the Data tab (or the Solver task pane, if your Excel version uses one).
3. Confirm the panel shows:

If any of this is blank, sparse, or unfamiliar, you are not looking at the right model — see Section 13.1.

15.2 The six constraints

These are already saved with the workbook; use this table to verify them, not to re-enter them from scratch:

Also confirm Make Unconstrained Variables Non-Negative is checked, and the solving method is Simplex LP — not GRG Nonlinear (this model is entirely linear, and Simplex LP is both faster and exact for it).

15.3 Run and retain the result

1. Note the seeded objective value shown in Solver Controls!H5 before solving.
2. Click Solve.
3. Read Solver’s status message — it should report that it found a solution satisfying all constraints.
4. Check that every row on Constraint Matrix still reads PASS.
5. Compare the new objective value to the seeded value.
6. If you need to keep the original seeded file reusable for teaching, save the solved version under a new file name before closing.

16. Seeded Plan Versus Solver Solution

The workbook opens with a feasible seeded plan. It is designed to:

• use the annual budgets;
• select four appeals each year;
• include Year-End;
• meet acquisition targets;
• remain below mail-volume limits;
• satisfy the cash floors; and
• respect tier and selected-appeal capacities.

The seeded plan is not represented as a proven optimum. Its purpose is to provide a valid starting point and make the workbook immediately inspectable — including by you, before you’ve run Solver even once.

Solver’s task is to improve the objective by changing appeal selections and tier allocations while maintaining every constraint.

17. Interpreting Solver Results

Do not judge the solution only by total revenue. Review:

• which appeals were selected;
• how much fixed cost was incurred;
• whether budgets and capacities are binding;
• the amount invested in acquisition;
• expected new donors;
• current-year net revenue;
• acquisition cohorts retained in later years;
• terminal donor value; and
• the slack remaining in each constraint.

A constraint with zero or very small slack is binding. Binding constraints explain why Solver cannot improve the objective by simply moving more money into the apparent highest-return activity.

Examples include:

• a fully used annual budget;
• exactly four selected appeals;
• a fully used upgrade tier;
• acquisition near its minimum donor target;
• mail volume near its annual maximum; or
• renewal spend equal to the capacity unlocked by selected appeals.

18. End-to-End Operating Procedure

Use this as the complete runbook each planning cycle.

Phase A: Data extraction

1. Export gift-level and constituent-level fields from Raiser’s Edge NXT (Section 10).
2. Store snapshots with extraction date and fiscal-year scope.

Phase B: Data preparation

1. Standardize IDs and fiscal-year assignment (Section 11.1).
2. Build the donor-cohort table and compute empirical retention rates (Section 11.2).
3. Build the stream-tier performance summary (Section 11.3).

Phase C: Tier calibration

1. Rank historical segments within each stream by productivity and assign four tiers (Section 11.4).
2. Estimate capacities and per-tier economics (Section 11.4).
3. Populate Historical Calibration (Section 11.5).
4. Validate that each stream remains diminishing by tier.

Phase D: Workbook refresh

1. Update Scenario Inputs, Appeal Selection, and Investment Tiers (Section 12).
2. Confirm dependency and cadence constraints still match current strategy.
3. Record assumption version metadata (date, owner, and source extracts) in Scenario Inputs’ Planning Basis column.

Phase E: Optimization and governance

1. Navigate to Solver Controls and confirm the model (Section 15.1).
2. Run Solver (Section 15.3).
3. Check that every Constraint Matrix row is PASS.
4. Review results with leadership (Section 17) and document the decision.

Phase F: Execution and monitoring

1. Translate stream allocations and selected appeals into campaign plans.
2. Track in-year actuals against modeled assumptions.
3. Recalibrate tiers, retention rates, and future/terminal values at the next cycle.

Recommended ownership and cadence:

1. Prospect research or analytics lead owns stream mapping and segment/tier calibration.
2. Database administrator owns extraction, field mapping, and data QA controls.
3. Fundraising operations owns Solver runs and scenario/decision documentation.
4. Monthly variance review, quarterly assumption check, annual full recalibration.

19. Terminology

Appeal A scheduled fundraising activity such as Spring, Fall, or Year-End. An appeal can support more than one fundraising stream.

Binary variable A decision variable restricted to 0 or 1. Here it represents whether an appeal is run.

Binding constraint A constraint with no remaining slack. It actively limits the solution.

Capacity The maximum amount of spend or contact volume that can reasonably be assigned to a tier, stream, or selected appeal.

Constraint A rule that every feasible solution must satisfy, such as a budget ceiling or minimum new-donor target.

Continuous variable A decision variable that can take any permitted numeric value. Here it represents dollars allocated to a stream and tier.

Decision variable A cell Solver is allowed to change.

Diminishing returns The principle that later increments of investment generally produce lower marginal returns as the strongest opportunities are exhausted.

Donor cohort The group of donors acquired in a specific fiscal year, tracked together for retention purposes.

Feasible solution A combination of decisions that satisfies all constraints. A feasible solution is not necessarily optimal.

Fixed cost A cost incurred when an appeal is activated, regardless of the amount invested in variable mailing activity.

Future value per new donor A planning value assigned to expected giving after the acquisition year.

Integer programming Optimization in which some decision variables must be whole numbers. Binary programming is the special case where values are restricted to 0 and 1.

Linear program An optimization model whose objective and constraints are linear functions of the decision variables.

Marginal return per dollar The expected revenue generated by the next dollar invested in a specific tier.

Mixed-integer linear program (MILP) A linear model containing both continuous and integer or binary decision variables.

Named range A human-readable label (such as Solver_Objective) that points to a specific cell or range, used so a Solver dialog or formula can reference “the objective” instead of a raw cell address.

Objective function The formula Solver maximizes or minimizes. This model maximizes three-year strategic value.

Optimal solution The best feasible solution identified for the specified objective and constraints.

Response rate Expected responses divided by expected contacts.

Run decision The binary appeal-selection variable.

Slack The unused room in a constraint. For a budget ceiling, slack is unspent allowable budget. For a minimum target, slack is performance above the target.

Strategic value Same-year net revenue plus modeled future donor value and terminal donor value.

Stream A fundraising treatment with distinct economics: Renewal, Reactivation, Acquisition, or High-Mass Upgrade.

Terminal value The estimated value of donors remaining at the end of the explicit planning horizon.

Tier One portion of a stream’s investment curve, with its own capacity and marginal return.

20. Assumptions and Limitations

This is a synthetic teaching model. Its results should not be treated as an organizational forecast without replacing assumptions with approved historical evidence.

Important limitations include:

• response and return coefficients are expected values rather than guaranteed outcomes;
• season-specific capacities are teaching assumptions;
• future value per donor is simplified;
• retention is represented through cohort rates rather than individual donor probabilities;
• uncertainty and risk are not modeled probabilistically;
• staff capacity is represented indirectly through appeal and mail-volume limits;
• the model optimizes direct-mail annual giving, not the complete fundraising portfolio; and
• the seeded plan is feasible but is not claimed to be Solver’s optimum.

For organizational use, calibrate budgets, fixed costs, capacities, response rates, average gifts, retention rates, and terminal value from approved campaign and donor history using the process in Sections 10-12.

21. What the Demonstration Should Show

A useful presentation compares three approaches:

1. Naive return ranking: invests first in the highest immediate-return tiers and tends to neglect acquisition.
2. Same-year optimization: protects cash and may fund only the minimum acquisition required.
3. Three-year strategic optimization: balances appeal fixed costs, current net revenue, capacity, new-donor targets, and later-year donor value.

The teaching point is not that Solver replaces fundraising judgment. Solver makes the implications of that judgment explicit and finds the strongest allocation that satisfies all documented policies and operational limits.