Ravindra BagaleCourses & study guides Track your progress

Excel Interview Prep — Accountant / Finance

By · Last updated:

Friends, accounts interview: GST register logic, EMI, ageing, recon, and control totals. Numbers must tie.

Fictional sample data for learning. Industry flavour: Trading company accounts (Surat / Leicester) ₹ / £. Brands and people are made up for practice.

Why → What → How

Why: Excel interviews are not memory tests of every function name. They check if you can clean a small sheet, write a correct formula, and explain the result in plain English.

What: This page has 28 real interview-style questions for Accountant / Finance. Each one has a short answer, vertical steps, a tiny example, a common wrong answer, and a follow-up question.

How — study plan:

  1. Skim all question titles once (10 minutes).
  2. Download the practice workbook and open sheet About this data.
  3. Answer aloud, then type the formula in the file.
  4. Check the yellow cells on sheet Answers only after you try.
  5. Revise with the three cards and take the quiz.

Download practice file: practice-accountant-interview.xlsx (includes About this data, practice sheets, Answers).

Ravindra Bagale's Tip

Friends, never force the GST portal to match by editing the total — find the missing invoice.

Ravindra Bagale's Tip

Yellow cells = inputs, locked formulas = trust. Auditors notice this habit.

Questions (28)

Q1 · Theory

How do you structure a simple GST sales register in Excel?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Columns: Date, Invoice, Party, Taxable, CGST, SGST, IGST, Total. One row per invoice line. Keep rates as numbers.

How — tiny example (vertical steps):

  1. Yellow inputs.
  2. Formulas for tax = Taxable*Rate.
  3. Separate CGST/SGST vs IGST.

Formula: =Taxable*0.09
Result: CGST
What it means: GST line.

Common wrong answer: Typing tax amounts by hand every time.

Q2 · Formula

PMT for an EMI — explain arguments.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): PMT(rate, nper, pv) returns the payment. Divide annual rate by 12 for monthly.

How — tiny example (vertical steps):

  1. Loan 500000, 10% yearly, 36 months.
  2. =PMT(10%/12,36,-500000).

Formula: =PMT(10%/12,36,-500000)
Result: EMI
What it means: Monthly instalment.

Common wrong answer: Using 10% without /12 for monthly EMI.

Q3 · Formula

Ageing buckets with nested IF or LOOKUP.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Bucket overdue days into 0-30, 31-60, 61-90, 90+ using nested IF or approximate LOOKUP on a slab table.

How — tiny example (vertical steps):

  1. Days = TODAY()-Due.
  2. =IF(days<=30,"0-30",IF(days<=60,"31-60",IF(days<=90,"61-90","90+"))).

Formula: =IF(E2<=30,"0-30",IF(E2<=60,"31-60",IF(E2<=90,"61-90","90+")))
Result: 31-60
What it means: Receivable ageing.

Common wrong answer: Hard-coding the bucket text without using days.

Q4 · Theory

Bank vs book reconciliation approach in Excel?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Two lists: bank statement and cash book. MATCH/XLOOKUP unmatched rows; SUMIF totals; tick cleared items.

How — tiny example (vertical steps):

  1. XLOOKUP each bank row in book.
  2. Filter #N/A as outstanding.
  3. Explain difference.

Formula: =XLOOKUP(A2,Book[Ref],Book[Amt],"Unmatched")
Result: recon
What it means: Find breaks.

Common wrong answer: Deleting bank rows until totals match.

Q5 · Formula

Budget vs actual variance %.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Variance = Actual-Budget. % = Actual/Budget-1 or Variance/Budget. Watch divide by zero.

How — tiny example (vertical steps):

  1. =IF(Budget=0,"n/a",Actual/Budget-1).

Formula: =C2/B2-1
Result: 8%
What it means: Over budget signal.

Common wrong answer: Showing only ₹ variance without %.

Q6 · Theory

Depreciation schedule — straight line in Excel?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Annual dep = (Cost-Salvage)/Life. Rows for each year with opening, dep, closing.

How — tiny example (vertical steps):

  1. Year 1 closing = Cost-Dep.
  2. Copy pattern for N years.

Formula: =(Cost-Salvage)/Life
Result: yearly dep
What it means: SLM schedule.

Common wrong answer: Depreciating below salvage by mistake.

Q7 · Formula

SUMIF for vendor-wise purchase total.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): SUMIF on vendor name to get purchase register totals for TDS or MIS.

How — tiny example (vertical steps):

  1. =SUMIF(Vendor,G2,Amount).

Formula: =SUMIF(A:A,G2,C:C)
Result: vendor total
What it means: Purchase MIS.

Common wrong answer: Filtering and copying visible totals only without SUBTOTAL.

Q8 · Theory

What is a trial balance sheet layout?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): List of ledger balances with Debit and Credit columns; totals must match.

How — tiny example (vertical steps):

  1. Account | Dr | Cr.
  2. =SUM(Dr) equals =SUM(Cr).

Formula: =SUM(B:B)
Result: TB totals
What it means: Books balance check.

