Call detail record analysis in Excel: usage and cost

Call data record analysis in Excel compares call volume, billed minutes and usage charges across teams and months. Start with aggregated business billing records, calculate each rate from summed inputs, and reconcile the result to the provider export. This workflow answers spending and usage questions for finance and telecom operations teams.

Define the business comparison first

Ask which team or destination category explains a change in usage charges. Write down the billing periods, included accounts, currency and charge components. A usage analysis should not quietly mix per-call charges with recurring subscriptions, taxes or credits.

The example uses a fictional US distributor's September and October 2026 business calling totals, all in USD. It compares Sales with Support, split into domestic and international categories. Those categories are reporting labels, not tariff promises.

Finance verifies the bill reconciliation, while the telecom operations owner checks provider definitions and investigates changes with the relevant team. An increase is a reason to inspect the records, rather than evidence of misuse or an incorrect bill.

Prepare a workbook without individual identifiers

Obtain the provider's billing export and field definitions. Confirm whether duration is in seconds or minutes, whether it reports actual or billed time, which call statuses incur charges, and how credits appear. Follow that export's definitions rather than assuming all providers report calls alike.

Prepare an aggregate table before sharing it. Use one row per month, team and destination category:

Keep actual phone numbers, individual names and detailed contact histories out of this comparison file. The business question needs team totals. Preserve the original export under your organization's normal access rules so the billing owner can resolve differences.

If the starting file has one row per call, build the monthly summary first. Assign each record to one team and one destination category, and use a consistent time zone for period boundaries. Check transfers or multiple call legs against the provider's billable-record definition before counting them separately.

The general Excel analysis workflow covers table preparation and control totals. To practice without company records, use the business analysis practice layout.

Work a complete usage comparison

Name the prepared table Usage. These are its complete example rows; no individual identifiers are needed.

Store month values as dates such as September 1, 2026, and format them for display. For a summary where A2 holds the month and B2 the team, use:

=SUMIFS(Usage[Calls],Usage[Month],$A2,Usage[Team],$B2)
=SUMIFS(Usage[BilledMinutes],Usage[Month],$A2,Usage[Team],$B2)
=SUMIFS(Usage[UsageChargeUSD],Usage[Month],$A2,Usage[Team],$B2)

Microsoft's SUMIFS documentation describes summing values that meet multiple criteria. Add Usage[Destination],$C2 when a summary row also specifies a destination category. For company monthly totals, use the month criterion alone.

The company comparison is:

Effective cost per minute is total usage charges divided by total billed minutes. For a summary with minutes in D2 and charges in E2:

=IF(D2=0,"",E2/D2)

October's calculation is 524 / 13,200 = $0.0397 per billed minute, rounded to four decimal places. September is 445 / 12,500 = $0.0356. Do not average category rates: their minute volumes differ.

Explain the increase from the same rows

Calls increased by 50, or 1.6%; billed minutes increased by 700, or 5.6%; usage charges increased by $79, or 17.8%. Percentage changes use September as the denominator and are rounded to one decimal place.

=(OctoberValue-SeptemberValue)/SeptemberValue

The charge bridge identifies the main contributor:

The proof is 445 + 10 + 75 - 6 + 0 = 524. Sales international contributes the largest increase. Its effective usage rate is unchanged: 150 / 1,000 and 225 / 1,500 both equal $0.15 per billed minute. More international minutes explain that category's increase; this table does not show a rate increase.

Reconcile each month's calls, minutes and usage charges to the provider export's corresponding controls. Then reconcile usage charges to the usage component of the bill. Keep recurring fees, taxes, credits and timing differences on a separate bridge to the invoice total. A zero difference proves completeness against that control; it does not prove every tariff was applied correctly.

Use the checked business report example to turn the result into an action with an owner. The Excel chart selection guide helps choose a display after reconciliation.

Where it goes wrong

• Seconds labeled as minutes. Confirm units before calculating a rate. Keep elapsed duration distinct from billed time.
• Unmatched team assignments. Preserve an unassigned category until the billing owner resolves it; filtering it out breaks completeness.
• Different charge scopes. Usage charges divided by minutes answer a different question from the full invoice divided by minutes.
• Errors hidden as zero. Microsoft documents IFERROR as replacing formula errors with a chosen value. Returning zero can make a broken lookup or denominator look like a real zero cost. Review the cause instead.
• Aggregate changes treated as individual behavior. Team totals describe spending patterns. They do not identify the reason for a particular person's call.

Covirage is not affiliated with Microsoft.

Compare your own usage summary in Covirage

Bring an Excel file with aggregated month, team, destination, call-count, billed-minute and usage-charge fields. Covirage maps the columns, its tools compute and check the business comparisons, and the external AI model explains the changes with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own export.

See Covirage for telecom teams for the industry context.