Marketing scenario analysis: spend, leads and revenue

Scenario analysis for marketing compares spend, leads and revenue under coherent sets of assumptions. Rebuild a historical campaign cohort first, then change the assumptions and check whether the resulting gross profit covers campaign spending. Higher revenue alone does not establish that a larger budget is worthwhile.

Start with one campaign cohort and one outcome

Choose the decision before opening the export: whether to hold spending, expand it, or wait for better conversion evidence. Define a campaign acquisition period and a consistent follow-up window for the customers it produces. A lead created near the end of a period needs the same opportunity to convert as one created near the start.

Bring a campaign-cost export, a deduplicated qualified-lead list, customer outcomes from the sales system, and the corresponding revenue and direct costs. Keep a campaign or cohort identifier through the files. Reconcile spending to the approved cost report and customers to the sales records before calculating the baseline.

Define conversion carefully. Google's conversion-rate definition divides conversions by tracked ad interactions. The worksheet here instead divides new customers by qualified leads. Those denominators answer different questions; a platform percentage cannot simply replace the worksheet's customer conversion assumption.

Use marketing funnel analysis to investigate the intermediate stages when a rate changes. This planning review needs a consistent chain and evidence for its assumptions, rather than a separate funnel redesign.

Rebuild the baseline from observed rows

Consider an illustrative US commercial-supplies company reviewing a fully matured acquisition cohort. Each acquired customer generated $3,000 of revenue during the agreed measurement window, and the matched sales have a 60% gross margin. These are example inputs, not industry benchmarks.

The historical export gives these rows. Currency values are exact USD; percentages are shown at the precision needed for the example.

For spreadsheet columns A through F matching that table, calculate aggregate cost per lead, customer conversion, revenue per customer and gross margin:

=SUM(B2:B3)/SUM(C2:C3)
=SUM(D2:D3)/SUM(C2:C3)
=SUM(E2:E3)/SUM(D2:D3)
=SUM(F2:F3)/SUM(E2:E3)

The results are $60 per lead, 5% customer conversion, $3,000 per customer and 60% gross margin. Gross profit after campaign spend is $36,000 minus $24,000, or $12,000. It excludes corporate overhead and other acquisition costs outside the campaign-cost file; label it accordingly.

The channel rows explain why averages need care. Email costs $12,000 / 300 = $40 per lead, while paid search costs $12,000 / 100 = $120. The aggregate $60 comes from total spend divided by total leads, rather than an average of channel costs. An expansion concentrated in the more expensive channel may change that aggregate.

For a broader acquisition-cost definition, use the owner page on customer acquisition cost. Keep the definition in this campaign decision fixed.

Build cases with linked assumptions

Keep the assumptions visible in columns A through F on a Cases worksheet. Set a proposed spending ceiling of $30,000, subject to management approval. Revenue per customer and gross margin remain unchanged across these cases so the spending and conversion assumptions are easy to inspect.

The adverse case assumes more expensive leads and weaker sales conversion. The expansion case assumes that reaching additional prospects increases acquisition cost while customer conversion holds. Neither relationship is guaranteed: the campaign owner must support it with comparable cohorts, available reach and sales follow-up capacity.

Add columns G through K for leads, customers, revenue, gross profit and gross profit after campaign spend. Enter these formulas in row 2 and fill down:

=B2/C2
=G2*D2
=H2*E2
=I2*F2
=J2-B2

This applies the same arithmetic to every case. Microsoft's introduction to What-If Analysis describes changing input values to examine their effect on formula results. The visible rows above make those changing inputs reviewable; the separate scenario analysis in Excel guide covers workbook controls in more depth.

Covirage is not affiliated with Google or Microsoft.

Read the results and prove the chain

The expansion spends $6,000 more than the current case but generates the same customers and revenue under its assumptions. Gross profit after spend therefore falls by $6,000. That is a reason to challenge the proposed expansion's assumptions and timing before authorizing the larger budget.

Prove each row backwards. Leads multiplied by cost per lead must return spending; customers divided by leads must return the conversion assumption; revenue multiplied by gross margin must return gross profit. The current case must also reproduce the historical totals. Do not sum the alternative cases: they describe mutually exclusive outcomes.

A useful boundary is the customer count required to cover campaign spending. Each customer contributes $3,000 × 60% = $1,800 before campaign spend. The current budget requires $24,000 / $1,800 = 13.33 customers, or at least 14 whole customers. At 400 leads, the conversion threshold is 3.33%, rounded to two decimal places. This covers the campaign spending only; use marketing ROI versus ROAS for the distinct return measures.

Where it goes wrong

• Unequal follow-up windows. Recent leads look weaker when they have had less time to buy. Compare mature cohorts or clearly label provisional outcomes.
• Conflicting denominators. Click conversion, qualified-lead conversion and opportunity win rate cannot share one assumption cell.
• Unlimited expansion at the current average cost. Additional reach may change channel mix, acquisition cost and sales workload. State the proposed mix explicitly.
• Revenue treated as spendable profit. Direct costs and other acquisition expenses still need funding. State the costs excluded from the case.
• Assumptions presented as observed results. Keep historical rows, proposed inputs and calculated outcomes visibly distinct, with owners for the inputs.

Check your campaign assumptions in Covirage

Upload a file or Google Sheet containing campaign costs, qualified leads, customer outcomes and matched revenue; Covirage maps the columns, and its tools compute and check the supplied figures. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, set up your business and bring a sample of your own data; account creation takes about a minute.

See revenue driver analysis for the supporting business context.