Building a Dynamic Sales Funnel Analysis in Excel
Instruction: Outline the process for creating a sales funnel analysis tool in Excel that adapts to varying stages and conversion rates.
Context: This question assesses the candidate's ability to apply Excel in marketing analysis, specifically in visualizing and analyzing sales conversion processes.
Updated
Official answer available
Read the opening below, then unlock the full answer and practical guidance.
Define an entry cohort, observation window and ordered stage rules before calculating a funnel. Count unique prospects who reached each stage within that definition; do not mix this month's leads with this month's sales from older cohorts.
For a hypothetical completed cohort, 100 prospects entered, 60 reached qualification, 30 reached proposal and 12 purchased. Adjacent conversion rates are 60%, 50% and 40%; overall conversion is 12%...