How to Calculate Customer Acquisition Cost (CAC)
by Month in Excel

Customer Acquisition Cost (CAC) by Month in Excel

1. Add Month and Year Columns to the Leads Table

• In the Leads dataset, create a Month column using the TEXT() function on the lead timestamp/date.
  • Use the month format code for the full month name.
• Create a Year column using the same TEXT() function on the same date field.
  • Use the year format code for a 4-digit year.
• If those helper columns already exist, keep them and move on.

2. Add Month and Year Columns to the Monthly Cost Table

• In the costs dataset (monthly marketing and software costs), create Month and Year the same way as the Leads tab.
• This ensures both tables can be filtered consistently by month and year.

3. Create a Unique Month-Year List for the Summary Table

• Build a summary area that will roll everything up by month.
• Use UNIQUE() on the Month-Year fields to generate a list of unique month-year combinations.
• Copy and paste values (Paste Values) so the list becomes static for reporting.
• Add a Date column for charting by combining the month and year into a real date value (so Excel charts it correctly on the x-axis).

4. Set Up Summary Columns

Create columns in your summary table for:

• Lead Cost
• Marketing Cost
• Software Cost
• New Customers
• Customer Acquisition Cost (CAC)
• Target (optional)

5. Calculate Monthly Lead Cost with SUMIFS

• Use SUMIFS() to sum Lead Cost from the Leads table for the selected month and year.
• Criteria should match:
  • Month in the Leads table equals the summary Month cell
  • Year in the Leads table equals the summary Year cell
• Fill the formula down for all months.
• Format as currency.

6. Calculate Monthly Marketing Cost with SUMIFS

• Use SUMIFS() on the Cost table to sum Marketing Cost for the same month and year.
• Fill down.
• Format as currency.

7. Calculate Monthly Software Cost with SUMIFS

• Use SUMIFS() on the Cost table to sum Software Tools for the same month and year.
• Fill down.
• Format as currency.

8. Count New Customers with COUNTIFS

• Add a New Customers column in the summary.
• Use COUNTIFS() on the Leads table to count rows where:
  • Customer type (or new customer flag) indicates New
  • Month matches the summary month
  • Year matches the summary year
• Fill down.

9. Fix the Customer Count to Include Only Closed Sales

• If your first result is too high, it is likely counting new leads, not new customers.
• Update the COUNTIFS() criteria to include an additional condition:
  • Deal status is Closed (or Sale = Yes, depending on your column)
• Fill down again to correct the monthly customer counts.

10. Calculate Customer Acquisition Cost (CAC)

• In the CAC column, calculate:
  • (Lead Cost + Marketing Cost + Software Cost) ÷ New Customers
• Fill down for all months.
• Format as currency and remove decimals if you want a cleaner dashboard look.

11. Add a Target Line

• Enter a target value (example used: 300).
• Reference the target cell down the column so changing it once updates all rows.

12. Build the Combo Chart

• Select the Date column, then also select:
  • New Customers
  • CAC
• Insert a chart using Insert → Recommended Charts, then switch to Combo if needed.

13. Assign the Axes Correctly

• Convert CAC to a Line series (dollar values).
• Put New Customers on the Secondary Axis (right side), because it is a different scale than dollars.
• This prevents the customer bars/values from looking tiny compared to CAC.

14. Clean Up the Chart Formatting

• Rename the chart title to something like CAC by Month.
• Make the target line visually lighter and easier to read (thin line, dotted style).
• Add data labels only to the CAC line to avoid clutter.
• If labels are hard to see, apply a fill color to the label background for better contrast.