Equity Research Banking Coverage Financial Statement Analysis and Excel Modeling

Excel Modeling: The Real Skills You Need to Crack Banking Interviews

Let’s be honest: in the world of banking, Excel modeling isn't just a "nice-to-have" bonus on your resume. It is the job. Whether you are aiming for a seat in equity research, investment banking, or credit analysis, the interview process is going to rigorously test your ability to not just talk about finance, but to actually build financial models that work.

This guide breaks down 15 practical Excel and financial statement questions that you are likely to face in the real world. We aren't just talking theory here. You will learn how to build three statement models from scratch, ensuring depreciation flows correctly into the balance sheet. We’ll cover how to calculate operating cash flow using the indirect method and how to build dynamic comp tables using INDEX MATCH so your data doesn’t break every time you add a column.

We go beyond the basics to look at how major players like JPMorgan Chase and Goldman Sachs actually operate. You will see how to use Goal Seek to reverse-engineer loan growth, how to construct sensitivity tables to analyze ROE under pressure, and even how to record simple VBA macros to automate boring data extraction.

From foundational concepts like how working capital impacts free cash flow to advanced headaches like circular references, this is your playbook for the technical side of the interview.

Tips and Tricks to Ace the Technical Round

1. Practice Building Models from Scratch

Don't just read a textbook on three statement models open a blank Excel sheet and build one. You need to understand the mechanics of how the Income Statement, Balance Sheet, and Cash Flow Statement actually talk to each other. Make sure your depreciation links correctly to PP&E. In a timed test, you might only have 30 minutes. Speed is just as important as accuracy.

2. Master the "Big Four" Functions

You don't need to know every function in Excel, but you need to own these four: SUMIF, INDEX MATCH, OFFSET, and IF statements. Forget VLOOKUP; professionals use INDEX MATCH because it’s robust and doesn't break when columns shift. Learn to use OFFSET for dynamic ranges and SUMIF for quick quarterly calculations.

3. Understand Banking-Specific Metrics

Banks are not like regular companies. While a retailer cares about EBITDA, a bank analyst lives and dies by Net Interest Margin (NIM). You need to know that an Efficiency Ratio below 60% is good, and you need to understand capital adequacy metrics like CET1 and Tier 1 ratios. Also, remember that for banks, Price-to-Book is often a better valuation metric than P/E.

4. Ditch the Mouse

If you are reaching for the mouse during an Excel test, you are losing time. Learn the keyboard shortcuts. Master Alt + E + S + V for Paste Values, F4 for absolute references, and Alt + = for auto-sum. If you need to build a sensitivity table, knowing Alt + D + T helps you set it up instantly. Speed signals competence.

5. Stress Test Your Own Work

A good modeler tries to break their own model before the interviewer does. If you build a calculator for loan growth, what happens if growth is 0%? What if it’s negative? Does your efficiency ratio formula hold up if expenses are higher than revenue? Testing these edge cases helps you catch errors early.

6. Handle Circular References Like a Pro

Bank models are famous for circular references interest expense changes debt, debt changes cash, and cash changes interest. It’s a loop. You must enable iterative calculations in Excel options. Set your iterations to 100. Explaining why this happens shows you understand the math behind the model.

7. Build Reusable Templates

Smart analysts don't reinvent the wheel. Create a template for a banking ratios calculator that spits out NIM, Loan-to-Deposit, and ROTCE. Add conditional formatting to flag bad numbers automatically. When an interviewer asks for a quick analysis, pasting data into your pre-made tool shows you think systematically.

8. Narrate Your Process

Getting the right answer is only half the battle. You need to articulate the why. When you are calculating cash flow, explain out loud: "I'm adding back the increase in deposits because for a bank, that is a liability that provides cash." Interviewers want to hear your financial intuition, not just see you type numbers.

9. Use Data Tables for "What-If" Analysis

Scenario analysis is a daily task in banking. Use Excel's Data Table feature to show how ROE shifts if interest rates go up or down. Use the Scenario Manager to compare "Base Case" vs. "Bear Case." Being able to set these up in under five minutes is a massive advantage.

10. Learn Just Enough VBA

You don't need to be a coder, but basic VBA helps. Knowing how to record a macro to format a messy sheet or loop through quarterly tabs to extract data saves hours of manual work. It demonstrates you care about automation and efficiency.

11. Format for the Human Eye

A model that works but looks messy will get you rejected. Use standard conventions: blue font for hardcoded inputs, black font for formulas. Use proper number formatting and clear headers. Clean formatting shows attention to detail a trait every VP looks for.

