Most marketing budget allocations come from gut feel, historical ratios, or whoever made the strongest case in the last planning meeting. That’s not the worst way to operate — but it’s almost never optimal. If you know your cost per lead for each channel and have min/max spend constraints per channel, you can find the exact allocation that generates the most leads from a fixed budget.
This tutorial solves that problem with SolveSheet in Google Sheets.
Download the example spreadsheet to follow along.
The Problem
Brightfield Agency has a $50,000 monthly marketing budget across five channels: Google Ads, Facebook Ads, LinkedIn Ads, Email, and SEO/Content. Each channel has a known cost per lead based on historical performance, a minimum monthly commitment, and a ceiling beyond which incremental spend stops being effective. The goal is to allocate the $50,000 to maximize total leads generated.
| Channel | Cost / Lead | Min Spend | Max Spend |
|---|---|---|---|
| Google Ads | $12 | $5,000 | $25,000 |
| Facebook Ads | $18 | $3,000 | $20,000 |
| LinkedIn Ads | $35 | $2,000 | $15,000 |
| $5 | $1,000 | $8,000 | |
| SEO / Content | $22 | $2,000 | $12,000 |
Setting Up the Model
Decision variables — Five cells representing dollar spend on each channel.
Objective — Total leads = sum of (spend ÷ cost per lead) across all channels. Maximize this. The formula is straightforward: each channel contributes spend/CPL leads, and you sum them all.
Constraints
- Total spend ≤ $50,000
- Each channel spend ≥ its minimum
- Each channel spend ≤ its maximum
- All values ≥ 0
This is a pure linear program — the objective and all constraints are linear in the decision variables. The free tier of SolveSheet handles it.
Running SolveSheet
- Open Extensions → SolveSheet.
- Set the objective to your total leads cell and choose Max.
- Set variable cells to the five spend cells.
- Add the budget constraint, five minimum spend constraints, and five maximum spend constraints.
- Click Solve.
Reading the Results
The solver will push as much money as possible into the cheapest channels first (Email at $5/lead, then Google Ads at $12/lead) until they hit their caps, then allocate remaining budget to the next-cheapest options. The Answer Report confirms which channels hit their ceiling (binding at maximum) and which have slack.
The insight isn’t just the answer — it’s the structure. Channels hitting their max cap are worth investigating: if the cap is artificial (a rule of thumb rather than a real efficiency cliff), raising it could meaningfully increase total leads.
Scenarios Worth Running
- Increase the total budget to $60,000 and re-solve — where does the extra $10,000 go?
- Update one channel’s cost per lead to reflect a recent campaign result and re-solve.
- Remove the Email maximum cap and see how much it pulls from other channels.
Each re-solve takes one click. What would take an hour of spreadsheet math runs in under a second.
Get SolveSheet
This model runs on the free tier — up to 10 variables and 5 constraints. For larger channel mixes, Pro handles up to 200 variables.