Travel and vehicles

Monthly conveyance claim sheet for field staff on fixed routes

A monthly conveyance claim sheet for field staff on fixed routes: columns, working Excel and Google Sheets formulas, a sample week and manager sign-off.

Editorial illustration for Monthly conveyance claim sheet for field staff on fixed routes

A monthly conveyance sheet for fixed routes needs one row per visit day, with date, route, mode, visit purpose, kilometres, amount and a manager sign-off column. Keep a small route table so the kilometres fill in by themselves, and a rate cell so the amounts follow from the kilometres. The rate and the rules come from your company, so enter your own.

Which columns should the sheet have?

  • A: Date.
  • B: Route, such as Route A.
  • C: Mode: bike, car, bus or auto.
  • D: Visit purpose, for example dealer visit or collection.
  • E: Kilometres, filled in from the route table.
  • F: Amount, calculated from the kilometres and the rate.
  • G: Manager sign-off, with initials and date.

Put the rate per kilometre in H1. The example uses ₹6, which is a placeholder for the sample, not a recommended rate. Put your route table in K2 to L4, with the route name in column K and the kilometres in column L.

What are the formulas?

  • Kilometres in E2: =IF(B2="","",VLOOKUP(B2,$K$2:$L$4,2,FALSE))
  • Amount in F2: =IF(E2="","",E2*$H$1)
  • Monthly total in F42: =SUM($F$2:$F$40)
  • Monthly kilometres in E42: =SUM($E$2:$E$40)
  • Kilometres for one route: =SUMIFS($E$2:$E$40,$B$2:$B$40,"Route A")

VLOOKUP with FALSE at the end looks for an exact match for the route name. Both functions work in Excel and Google Sheets. If a day's trip was not on a fixed route, type the actual kilometres into column E by hand, and put the reason in the visit purpose.

What does a sample week look like?

Say the route table has Route A at 38 km, Route B at 52 km and Route C at 24 km, with the rate at ₹6 per km. These numbers are invented.

DateRouteModePurposeKmAmountSign-off
3 NovRoute ABikeDealer visits38₹228Initials and date
4 NovRoute BBikeCollections52₹312Initials and date
5 NovRoute ABikeStock check38₹228Initials and date
6 NovRoute CBikeNew outlet visit24₹144Initials and date
7 NovRoute BBikeCollections52₹312Initials and date

Total kilometres are 38 + 52 + 38 + 24 + 52, which is 204. At ₹6, that is ₹1,224, which equals 228 + 312 + 228 + 144 + 312.

How do I handle the manager sign-off?

A sign-off column works best when it is filled weekly rather than at month-end. Send the manager the week's rows on Friday, and ask for initials and a date in column G. At month-end, nothing has to be reconstructed, and queries are caught while the visits are fresh.

What if routes change?

Change the route table, not the old rows. If Route B becomes 55 km from the first of next month, make a new sheet for that month, or add a new route called Route B2, so past months keep the figures that were true at the time.

Leave days, holidays and half days still deserve a row. Write Leave or Holiday in the purpose column and leave the route blank, so the month shows every day accounted for. Protect the formula cells in columns E and F, or keep them in a different colour, so a stray keystroke does not overwrite them.

When a sheet stops being enough

If your conveyance is partly paid by UPI or fuel cards, SpendixAI can bring the statement lines in by account, so you can see what you actually spent against what you claim. Your sheet remains the claim document.

Month-end checklist

  • Every working day has a row, or a reason for the gap.
  • Kilometres come from the route table, or are marked as manual.
  • The total matches the sum of the amounts.
  • The manager has signed every week.
  • You have saved a copy for yourself.
SPENDIXAI JOURNAL

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

Explore your reports