12. Simplicity vs. Complexity

Know when to be fancy and when to be simple. INDEX MATCH is great, but sometimes a simple logic check is better. Interviewers want to see that you understand the trade-off between a complex solution and a robust one.

13. Practice Under the Gun

The pressure changes everything. You might be given 20 minutes to fix a broken spreadsheet. Practice with a timer running. It reveals whether you actually know the formulas or if you just memorized a tutorial. Start with 30-minute drills and try to get down to 15.

14. Prepare for the "Fix It" Question

Sometimes, you will be handed a model that is already broken. Common traps include circular references without iterations enabled, unbalanced balance sheets, or hardcoded numbers hiding inside formulas. Develop a systematic process for debugging these errors quickly.

15. Read Real 10-Ks

Don't just stay in Excel. Download the 10-K filings for banks like Citi or Bank of America. Look at how they present their segment reporting and capital ratios. referencing real-world data points during your interview proves you’ve done your homework.

Financial Statement Analysis and Excel Modeling

1. Build a three statement model where the income statement drives the balance sheet and cash flow statement. How do you link depreciation from the income statement to fixed assets on the balance sheet?

Suggested Answer:

To link depreciation correctly, I treat it as a single value that flows through all three statements to keep the model balanced.

Here is the breakdown of the flow:

1. Income Statement: I record Depreciation as an expense. If Depreciation is $100, it reduces Pre-Tax Income by $100. Assuming a 20% tax rate, Net Income drops by $80.

2. Cash Flow Statement: I start with that reduced Net Income. Since Depreciation is a non-cash expense, I add the full $100 back in the Cash Flow from Operations section.

3. Balance Sheet: I link the $100 expense to the Property, Plant, & Equipment (PP&E) schedule. The formula for the closing balance is:

   • Ending PP&E = Beginning PP&E + CapEx - Depreciation

   • So, the asset value on the Balance Sheet decreases by the exact $100 used on the Income Statement.

Pro Tip for Candidates:

Be explicit about the "Tax Shield." Explain that while Depreciation is a non-cash expense, it reduces taxable income, which actually saves the company real cash in the current period.

2. A bank reports Net Income of $450 million with $80 million depreciation, $120 million increase in loans, and $200 million increase in deposits. Calculate cash from operations using the indirect method.

Suggested Answer:

I use the indirect method, which starts with Net Income and adjusts for non-cash items and working capital changes.

Here is my step-by-step calculation:

• Step 1 (Start): Net Income = +$450 million.

• Step 2 (Non-Cash Adjustments): Add back Depreciation because it was subtracted to find Net Income but didn't use cash.

  • Calculation: $450 + $80 = $530 million.

• Step 3 (Asset Changes): Loans increased by $120 million. An increase in an asset is a "use" of cash (we lent money out).

  • Calculation: $530 - $120 = $410 million.

• Step 4 (Liability Changes): Deposits increased by $200 million. An increase in a liability is a "source" of cash (we received money).

  • Calculation: $410 + $200 = $610 million.

Final Answer: Operating Cash Flow is $610 million.

Pro Tip for Candidates: Memorize the rule: Asset Up = Cash Down. Liability Up = Cash Up. This prevents you from getting the signs wrong under pressure.

3. In Excel, create dynamic formulas using INDEX MATCH to pull P/E ratios for JPMorgan, Goldman Sachs, and Citi from a comparable company dataset.

Suggested Answer:

I use INDEX MATCH instead of VLOOKUP because it is more robust it does not break if columns are added to the source data later.

Here is the formula structure I would use:

=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))

Detailed Breakdown:

• Return_Column: The column containing the P/E ratios (e.g., Column D).

• Lookup_Value: The specific bank name I am searching for (e.g., "JPMorgan" in cell A2).

• Lookup_Column: The column containing the list of bank names (e.g., Column A).

• 0: Specifies an exact match.

Calculation Example:

If I change the bank name in cell A2 from "JPMorgan" to "Citi," the MATCH function updates the row number, and INDEX pulls the new P/E ratio instantly without me changing the formula.

Pro Tip for Candidates: Mention that you lock the ranges (using F4 to add dollar signs, e.g., $A$1:$A$100) so you can drag the formula down for all companies without the references shifting.

4. How would you use SUMIF and conditional formatting in Excel to flag all quarters where a bank's efficiency ratio exceeded 65% over a 10 year period?

