Ravindra BagaleCourses & study guides मराठी Track your progress

Excel for Accountants and Finance Teams: GST, P&L, Balance Sheet and Budgets (With Templates)

By Last updated:

This guide is for people in the accountants profession who use Excel every day. You will see every common file, the formulas people actually type, and downloadable templates with made-up Indian ₹ data. Related short lessons are linked — we do not re-teach them here.

What you'll learn

  • Which roles use Excel in accountants
  • Every common Excel file type for this profession
  • Sample tables with realistic ₹ examples (made-up names)
  • Formulas: GST on taxable, Running balance, Variance ₹, Variance % and more
  • Ready templates you can download and reuse

Downloads: accountants-gst-pnl-budget.xlsx (GST sales/purchase + P&L + balance sheet + budget vs actual + debtors ageing) · accountants-cash-bank-recon.xlsx (Cash book + bank book + bank reconciliation)

Example data only

All names, amounts, rates and dates are made up for practice. They are not real client or employer records. Follow your organisation's own rules and software for live work.

In short

One profession → many Excel files. Each file is one clear list. Yellow cells are inputs; green cells are formulas. Start with the Roles table, then open the matching template.

Why

Some topics already have a focused lesson. Use those for depth; use this guide for the full profession picture.

What

Chapter What it covers
22. KPI in All Industries Finance and cost KPIs.
Home Budget (Daily Life) Simple P&L style for home — same idea as business.
Excel Dashboards gallery Ready finance-style dashboards.

How

  1. Skim the link if you need that one skill.
  2. Come back here for the full file list and templates.

1. Who uses Excel

Why

Books start as lists. Excel is where vouchers become reports.

What

Role Typical Excel work
Junior accountant Voucher entry, cash book, GST invoice register.
Accounts executive Bank recon, vendor bills, debtor ageing.
Finance analyst Budget vs actual, variance, MIS pack.
CA / audit support Trial balance extract, lead schedules, sample checks.

How

  1. Open the Roles table.
  2. Match your job to one file.
  3. Download the matching template.

2. Every common Excel file

Why

If you only learn one dashboard, you miss the daily registers that feed it.

What

File What one row / sheet holds
GST sales / purchase register Invoice no, party, taxable value, CGST, SGST, IGST, invoice date.
Cash / bank book Date, narration, debit, credit, running balance.
Bank reconciliation Book vs statement; unmatched lines; difference.
Profit & Loss (monthly) Income and expense heads; gross and net profit.
Balance sheet (snapshot) Assets, liabilities, equity; check Assets = Liabilities + Equity.
Budget vs actual Budget, actual, variance ₹ and % by head.
Debtor / creditor ageing 0–30, 31–60, 61–90, 90+ buckets.
Fixed asset register Asset, cost, dep %, WDV, location.

How

  1. Pick the file that matches your role.
  2. Download the template that contains that sheet.
  3. Replace yellow sample rows with your practice data (still made-up if you share screenshots).

3. Formulas people use

Why

Typed numbers go stale. Formulas update when inputs change.

What

Name Formula What it does
GST on taxable =taxable*rate CGST/SGST or IGST amount.
Running balance =prev+debit-credit Cash/bank book balance.
Variance ₹ =actual-budget Over or under budget.
Variance % =variance/budget Percent over/under.
Ageing bucket =IF(days<=30,"0-30",IF(days<=60,"31-60",IF(days<=90,"61-90","90+"))) Debtors days past invoice.
Depreciation (SLM simple) =cost*rate Simple teaching dep for the year.
SUMIF by head =SUMIF(head_range,head,amount_range) Total one expense head.
Balance check =assets-(liab+equity) Must be 0 on a clean sheet.

How

  1. Open the Formulas used sheet inside each template.
  2. Change only yellow cells.
  3. Watch green cells recalculate.

3. GST invoice register

Why

Without a clean register, GSTR filing and ITC checks become slow.

What

One sales sheet and one purchase sheet with tax columns.

How

  1. Download accountants-gst-pnl-budget.xlsx › GST sales.
  2. Yellow = party, taxable, rate. Green = tax amounts.
  3. Filter IGST rows for inter-state invoices.
  4. Read the totals row: Taxable and tax sums.
P&L snapshot — Gross and Net are formulas

Figure. P&L snapshot — Gross and Net are formulas

4. P&L and balance sheet

Why

Owners ask: did we make profit, and what do we own vs owe?

What

A monthly P&L and a simple balance sheet with a balance check cell.

How

  1. Open sheet P&L. Income minus expenses = Net profit (green).
  2. Open Balance sheet. Assets must equal Liabilities + Equity.
  3. If Balance check is not 0, find the mistyped yellow cell.

5. Budget vs actual and ageing

Why

Budget without actuals is a wish list. Ageing shows who is late to pay.

What

Budget vs actual with variance, plus debtor ageing buckets.

How

  1. Open Budget vs actual. Change one Actual (yellow); Variance updates.
  2. Open Debtors ageing. Days late drives the bucket formula.
  3. Sort 90+ first for follow-up calls.

6. Sheet previews

Why

See the layout before you open Excel.

What

Screenshots of the main sheets in the downloadable templates.

How

Scroll the figures, then download the matching .xlsx.

GST sales — yellow inputs, green tax formulas

Figure. GST sales — yellow inputs, green tax formulas

P&L snapshot — Gross and Net are formulas

Figure. P&L snapshot — Gross and Net are formulas

7. Common mistakes

Mistake What goes wrong Fix
Typing tax as a fixed number Rate change breaks every row. Tax = Taxable × Rate in green cells.
Mixing CGST+SGST with IGST on one invoice Wrong GSTR picture. Use IGST only for inter-state; else CGST+SGST.
Deleting formula rows on P&L Net profit goes blank. Clear yellow inputs only.
Using real client PAN in practice files Privacy risk. These templates use made-up names only.

8. FAQ

Are GST rates here official?

Rates in the file are teaching examples. Confirm live rates from GST portal / your CA.

Can LibreOffice open these?

Yes — checked with 0 formula errors.

Is this a full tally replacement?

No. It teaches the Excel files accountants still keep beside Tally/ERP.

9. Try at home

Try at home

  1. On GST sales, change Taxable for INV1001; confirm CGST and SGST change.
  2. On P&L, what is Net profit? (green total)
  3. On Debtors, how many invoices are in bucket 90+?

Recap

In short

  1. 4 roles use Excel in this profession.
  2. 8 common file types are listed above.
  3. 2 downloadable templates with yellow inputs and green formulas.
  4. Rates and rules here are teaching examples only.
  5. Use Related chapters for focused skills.

Quick revision cards

Quick revision

1 interview card, 1 comparison card and 1 revision card. Save on your phone or download as PNG. Numbers are made up for practice.

Interview card: How do you get CGST and SGST on a taxable invoice in Excel?
Card 1 of 3: Interview card – How do you get CGST and SGST on a taxable invoice in Excel? · Download PNG
Comparison card: P&L vs Balance sheet
Card 2 of 3: Comparison card – P&L vs Balance sheet · Download PNG
Revision card: Budget vs actual: what is variance?
Card 3 of 3: Revision card – Budget vs actual: what is variance? · Download PNG