Tax and GST

An input credit register template with a status column

An input tax credit register in Excel or Sheets: columns for supplier, GSTIN, bill, tax amount, tag and status, with a filled month. No eligibility claims.

Editorial illustration for An input credit register template with a status column

An input credit register is a plain sheet with one row per purchase bill. Ten columns do the job: date, supplier, GSTIN, bill number, taxable amount, tax amount, purchase tag, status, who acts next and notes. The status column holds one of three words, Checked, Pending or Ask CA, and it is the part that makes the sheet useful. The register records facts; it never says whether any credit is allowed.

What columns should the register have?

ColumnWhat goes in it
A DateBill date
B SupplierName as printed on the bill
C GSTINSupplier's GSTIN, or Unregistered
D Bill no.As printed
E Taxable valueAmount before tax
F Tax amountTax shown on the bill
G TagStock, Expense or Equipment
H StatusChecked, Pending or Ask CA
I Next actionWho must do what
J NotesAnything unusual

What does a filled month look like?

Here are four sample rows with invented figures.

DateSupplierBill no.TaxableTaxTagStatus
02 OctSharma PackagingSP-218₹20,000₹3,600StockPending
05 OctCity ElectricalsCE-771₹5,000₹900ExpenseChecked
11 OctRao Machine WorksRM-40₹40,000₹7,200EquipmentAsk CA
18 OctLocal carpenterNo bill number₹6,500₹0ExpensePending

The tax figures are only illustrations of how to fill the columns and are not any real rate.

How do you total the month?

Assume the data sits in rows 2 to 500, with Tax amount in column F and Status in column H. These formulas work in Excel and in Google Sheets.

  • Total tax recorded for the month: =SUM($F$2:$F$500)
  • Tax on rows marked Checked: =SUMIFS($F$2:$F$500, $H$2:$H$500, "Checked")
  • Tax on rows marked Pending: =SUMIFS($F$2:$F$500, $H$2:$H$500, "Pending")
  • Count of rows marked Ask CA: =COUNTIF($H$2:$H$500, "Ask CA")

For the sample, SUMIFS for Checked gives ₹900, Pending gives ₹3,600 plus ₹0, which is ₹3,600, and the count of Ask CA is 1. The Ask CA row carries ₹7,200 of tax that is still unanswered.

How do you restrict the status column?

Use data validation so the cell accepts only the three words. In Excel, choose Data then Data Validation, set Allow to List and type Checked,Pending,Ask CA. In Google Sheets, use Data then Data validation and choose Dropdown. This avoids typos that break your COUNTIF.

What is the status for?

  • Checked: you have the bill, the supplier's details look right and it is saved.
  • Pending: something is missing and you know who must act.
  • Ask CA: you are unsure, and the question is written in Notes.

When does a sheet stop being enough?

A sheet is fine for dozens of bills a month. When purchase entries pile up and you are typing from bank statements, SpendixAI can import the statement, categorise rows, and carry GST rate and ITC-eligible settings on a category so your reports show an estimate of GST inside the spending, to be confirmed with invoices and a CA. You can also download the data as a CSV.

How do you add a bill age to the register?

Add an eleventh column, K, called Age in days, and use =IF($H2="Pending", TODAY()-$A2, "") in row 2, then fill down. It shows how long each pending bill has been sitting, counted from the bill date, and stays blank for Checked rows. Sort by it every week and the oldest pending items rise to the top. In Google Sheets the same formula works unchanged.

Habits that keep the register honest

  1. Add a row the day the bill arrives, not at month end.
  2. Never delete a row; mark it instead.
  3. Keep the bill number the same as on the paper.
  4. Review Pending rows every week.
  5. Hand the register to the CA with the bills.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports