Retail banking revenue analysis by product and branch

Retail banking revenue analysis separates interest and fee movements by branch, product and customer segment. Use average balances for interest, the relevant account or transaction activity for fees, and consistent funding treatment. A reconciled comparison shows whether the movement came from business volume, product mix, effective rates or fee realization.

Define the branch and product boundary

Choose a stable branch assignment: account servicing branch, originating branch or a documented management allocation. Retain both the previous and current assignment when accounts move. A transfer between branches can change each branch's result without changing bank revenue.

Keep product and customer segment as separate fields. Branch totals can conceal a shift from lower-yield to higher-yield lending or from waived to billable service accounts. Compare the same reporting periods and currency before drawing an operating conclusion.

The FDIC Quarterly Banking Profile covers aggregate bank earnings, lending and deposit activity. It provides industry context; the branch explanation still needs your own account and posting records. Use commercial banking revenue analysis for business lending portfolios with a different relationship and product structure.

Prepare the exports and summarize them

Bring the interest-income ledger, average loan balances, deposit interest expense and service-fee postings. Include account identifier, reporting period, branch, product, customer segment, currency, average balance, recognized income and any allocation code. Fee detail needs the billable activity, signed fee amount, waiver and refund fields.

Use a consistent account population when deriving yields. If a product has interest income but no matching balance, investigate missing account mappings or accounting adjustments before calculating a rate. Record new products and discontinued products explicitly instead of dropping unmatched rows.

In Excel, suppose Detail has period in A, branch in B, product in C and income in D. With period in B1, branch in A2 and product in C1:

=SUMIFS(Detail!$D:$D,Detail!$A:$A,B$1,Detail!$B:$B,$A2,Detail!$C:$C,$C$1)

Microsoft documents SUMIFS for sums that meet multiple criteria. Apply the same filters to the balance population. Do not average individual yields to create a branch rate. Covirage is not affiliated with Microsoft.

Work the interest comparison

The following US bank example compares two quarters using an illustrative 0.25 accrual factor under a 30/360 convention. Both branches have the same included loan product scope. Actual reports must preserve the ledger's day-count convention and recognize a separate day effect when periods differ.

Interest income = Average loan balance × Annualized effective yield × Accrual factor

With prior balance in B, current balance in C, prior yield in D and current yield in E, calculate the balance and rate effects:

=(C2-B2)*D2*0.25
=C2*(E2-D2)*0.25

Dollar results are rounded to the nearest dollar after calculation. Total balances did not grow. Their allocation shifted toward North, which had the lower prior yield, producing a negative balance-mix effect. Improved effective yields more than offset that change. This calculation applies rate last, so the rate component includes the interaction with changing balances.

Separate fee activity from realized charges

For recurring account fees, use billable account-months. The next table uses average monthly billable accounts over three months and average realized monthly fees after included waivers and refunds. It excludes transaction fees, which need their own activity denominator.

Using count columns B and C and monthly fee columns D and E, calculate:

=B2*D2*3
=C2*E2*3
=(C2-B2)*D2*3
=C2*(E2-D2)*3

The last two formulas separate activity from realized fee changes.

Total billable accounts stayed constant, so the positive $600 activity effect comes from branch mix. The $7,500 realized fee effect could reflect charges, waivers or account mix. Inspect those rows before describing it as repricing.

Reconcile the income that management uses

Assume the included external funding expense rises from $400,000 to $450,000. Keep it explicit rather than implying the loan yield increase reached operating income unchanged.

The bridge proves the movement: −$12,500 + $72,500 + $600 + $7,500 − $50,000 = $18,100. That equals $1,334,100 − $1,316,000. Finance should tie each component to its ledger scope; treasury should explain funding expense and any separate internal funding credits.

Use a retail banking dashboard to monitor the reconciled components. Assess expenses through retail banking cost analysis before making a profitability claim.

Where it goes wrong

• Branch transfers treated as acquisition. Retain account identifiers and transfer dates.
• Deposit balances called income. Separate balances, external expense, fees and internal funding allocations.
• All open accounts treated as billable. Match the fee denominator to actual activity and waiver rules.
• Internal credits added to consolidated income. Eliminate internal allocations when reconciling to the bank total.
• Effective yields treated as pure pricing. Changes in account mix, nonaccrual status and interest reversals can move the result.

Do this on your own file in Covirage

Upload your branch income ledger, average-balance export and fee activity file with matched periods. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account in about a minute and bring a sample of your own data.

See revenue driver analysis for the supporting workflow from income movements to their contributing rows.