22.6 Banking

By Ravindra Bagale Last updated: 6 October 2026

Branch interviews ask CASA (current and savings accounts as a share of deposits) and NPA (non-performing loans as a share of loans). This is a fictional class bank. It is not a result of any bank that operates in Maharashtra.

KPI	What the number means	Who uses it	Action it drives
CASA ratio	CASA balances divided by total deposits.	Branch head	A low ratio means costly deposits. Do not fix it by opening empty accounts.
NPA %	NPA loan amount divided by the loan book.	Credit manager	A rising NPA stops the next disbursement until those accounts are called.
New accounts	Count of accounts opened. Often a vanity metric on its own.	Only with a balance rule	An account with no balance is not CASA.

Fictional sample rows. One month. Figures are in ₹ lakh so the table fits. 1 lakh = ₹1,00,000. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblBank (Table Design › Table Name).

Branch	City	CASA ₹ lakh	Deposits ₹ lakh	Loans ₹ lakh	NPA ₹ lakh	New accounts
Camp	Pune	40	50	30	1.5	80
Kothrud	Pune	20	40	25	3.0	120
CBS	Nashik	35	45	20	0.4	40
Gangapur	Nashik	15	30	18	2.5	90
Kolhapur	Kolhapur	28	36	22	0.8	50
Nagpur	Nagpur	22	40	28	4.0	110

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI	Excel formula	Result	Number format
CASA ratio	`=SUM(tblBank[CASA ₹ lakh])/SUM(tblBank[Deposits ₹ lakh])`	66.4%	`0.0%`
NPA %	`=SUM(tblBank[NPA ₹ lakh])/SUM(tblBank[Loans ₹ lakh])`	8.5%	`0.0%`

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column	Formula in the first row
CASA ratio	`=[@[CASA ₹ lakh]]/[@[Deposits ₹ lakh]]`
NPA %	`=[@[NPA ₹ lakh]]/[@[Loans ₹ lakh]]`

Expected result per row:

Branch	CASA ratio	NPA %
Camp	80.0%	5.0%
Kothrud	50.0%	12.0%
CBS	77.8%	2.0%
Gangapur	50.0%	13.9%
Kolhapur	77.8%	3.6%
Nagpur	55.0%	14.3%

One city with SUMIFS. CASA ratio for Pune only:

=SUMIFS(tblBank[CASA ₹ lakh],tblBank[City],"Pune")/SUMIFS(tblBank[Deposits ₹ lakh],tblBank[City],"Pune")

Result for Pune: 66.7%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

	Good KPI	Vanity metric
From the same rows	Kothrud NPA 12.0% and CASA 50.0%	Kothrud 120 new accounts
Why	The loan book is the risk.	The account-opening drive won the week and worsened nothing visible on that card. CBS opened 40 and has a 2.0% NPA.

What the manager does. The Pune regional manager (fictional class bank) stops the Kothrud account drive and pulls the ₹3 lakh NPA list for a call this week. The Nashik CBS manager does not get a target of 120 accounts to "match Kothrud". Gangapur's NPA is 2.5 ÷ 18 = 13.9%. That branch is the Nashik problem, not CBS.

Practice workbook and guide: Banking dashboard workbook (Excel) · How to build the Banking dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

NPA % here is amount divided by amount: NPA in ₹ lakh divided by loans in ₹ lakh. A count of bad accounts is a different metric; keep it in a separate column.