Common wrong answer: Netting Dr/Cr into one column without labels.

Q9 · Formula

XIRR vs IRR — interview line?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): IRR assumes equal periods. XIRR uses real dates — better for irregular cash flows.

How — tiny example (vertical steps):

  1. Cashflows in A, dates in B.
  2. =XIRR(A2:A10,B2:B10).

Formula: =XIRR(A2:A10,B2:B10)
Result: rate
What it means: Project return.

Common wrong answer: Using IRR on dated irregular investments.

Q10 · Theory

Month-close MIS pack — what do you include?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): P&L vs budget, cash, ageing, GST summary, commentary. Freeze numbers; date-stamp file.

How — tiny example (vertical steps):

  1. Refresh.
  2. Pivot summaries.
  3. PDF for CFO.

Formula: (MIS pack)
Result: month close
What it means: Finance interview story.

Common wrong answer: Sending live linked files that break offline.

Q11 · Practical

Build ageing on practice invoices sheet.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Compute days overdue, bucket, Pivot by bucket and customer.

How — tiny example (vertical steps):

  1. Days formula.
  2. Bucket IF.
  3. Pivot.

Formula: =TODAY()-Due
Result: ageing
What it means: AR focus list.

Common wrong answer: Using invoice date instead of due date.

Q12 · Formula

CGST+SGST vs IGST formula switch.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): IF customer state = company state then split CGST/SGST else IGST full rate.

How — tiny example (vertical steps):

  1. =IF(State=Home, Taxable*0.09, 0) for CGST.

Formula: =IF(D2=$H$1,B2*0.09,0)
Result: CGST
What it means: Place of supply logic.

Common wrong answer: Charging both IGST and CGST on same line.

Q13 · Theory

TDS register essentials?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Section, vendor, PAN, base amount, rate, TDS, due date, paid flag. SUMIF by section for payment challan.

How — tiny example (vertical steps):

  1. Track due dates.
  2. Reconcile to books.

Formula: =SUMIF(Section,"194C",TDS)
Result: section total
What it means: Compliance MIS.

Common wrong answer: Keeping TDS only in email folders.

Q14 · Formula

Running balance for cash book.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Balance = previous balance + receipt - payment. First row special; then copy down.

How — tiny example (vertical steps):

  1. D2 = Opening+C2-B2.
  2. D3 = D2+C3-B3.

Formula: =D2+C3-B3
Result: running bal
What it means: Cash book.

Common wrong answer: Using SUM expanding range incorrectly.

Q15 · Scenario

Auditor asks how you know Pivot matches ledger.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Show source Table, filters, and a control total SUMIFS that equals Pivot grand total.

How — tiny example (vertical steps):

  1. Control cell.
  2. Compare to Pivot.
  3. Screenshot both.

Formula: =SUMIFS(...)
Result: control
What it means: Audit defence.

Common wrong answer: Saying "Excel calculated it" with no control.

Q16 · Theory

P&L vertical format in Excel?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Income block, COGS, Gross Profit, Expenses, Net Profit — formulas between sections, not typed nets.

How — tiny example (vertical steps):

  1. GP = Income-COGS.
  2. NP = GP-Expenses.

Formula: =B10-B20
Result: Gross Profit
What it means: Statement logic.

Common wrong answer: Typing Net Profit as a number.

Q17 · Formula

FV of SIP — rough Excel approach.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): FV(rate, nper, pmt) with negative pmt for money out. Monthly rate = annual/12.

How — tiny example (vertical steps):

  1. =FV(1%/12,60,-5000).

Formula: =FV(1%/12,60,-5000)
Result: corpus
What it means: SIP future value.

Common wrong answer: Using annual rate with monthly pmt without converting.

Q18 · Practical

Reconcile two amount columns with a helper Match column.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): XLOOKUP or COUNTIF to mark Matched/Unmatched; filter Unmatched.

How — tiny example (vertical steps):

  1. =IF(COUNTIF(Bank,A2),"Matched","Open").

Formula: =IF(COUNTIF(Bank[Amt],A2),"Matched","Open")
Result: status
What it means: Recon helper.

Common wrong answer: Sorting both sheets differently and eyeballing only.

Q19 · Theory

Currency: store numbers, format as ₹.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Keep values numeric; use cell format ₹. Never store "₹1,200" as text.

How — tiny example (vertical steps):

  1. Format Cells → Currency.
  2. SUM works on numbers.

Formula: ₹ format
Result: numeric
What it means: Clean finance data.

Common wrong answer: Apostrophe before amounts.

Q20 · Formula

YEARFRAC or DATEDIF for interest days?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): DATEDIF or simple end-start for days; YEARFRAC for fractional years in some interest methods.

How — tiny example (vertical steps):

  1. =B2-A2 days.
  2. Know bank day-count basis.

Formula: =B2-A2
Result: days
What it means: Interest base.

Common wrong answer: Ignoring the day-count basis the policy needs.

Q21 · Scenario

GST portal total ≠ Excel register.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Check date filters, credit notes, amendments, cancelled invoices, and rate mistakes. Export portal CSV and MATCH.

