Microsoft 365 license reconciliation for MSPs, step by step

License reconciliation, for an MSP, means checking that the Microsoft 365 licenses you pay for line up with the licenses you bill, client by client and product by product. It is not the same as a license audit inside a client's tenant (who is assigned which license, and who has left). That matters too, but it answers a different question. Reconciliation asks: for every seat on my distributor bill, is there a seat on a client invoice, and the other way round?

This guide covers the inputs, a spreadsheet layout, the matching rules that cause most of the work, and a routine that keeps it to a short monthly job.

The two inputs

What you pay for. The distributor's bill for the month, as a spreadsheet:

• Pax8 or Sherweb: the invoice or billing detail export from the partner portal.
• Microsoft Partner Center (direct-bill partners): the reconciliation file for new commerce purchases, which lists charges per customer and product.

You need, per row: the customer, the product, the quantity and the cost.

What you billed. Your invoice lines for the same month:

• QuickBooks Online: a sales report by customer (Sales by Customer Detail) with the product or service, quantity, sales price and amount columns.
• A PSA: an export of invoice lines (customer, item, quantity, price).

Check both exports against their own totals before you start. A reconciliation built on half a file finds problems that are not there.

A spreadsheet layout

Put each input on its own tab, then build a third tab that compares them.

The sums are SUMIFS over the distributor and invoice tabs, keyed on the paired names. The finding, with seats paid in D and seats billed in E:

=IF(AND(D2=0,E2>0),"Cancelled, still billed",IF(AND(D2>0,E2=0),"Never billed",IF(D2>E2,"Under-billed",IF(E2>D2,"Over-billed","Matches"))))

Add one more check for price: a line where the unit price is below the unit cost is billed below cost, even if the counts match.

The matching rules that take the time

The formulas are the easy part. Almost all the effort is in making the two files speak the same language, and doing it once so next month is faster.

Client names. The distributor has "HARBOR DENTAL GROUP LLC"; your invoices have "Harbor Dental". Keep a two-column pairing list and look names up through it. Mark your own company's licenses as internal so they are not reported as never billed.

Product names. The distributor bills "Microsoft 365 Business Premium" and your invoice says "M365 Premium - monthly" or "Managed User - Premium". Keep a second list that maps every invoice item to the license it represents.

Bundles. If one invoice item covers more than one license per user (say, Business Premium plus a backup product), record the seats per unit for each license it includes. Products that a bundle price covers and that you never bill separately should be excluded from the comparison, or they will show as never billed every month.

Prorations and credits. Mid-month additions and removals show up as partial charges and credits. Net them per customer and product before comparing. A product that nets to zero seats was cancelled.

Months. If you invoice in advance, the invoice that should match September's distributor bill may be dated October. Pick a rule and use it every time.

What to do with each finding

• Under-billed or never billed: add the seats to the agreement or the next invoice. Decide whether to back-bill earlier months; that depends on your contract and the client relationship.
• Cancelled, still billed: stop the line and decide on a credit. This is the finding that protects trust, so fix it first even though it costs you money.
• Over-billed: correct the quantity. Often a removed user whose license was released at the distributor but not on the agreement.
• Billed below cost: your cost changed and the price did not, or the item is mapped to the wrong license.
• Intentional differences: note them, so the same line does not need a second look next month.

Microsoft's new commerce rules affect what you can do about overpaying. Seats can be added at any time, but reductions are only possible within a short window after the seats were added or renewed (Microsoft's cancellation policy). A reconciliation that runs monthly catches an unused seat early enough to plan for the renewal date, even when it cannot be removed today.

A monthly routine

• Download the distributor detail when the bill posts, and check its total.
• Export the matching month of invoice lines, and check its total.
• Add any new clients and items to the pairing lists.
• Refresh the comparison tab and sort by monthly impact.
• Fix real differences before the next invoice run, and note the rest.
• Keep the finished sheet, so next month can show what came back.

When the spreadsheet gets heavy

The spreadsheet works, and its weak points are predictable: pairing lists that drift out of date, formulas someone overwrote, and a month that gets skipped because invoicing is late.

License Drift runs this reconciliation from the same two exports. It reads Pax8, Sherweb and Partner Center files and QuickBooks or PSA invoice exports, asks you to pair each new client and invoice item once, remembers the pairs, and emails the under-billed, over-billed and cancelled-but-still-billed lines with a monthly dollar estimate. You can mark each line fixed, accepted or ignored, and a reminder arrives on the day of the month you choose.