Challenge Scenario
In paid digital media (Google, Meta, TikTok), performance marketers optimize budgets based on two core efficiency metrics: Return on Ad Spend (ROAS) (Revenue / Spend) and Cost Per Acquisition (CPA) (Spend / Purchases). Campaigns generating 3.0x ROAS or higher are classified as "SCALABLE", while lower-performing campaigns require optimization ("OPTIMIZE").
As a Performance Marketing Lead, you are auditing four active campaigns:
- ROAS Multiplier (Column E): Divide attributed revenue by ad spend:
=D2/B2.
- Cost Per Acquisition CPA (Column F): Divide ad spend by purchases:
=B2/C2.
- Campaign Tag (Column G): If ROAS is 3.0 or higher (
E2 >= 3.0), assign "SCALABLE"; otherwise assign "OPTIMIZE".
- Total Ad Spend (Cell B7): Sum total media budget using
SUM(B2:B5).
- Total Attributed Revenue (Cell D7): Sum total sales revenue generated using
SUM(D2:D5).
Your Sheet Layout
Understanding the digital ad performance table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D5 |
Campaign, Spend, Purchases, Revenue |
Ad Performance Data |
Media investments and attributed conversions |
Read-only advertising data. |
E2:E5 |
ROAS Multiplier |
Efficiency Ratio |
=D2/B2 |
Calculates dollars earned per $1.00 of ad spend. |
F2:F5 |
CPA ($) |
Acquisition Cost |
=B2/C2 |
Calculates marketing cost per customer purchase. |
G2:G5 |
Campaign Tag |
Optimization Tag |
=IF(E2>=3, "SCALABLE", "OPTIMIZE") |
Flags SCALABLE vs OPTIMIZE ad groups. |
B7 |
Total Ad Spend |
Summary |
=SUM(B2:B5) |
Total media expenditure ($13,000.00). |
D7 |
Total Sales Revenue |
Summary |
=SUM(D2:D5) |
Total generated sales revenue ($44,700.00). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells E2:E5, calculate ROAS multiplier (= Revenue / Ad Spend).
2
In cells F2:F5, calculate CPA (= Ad Spend / Purchases).
3
In cells G2:G5, assign tag ("SCALABLE" if ROAS >= 3.0, otherwise "OPTIMIZE").
4
In cell B7, sum total ad spend, and in D7 sum total revenue.
Solution & Formula Breakdown
Let's review the paid media efficiency calculations:
1. Return on Ad Spend (ROAS) Calculation (E2:E5)
=D2/B2
- Google Search: $14,000 revenue / $4,000 spend = 3.50x ROAS.
- Meta Lookalike: $6,000 revenue / $2,500 spend = 2.40x ROAS.
- TikTok Video: $22,000 revenue / $5,000 spend = 4.40x ROAS.
- Display Retargeting: $2,700 revenue / $1,500 spend = 1.80x ROAS.
2. Cost Per Acquisition (CPA) Calculation (F2:F5)
=B2/C2
- Google Search: $4,000 spend / 120 orders = $33.33 CPA.
- Meta Lookalike: $2,500 spend / 50 orders = $50.00 CPA.
- TikTok Video: $5,000 spend / 200 orders = $25.00 CPA.
- Display Retargeting: $1,500 spend / 30 orders = $50.00 CPA.
3. Campaign Scaling Status (G2:G5)
=IF(E2>=3, "SCALABLE", "OPTIMIZE")
- Google Search (3.5x): >= 3.0 → SCALABLE.
- Meta Lookalike (2.4x): < 3.0 → OPTIMIZE.
- TikTok Video (4.4x): >= 3.0 → SCALABLE.
- Display Retargeting (1.8x): < 3.0 → OPTIMIZE.
4. Overall Portfolio Totals (B7 & D7)
- Cell B7 (Total Spend):
=SUM(B2:B5) → $13,000.00.
- Cell D7 (Total Revenue):
=SUM(D2:D5) → $44,700.00 (Blended ROAS = 3.44x).
A ROAS of 3.5 means you earned $3.50 in top-line revenue for every $1.00 invested in advertising!