Excel Test for Marketing & Sales Operations

A marketing manager spent $4,800 on a LinkedIn Ad campaign which generated 120 qualified leads. What is the standard formula to calculate Cost Per Lead (CPL)?

In target cell D2, calculate the Click-Through Rate (CTR) by dividing Clicks (C2) by Impressions (B2).

A performance marketing analyst reports an ad spend of $10,000 and gross revenue generated of $45,000. How is Return on Ad Spend (ROAS) interpreted?

Which of the following metrics are standard quantitative KPIs used in digital marketing campaign reporting? (Select all that apply)

In target cell D2, calculate Cost Per Acquisition (CPA) by dividing Total Spend (B2) by Customers Acquired (C2).

You have a list of lead scores in range C2:C100. Which formula counts how many leads scored 80 points or higher?

A sales operations team defines a 'Qualified Lead' as having an Engagement Score >= 70 AND an Annual Revenue >= 500000. Which Excel formulas correctly evaluate whether Lead row 2 meets both conditions? (Select all that apply)

In target cell D2, calculate the ROAS ratio by dividing Revenue (C2) by Ad Spend (B2).

A SaaS marketing director calculates an LTV:CAC ratio of 4.2:1. What does this benchmark indicate for business health?

Which Excel functions allow a Sales Ops analyst to aggregate revenue or count pipeline opportunities filtered by BOTH Sales Rep AND Deal Stage? (Select all that apply)

In target cell C2, evaluate Lead Score in B2. If B2 >= 80 return "Hot", otherwise if B2 >= 50 return "Warm", otherwise return "Cold".

When calculating tiered sales commission rates from a bracket table where reps earn rates based on hitting minimum revenue thresholds, which lookup configuration is required?

When preparing lead lists exported from trade shows for import into HubSpot or Salesforce, which Excel functions help clean messy contact data? (Select all that apply)

In target cell D6, calculate the total revenue for deals in "Closed Won" stage from range B2:C5 using SUMIF.

A Sales Ops manager wants to sum deals where Rep is "Sarah" AND Stage is "Closed Won". Which formula syntax is correct?

Which expenditures should be included in the numerator when calculating a company's total Customer Acquisition Cost (CAC)? (Select all that apply)

In target cell D2, count how many deals belong to Rep "Marcus" (A2:A6) with Stage "Negotiation" (B2:B6) using COUNTIFS.

When matching CRM Contact IDs against a webinar attendee list, unmatched IDs produce a #N/A error. How can you display "Not Found" instead?

Sales Velocity measures how quickly deals move through the pipeline to generate revenue. Which variables are part of the standard Sales Velocity formula? (Select all that apply)

In target cell E2, look up the commission rate for Rep Sales in B2 from tier table A6:B9 using VLOOKUP with approximate match.

A sales representative closed $165,000 against a quarterly quota of $150,000. Which formula calculates their quota attainment percentage?

In a standard B2B Demand Generation pipeline, which milestone stages occur sequentially between visitor and customer? (Select all that apply)

In target cell E2, calculate the total pipeline value for Rep "Jordan" where Stage is "Proposal" using SUMIFS.

A subscription software company started the month with 800 customers, gained 60 new customers, and lost 24 customers. How is monthly Customer Churn Rate calculated?

Scenario Background: The CMO requested an end-of-month performance audit of paid acquisition channels. Leadership needs to know total revenue generated by high-performing channels that achieved a ROAS of at least 3.0x.

In target cell D7, calculate total revenue from range C2:C5 for channels with a ROAS (D2:D5) of 3.0 or greater using SUMIF.

Scenario Background: The VP of Sales needs total quarterly commission payout. Reps receive a base commission percentage on closed deals: 10% if sales >= $50,000, otherwise 5%.

In target cell D2, calculate the commission payout for sales rep Alex by multiplying Closed Sales (B2) by the appropriate rate using an IF function: if B2>=50000 then 10% (0.1), else 5% (0.05).

Which marketing attribution model assigns 100% of conversion credit to the very first touchpoint a buyer interacted with?

Scenario Background: Marketing generates inbound leads and scores them based on company size and intent. Sales Ops needs an automated qualification formula in target cell D2 to route high-value leads.

In target cell D2, assign "Priority Sales" if Lead Score (B2) >= 80 AND Employee Count (C2) >= 100, otherwise return "Nurture Queue" using IF and AND.

Scenario Background: During a board meeting review, the executive team is evaluating unit economics across customer cohorts. The VP of Growth needs the LTV to CAC ratio computed for the enterprise cohort.

In target cell D2, calculate the LTV:CAC ratio by dividing Customer Lifetime Value (B2) by Customer Acquisition Cost (C2).

Scenario Background: At quarterly close, Sales Ops reconciles opportunity conversion. In the executive summary KPI cell D6, calculate the Win Rate percentage by dividing Closed Won deals by Total Opportunities.

In target cell D6, calculate the Win Rate by counting "Closed Won" deals in range B2:B5 using COUNTIF, divided by the total deal count of 4 (or COUNTA(B2:B5)).