How — tiny example (vertical steps):

  1. Filter month.
  2. Match invoice numbers.
  3. Explain gaps.

Formula: MATCH invoice
Result: gap list
What it means: Compliance debug.

Common wrong answer: Forcing Excel to equal portal by editing totals.

Q22 · Formula

Weighted average cost basic idea?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Total cost / total qty for the pool. SUMPRODUCT of qty*rate / SUM qty.

How — tiny example (vertical steps):

  1. =SUMPRODUCT(qty,rate)/SUM(qty).

Formula: =SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)
Result: WAC
What it means: Inventory costing.

Common wrong answer: Averaging rates without weighting by qty.

Q23 · Theory

Protect formula cells but allow journal inputs.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Unlock input range, lock formula cells, Protect Sheet. Yellow for inputs.

How — tiny example (vertical steps):

  1. Format → unlock inputs.
  2. Protect Sheet.

Formula: (Protect)
Result: safe TB
What it means: Controlled entry.

Common wrong answer: Protecting with all cells locked.

Q24 · Practical

Build budget vs actual for 3 expense heads.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Table with Budget, Actual, Variance, %. Conditional format red if adverse.

How — tiny example (vertical steps):

  1. Formulas for var and %.
  2. CF on %.

Formula: =C2-B2
Result: variance
What it means: Expense control.

Common wrong answer: Mixing income and expense sign logic.

Q25 · Theory

What is a chart of accounts sheet for?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Master list of account codes and names used in journals and P&L mapping — one source of truth.

How — tiny example (vertical steps):

  1. Code | Name | Type | P&L/BS.

Formula: (COA)
Result: master
What it means: Mapping control.

Common wrong answer: Typing account names free-text every journal.

Q26 · Formula

EOMONTH for month-end accruals.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): EOMONTH(date,0) month end of date; EOMONTH(date,1) next month end.

How — tiny example (vertical steps):

  1. =EOMONTH(A2,0).

Formula: =EOMONTH(A2,0)
Result: month end
What it means: Accrual dating.

Common wrong answer: Hard-coding 30 for every month.

Q27 · Scenario

Explain a ₹1 difference in TB.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Usually rounding or a missed paisa. Check ROUND on tax lines; find the odd entry with conditional formatting.

How — tiny example (vertical steps):

  1. Sort by amount.
  2. Search 0.01 diffs.

Formula: ROUND(tax,2)
Result: paisa
What it means: TB tidy.

Common wrong answer: Hiding the difference in suspense without finding it.

Q28 · Theory

Interview: Excel vs Tally/ERP — your line?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): ERP is system of record; Excel is analysis and working papers. Do not maintain parallel unofficial books.

How — tiny example (vertical steps):

  1. Export → analyse in Excel.
  2. Post final entries in ERP.

Formula: (ERP+Excel)
Result: right tool
What it means: Mature answer.

Common wrong answer: Keeping the only GST register in a personal laptop Excel.

Practice tasks (12)

  1. 1. GST line CGST/SGST formulas (medium)
    Hint: Lock the home-state cell with $.
  2. 2. PMT EMI for practice loan (easy)
    Hint: Rate/12 and negative PV.
  3. 3. Ageing buckets column (medium)
    Hint: Nested IF on days overdue.
  4. 4. Bank vs book unmatched list (hard)
    Hint: XLOOKUP → filter Missing.
  5. 5. Budget variance % with IF (easy)
    Hint: Guard divide by zero.
  6. 6. SLM depreciation 5-year table (medium)
    Hint: Opening − dep = closing.
  7. 7. Vendor SUMIF purchases (easy)
    Hint: Criteria is vendor cell.
  8. 8. XIRR on dated cashflows (medium)
    Hint: Same order for amounts and dates.
  9. 9. Cash book running balance (medium)
    Hint: Prior balance + in − out.
  10. 10. Control total vs Pivot (medium)
    Hint: SUMIFS must equal Pivot.
  11. 11. WAC with SUMPRODUCT (medium)
    Hint: Weight by qty.
  12. 12. Protect sheet unlock inputs (easy)
    Hint: Yellow cells unlocked first.

Quiz (8 questions)

1. Monthly EMI uses annual rate divided by 12 in PMT.

2. Ageing should use due date, not only invoice date.

3. XIRR needs real dates for irregular cash flows.

4. Store amounts as text with ₹ signs for accuracy.

5. CGST and IGST should both apply on the same intra-state line.

6. Control totals help auditors trust your Pivot.

7. EOMONTH helps month-end dating.

8. ERP should remain the system of record over personal Excel books.

Interview, comparison and revision cards

Standalone cards (no tip or photo strip). Each has a QR to this lesson. Download the PNG and revise offline.

PMT ageing
Interview card 1/3 — PMT + ageing
IRR vs XIRR
Comparison card 2/3 — IRR vs XIRR
Accounts controls
Revision card 3/3 — Accounts controls

Next: back to the Interview Prep hub · related lesson Formulas and Functions.