Exercise Overview

Time Required: 45 minutes

Format: Individual or pairs

Deliverable: A channel budget allocation model with ROI projections

Objective

Build a quantitative model to allocate marketing budget across channels based on expected performance, historical data, and strategic priorities. You'll learn to balance efficiency, reach, quality, and experimentation in your channel mix.

Scenario

You're the VP of Marketing at a B2B SaaS company with the following context:

Company Profile:

• Product: Project management software for marketing teams
• ACV: $12,000
• Target: Mid-market (100-1000 employees)
• Sales cycle: 45 days average
• LTV: $36,000 (3-year average customer lifetime)

Current Performance (Last Quarter):

• Total marketing budget: $100,000
• Leads generated: 800
• MQLs: 200
• Opportunities: 40
• Closed deals: 12
• Overall CAC: $8,333

Your Challenge: Plan next quarter's $150,000 budget to generate 15+ deals while improving efficiency.

Instructions

Part 1: Historical Channel Analysis (15 minutes)

Review last quarter's performance by channel and calculate key metrics:

Channel	Spend	Leads	Lead%	CPL	MQLs	MQL Rate	Opps	Opp Rate	Deals	Close Rate	CAC
Paid Search	$25,000	250	31%	$100	65	26%	13	20%	4	31%	$6,250
LinkedIn	$30,000	150	19%	$200	52	35%	13	25%	4	31%	$7,500
Email/Nurture	$5,000	200	25%	$25	50	25%	10	20%	2	20%	$2,500
Display	$15,000	100	13%	$150	10	10%	2	20%	1	50%	$15,000
Events/Webinars	$20,000	100	13%	$200	23	23%	7	30%	3	43%	$6,667
Content Synd	$5,000	-	-	-	-	-	-	-	-	-	-

Calculate for each channel:

1. CPL (Cost Per Lead) = Spend / Leads
2. MQL Rate = MQLs / Leads
3. Opportunity Rate = Opportunities / MQLs
4. Close Rate = Deals / Opportunities
5. CAC (Customer Acquisition Cost) = Spend / Deals

Channel Insights

Answer these questions based on the data:

Best CPL: _

Best MQL Rate: _

Best Close Rate: _

Lowest CAC: _

Which channel would you:

• Scale up? _ (Why?)
• Optimize? _ (Why?)
• Cut or reduce? _ (Why?)

Part 2: Budget Allocation Model (20 minutes)

Now plan your $150,000 budget allocation for next quarter.

Allocation Framework

Use this framework to guide your decisions:

Proven Performers (60-70% of budget):

Channels with demonstrated ROI and room to scale

Optimization Plays (20-30% of budget):

Channels with potential but need improvement

Experimental (10% of budget):

New channels or tactics to test

Your Budget Plan

Channel	Last Q Spend	Last Q CAC	Proposed Spend	% of Budget	Expected Leads	Expected MQL Rate	Expected CAC	Rationale
Paid Search	$25,000	$6,250
LinkedIn	$30,000	$7,500
Email/Nurture	$5,000	$2,500
Display	$15,000	$15,000
Events/Webinars	$20,000	$6,667
Content Synd	$5,000	N/A
NEW: Podcast Ads	$0	N/A
NEW: CTV	$0	N/A
TOTAL	$100,000		$150,000	100%

Projection Calculations

For each channel, project expected outcomes:

Example for Paid Search:

• Proposed Spend: $40,000 (60% increase)
• Expected CPL: $105 (assuming 5% efficiency loss due to scale)
• Expected Leads: 381
• Expected MQL Rate: 26% (same as last Q)
• Expected MQLs: 99
• Expected Deals: 6 (using same 20% opp rate, 30% close)
• Expected CAC: $6,667

Complete these projections for all channels in your plan.

Part 3: Scenario Planning (10 minutes)

Create three budget scenarios and compare outcomes:

Scenario A: Conservative (Scale Winners)

Allocate 90% to top 3 performing channels from last quarter.

Expected Outcomes:

• Total Deals: _
• Blended CAC: $_
• Total MQLs: _

Pros/Cons:

• Pro: _
• Con: _

Scenario B: Balanced (Your Plan Above)

Your proposed allocation from Part 2.

Expected Outcomes:

• Total Deals: _
• Blended CAC: $_
• Total MQLs: _

Scenario C: Aggressive (Bet on New Channels)

Allocate 30% of budget to experimental channels.

Expected Outcomes:

• Total Deals: _
• Blended CAC: $_
• Total MQLs: _

Pros/Cons:

• Pro: _
• Con: _

Scenario Comparison Table

Metric	Scenario A (Conservative)	Scenario B (Balanced)	Scenario C (Aggressive)	Recommendation
Total Budget	$150,000	$150,000	$150,000
Expected Deals
Blended CAC
Total MQLs
Risk Level	Low	Medium	High
Learning Value	Low	Medium	High

Which scenario would you recommend and why?

---

Deliverable Format

Create a presentation with:

1. Slide 1: Historical Performance
   • Last quarter's results by channel
   • Key insights and learnings
2. Slide 2: Budget Allocation Recommendation
   • Your proposed channel mix (table with spend and %)
   • Rationale for key decisions
3. Slide 3: Expected Outcomes
   • Projected leads, MQLs, deals by channel
   • Overall goals vs. last quarter
4. Slide 4: Scenario Analysis
   • Comparison of 3 scenarios
   • Recommendation with risk assessment
5. Slide 5: Monthly Review Plan
   • How you'll monitor performance
   • Trigger points for reallocation

Discussion Questions

1. Diminishing Returns: How did you account for the fact that doubling spend on a channel might not double results? What efficiency assumptions did you make?
2. New Channels: How much budget did you allocate to experimental channels? How did you estimate their performance without historical data?
3. Quality vs. Volume: Some channels deliver more leads at higher CPL but better quality. How did you balance quantity vs. quality in your allocation?
4. Time Horizon: Some channels (like content SEO) take time to ramp. How does campaign duration affect your channel mix?
5. Flexibility: If LinkedIn's CPL increased 50% mid-quarter, what would be your reallocation plan?

Advanced Challenge

Build a Dynamic Model in Spreadsheet

Create an Excel/Google Sheets model with:

1. Input sheet: Where you can adjust spend by channel
2. Calculation engine: Automatically calculates leads, MQLs, deals, CAC
3. Assumptions: Variables for CPL, MQL rate, opp rate, close rate by channel
4. Goal seek: Formula to show required spend to hit deal target
5. Scenario manager: Toggle between conservative/balanced/aggressive

Bonus: Add conditional formatting to highlight when CAC exceeds LTV/3 threshold.

Real-World Application

After completing the exercise, apply these principles to your actual marketing budget:

1. Audit: Pull last quarter's performance by channel
2. Calculate: Compute CPL, MQL rate, CAC for each
3. Optimize: Identify which channels to scale, optimize, or cut
4. Plan: Create your own budget allocation model
5. Review: Set up monthly reviews to rebalance based on performance