Suggested Answer:

First, I use SUMIF to quantify the issue, then Conditional Formatting to visualize it.

Calculation Logic:

Assume I have 40 quarters of data in Column B.

Formula: =COUNTIF(B2:B41, ">0.65")

• If the result is 10, I know that 25% of the time (10 out of 40 quarters), the bank was inefficient.

Visual Setup:

1. Highlight the Efficiency Ratio column (B2:B41).

2. Go to Conditional Formatting > Highlight Cell Rules > Greater Than.

3. Input 0.65 (or 65%).

4. Select "Red Fill with Dark Red Text."

This turns the spreadsheet into a heat map where I can instantly point out periods of stress to a client.

Pro Tip for Candidates:When analyzing efficiency ratios, mention that for banks, "lower is better." A ratio of 55% is excellent, while anything over 65% typically signals cost management issues.

5. Walk through building a sensitivity table in Excel showing how ROE changes with NIM ranging from 2.0% to 3.5% and asset turnover from 0.05 to 0.08.

Suggested Answer:

I use a Data Table to perform this "What-If" analysis. This allows me to see the ROE output for 20+ different scenarios at once without rewriting formulas.

Detailed Breakdown:

1. Base Formula: In the top-left corner of my table range, I link to the main ROE calculation:

   • ROE = Net Income / Total Equity

2. Row Inputs: I list the Net Interest Margin (NIM) variance across the top row: 2.0%, 2.5%, 3.0%, 3.5%.

3. Column Inputs: I list the Asset Turnover variance down the left column: 0.05, 0.06, 0.07, 0.08.

4. Execution: I highlight the table, select "Data Table," and link the Row Input Cell to the actual NIM percentage in my model and the Column Input Cell to the Asset Turnover variable.

Example Output:

Excel calculates that at 2.0% NIM and 0.05 Turnover, ROE might be 8%. At 3.5% NIM and 0.08 Turnover, ROE jumps to 22%.

Pro Tip for Candidates:

Always verify the corners of your table. If the top-left result looks reasonable but the bottom-right looks impossible, check if you swapped your Row and Column inputs.

6. A regional bank shows accounts receivable increased 25%, inventory unchanged, and accounts payable decreased 15%. Explain the working capital impact on free cash flow.

Suggested Answer:

Both of these changes negatively impact Free Cash Flow (FCF).

Calculation Breakdown:

Let's assume the starting balances were:

• Accounts Receivable (AR): $100 million

• Accounts Payable (AP): $100 million

Change 1: AR Increased by 25% ($25m)

• New Balance: $125 million.

• Impact: An increase in a current asset is a Use of Cash. The bank recorded revenue but hasn't received the cash yet.

• FCF Effect: -$25 million.

Change 2: AP Decreased by 15% ($15m)

• New Balance: $85 million.

• Impact: A decrease in a current liability is a Use of Cash. The bank paid its vendors faster than usual.

• FCF Effect: -$15 million.

Total Impact:

Total Cash Drain = -$25m (AR) - $15m (AP) = -$40 million.

This reduces the Free Cash Flow by $40 million compared to a scenario with no working capital changes.

Pro Tip for Candidates:

Point out that banks usually have low inventory, so the "Inventory unchanged" part of the question is a distractor. The focus should remain on receivables and payables.

7. Create an Excel template that automatically calculates all key banking ratios (CET1, Tier 1, loan to deposit, NIM, efficiency ratio) when you input financial statement data.

Suggested Answer:

I structure the template with a distinct Input block and Output block.

Calculation Formulas:

1. CET1 Ratio: Common Equity Tier 1 Capital / Risk-Weighted Assets

   • Example: $12B / $100B = 12.0%

2. Tier 1 Ratio: Tier 1 Capital / Risk-Weighted Assets

   • Example: ($12B CET1 + $2B Preferreds) / $100B = 14.0%

3. Loan-to-Deposit (LDR): Total Loans / Total Deposits

   • Example: $80B / $90B = 88.9%

4. Net Interest Margin (NIM): (Interest Income - Interest Expense) / Average Earning Assets

   • Example: ($5B - $2B) / $100B = 3.0%

5. Efficiency Ratio: Non-Interest Expense / Total Revenue

   • Example: $3B / ($3B NII + $2B Fees) = 60.0%

Pro Tip for Candidates:

I add an "Alert" column next to the ratios using simple IF statements (e.g., =IF(CET1<0.10, "CHECK CAPITAL", "OK")) to instantly flag regulatory breaches.

