A free telecom commission reconciliation spreadsheet template

Here is a commission reconciliation template. No email address, no form, no thank-you page.

Download the template (CSV)

It opens in Excel, Google Sheets, Numbers, or anything else that reads CSV. It ships with ten example rows so you can see the shape before you delete them and put your own book in. The rest of this post explains what each column is for, which formulas to paste in, and how to run it monthly.

Why a CSV and not a spreadsheet file

CSV cannot carry formulas, so the four calculated columns arrive empty and you paste the formulas in once. That is a deliberate tradeoff: a CSV opens correctly in every tool, whereas an XLSX built in Excel tends to arrive in Google Sheets with something subtly broken.

The formulas are below. It is about a minute of setup.

The columns

The template has sixteen columns in three groups.

What you sold. Client, Site, Supplier, Service ID, Service Type, Contract Start, Contract Term (months). This is your service inventory. Service ID is the important one: it should be the supplier’s identifier for the circuit or service, because that is what their statement will reference.

What you expect. Client Monthly Billing, Commission Rate (%), Expected Commission. Billing is what the supplier bills your client each month. The rate comes from your agreement for that supplier and product. Expected is calculated.

What happened. Statement Month, Paid Commission, Variance, Variance %, Status, Note. Paid comes off the statement. The two variance columns are calculated. Status and Note are how you track the chase.

The four formulas

Assuming your data starts in row 2 and the columns are in the order shipped:

Expected Commission (column J):

=ROUND(H2*(I2/100),2)

Variance (column M): positive means you were underpaid.

=IF(L2="","",ROUND(J2-L2,2))

Variance % (column N):

=IF(OR(L2="",J2=0),"",ROUND((J2-L2)/J2*100,1))

Status (column O): replace the shipped “Open” with this once you are entering paid figures.

=IF(L2="","Not paid",IF(ABS(M2)<0.01,"Match",IF(M2>0,"Underpaid","Overpaid")))

Select each cell and drag down. Then sort by Variance descending and work from the top.

How to run it monthly

Before the statements arrive, update the inventory rows. Add services that went live, remove disconnects, change billing amounts where the client’s spend changed, and update rates where a contract renewed. This is the step that decides whether the whole exercise works.

When the statement arrives, copy your inventory block to a new tab named for the month, set Statement Month, and fill Paid Commission per line from the statement.

Then sort by Variance and triage from the largest gap down. Three buckets: explained, timing, and real. Only the third needs a supplier query.

Record the chase in the Note column. Dispute reference, date raised, what they said. This is the column people leave blank, and it is the one that turns finding a variance into recovering money.

Keep the month tab. Do not overwrite last month. The archive is what lets you answer “what did we expect in March” after a true-up rewrites the statement in July. That question comes up more than you would think, and true-ups are a normal part of how telecom commissions work, not an anomaly.

What this template will not do

Worth saying plainly, because a template that oversells itself wastes your time.

It will not parse statements. You are typing or pasting the paid figures. With a handful of suppliers this is twenty minutes. With twenty suppliers it is your afternoon, every month.

It will not match identifiers for you. When the supplier’s reference does not match yours, you resolve it by hand, every month, forever.

It will not catch a service missing from your own inventory. If a service was never entered, the template has no expectation for it and no variance appears. Its accuracy is bounded by how well you maintain the inventory block.

It will not track a dispute past the note. No reminders, no ageing, no escalation.

It does not handle spiffs or clawbacks natively. Add them as separate rows with a clear Service Type, or they will distort your variance column.

Those limits are the honest boundary of a spreadsheet. They are also, in order, the reasons agencies eventually stop using one.

When to stop using this

The threshold is not agency size, it is any of these:

• More than one person needs to run the process
• Suppliers arrive in enough different formats that parsing dominates the work
• You have found variances but cannot say which were recovered
• Somebody asks what you expected in a past month and you cannot answer

We build commission management software that removes those limits, so weigh that recommendation accordingly. But use the spreadsheet first. It costs nothing, it will tell you your real variance rate, and if it turns out your book is clean you have saved yourself a purchase.

The template is genuinely free and genuinely ungated. Take it, fork it, put your own name on it. If it finds you money, that is the point.