Spreadsheets

A daily collection and expense sheet for a small clinic in Excel

An Excel daily collection and expense register for a small clinic: consultations, procedures, cash versus UPI, costs and a monthly roll-up, with no patient data.

Editorial illustration for A daily collection and expense sheet for a small clinic in Excel

A small clinic can keep its accounts in Excel with one row per day for collections and a second sheet for expenses. The daily row holds counts and amounts by cash and UPI, a check column makes sure the totals agree, and a monthly roll-up uses SUMIFS. Keep patient names and diagnoses out of it entirely. You only need numbers.

What goes in the daily collection row?

ColumnHeadingExample (sample)
ADate4-Nov-2026
BConsultations (count)24
CConsultation collection₹12,000
DProcedures (count)3
EProcedure collection₹4,500
FTotal collection=C2+E2
GCash₹7,500
HUPI and card₹9,000
ICheck=F2-(G2+H2)

The check column should read zero every day. If it shows a figure, the cash and UPI split does not match the total, and you have found an error while it is still fresh. The example row uses a made-up average fee of ₹500 per consultation; use your own.

Why separate cash from UPI?

Cash is counted in the drawer, UPI is checked against the bank or app. Keeping them in separate columns lets you match the cash column to what you deposit or hold, and the UPI column to the credits on your bank statement. Differences appear in one place, not both.

What expenses should you log?

On a second sheet, keep one row per expense with Date, Category, Description and Amount. Typical categories for a clinic are consumables, lab fees, rent, staff salary, electricity and internet, cleaning, repairs, and your own professional fees and subscriptions.

  • Consumables: gloves, syringes, dressing material, disposables
  • Fixed monthly: rent, staff salary, internet, software
  • Occasional: equipment servicing, repairs, signage

Rent and salary can be entered once on the day they are paid. The monthly roll-up will place them in the right month.

How do you build the monthly roll-up?

On a Monthly tab, put the first day of each month in column A, starting with 1-Apr-2026. Then:

  • Total collection: =SUMIFS(Daily!$F$2:$F$400, Daily!$A$2:$A$400, ">="&A2, Daily!$A$2:$A$400, "<"&EDATE(A2,1))
  • Total expenses: =SUMIFS(Expenses!$D$2:$D$400, Expenses!$A$2:$A$400, ">="&A2, Expenses!$A$2:$A$400, "<"&EDATE(A2,1))
  • Surplus: =B2-C2

If collection is ₹3,20,000 in a month and expenses are ₹2,10,000, the surplus is ₹1,10,000. That is a sample, not a benchmark. Whether the surplus is the doctor's pay or reinvestment is your call, and how it is treated for tax is a question for your CA.

How do you spot the leaks?

Add a column for consumables as a share of collection: =consumables / total collection for the month. If it creeps up for three months in a row, check purchase quantities, wastage and expiry. For days with a low consultation count but unchanged costs, compare against the weekday: some clinics are quieter on specific days, and a weekday column from =TEXT(A2,"ddd") shows the pattern.

What to do at the end of each month

  1. Check that the check column totals zero for the month.
  2. Compare the UPI and card total with the credits on your bank statement.
  3. Enter any pending expenses you paid in cash.
  4. Save a dated copy of the file, for example Clinic-2026-10, before moving on.

How do you handle a day the clinic is closed?

Either leave the row out or enter zeros. Leaving it out keeps the sheet shorter, while entering zeros makes a missing day easy to spot because every date from the first to the last has a row. If you choose zeros, add a column called Open with Yes or No and count the Yes entries to see how many working days each month had. Then collection per working day, not per calendar day, is the fair figure to compare.

The clinic's card and bank statements can also be imported into SpendixAI for categorisation, which is a useful second look at where the expense money went.

SPENDIXAI JOURNAL

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

Explore your reports