8. How do you build circular references in Excel for modeling interest expense that depends on debt balance which itself depends on the cash flow statement?

Suggested Answer:

I build the model to calculate interest based on the average debt balance, knowing this creates a circular loop.

The Calculation Loop:

1. Interest Expense: Calculated as (Beginning Debt + Ending Debt) / 2 * Interest Rate.

2. Net Income: Interest Expense reduces Net Income.

3. Cash Flow: Lower Net Income means less Cash Flow Available to Pay Debt.

4. Ending Debt: Less cash flow means higher Ending Debt.

5. Loop: Higher Ending Debt increases the Interest Expense in step 1.

To make this work, I go to Excel Options > Formulas and enable Iterative Calculations. I set max iterations to 100 and maximum change to 0.001. This allows Excel to recalculate the loop 100 times until the numbers converge and stabilize.

Pro Tip for Candidates:

Mention that you always build a "Circuit Breaker" toggle. This is a cell that, when switched to "OFF", sets interest to zero to break the circle if the model errors out.

9. Using Goal Seek in Excel, determine what loan growth rate is needed for a bank to achieve 15% ROE given current NIM of 2.8% and efficiency ratio of 62%?

Suggested Answer:

I use Goal Seek to reverse-engineer the required growth rate.

Scenario Setup:

• Current ROE: 12% (based on 5% loan growth).

• Target ROE: 15%.

• Constraint: NIM stays at 2.8% and Efficiency at 62%.

Goal Seek Inputs:

1. Set Cell: The cell containing the final ROE formula.

2. To Value: 0.15 (15%).

3. By Changing Cell: The hardcoded "Loan Growth %" input cell.

Result Breakdown:

Excel iterates through different growth rates. It might find that to get the extra earnings needed to hit 15% ROE, the bank needs to grow its loan book by 18.5% instead of 5%. This helps management decide if the target is realistic.

Pro Tip for Candidates:

While Goal Seek gives a precise answer, I would warn management that growing loans at 18.5% might increase default risk, so the "math" answer isn't always the "strategic" answer.

10. Build a waterfall chart in Excel showing the bridge from net income to tangible book value per share for a bank over 5 years?

Suggested Answer:

A waterfall chart visualizes how capital is built up and paid out.

Calculation Breakdown (Per Share):

• Start (Year 0 TBVPS): $20.00

• Plus: Cumulative Net Income (5 Years): +$15.00

• Minus: Cumulative Dividends: -$5.00

• Minus: Share Buybacks: -$2.00

• Plus/Minus: OCI Adjustments: -$1.00

• End (Year 5 TBVPS): $27.00

Charting Logic:

In the waterfall chart, the $20.00 and $27.00 are full columns (Totals). The Income, Dividends, and Buybacks are floating segments that bridge the gap. This clearly shows that while the bank earned $15.00 per share, it returned $7.00 to shareholders, resulting in $7.00 of net book value creation ($27 - $20).

Pro Tip for Candidates:

This chart is essential for "Capital Allocation" discussions, showing whether a bank is hoarding capital or returning it to investors.

11. A bank's balance sheet must always balance. If retained earnings increased by $300 million and dividends paid were $100 million, walk through how this flows to shareholders equity?

Suggested Answer:

I explain this using the fundamental accounting equation: Assets = Liabilities + Equity.

Calculation Breakdown:

1. Derive Net Income: Retained Earnings grows by Net Income minus Dividends.

   • Formula: Change in RE ($300m) = Net Income - Dividends ($100m).

   • Therefore: Net Income = $400 million.

2. Asset Side Impact:

   • The bank earned $400m in cash/assets (Net Income).

   • The bank paid out $100m in cash (Dividends).

   • Net Asset Change: +$300 million.

3. Equity Side Impact:

   • Retained Earnings increased by $300 million.

   • Net Equity Change: +$300 million.

Conclusion: Since Assets increased by $300m and Equity increased by $300m, the balance sheet remains perfectly balanced.

Pro Tip for Candidates:

Always double-check the dividend flow. Many candidates forget that paying a dividend reduces Cash (Assets), not just Equity.

12. Create macros in Excel using VBA to automate the extraction of quarterly NIM, loan growth, and ROE from 20 quarterly sheets?

Suggested Answer:

I use a simple VBA loop to extract data from standardized sheets (named Q1, Q2, etc.) into a Summary tab.

Logic Breakdown:

1. Initialize: Create a variable i to count rows on the Summary sheet, starting at Row 2.

