Day 28: Sales & Marketing Analytics in Excel

=Actual/Target-1 gives the growth/decline percentage. =Actual/Target gives the achievement ratio (1.0 = 100% achievement). For a sales dashboard: create a column for Actual, Target, Achievement % (=Actual/Target formatted as percentage), and a Status column using IF: =IF(B2/C2>=1,"✅ On Track",IF(B2/C2>=0.85,"⚠️ At Risk","❌ Below Target")). Use conditional formatting on the Status column — green/yellow/red — to make it scannable at a glance.

Simple CLV: =AverageOrderValue * PurchaseFrequency * CustomerLifespan. More precise CLV using margin: =(AverageOrderValue * GrossMargin * PurchaseFrequency) / ChurnRate. Example: ₹2,000 average order, 40% gross margin, 4 purchases/year, 25% annual churn: =(2000*0.4*4)/0.25 = ₹12,800 CLV. In Excel, if you have transaction data: calculate AverageOrderValue and PurchaseFrequency using AVERAGEIF and COUNTIF per customer, then apply the formula.

CAC = Total Marketing Spend / New Customers Acquired in the same period. =SUM(MarketingSpend) / COUNTIF(CustomerType,"New"). ROAS = Revenue from Ad Campaign / Ad Spend. =CampaignRevenue / CampaignSpend. Higher ROAS = better return. A ROAS of 3× means ₹3 revenue for every ₹1 spent. Track both monthly by channel using a Pivot Table with Channel in Rows, and calculating CAC and ROAS as calculated fields or separate formula columns.

A funnel tracks conversion at each stage of a process — e.g. website visits → leads → qualified leads → proposals → closed deals. Structure: Stage column, Count column, Conversion Rate column (= current / previous stage). Visualise with a Funnel chart (Insert → Chart → Funnel in Excel 2016+). Alternatively, use a bar chart sorted from largest to smallest with the stages on the Y axis. Key analysis: which stage has the biggest drop-off? That is where to focus optimisation effort.

Step 1: sort customers by revenue descending. Step 2: add a cumulative revenue column using a running sum: =SUM($B$2:B2) where B is the Revenue column. Step 3: calculate cumulative revenue as % of total: =C2/SUM($B$2:$B$100). Step 4: find where the cumulative % crosses 80% — those are your top customers. Step 5: create a combination chart with bars for individual revenue and a line for cumulative %. This is the classic Pareto chart. In Excel 2016+, Insert → Chart → Histogram → Pareto creates it automatically.