2. Loop: For Each Worksheet in Workbook

3. Extraction Command:

   • Summary.Cells(i, 1) = Worksheet.Name (Gets "Q1")

   • Summary.Cells(i, 2) = Worksheet.Range("B10") (Gets NIM, e.g., 3.1%)

   • Summary.Cells(i, 3) = Worksheet.Range("B15") (Gets ROE, e.g., 12%)

4. Increment: i = i + 1 (Move to the next row for the next quarter).

5. End Loop.

Result:

This script runs in under 1 second and populates a 20-row table perfectly, avoiding the manual errors of copying and pasting 60 separate data points.

Pro Tip for Candidates:

State that you focus on "recording" the macro first to get the syntax, then editing the code to add the loop. It shows you are practical, not just a coder.

13. How would you use PivotTables in Excel to analyze which business segment (retail, corporate, investment banking) contributes most to revenue growth at a universal bank?

Suggested Answer:

I use a PivotTable to compare year-over-year changes across segments.

Setup and Calculation:

1. Raw Data: Columns for Year (2023, 2024), Segment, and Revenue.

2. Pivot Rows: Segment (Retail, Corporate, IB).

3. Pivot Columns: Year.

4. Pivot Values: Sum of Revenue.

Growth Calculation:

I add the Revenue field a second time to the "Values" area. I right-click this second column and select "Show Values As" > "% Difference From" > Base Item: Previous Year.

Example Result:

• Retail: $100m to $105m = +5%

• Corporate: $200m to $200m = 0%

• IB: $50m to $75m = +50%

This highlights that while Retail is the largest segment, IB is the growth engine.

Pro Tip for Candidates:

PivotTables are great, but emphasize that they are static. If the underlying data changes, you must hit "Refresh," or the analysis will be outdated.

14. Build a scenario manager in Excel showing best case, base case, and worst case outcomes for a bank's net interest income under different rate environments?

Suggested Answer:

I use Scenario Manager to toggle between different interest rate assumptions to stress-test Net Interest Income (NII).

Calculation Logic (NII = Interest Income - Interest Expense):

• Base Case: Rates at 5%.

  • Income ($500m) - Expense ($200m) = $300m NII.

• Best Case: Rates Rise to 6% (Assets reprice faster than liabilities).

  • Income ($600m) - Expense ($220m) = $380m NII.

• Worst Case: Rates Drop to 4% (Assets yield less, liability costs sticky).

  • Income ($400m) - Expense ($180m) = $220m NII.

Execution:

I input these three sets of assumptions into Scenario Manager. I then hit "Summary," and Excel generates a new tab showing the NII of $300m, $380m, and $220m side-by-side.

Pro Tip for Candidates:

This demonstrates "Risk Awareness." Interviewers want to see that you understand the bank's profitability is highly sensitive to external interest rate changes.

15. Using OFFSET function combined with SUM, create a dynamic rolling 4 quarter average calculator for a bank's quarterly earnings in Excel?

Suggested Answer:

I use OFFSET to create a "moving window" that always captures the most recent 4 quarters.

Formula Breakdown:

=AVERAGE(OFFSET(Reference_Cell, 0, 0, -4, 1))

Detailed Explanation:

• Reference_Cell: The cell containing the most recent quarter's earnings (e.g., Q4 2024).

• 0, 0: Stay in the current row and column.

• -4: Look at a height of 4 rows upwards (capturing Q4, Q3, Q2, Q1).

• 1: Width of 1 column.

Calculation:

If the last 4 quarters of earnings were $1.0B, $1.2B, $1.1B, and $1.3B, the function creates a range of these four numbers and averages them to $1.15B. As soon as I add Q1 2025, the formula (if built dynamically) shifts to include the new data and drop the oldest quarter.

Pro Tip for Candidates:

I usually advise using INDEX over OFFSET in large models because OFFSET is "volatile" it recalculates every time anything changes in the workbook, which can slow down large files.

Final Thoughts

At the end of the day, Excel modeling for banking is a mix of technical skill and financial logic. You need to know the formulas, but you also need to know why a specific MCLR cut impacts the bottom line.

The questions and tips in this guide cover the core skills: integration, dynamic formulas, sensitivity analysis, and automation. Work through them, break them, and build them again.

Remember, interviewers aren't looking for a robot. They want someone who can catch their own mistakes and explain complex concepts simply. If you can build a solid model and explain your logic clearly, you are ready for the interview.